Dashboard

Databases

Import a database

Replace a database's contents with a dump file, with a backup taken first.

An import loads a dump file into a PostgreSQL database, for example when you move an existing app to HCode. It replaces everything in the database with what the file contains, and by default HCode backs up the current data first.

Imports are for PostgreSQL only. Redis databases have no Import button.

Files you can import

File What it is
.sql Plain SQL, such as the output of pg_dump with its default format
.sql.gz The same, gzipped
.dump An archive made with pg_dump --format=custom
.dump.gz The same, gzipped

A file can be up to 500 MB as uploaded. A gzipped file can be up to 10 GB once it is unpacked.

A .sql file is run with psql, and psql commands in it (lines starting with a backslash, such as \connect or \copy) are not allowed.

To make an archive from an existing database:

bash
pg_dump --format=custom --dbname "$SOURCE_DATABASE_URL" --file export.dump

HCode ignores ownership and permissions when it loads an archive. A .sql file is different: it stops at its first error, so a statement that names a user from your old server fails the import. Leave those out when you make the file:

bash
pg_dump --no-owner --no-privileges --dbname "$SOURCE_DATABASE_URL" --file export.sql

Run an import

  1. Go to Databases and click Import on the database's card.
  2. Under Upload file, choose your file or drag it onto the box.
  3. Leave Backup before importing on unless you have a reason not to.
  4. Turn on Test run only if you want to check the file first. See Test the file first.
  5. Click Start import (or Start dry-run for a test run).

The button shows the upload progress, then "Import running…". When the import finishes you get "Database import completed", or "Import failed:" followed by the reason. The table list reloads with the new data.

What happens to the database

The import is loaded into a fresh copy of the database first and checked there. Only when that works does HCode switch the database over to the new copy, so a file that fails halfway leaves your current data as it was.

The switch restarts the database once and drops the connections your app has open. If your app doesn't reconnect by itself, restart it after the import.

Afterwards the database holds exactly what the file contained. Tables that were there before and aren't in the file are gone.

The backup before an import

Backup before importing is on by default. HCode runs the database's backup schedule once before the import starts, and that backup appears with the database's other backups, so you can restore it if the import was a mistake.

This needs an active backup schedule for the database. Without one, the import is refused with "this database has no enabled backup policy, so no snapshot can be taken before the import replaces it". Add a schedule on the Backups page (see Scheduled backups), or turn the backup off.

To turn it off, switch Backup before importing off and type a short reason in the box that appears. The dashboard won't start the import without one: "Turning off the pre-import backup requires a short reason". With the backup off, there is no copy of the data the import replaces.

Test the file first

Test run only checks a file without changing any data:

  • For .sql and .sql.gz, HCode runs the file inside a transaction and rolls it back. The file can be up to 64 MB once unpacked, and it can't contain statements of its own that start or end a transaction: BEGIN, START TRANSACTION, COMMIT, END, ROLLBACK, ABORT or PREPARE TRANSACTION. The same words inside a function body, a DO block, a CASE ... END expression or a string are fine, and so is ROLLBACK TO a savepoint. A file with one fails with a message naming the line, for example "a dry run cannot check a file that manages its own transactions: line 12 starts a COMMIT statement. Remove the transaction statements from the file, or import it without a dry run".
  • For .dump and .dump.gz, HCode checks that the archive can be read. It doesn't load it.

A test run doesn't need a backup schedule, because nothing is replaced. When it passes you get "Dry-run validation completed".

Follow an import

The Recent imports list shows your last 10 imports with their format, options and status. Click Logs → on one to see its output under Import logs. When an import fails, its error is shown under its row as well.

Only one import can run on a database at a time. Starting a second is refused with "database import already running".

When an import is refused

  • "This database is being set up or moved. Wait for that to finish, then try again."
  • "supported formats are .sql, .sql.gz, .dump, .dump.gz": rename the file so it ends in one of these, or convert it.
  • "Upload exceeds 500MB limit" or "upload exceeds size limit": the file is over 500 MB. Gzip it and upload the .gz file instead.

Imports, test runs included, are also refused while your organisation is over its storage or bandwidth allowance. See Usage and limits.

Owners, admins and members can run imports. See Roles and permissions.