Re: How to transfer databases form one server to other

On 1/26/20 2:47 PM, Andrus wrote:
> Hi!
>
>> 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.
>
> I want to create test transfer first, check applications work and after
> that final transfer.
>
>> 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)
>
> Postgres version 12 pg_dump probably cannot installed in old server
> (Debian Squeeze 9).
> Running pg_dump in new server probably takes much more time since data
> is read from uncompressed form and dumping is time-consuming process.
> (internet connection between those server is fast, SSH copy speed was
> 800 Mbit (not 100 Mbit as I wrote), it took 5 minutes to copy 37 GB).

Test it and see how slow/fast it is.

>
> There are also some hundred of Postgresql login and group roles in old
> server used also in access rights in databases.
> Those needs transferred also.

pg_dumpall -g > globals.sql

will get you the global information. See:

https://www.postgresql.org/docs/12/app-pg-dumpall.html

More comment inline below.
>
> My plan is:
>
> 1. Use pg_dump 9.1 in old server to create 24 .backup files in custom
> format.
> 2. Use pgAdmin "backup globals" command to dump role definitions is old
> server to text file.
> 3. Manually edit role definitions to delete role postgres since it
> exists in new server.

No need, it will throw a harmless error message and continue on.

> 4. Run edited role definitons script using pgadmin in new server to
> create roles
> 5. Use Midnight Commander to copy 24 .backup files from old to new server
> 6. Use Postgres 12 pg_restore with job count 4 to restore  those 24
> databases to new server sequentially.
>
> To repeat transfer after testing:
>
> 1. Delete restored databases.
> 2. Delete imported roles in new server

That will probaly not end well. I'm guessing there are objects that have
a dependency on the the roles.

From you questions above and below I would say you need to set up a
test bed and try an dump/restore on a single database. That will help
focus you on the actual problems. I'm guessing there will be more then
you have mentioned so far.

> 3. Proceed 1-6 from plan again.
>
>
> Questions:
>
> 1. pgAdmin allows only deletion roles one by one.
> Deleting hundreds of roles is huge work.
> How to invoke command like
>
> DELETE ALL ROLES EXCEPT postgres
>
> ?
> Is there some command, script or pgadmin GUI for this ?
>
> 2. Is it OK to restore from 9.1 backups or should I create backups using
> pg_dump from Postgres 12 ?
> I have done some minor testing and havent found issues.
>
> 3. How to create shell script which reads all files from /root/backup
> directory from old server?
> (I'm new to linux, this is not postgresql related question)
>
> 4. Are there some settings which can used to speed up restore process ?
> Will turning fsync off during restore speed up it ?
> New server has 11 GB ram . No other applications are running during
> database transfer.
> shared_buffer=1GB setting is currently used in postgresql.conf
>
> 5. Can this plan improved
>
> Andrus.
>
>
>

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