# How to migrate PostgreSQL to Quake AI Source: https://docs.quake.ai/docs/databases/migration/migrate-postgresql Markdown: https://docs.quake.ai/docs/databases/migration/migrate-postgresql.md > Move an existing PostgreSQL database onto a Quake AI instance: check versions, dump the source, transfer the dump through a bastion, restore, verify, and cut the application over. --- # 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. - A PostgreSQL target on Quake AI, deployed with [the self-managed PostgreSQL template](/resources/deployments/deploy-self-managed-postgres-template) - Credentials on the source database for a role that can read every object you plan to move - A bastion, VPN, or jump instance with a route into the target's private subnet. See [SSH bastion access into a private subnet](/docs/network/how-to/ssh-bastion-access) - Disk space on the workstation or transfer host for the compressed dump 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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](/docs/block/how-to/extend-volume)). ## Step 5: Restore into the target SSH to the target instance and load the roles first, then the database: ```bash 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: ```bash 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: ```bash 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: ```bash 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](/docs/databases/how-to/connect-to-a-private-database). 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](/docs/databases/concepts/database-backup-and-recovery): set up the recovery layers on the new target - [How to restore PostgreSQL from a self-managed-postgres backup](/docs/automation/how-to/restore-postgres-from-backup): rehearse recovery once the data has landed - [Back up to Quake AI object storage](/docs/object/migration/backup-to-quake-ai): copy dumps off the data volume