Dump and restore

Moving a database into Balta is pg_dump and pg_restore. Standard PostgreSQL tooling, standard commands, nothing Balta-specific except the connection string.

Read compatibility notes first. It lists what will not come across, and finding one of those halfway through a six-hour copy costs you the copy.

Take the dump #

Use the custom format. It is compressed, it restores in parallel, and it lets you restore selectively.

pg_dump \
  --format=custom \
  --no-owner \
  --no-privileges \
  --file=appdb.dump \
  "postgresql://[email protected]/appdb"

--no-owner and --no-privileges matter here. Your source database's ownership and grants refer to roles that do not exist on the target, and restoring them produces a wall of errors that hides the real ones. Create your roles on the target first, then apply grants afterwards.

Use a pg_dump at least as new as the target server. A newer pg_dump reading an older server is supported; an older pg_dump reading a newer server is not.

Create your roles on the target #

CREATE ROLE app_migrator LOGIN PASSWORD 'REDACTED-EXAMPLE-ONLY';
CREATE ROLE app_user LOGIN PASSWORD 'REDACTED-EXAMPLE-ONLY';

Restore as a role that will own the schema, not as your application role. These docs use app_migrator for it throughout. See databases and roles.

Restore it #

pg_restore \
  --dbname="postgresql://[email protected]:25439/appdb?sslmode=verify-full&sslrootcert=system" \
  --no-owner \
  --no-privileges \
  --jobs=4 \
  --verbose \
  appdb.dump

--jobs restores in parallel and is the difference between minutes and hours on a large dump. Keep it at or below your plan's vCPU count: more parallelism than you have CPU makes the restore slower, not faster, and competes with the I/O the restore itself needs.

The example uses 25439, the direct port of one service. Use your own service's, the higher of the two on its Connect tab. A long restore on the direct endpoint does not occupy a pooled server connection for its duration.

Order of operations #

1. Use appdb, the database your service was created with
2. Create your roles with SQL
3. Restore the schema and data
4. Apply grants and default privileges
5. ANALYZE

CREATE DATABASE fails because your managed elevated role does not hold CREATEDB, so do not use --create when you dump or restore.

Run ANALYZE #

ANALYZE;

A freshly restored database has no statistics. The planner will make poor choices until it does, and the resulting "the new database is slow" is nearly always this.

What you will see #

pg_restore --verbose prints an item per object and ends with a summary. The last line is the one to read:

pg_restore: warning: errors ignored on restore: 3

pg_restore carries on past a failed statement by default, so a partial restore can look finished. Three ignored errors is a restore you have to inspect. A clean restore prints no such warning.

Then check the data landed:

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;

Run it against the source too, and compare.

Troubleshooting #

errors ignored on restore. Read them with --verbose and look for the first one. The usual causes are an extension outside the catalogue, an object owned by a role that does not exist here, and anything needing superuser. See compatibility notes.

permission denied to create extension. The extension is not one your role can install. See supported extensions.

invalid byte sequence for encoding "UTF8". The source is SQL_ASCII and holds bytes that are not valid UTF-8. See compatibility notes.

Skip an object you do not want. Build a list and edit it:

pg_restore --list appdb.dump > appdb.list
# Put a semicolon at the start of each line you do not want, then:
pg_restore --use-list=appdb.list --no-owner --no-privileges \
  --dbname="postgresql://..." appdb.dump

The restore is slower than expected. Check --jobs against your plan's vCPU count, and restore over the direct endpoint rather than the pooled one.

Related #

• Compatibility notes
• Exporting
• Privileges