Обсуждение: More efficient pg_restore method?

Поиск
Список
Период
Сортировка

More efficient pg_restore method?

От
Ron
Дата:
Pg 9.6.9 on Linux...

Given a backup server storing a "format=directory" database backup, and a 
database server, should I:

Option #1: run pg_restore on the backup server and "push" the data to the 
database server via port 5432, or
Option #2: have the backup server serve the dump directory via NFS, and run 
pg_restore on the database server, pulling the data via nfs protocol?

(It'll be a multi-threaded restore over a 10Gb pipe.)

-- 
Angular momentum makes the world go 'round.


Re: More efficient pg_restore method?

От
Evan Bauer
Дата:
Ron,

A couple of starting questions:

  1. What is the size and latency of the network pipe between the primary and backup servers?  
  2. What is the size of the database you need to restore? 
  3. Is there a reason not to do a network copy of the backup directory contents to the database server and run the pg_restore locally?

Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr@gmail.com> wrote:


Pg 9.6.9 on Linux...

Given a backup server storing a "format=directory" database backup, and a database server, should I:

Option #1: run pg_restore on the backup server and "push" the data to the database server via port 5432, or
Option #2: have the backup server serve the dump directory via NFS, and run pg_restore on the database server, pulling the data via nfs protocol?

(It'll be a multi-threaded restore over a 10Gb pipe.)

--
Angular momentum makes the world go 'round.


Re: More efficient pg_restore method?

От
Ron
Дата:
On 08/28/2018 11:57 AM, Evan Bauer wrote:
Ron,

A couple of starting questions:

  1. What is the size and latency of the network pipe between the primary and backup servers? 

10Gb WAN.  Don't know the latency.

  1. What is the size of the database you need to restore?

6.5TB dump directory, 3.0TB data/base directory.

  1. Is there a reason not to do a network copy of the backup directory contents to the database server and run the pg_restore locally?

Option #3!!!  I'll research that.


Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr@gmail.com> wrote:


Pg 9.6.9 on Linux...

Given a backup server storing a "format=directory" database backup, and a database server, should I:

Option #1: run pg_restore on the backup server and "push" the data to the database server via port 5432, or
Option #2: have the backup server serve the dump directory via NFS, and run pg_restore on the database server, pulling the data via nfs protocol?

(It'll be a multi-threaded restore over a 10Gb pipe.)

--
Angular momentum makes the world go 'round.



--
Angular momentum makes the world go 'round.

Re: More efficient pg_restore method?

От
Evan Bauer
Дата:
Ron,

Even with the speed of your WAN circuit, I would try to do all restores locally.  The latency on a WAN connection (especially with all of the files in a format=directory backup) is a concern.  The outline of how I would do it are:

  1. rsync or ftp the files to either the database server’s local storage or to or LAN-connected SAN/NAS storage
  2. Verify the copy with a checksum 
  3. Perform the pg_restore locally

Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 13:00, Ron <ronljohnsonjr@gmail.com> wrote:

On 08/28/2018 11:57 AM, Evan Bauer wrote:
Ron,

A couple of starting questions:

  1. What is the size and latency of the network pipe between the primary and backup servers?  

10Gb WAN.  Don't know the latency.

  1. What is the size of the database you need to restore? 

6.5TB dump directory, 3.0TB data/base directory.

  1. Is there a reason not to do a network copy of the backup directory contents to the database server and run the pg_restore locally?

Option #3!!!  I'll research that.


Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr@gmail.com> wrote:


Pg 9.6.9 on Linux...

Given a backup server storing a "format=directory" database backup, and a database server, should I:

Option #1: run pg_restore on the backup server and "push" the data to the database server via port 5432, or
Option #2: have the backup server serve the dump directory via NFS, and run pg_restore on the database server, pulling the data via nfs protocol?

(It'll be a multi-threaded restore over a 10Gb pipe.)

-- 
Angular momentum makes the world go 'round.



-- 
Angular momentum makes the world go 'round.

Re: More efficient pg_restore method?

От
Ron
Дата:
On 08/28/2018 11:48 AM, Ron wrote:
>
> Pg 9.6.9 on Linux...
>
> Given a backup server storing a "format=directory" database backup, and a 
> database server, should I:
>
> Option #1: run pg_restore on the backup server and "push" the data to the 
> database server via port 5432, or
> Option #2: have the backup server serve the dump directory via NFS, and 
> run pg_restore on the database server, pulling the data via nfs protocol?
>
> (It'll be a multi-threaded restore over a 10Gb pipe.)

Turns out that pushing the data over port 5432 is about 3x faster than 
pulling it across nfs (at least when a WAN is involved).

-- 
Angular momentum makes the world go 'round.