From: MichaelDBA <MichaelDBA@sqlexec.com>
To: Don Seiler <don@seiler.us>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Standby Mechanics: WAL vs Streaming
Date: Sat, 26 Aug 2017 22:42:55 -0400
Message-ID: <59A231AF.3070107@sqlexec.com> (raw)
In-Reply-To: <CAHJZqBA35ZnPc0pnxC_XoZUxoXNeBjBWMW0cNtKv9zut+LoJ=A@mail.gmail.com>
References: <CAHJZqBA35ZnPc0pnxC_XoZUxoXNeBjBWMW0cNtKv9zut+LoJ=A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: MichaelDBA@sqlexec.com, don@seiler.us
Subject: Re: Standby Mechanics: WAL vs Streaming
In-Reply-To: <59A231AF.3070107@sqlexec.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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