Re: How to transfer databases form one server to other - Mailing list pgsql-general

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.klaver@aklaver.com