Migrate a PostgreSQL database to Plexivo

Move a PostgreSQL database to Plexivo Postgres with pg_dump and pg_restore: the right flags, versions, extensions, TLS and a cut-over checklist.

There are two ways to bring a PostgreSQL database to Plexivo Postgres. Plexivo can copy it for you from any database it can reach over the internet. Or you move it yourself with pg_dump and pg_restore, which this page shows, when the source is not reachable from outside or you want to control each step.

Let Plexivo copy itIf your source is reachable from the internet, choose Migrations → New migration and paste its connection string. Plexivo checks the extensions, copies the database and restores it for you. See Move a PostgreSQL database to Plexivo Postgres.

Before you begin#

Versions. Plexivo Postgres runs PostgreSQL 18, recommended, or 17. Create the new database on a version the same as your source or newer. Use the pg_dump and pg_restore of that newer version: a newer pg_dump reads older servers.
Extensions. Every extension your source uses must be one your app user can create. Check the list on the new database with the query in Extensions.
An empty database. Create a Plexivo Postgres database with room for your data. See Create a database.
A way in. From your computer, open a tunnel with plexivo db connect, or add your address under Trusted networks. See Networking.

Move it#

Get both connection strings. Your source’s, as SOURCE_URL. From the new database’s Connect tab, choose Reveal and copy its connection string as TARGET_URL, and choose Download CA certificate to save plexivo-postgres-ca.pem in the same folder.
bash
```
export SOURCE_URL='postgresql://user:PASSWORD@old-host:5432/mydb?sslmode=require'
export TARGET_URL='postgresql://app:PASSWORD@orders-db-3f9a1c.db.plexivo.app:31542/app?sslmode=verify-full&sslrootcert=plexivo-postgres-ca.pem'
```
Stop writes. Put your app in maintenance mode, or stop it, so nothing written after the dump is left behind.
Dump the source. Use the custom format, and leave out the source’s owners, grants, publications and subscriptions, which belong to the old server.
bash
```
pg_dump -Fc --no-owner --no-acl --no-publications --no-subscriptions \
  -d "$SOURCE_URL" -f app.dump
```

Leave out provider-only objects. Monitoring extensions such as pg_stat_statements and event triggers hold no data and need a superuser. Make a list of everything else:

bash

pg_restore -l app.dump \
  | grep -v -E ' EVENT TRIGGER | COMMENT - EXTENSION | EXTENSION - "?(pg_stat_statements|pg_stat_monitor|pg_buffercache|pgaudit|pg_repack|plpgsql)"? *$' \
  > keep.list

Restore into Plexivo Postgres. Everything restored is owned by app. --exit-on-error stops at the first error instead of leaving a half-restored database. For a large database, --jobs 4 restores tables in parallel; keep it below your connection limit.
bash
```
pg_restore --no-owner --no-acl --exit-on-error --jobs 4 \
  -L keep.list -d "$TARGET_URL" app.dump
```
Refresh statistics. Run ANALYZE so the new database plans queries well from the first request. Warnings that it skips system tables are expected.
bash
```
psql "$TARGET_URL" -c 'ANALYZE;'
```
Check the data. Compare row counts of your important tables on both sides, and the next value of their sequences.
bash
```
psql "$SOURCE_URL" -Atc 'select count(*) from orders'
psql "$TARGET_URL" -Atc 'select count(*) from orders'
```
Switch your app over. For a Plexivo App Runner app, connect the database on the app’s Environment tab: Plexivo sets DATABASE_URL and DATABASE_CA_CERT. For an app elsewhere, give it the new connection string and the CA certificate. Then end maintenance mode.

TLS#

Plexivo Postgres accepts only TLS connections. With sslmode=verify-full and the database’s CA certificate, your client checks both the certificate and the host name. Apps connected in Plexivo receive the certificate as DATABASE_CA_CERT. Never turn off certificate checks. See TLS.

Through a plexivo db connect tunnel, connect to 127.0.0.1 on the port it prints, with sslmode=require, as Connect shows.

Cut-over checklist#

Plan the switch for a quiet hour, and tell your users if the app will be read-only for a while.
Do a full practice run first, and time it: the real move takes about as long.
Stop writes, dump, restore, run ANALYZE, and check row counts.
Switch the app to the new connection string with TLS checks on.
Test sign-in, a write and a read in the app, then end maintenance mode.
Keep the old database, read-only, for a few days as your way back.
Then delete it, and remove its passwords from wherever you kept them.

For businesses For a large or busy database, a practice run tells you how long writes must stop. Plexivo support can plan the move and the switch-over with you.

Free migration help#

Prefer a hand? Tell us about your database, and Plexivo support plans the move with you. Ask for free migration help.

Next steps#

Connect your appDrivers, the pooler and TLS. Backups and restoreRestore to any moment in your backup period.

PostgreSQL is a registered trademark of the PostgreSQL Community Association of Canada.

Updated 2 November 2026