Re: How to transfer databases form one server to other

On 1/26/20 8:59 AM, Andrus wrote:
> Hi!
>
> 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 ?

Before you do any of this I would check the Release Notes for the first
release of each major release. Prior to version 10 that would be X.X.x
where X is a major release. For 10+ that is X.x. I would also test the
upgrade before doing it on your production setup.

Best practice if you are going the dump/restore route is to use the
pg_dump binary from the new server(12) to dump the old server(9.1)

>
> 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.
>
> Andrus.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com