How to migrate PostgreSQL to Quake AI

Move a PostgreSQL database from another host or provider onto a Quake AI instance using a logical dump. The procedure works from any source that exposes a PostgreSQL endpoint you can reach with pg_dump, including a managed database elsewhere, a VM you run, or a container.

Replace SOURCE_HOST, SOURCE_USER, and SOURCE_DB with the source connection values, DB_PRIVATE_IP with the target's private address, JUMP_HOST with the bastion address, and YOUR_PRIVATE_KEY_PATH with the key matching the deployment's key_name.

Step 1: Compare major versions#

Read the version at both ends before you dump anything:

psql -h SOURCE_HOST -U SOURCE_USER -d SOURCE_DB -c "SHOW server_version;"
ssh -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST ubuntu@DB_PRIVATE_IP \
  'psql --version'

Restoring into the same major version or a newer one is the straightforward path. The template's pg_version variable sets the target major version at apply time, so redeploy the target with a matching value when the source runs an older release than the default. Run pg_dump from the client package matching the newer of the two versions.

Step 2: Inventory what has to move#

A dump of one database carries its schemas, tables, data, indexes, and constraints. Roles and grants live at the cluster level, and extensions have to exist on the target before a restore can reference them:

psql -h SOURCE_HOST -U SOURCE_USER -d SOURCE_DB -c "\du"
psql -h SOURCE_HOST -U SOURCE_USER -d SOURCE_DB -c "SELECT extname, extversion FROM pg_extension;"

Install any extension the source uses on the target instance before Step 5, and confirm the package exists for the target's PostgreSQL version. An extension the target cannot provide changes the migration plan rather than the restore command.

Step 3: Dump the source#

Take a compressed custom-format dump of the database, and a globals-only dump for roles:

pg_dump -h SOURCE_HOST -U SOURCE_USER -d SOURCE_DB -Fc -f source-db.dump
pg_dumpall -h SOURCE_HOST -U SOURCE_USER --globals-only -f source-globals.sql

Custom format (-Fc) compresses the dump and lets pg_restore load objects selectively and in parallel. For a large database, run the dump during a quiet period: the dump reads a consistent snapshot, and writes that land after it starts do not appear in the file.

Record a row count for the largest tables now, so Step 6 has something to compare against:

psql -h SOURCE_HOST -U SOURCE_USER -d SOURCE_DB \
  -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;"

Step 4: Transfer the dump into the subnet#

Copy both files to the target through the bastion:

scp -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST \
  source-db.dump source-globals.sql ubuntu@DB_PRIVATE_IP:/tmp/

The target's data volume holds the database, the dump, and the template's scheduled backups. Check free space before a large transfer, and extend the volume first if the dump would crowd it (see How to extend volume storage capacity).

Step 5: Restore into the target#

SSH to the target instance and load the roles first, then the database:

ssh -i YOUR_PRIVATE_KEY_PATH -J ubuntu@JUMP_HOST ubuntu@DB_PRIVATE_IP
sudo -u postgres psql -f /tmp/source-globals.sql

Role statements that collide with the db_user role cloud-init already created print errors. Those collisions are expected and do not stop the restore.

Create the destination database and restore into it:

sudo -u postgres createdb SOURCE_DB
sudo -u postgres pg_restore -d SOURCE_DB --no-owner --role=appuser -j 4 /tmp/source-db.dump

--no-owner drops ownership statements that reference roles the target does not have, and --role assigns the restored objects to the application role. Drop -j 4 when the instance has fewer cores to spare.

Step 6: Verify the restore#

Compare the object inventory and row counts against the numbers from Step 3:

sudo -u postgres psql -d SOURCE_DB -c "\dt"
sudo -u postgres psql -d SOURCE_DB \
  -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10;"

n_live_tup is an estimate that ANALYZE refreshes. Run ANALYZE before you compare, and confirm exact counts with SELECT count(*) on the tables that matter most. Then confirm the application role authenticates and reads:

PGPASSWORD='YOUR_DB_PASSWORD' psql -h 127.0.0.1 -U appuser -d SOURCE_DB -c "SELECT 1;"

Step 7: Cut the application over#

1. Stop writes at the source, or put the application in maintenance mode.
2. Take a final dump of any tables that changed since Step 3, and restore them the same way.
3. Update the application's connection string to the target's private address and port 5432. See How to connect to a database on a private network.
4. Start the application and confirm it reads and writes against the new target.
5. Trigger a backup on the target with sudo /usr/local/bin/pg-backup.sh so the first post-cutover dump exists immediately.

Keep the source database readable until the target has run through a full business cycle. The rollback path is the connection string you changed in step 3.

Next steps#

• Database backup and recovery: set up the recovery layers on the new target
• How to restore PostgreSQL from a self-managed-postgres backup: rehearse recovery once the data has landed
• Back up to Quake AI object storage: copy dumps off the data volume