Message-ID: <59A231AF.3070107@sqlexec.com>
Date: Sat, 26 Aug 2017 22:42:55 -0400
From: MichaelDBA <MichaelDBA@sqlexec.com>
User-Agent: Postbox 5.0.17 (Windows/20170804)
MIME-Version: 1.0
To: Don Seiler <don@seiler.us>
CC: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Standby Mechanics: WAL vs Streaming
References: 
 <CAHJZqBA35ZnPc0pnxC_XoZUxoXNeBjBWMW0cNtKv9zut+LoJ=A@mail.gmail.com>
In-Reply-To: 
 <CAHJZqBA35ZnPc0pnxC_XoZUxoXNeBjBWMW0cNtKv9zut+LoJ=A@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------090404010805040904010105"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------090404010805040904010105
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

pgbackrest archives WALs faster than a custom archive_command command.  
I use pgbackrest to automate archiving of WALs from primary to 
pgbackrest server.  From there, my restore_command on the slave pulls 
WALs if needed to catch up to SR (streaming replication).

Some of your other questions....

The standby will recover from the streaming xlog data if local WAL files 
are not available. (So the standby prefers local WAL files to streaming?)
The other way around: The standby prefers streaming replication.  It 
only pulls WALs (restore_command) if it is too far behind.

I saw that the WAL archives were being removed from the archive 
directory. If the standby was already ahead of the WAL files because of 
streaming replication, would it still use the restore_command to restore 
WAL files from archive to px_log directory? Or would it just delete the 
archive files?
The archived files in your case (stored on the standby) would only be 
removed if you had  pg_archivecleanup command also in your recovery.conf 
file for WAL cleanup (archive_cleanup_command).

As far as the lag goes, what can we do to increase the throughput for 
streaming replication? We tried to give it almost all of our 100Mb pipe 
between data centers and there was still significant lag until it 
recovered past the point of my manual vacuums.
What do you expect?  Manual vacuum full on big tables will generate a 
lot of WAL thereby causing SR lag.

The archive_command on the primary seemed to run serial and wait, since 
it took 4-5 seconds to rsync each 16Mb WAL file from primary to standby. 
Is there any way to parallelize this? I guess we could just have 
archive_command do a local copy and then have a separate script in cron 
to handle some multiple rsync calls (with a max concurrency, of course)?
See my solution using pgbackrest above.

I'm going to assume that having both streaming replication and WAL 
rsyncing are going to compete for bandwidth. Would it be advisable to 
only do streaming most of the day and then maybe rsync WAL files in a 
nightly batch? Now that I think of it, we do hourly snapmirrors of that 
WAL storage already (also using the same bandwidth), perhaps we just use 
that for the WAL replication.
How about doing your manual vacuums at night time during low load times?

Regards,
Michael Vitale


--------------090404010805040904010105
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html><head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head><body bgcolor="#FFFFFF" text="#000000">pgbackrest archives WALs 
faster than a custom archive_command command.  I use pgbackrest to 
automate archiving of WALs from primary to pgbackrest server.  From 
there, my restore_command on the slave pulls WALs if needed to catch up 
to SR (streaming replication). <br>
<br>
Some of your other questions....<br>
<br>
The standby will recover from the streaming xlog data if local WAL files
 are not available. (So the standby prefers local WAL files to 
streaming?)<br>
<span style="color: rgb(195, 38, 13);">The other way around: The standby
 prefers streaming replication.  It only pulls WALs (restore_command) if
 it is too far behind.</span><br>
<span>

</span><br>
I saw that the WAL archives were being removed from the archive 
directory. If the standby was already ahead of the WAL files because of 
streaming replication, would it still use the restore_command to restore
 WAL files from archive to px_log directory? Or would it just delete the
 archive files?<br>
<span style="color: rgb(195, 38, 13);">The archived files in your case 
(stored on the standby) would only be removed if you had  
pg_archivecleanup command also in your recovery.conf file for WAL 
cleanup (archive_cleanup_command)</span>.<br>
<br>
As far as the lag goes, what can we do to 
increase the throughput for streaming replication? We tried to give it 
almost all of our 100Mb pipe between data centers and there was still 
significant lag until it recovered past the point of my manual vacuums.<br>
<span style="color: rgb(195, 38, 13);">What do you expect?  Manual 
vacuum full on big tables will generate a lot of WAL thereby causing SR 
lag.</span> <br>
<br>
The
 archive_command on the primary seemed to run serial and wait, since it 
took 4-5 seconds to rsync each 16Mb WAL file from primary to standby. Is
 there any way to parallelize this? I guess we could just have 
archive_command do a local copy and then have a separate script in cron 
to handle some multiple rsync calls (with a max concurrency, of course)?<br>
<span style="color: rgb(195, 38, 13);">See my solution using pgbackrest 
above.</span><br>
<br>
I'm
 going to assume that having both streaming replication and WAL rsyncing
 are going to compete for bandwidth. Would it be advisable to only do 
streaming most of the day and then maybe rsync WAL files in a nightly 
batch? Now that I think of it, we do hourly snapmirrors of that WAL 
storage already (also using the same bandwidth), perhaps we just use 
that for the WAL replication.<br>
<span style="color: rgb(195, 38, 13);">How about doing your manual 
vacuums at night time during low load times?</span><br>
<br>
Regards,<br>
Michael Vitale<br>
<br>
</body></html>

--------------090404010805040904010105--