How to transfer postgres databases from one server to other

Question

VPS server has old Debian 6 Squeeze with Postgres 9.1 It has 24 databases. Every night backup copies are created using pg_dump to /root/backups directory for every database. This directory has 24 .backup files with total size 37 GB.

I installed new VPS server with Debian 10 and Postgres 12.

How to transfer those databases to new server ?

Both server have ssh and root user, postgres port 5432 open, 100 MB internet connection and fixed IP addresses. In night they are not used by users, can stopped during move.

Should I download .backup files and use pg_restore or use pipe to restore whole cluster.

Answer 1

Well, according to the instructions you should use the postgres 12 tools against the 9.1 database when dumping for an upgrade

so your first step it to upgrade your backup procedure to use the pg_dump from postgres 12

If your network between the two VPSs has low tatency you can do that over the network, but if not you'll need to find a different way.

Possibly copy the 9.1 data files onto the 12 server using rsync and the run pg_upgrade --link over those files to upgrade the 9.1 files to 12

you can do an initial pass with rsync while the database service is running and then shut it down for the final pass.