Thread: How to transfer databases form one server to other

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 ?

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. 

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

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.

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

On 1/26/20 7:30 PM, Adrian Klaver wrote:

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.

I ran uncompressed pg_dump on multiple TB+ sized databases from v8.4 servers across the LAN using 9.6 binaries on the remote server.  It was quite fast.  Threading was key.

Hi!

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

By my knowledge, pgAdmin executes script in single transaction and rolls it
back on error.
Should psql used or is there some option in pgadmin.

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

If imported databases are dropped before, there will be hopefully no
dependencies.

Andrus. 

On 26/01/2020 22:47, 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).

I have it installed on my Debian 9 laptop, from the PostgreSQL apt repo:

  https://apt.postgresql.org

Ray.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 1/26/20 10:44 PM, Andreas Joseph Krogh wrote:

På mandag 27. januar 2020 kl. 03:26:59, skrev Ron <ronljohnsonjr@gmail.com>:

[..]
I ran uncompressed pg_dump on multiple TB+ sized databases from v8.4 servers across the LAN using 9.6 binaries on the remote server.  It was quite fast.  Threading was key.

 

According to the manual: https://www.postgresql.org/docs/12/app-pgdump.html

the "directory format" is the only format which supports parallel dumps, if I'm not reading it wrong.

 

How did threading solve "between database" dump/restore for you? Did you dump to "directory format" first, then restore?

Yes.

If so, then that requires quite a bit of temp-space...

Correct. The databases are mostly compressed TIFF and PDF images in bytea fields, so having Postgres try and compress them again was slow and used a lot of CPU.  Thus, I did uncompressed backups, and that took a lot of scratch disk space.

(We were not only upgrading Postgres 8.4 to 9.6, but also RHEL 5.10 to 6.10, and moving to a geographically distant data center.  Thus, I deemed pg_upgrade to be impractical.)

We spun up some VMs with 10 total TB in the same DC as the source (physical) servers, and I installed Pg 9.6 on these "intermediate servers", and did remote pg_dumps of the 8.4 servers.  Then I installed 9.6 on the VMs in the new DC, and NFS mounted the intermediate servers' volumes and ran multi-threaded pg_restore on the new servers.  They pulled the data across the WAN.

Hi!

>> Postgres version 12 pg_dump probably cannot installed in old server
>> (Debian Squeeze 9).

>I have it installed on my Debian 9 laptop, from the PostgreSQL apt repo:

>  https://apt.postgresql.org

Oled server uses Debian Sqeeze whose version is 6.
I mistakenly typed Debian Squeeze 9, I'm sorry.

Andrus.

On 27/01/2020 12:40, Andrus wrote:
> Hi!
> 
>>> Postgres version 12 pg_dump probably cannot installed in old server
>>> (Debian Squeeze 9).
> 
>> I have it installed on my Debian 9 laptop, from the PostgreSQL apt repo:
> 
>>  https://apt.postgresql.org
> 
> Oled server uses Debian Sqeeze whose version is 6.
> I mistakenly typed Debian Squeeze 9, I'm sorry.

No problem! :-)

R.

-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie

On 1/26/20 10:56 PM, Andrus wrote:
> Hi!
> 
>>> 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.
> 
> By my knowledge, pgAdmin executes script in single transaction and rolls it
> back on error.
> Should psql used or is there some option in pgadmin.

There are options on pgAdmin:
https://www.pgadmin.org/docs/pgadmin4/4.17/restore_dialog.html
See:

Single transaction
Exit on error

Can't remember if you are taking a custom format backup or plain text. 
If plain text you can use psql. If custom then will need to use pg_restore.

> 
>>> 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.
> 
> If imported databases are dropped before, there will be hopefully no
> dependencies.

Roles are global, dependencies can be local to a database. The issue is 
if objects in a restored database depend on roles that no longer exist 
in the global context.

> 
> Andrus.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Am 27.01.20 um 21:47 schrieb Adrian Klaver:
> On 1/26/20 10:56 PM, Andrus wrote:
>> Hi!
>>
>>>> 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.
>>
>> By my knowledge, pgAdmin executes script in single transaction and 
>> rolls it
>> back on error.
>> Should psql used or is there some option in pgadmin.
> 
> There are options on pgAdmin:
> https://www.pgadmin.org/docs/pgadmin4/4.17/restore_dialog.html
> See:
> 
> Single transaction
> Exit on error
> 
> Can't remember if you are taking a custom format backup or plain text. 
> If plain text you can use psql. If custom then will need to use pg_restore.
> 

I have no read the fill post but perhaps netcat or ssh tunnel can be an 
option to tansfer db's from a to b.