
Import data from another postgres cluster into your Managed Postgres Cluster
If youāre migrating to Fly Managed Postgres (MPG) from an unmanaged Fly Postgres cluster or another Postgres provider, youāll need to import your data into your MPG cluster. There isnāt currently an automated way to migrate an existing DBās data, but you can accomplish this with a standard Postgres dump and restore process after creating your MPG cluster. Your MPG cluster runs within your Fly.io private network, so it isnāt reachable over the public internet.fly mpg connect opens the tunnel, looks up the credentials, and runs psql for you, so you can restore from your local machine without setting up a proxy or copying a password. Youāll need pg_dump and psql installed locally, and flyctl v0.5.9 or later.
-
Create your Managed Postgres cluster from the Fly.io dashboard, or using
fly mpg create -
Dump your existing database to a file:
Make sure
pg_dumpfinishes without an error before you go on. A dump that failed partway leaves an incomplete file, and an incomplete file can restore without complaint. -
If your
pg_dumpis version 17 or later and your cluster runs Postgres 16 (thefly mpg createdefault), remove one setting from the top of the dump.pg_dump17 and later writesSET transaction_timeout = 0;there, and Postgres 16 doesnāt know that setting. Check your version withpg_dump --version, then remove the line:The1,20keeps the edit inside the dumpās header, so it canāt touch your data. -
Find the cluster ID with
fly mpg list, then restore the file into your MPG cluster. If you ran step 3, restoredump-pg16.sqlinstead ofdump.sql:-qkeeps psql quiet, so errors stand out.--single-transactionwraps the restore in one transaction: if an error stops it, nothing is left behind and you can run it again. The exception is a dump that contains large objects: it carries its own transactions, so a restore that fails after them leaves what it already restored. The restore goes into the default database as the default user. Add--databaseor--usernamebefore the--to pick others. -
Check the result. A restore that worked prints no errors, only a few query results such as
set_configandsetval, and exits with status0. One that failed prints psqlās error and a line likeError: exit status 3, and exits with status1.
Restore errors
unrecognized configuration parameter "transaction_timeout": The dump came frompg_dump17 or later and the cluster runs Postgres 16. Go back to step 3, then restoredump-pg16.sql.must be owner of extension: The dump has aCOMMENT ON EXTENSIONstatement for an extension that comes preinstalled on MPG, such aspgauditorpg_stat_monitor, and the default user doesnāt own those. Youāll see this when the source is another MPG cluster. If you reach that cluster throughfly mpg proxy, give the proxy a different--local-port, becausefly mpg connectalways uses 16380. Dump again withpg_dump17 or later and leave those extensions out, then repeat steps 3 and 4:server closed the connection unexpectedlyorconnection to server was lost: The connection to your cluster dropped. A restore needs the connection for the whole run, and a large one can take a while. With--single-transaction, a restore that was still running is rolled back, unless the dump contains large objects. Check whether your tables are there before you run it again, in case the connection dropped just as the restore finished.already exists: The database isnāt empty. An earlier restore finished, ran without--single-transaction, or was piped from apg_dumpthat failed partway. Restore into an empty database instead: create one withfly mpg databases create <CLUSTER_ID> --name <database-name>, then add--database <database-name>before the--in the restore command. Pass the same flag tofly mpg attachwhen you attach your app.out of shared memory, with a hint aboutmax_locks_per_transaction: The dump has more tables than one transaction can lock, so the restore was rolled back. Restore without--single-transaction, as infly mpg connect <CLUSTER_ID> -f dump.sql -- -q. psql still stops at the first error, but what it restored before the error stays. If that happens, restore into a new, empty database before you try again.- Another error youād rather skip: Replace
--single-transactionwith-v ON_ERROR_STOP=0, as infly mpg connect <CLUSTER_ID> -f dump.sql -- -q -v ON_ERROR_STOP=0. psql then carries on past errors, and the exit status is0even when statements fail, so read every error it prints. Only do this into an empty database: over a partial import, tables that already loaded get their rows twice.
Piping a small database
For a small database you can skip the file and pipepg_dump straight in. Pass -f - to read the dump from stdin. Set pipefail as well: without it, a pg_dump that fails looks like a restore that worked.
sed expression to the pipe, without the file names: pg_dump ... | sed '1,20{/^SET transaction_timeout = 0;$/d;}' | fly mpg connect ....
If pg_dump fails partway, psql can commit what it received, which leaves part of your data imported even though the command fails. Restore into a new, empty database before you try again, as described under already exists. Prefer the file for anything large.
Using another tool
Tools that make their own connection, likepg_restore with a custom-format dump, need an address to connect to. Run fly mpg proxy <CLUSTER_ID> and point the tool at the local port it prints, using the username and password from your clusterās connection string in the dashboard. See Setting up a Proxy Connection.
For larger or more complex databases, you may need to break the dump and restore process into multiple steps. You may also consider using dedicated PG migration tools like pgloader or pgcopydb.
Limitations
Some source databases may not be a good fit for importing into a Fly Managed Postgres cluster. Some common cases that will prevent imports are if your source database:- Relies on a third party extension that isnāt supported by MPG
- Is larger than 1 TB (the maximum storage limit for MPG clusters)
fly-user or another user with the schema_admin role.