Re: getting pg_basebackup to use remote destination

On Mon, Dec 31, 2018 at 12:05 PM Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Sun, Dec 30, 2018 at 6:17 PM Chuck Martin <clmartin(at)theombudsman(dot)com>
> wrote:
>
>> Maybe I need to rethink ths and take Jeff's advice. I executed this:
>>
>> pg_basebackup -h [main server's URL] -U postgres -P -v -X s -D
>> /mnt/dbraid/data
>>
>> 8 hours ago, and it is now still at 1%. Should it be that slow? The
>> database in question is about 750 GB, and both servers are on the same GB
>> ethernet network.
>>
>
> Over gigabit ethernet, it should not be that slow. Unless the network is
> saturated with other traffic or something. Might be time to call in the
> network engineers. Can you transfer static files at high speeds between
> those two hosts using scp or rsync? (Or use some other technique to take
> PostgreSQL out of the loop and see if your network is performing as it
> should)
>
> Are you seeing transfers at a constant slow rate, or are their long
> freezes or something? Maybe the initial checkpoint was extremely slow?
> Unfortunately -P option (even with -v) doesn't make this easy to figure
> out. So alas it's back to old school stopwatch and a pen and paper (or
> spreadsheet).
>
> Cheers,
>
> Jeff
>
Using iperf, the transfer speed between the two servers (from the main to
the standby) was 938 Mbits/sec. If I understand the units correctly, it is
close to what it can be.

Your earlier suggestion was to do the pg_basebackup locally and rsync it
over. Maybe that would be faster. At this point, it is saying it is 6%
through, over 24 hours after being started.

Chuck Martin
Avondale Software