Re: More efficient pg_restore method?

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(at)evanbauer(dot)com
+1 646 641 2973
Skype: evanbauer

> On Aug 28, 2018, at 13:00, Ron <ronljohnsonjr(at)gmail(dot)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(at)evanbauer(dot)com <mailto:eb(at)evanbauer(dot)com>
>> +1 646 641 2973
>> Skype: evanbauer
>>
>>
>>> On Aug 28, 2018, at 12:48, Ron <ronljohnsonjr(at)gmail(dot)com <mailto:ronljohnsonjr(at)gmail(dot)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.