pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMore efficient pg_restore method?
5+ messages / 2 participants
[nested] [flat]
* More efficient pg_restore method?
@ 2018-08-28 16:48 Ron <ronljohnsonjr@gmail.com>
2018-08-28 16:57 ` Re: More efficient pg_restore method? Evan Bauer <evanbauer@mac.com>
2018-08-30 17:30 ` Re: More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ron @ 2018-08-28 16:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: More efficient pg_restore method?
2018-08-28 16:48 More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
@ 2018-08-28 16:57 ` Evan Bauer <evanbauer@mac.com>
2018-08-28 17:00 ` Re: More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Evan Bauer @ 2018-08-28 16:57 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
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.
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: More efficient pg_restore method?
2018-08-28 16:48 More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
2018-08-28 16:57 ` Re: More efficient pg_restore method? Evan Bauer <evanbauer@mac.com>
@ 2018-08-28 17:00 ` Ron <ronljohnsonjr@gmail.com>
2018-08-28 17:13 ` Re: More efficient pg_restore method? Evan Bauer <evanbauer@mac.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ron @ 2018-08-28 17:00 UTC (permalink / raw)
To: Evan Bauer <evanbauer@mac.com>; +Cc: pgsql-admin@lists.postgresql.org
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 <mailto:eb@evanbauer.com>
> +1 646 641 2973
> Skype: evanbauer
>
>
>> On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr@gmail.com
>> <mailto: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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: More efficient pg_restore method?
2018-08-28 16:48 More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
2018-08-28 16:57 ` Re: More efficient pg_restore method? Evan Bauer <evanbauer@mac.com>
2018-08-28 17:00 ` Re: More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
@ 2018-08-28 17:13 ` Evan Bauer <evanbauer@mac.com>
0 siblings, 0 replies; 5+ messages in thread
From: Evan Bauer @ 2018-08-28 17:13 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
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:
>>
>> 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,
>>
>> - Evan
>>
>> Evan Bauer
>> eb@evanbauer.com <mailto:eb@evanbauer.com>
>> +1 646 641 2973
>> Skype: evanbauer
>>
>>
>>> On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr@gmail.com <mailto: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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: More efficient pg_restore method?
2018-08-28 16:48 More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
@ 2018-08-30 17:30 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Ron @ 2018-08-30 17:30 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-08-30 17:30 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-08-28 16:48 More efficient pg_restore method? Ron <ronljohnsonjr@gmail.com>
2018-08-28 16:57 ` Evan Bauer <evanbauer@mac.com>
2018-08-28 17:00 ` Ron <ronljohnsonjr@gmail.com>
2018-08-28 17:13 ` Evan Bauer <evanbauer@mac.com>
2018-08-30 17:30 ` Ron <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox