Migrate to Canner Postgres.

Which route

Dump and restore (migrate-in) is the simplest and works on any Canner database. Writes to the old database stop while it runs, so the outage is as long as the copy. Fine for a small database, or one you can take offline overnight.

Follow live (an inbound subscription) copies the existing rows, then streams changes from the old server until you cut over. It needs the dedicated instance (CA$9 a month per project, any paid plan) and a source you can configure for logical replication. Choose it when downtime has to be seconds. Details on the instance are in Postgres databases.

Either way the source must be reachable from the internet at a public address. A database on a private network, or one that only accepts connections from inside a VPC, will not work until you open it to Canner.

Before you start

• Provision the Canner database first, on the Postgres version you want (17 or 18). It is chosen at creation.
• Check the storage pool: the copy counts toward your organization’s limit (plans and limits). Backups and uploads stop at 100%, and database writes lock at 10% over.
• List the extensions your database uses. The dedicated instance offers 14 (pgvector, PostGIS, pg_trgm, pg_cron, hstore and others); postgres_fdw, dblink and file_fdw are not offered.

Route 1: dump and restore with migrate-in

# Stream a database straight from a publicly reachable Postgres server
canner db migrate-in --from postgres://user:pass@source.example.com:5432/appdb

Canner streams the database from the source with no local file, which is why it suits databases too large to download. The target must be empty unless you choose to replace it, in which case Canner snapshots the previous contents first. The restore is all-or-nothing: if it fails, the Canner database is exactly as it was. If you already have a dump, canner db import backup.dump takes a pg_dump custom-format file (make it with pg_dump --format=custom --no-owner).

Sequence values, indexes, constraints and data come across with a dump. Owners and privileges do not: objects belong to your Canner database role, and you set up any other roles afterwards.

Route 2: follow the old server live

1. Prepare the source. It needs wal_level = logical and a user with the REPLICATION attribute (on a managed service this is a parameter or a role setting; the provider’s docs say how). Then publish the tables:

   -- On the source (needs wal_level = logical and a user with the REPLICATION attribute)
   CREATE PUBLICATION app_pub FOR ALL TABLES;

2. Create the tables on Canner. Logical replication copies rows, not schema. Restore the schema first, with the same table names and columns:

   # Copy the schema only: tables must exist here, with the same names and columns
   pg_dump --schema-only --no-owner --no-privileges "$SRC_URL" | psql "$CANNER_URL"

   Turn on external access for the target so you can connect from your own machine (TLS-only, IP allowlist).
3. Install extensions your schema needs before the subscription starts, on the dedicated instance.
4. Add the subscription with the connection URL read from an environment variable, so the password is never printed:

   export SRC_URL='postgres://repl_user:pass@source.example.com:5432/appdb'
   canner db subscriptions appdb add legacy --from-env SRC_URL --publication app_pub

   Canner copies the existing rows, then streams changes. The database shows how many tables are in step and when the last change arrived. Up to five subscriptions per database. If a change cannot be applied (a missing table, a conflicting row) it shows Failing, you get an alert, and it retries by itself once you fix the cause.

Two behaviours to plan for. Changes are applied with your table owner’s rights, never as a superuser, so triggers on your tables run with your rights only. And they run with an empty search_path, so a trigger function must use schema-qualified names (public.audit, not audit). For the reasoning, see Running logical replication safely.

Cutover checklist

1. Wait for the subscription to be in step, with every table caught up and the last-change time current.
2. Stop writes on the source. Put the app in maintenance mode or point it at a read-only user, and let the last changes arrive.
3. Fix the sequences. Logical replication does not carry sequence values, so an auto-increment or identity column would restart from the old value on Canner and collide. Read the current values from the source and set them here:

   -- Run on the SOURCE. It prints one setval() statement per sequence.
   SELECT format('SELECT setval(%L, %s, true);',
                 quote_ident(schemaname) || '.' || quote_ident(sequencename),
                 last_value)
   FROM pg_sequences
   WHERE last_value IS NOT NULL;
   -- Run the printed statements on the Canner database.

4. Check extensions and triggers. Every extension is installed; trigger functions use schema-qualified names.
5. Recreate roles. Dumps carry no owners or privileges. Create the read-only or read-write roles your analysts and services need (canner db roles <name> create bi --read-only) and re-issue grants.
6. Remove the subscription. This removes the slot on the source. If the source is already gone, use --force.
7. Point the app at Canner. DATABASE_URL is injected on the next deploy; redeploy, then run your smoke tests.
8. Turn on backups. Set a backup policy right away (canner db backup-policy <name> --enable --every 24 --keep 7), and consider point-in-time recovery and an off-site copy.

Rolling back

• Before the cutover, nothing needs undoing. Remove the subscription and the source is untouched; the Canner database can be deleted or replaced.
• Right after the cutover, the old server still has the data as of the moment you stopped writes. Point the app back at it and you lose only what was written on Canner in between.
• If you need those writes, send them back with migrate-out (canner db migrate-out), which sends a Canner database to a server you name, or export a pg_dump and load the differences by hand.

Keep the old server, read-only, for a few days before you delete it. Note that Canner runs in one region (Montreal) with no automatic failover, so your first backup and your off-site copy matter from day one.