I found the following document and actually tried fly import.
The command seemed to succeed, but the actual data was not migrated.
The tables themselves were migrated
fly pg import postgres://yyy:xxx@ec2-xxx-yyy-zz-xxx.compute-1.amazonaws.com:5432/xxx --app test-db
Some regions require a paid plan (bom, fra, maa).
See https://fly.io/plans to set up a plan.
? Choose a region to deploy the migration machine: Tokyo, Japan (nrt)
? Select VM size: shared-cpu-1x - CPU Kind: Shared, vCPUs: 1 Memory: 256MB
Waiting for machine xxx to start...
Connecting to xxx... complete
[info] Running pre-checks...
[info] Source Postgres version: 14.8 (Ubuntu 14.8-1.pgdg20.04+1)
[info] Target Postgres version: 15.3 (Debian 15.3-1.pgdg120+1)
[info] Pre-checks completed without issue
[info] Starting import process... (This could take a while)
[info] Import complete!
Waiting for machine xxx to stop...
yyy has been destroyed
select count(*) from users;
=> 1
select count(*) from another_tables;
=> 0
logs:
2023-08-06T07:30:12.652 app[xxx] nrt [info] server stopped
2023-08-06T07:30:12.654 app[xxx] nrt [info] PostgreSQL init process complete; ready for start up.
2023-08-06T07:30:12.666 app[xxx] nrt [info] 2023-08-06 07:30:12.666 UTC [255] LOG: starting PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-08-06T07:30:12.667 app[xxx] nrt [info] 2023-08-06 07:30:12.667 UTC [255] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-08-06T07:30:12.667 app[xxx] nrt [info] 2023-08-06 07:30:12.667 UTC [255] LOG: listening on IPv6 address "::", port 5432
2023-08-06T07:30:12.668 app[xxx] nrt [info] 2023-08-06 07:30:12.668 UTC [255] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-08-06T07:30:12.670 app[xxx] nrt [info] 2023-08-06 07:30:12.669 UTC [315] LOG: database system was shut down at 2023-08-06 07:30:12 UTC
2023-08-06T07:30:12.671 app[xxx] nrt [info] 2023-08-06 07:30:12.671 UTC [255] LOG: database system is ready to accept connections
2023-08-06T07:30:38.570 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.569 UTC [25650] ERROR: database "zzz" is being accessed by other users
2023-08-06T07:30:38.570 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.569 UTC [25650] DETAIL: There is 1 other session using the database.
2023-08-06T07:30:38.570 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.569 UTC [25650] STATEMENT: DROP DATABASE zzz;
2023-08-06T07:30:38.572 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.571 UTC [25650] ERROR: database "zzz" already exists
2023-08-06T07:30:38.572 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.571 UTC [25650] STATEMENT: CREATE DATABASE zzz WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
2023-08-06T07:30:38.617 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.616 UTC [25722] ERROR: schema "heroku_ext" already exists
2023-08-06T07:30:38.617 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.616 UTC [25722] STATEMENT: CREATE SCHEMA heroku_ext;
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.623 UTC [25722] ERROR: relation "ar_internal_metadata" already exists
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.623 UTC [25722] STATEMENT: CREATE TABLE public.ar_internal_metadata (
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | key character varying NOT NULL,
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | value character varying,
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | created_at timestamp(6) without time zone NOT NULL,
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | updated_at timestamp(6) without time zone NOT NULL
2023-08-06T07:30:38.624 app[yyy] nrt [info] postgres | );
2023-08-06T07:30:38.625 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.625 UTC [25722] ERROR: relation "clients" already exists
2023-08-06T07:30:38.625 app[yyy] nrt [info] postgres | 2023-08-06 07:30:38.625 UTC [25722] STATEMENT: CREATE TABLE public.clients (
...
Was fly pg import run prior? It appears the tables were already imported based on the errors in the logs. If the database structure already exists, there is a command to import the data only under fly postgres import by passing the --data-only flag.
I tried the --data-only option, but the data migration did not complete successfully.
console:
$ fly pg import postgres://zzz:yyy@ec2-xx-yyy-zzz-ppp.compute-1.amazonaws.com:5432/xxx --data-only --app foo-db
Some regions require a paid plan (bom, fra, maa).
See https://fly.io/plans to set up a plan.
? Choose a region to deploy the migration machine: Tokyo, Japan (nrt)
? Select VM size: shared-cpu-1x - CPU Kind: Shared, vCPUs: 1 Memory: 256MB
Created an ephemeral machine 3d8d11db115689 to run the import process.
Connecting to fdaa:2:5a3e:a7b:d86c:71d0:d21a:2... complete
[info] Running pre-checks...
[info] Source Postgres version: 14.8 (Ubuntu 14.8-1.pgdg20.04+1)
[info] Target Postgres version: 15.3 (Debian 15.3-1.pgdg120+1)
[info] Pre-checks completed without issue
[info] Starting import process... (This could take a while)
[info] Import complete!
Waiting for ephemeral machine 3d8d11db115689 to be destroyed ... done.
logs:
2023-08-09T10:25:23.811 runner[3d8d11db115689] nrt [info] Pulling container image registry-1.docker.io/flyio/postgres-importer:15.2
2023-08-09T10:25:24.581 runner[3d8d11db115689] nrt [info] Pulling container image registry-1.docker.io/flyio/postgres-importer:15.2
2023-08-09T10:25:25.622 runner[3d8d11db115689] nrt [info] Successfully prepared image registry-1.docker.io/flyio/postgres-importer:15.2 (1.041241646s)
2023-08-09T10:25:27.025 runner[3d8d11db115689] nrt [info] Configuring firecracker
2023-08-09T10:25:27.188 app[3d8d11db115689] nrt [info] [ 0.038561] PCI: Fatal: No config space access function found
2023-08-09T10:25:27.417 app[3d8d11db115689] nrt [info] INFO Starting init (commit: b437b5b)...
2023-08-09T10:25:27.433 app[3d8d11db115689] nrt [info] INFO Preparing to run: `docker-entrypoint.sh postgres` as root
2023-08-09T10:25:27.444 app[3d8d11db115689] nrt [info] INFO [fly api proxy] listening at /.fly/api
2023-08-09T10:25:27.452 app[3d8d11db115689] nrt [info] 2023/08/09 10:25:27 listening on [fdaa:2:5a3e:a7b:d86c:71d0:d21a:2]:22 (DNS: [fdaa::3]:53)
2023-08-09T10:25:27.664 app[3d8d11db115689] nrt [info] The files belonging to this database system will be owned by user "postgres".
2023-08-09T10:25:27.664 app[3d8d11db115689] nrt [info] This user must also own the server process.
2023-08-09T10:25:27.665 app[3d8d11db115689] nrt [info] The database cluster will be initialized with locale "en_US.utf8".
2023-08-09T10:25:27.665 app[3d8d11db115689] nrt [info] The default database encoding has accordingly been set to "UTF8".
2023-08-09T10:25:27.665 app[3d8d11db115689] nrt [info] The default text search configuration will be set to "english".
2023-08-09T10:25:27.665 app[3d8d11db115689] nrt [info] Data page checksums are disabled.
2023-08-09T10:25:27.665 app[3d8d11db115689] nrt [info] fixing permissions on existing directory /var/lib/postgresql/data ... ok
2023-08-09T10:25:27.666 app[3d8d11db115689] nrt [info] creating subdirectories ... ok
2023-08-09T10:25:27.666 app[3d8d11db115689] nrt [info] selecting dynamic shared memory implementation ... posix
2023-08-09T10:25:27.697 app[3d8d11db115689] nrt [info] selecting default max_connections ... 100
2023-08-09T10:25:27.720 app[3d8d11db115689] nrt [info] selecting default shared_buffers ... 128MB
2023-08-09T10:25:27.725 app[3d8d11db115689] nrt [info] selecting default time zone ... Etc/UTC
2023-08-09T10:25:27.728 app[3d8d11db115689] nrt [info] creating configuration files ... ok
2023-08-09T10:25:27.893 app[3d8d11db115689] nrt [info] running bootstrap script ... ok
2023-08-09T10:25:28.361 app[3d8d11db115689] nrt [info] performing post-bootstrap initialization ... ok
2023-08-09T10:25:28.397 app[3d8d11db115689] nrt [info] syncing data to disk ... ok
2023-08-09T10:25:28.397 app[3d8d11db115689] nrt [info] initdb: warning: enabling "trust" authentication for local connections
2023-08-09T10:25:28.397 app[3d8d11db115689] nrt [info] initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
2023-08-09T10:25:28.397 app[3d8d11db115689] nrt [info] Success. You can now start the database server using:
2023-08-09T10:25:28.397 app[3d8d11db115689] nrt [info] pg_ctl -D /var/lib/postgresql/data -l logfile start
2023-08-09T10:25:28.426 app[3d8d11db115689] nrt [info] waiting for server to start....2023-08-09 10:25:28.426 UTC [300] LOG: starting PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-08-09T10:25:28.427 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.427 UTC [300] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-08-09T10:25:28.429 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.429 UTC [303] LOG: database system was shut down at 2023-08-09 10:25:28 UTC
2023-08-09T10:25:28.431 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.431 UTC [300] LOG: database system is ready to accept connections
2023-08-09T10:25:28.514 app[3d8d11db115689] nrt [info] done
2023-08-09T10:25:28.514 app[3d8d11db115689] nrt [info] server started
2023-08-09T10:25:28.588 app[3d8d11db115689] nrt [info] /usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2023-08-09T10:25:28.590 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.589 UTC [300] LOG: received fast shutdown request
2023-08-09T10:25:28.593 app[3d8d11db115689] nrt [info] waiting for server to shut down....2023-08-09 10:25:28.590 UTC [300] LOG: aborting any active transactions
2023-08-09T10:25:28.593 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.593 UTC [300] LOG: background worker "logical replication launcher" (PID 306) exited with exit code 1
2023-08-09T10:25:28.593 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.593 UTC [301] LOG: shutting down
2023-08-09T10:25:28.593 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.593 UTC [301] LOG: checkpoint starting: shutdown immediate
2023-08-09T10:25:28.595 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.594 UTC [301] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2023-08-09T10:25:28.597 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.597 UTC [300] LOG: database system is shut down
2023-08-09T10:25:28.690 app[3d8d11db115689] nrt [info] done
2023-08-09T10:25:28.690 app[3d8d11db115689] nrt [info] server stopped
2023-08-09T10:25:28.692 app[3d8d11db115689] nrt [info] PostgreSQL init process complete; ready for start up.
2023-08-09T10:25:28.705 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.705 UTC [255] LOG: starting PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
2023-08-09T10:25:28.706 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.706 UTC [255] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-08-09T10:25:28.706 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.706 UTC [255] LOG: listening on IPv6 address "::", port 5432
2023-08-09T10:25:28.707 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.707 UTC [255] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-08-09T10:25:28.709 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.708 UTC [315] LOG: database system was shut down at 2023-08-09 10:25:28 UTC
2023-08-09T10:25:28.711 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:28.711 UTC [255] LOG: database system is ready to accept connections
2023-08-09T10:25:31.880 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.880 UTC [255] LOG: received fast shutdown request
2023-08-09T10:25:31.881 app[3d8d11db115689] nrt [info] INFO Sending signal SIGINT to main child process w/ PID 255
2023-08-09T10:25:31.885 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.881 UTC [255] LOG: aborting any active transactions
2023-08-09T10:25:31.885 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.884 UTC [255] LOG: background worker "logical replication launcher" (PID 318) exited with exit code 1
2023-08-09T10:25:31.885 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.884 UTC [313] LOG: shutting down
2023-08-09T10:25:31.885 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.885 UTC [313] LOG: checkpoint starting: shutdown immediate
2023-08-09T10:25:31.889 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.886 UTC [313] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB
2023-08-09T10:25:31.889 app[3d8d11db115689] nrt [info] 2023-08-09 10:25:31.888 UTC [255] LOG: database system is shut down
2023-08-09T10:25:32.451 app[3d8d11db115689] nrt [info] INFO Main child exited normally with code: 0
2023-08-09T10:25:32.451 app[3d8d11db115689] nrt [info] INFO Starting clean up.
2023-08-09T10:25:32.452 app[3d8d11db115689] nrt [info] WARN hallpass exited, pid: 256, status: signal: 15 (SIGTERM)
2023-08-09T10:25:32.453 app[3d8d11db115689] nrt [info] 2023/08/09 10:25:32 listening on [fdaa:2:5a3e:a7b:d86c:71d0:d21a:2]:22 (DNS: [fdaa::3]:53)
2023-08-09T10:25:33.447 app[3d8d11db115689] nrt [info] [ 6.293713] reboot: Restarting system