Re: [ADMIN] Standby Mechanics: WAL vs Streaming

Поиск
Список
Период
Сортировка
От MichaelDBA
Тема Re: [ADMIN] Standby Mechanics: WAL vs Streaming
Дата
Msg-id 59A231AF.3070107@sqlexec.com
обсуждение исходный текст
Ответ на [ADMIN] Standby Mechanics: WAL vs Streaming  (Don Seiler <don@seiler.us>)
Ответы Re: [ADMIN] Standby Mechanics: WAL vs Streaming
Список pgsql-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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Don Seiler
Дата:
Сообщение: [ADMIN] Standby Mechanics: WAL vs Streaming
Следующее
От: Don Seiler
Дата:
Сообщение: Re: [ADMIN] Standby Mechanics: WAL vs Streaming