Migrate a PostgreSQL database with pg_dump and pg_restore

Moving a database to Runsite is a dump and a restore. You run pg_dump against the old host, then pg_restore against the external URL of a new Runsite database. Both commands run on your own machine. Nothing has to be installed on either server.

Before you start

• Create the target database on the same PostgreSQL major version as the source, or a newer one. Runsite offers 14, 15, 16 and 17. You can’t restore a dump into an older major version.
• Install client tools at least as new as the newest server involved. pg_dump refuses to dump a server newer than itself. On macOS, brew install libpq. On Debian or Ubuntu, install postgresql-client-17 from the PostgreSQL apt repository.
• Copy the external URL from the database’s Overview tab. Click the eye icon to reveal the password. See Connecting to PostgreSQL for the difference between the internal and external URLs.
• Stop writes to the old database before the final dump, or you’ll lose whatever lands in between. For a small app, stopping the app for a few minutes is the simplest way.

Put both URLs in shell variables so the commands below stay readable:

export SOURCE_URL="postgresql://user:password@old-host:5432/app"
export TARGET_URL="postgresql://app_user:password@dpg-xxxxxxxx.eu-central-postgres.runsite.app/app?sslmode=verify-full&sslrootcert=system"

The Runsite endpoint only accepts TLS connections. sslmode=verify-full also checks the certificate, which is a public Let’s Encrypt one. sslrootcert=system needs libpq 16 or newer. With older clients, use sslmode=require instead.

1. Dump the source database

pg_dump --format=custom --no-owner --no-privileges \
  --dbname "$SOURCE_URL" --file app.dump

--format=custom writes a compressed archive that pg_restore can load in parallel. --no-owner and --no-privileges drop the ALTER OWNER and GRANT statements that name roles from the old server. Those roles don’t exist on Runsite.

2. Restore into Runsite

pg_restore --no-owner --no-privileges --jobs 4 \
  --dbname "$TARGET_URL" app.dump

Every restored table and sequence ends up owned by your Runsite user. --jobs 4 loads four tables at a time. Raise it for a big dump, lower it on a small plan.

If you’d rather work with a plain SQL file, pipe one tool into the other:

pg_dump --no-owner --no-privileges "$SOURCE_URL" \
  | psql "$TARGET_URL" -v ON_ERROR_STOP=1

ON_ERROR_STOP=1 makes psql stop at the first error instead of printing a hundred more.

3. Check the data

Open the Explorer tab and look at the table list. For a closer check, compare row counts on both sides:

psql "$TARGET_URL" -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;"

n_live_tup is an estimate that only fills in after ANALYZE. Run psql "$TARGET_URL" -c "ANALYZE;" first, or use SELECT count(*) on the tables you care about.

4. Point your app at the new database

In your web service’s Environment tab, set DATABASE_URL to the database’s internal URL and pick Save and restart. Services on Runsite should use the internal URL. It stays on the private network and skips the TLS proxy.

Keep the old database around for a few days until you’re sure nothing is missing.

Errors you might hit

role "postgres" does not exist or must be owner of .... The dump still contains ownership or grant statements. Add --no-owner --no-privileges to pg_restore (or to pg_dump for plain SQL).

permission denied to create extension. Your Runsite user owns its database but isn’t a superuser, so extensions that need superuser rights can’t be created. Trusted extensions (pgcrypto, citext, hstore, uuid-ossp and others) work. If the dump needs something else, drop that extension on the source first or ask in the support chat in the dashboard.

server version mismatch or aborting because of server version mismatch. Your pg_dump is older than the source server. Upgrade the client tools.

The connection closes right away, or you see server closed the connection unexpectedly. The client is connecting without TLS. Check that the URL has sslmode=require or verify-full, and that you used the external URL, not the internal one. More causes are in Fix PostgreSQL connection errors.

Exporting your data from Runsite

Leaving works the same way in reverse: run pg_dump with the Runsite external URL as the source.

pg_dump --format=custom --no-owner --no-privileges \
  --dbname "$TARGET_URL" --file runsite-export.dump

You can also download a ready-made copy. Open the Backups tab and click Download next to any completed backup. You get a gzipped plain SQL file that loads into any PostgreSQL:

gunzip -c backup.sql.gz | psql "$OTHER_DATABASE_URL" -v ON_ERROR_STOP=1

Backups run automatically every day, and you can take one yourself with Create Backup. See PostgreSQL backups.