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

From Andrus
Subject Re: How to transfer databases form one server to other
Date
Msg-id 111D51B7C5C64419810F09C229E1D917@dell2
Whole thread Raw
In response to Re: How to transfer databases form one server to other  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: How to transfer databases form one server to other
List pgsql-general
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).

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.

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






pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: How to transfer databases form one server to other
Next
From: Adrian Klaver
Date:
Subject: Re: How to transfer databases form one server to other