Обсуждение: More efficient pg_restore method?
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.
Ron,
A couple of starting questions:
- What is the size and latency of the network pipe between the primary and backup servers?
- What is the size of the database you need to restore?
- 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
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.
On 08/28/2018 11:57 AM, Evan Bauer wrote:
10Gb WAN. Don't know the latency.
6.5TB dump directory, 3.0TB data/base directory.
Option #3!!! I'll research that.
Ron,A couple of starting questions:
- What is the size and latency of the network pipe between the primary and backup servers?
10Gb WAN. Don't know the latency.
- What is the size of the database you need to restore?
6.5TB dump directory, 3.0TB data/base directory.
- 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,- EvanOn 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.
Angular momentum makes the world go 'round.
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:
- rsync or ftp the files to either the database server’s local storage or to or LAN-connected SAN/NAS storage
- Verify the copy with a checksum
- Perform the pg_restore locally
Cheers,
- Evan
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:
- What is the size and latency of the network pipe between the primary and backup servers?
10Gb WAN. Don't know the latency.
- What is the size of the database you need to restore?
6.5TB dump directory, 3.0TB data/base directory.
- 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,- EvanOn 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.
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.