Обсуждение: archive_command options?
Postgresql documentation lists the following as an archive command for log shipping:
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
The problem is that it is very fragile. If anything breaks the copy (either system failing, network issues) then log shipping stops because the file exists in some intermediate state. The result is that logs build up on the source system filling disk space. I've had to deal with a few of these this week, in two cases the file system was 98% full. Is there a reason rsync isn't used? It has the ability to restart interrupted transfers. I don't want to use it only to find out i got myself into another predicament. Any other less-fragile options? Thanks for the help.
LEROY TENNISON <leroy_tennison@prodigy.net> writes: > Postgresql documentation lists the following as an archive command > for log shipping: > > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/ > server/archivedir/%f' > > The problem is that it is very fragile. If anything breaks the copy > (either system failing, network issues) then log shipping stops > because the file exists in some intermediate state. The result is > that logs build up on the source system filling disk space. I've had > to deal with a few of these this week, in two cases the file system > was 98% full. Is there a reason rsync isn't used? It has the > ability to restart interrupted transfers. I don't want to use it > only to find out i got myself into another predicament. Any other > less-fragile options? Thanks for the help. You are free to use any $better thing as archive command. The classic example given in the docs is appealing for simplicity's sake. I don't understand your piece about "restart interrupted transfers". Postgres will only archive 1 file at a time anyhow and will retry/restart till whatever your archive_command is returns true. 16M WAL segments aren't these days large enough IMO that being able to carry on a half-copied file is compelling. HTH > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net
Hello Leroy, I was unhappy with the situation you described as well, and wrote a trivial (but better -- although not perfect :)) replacement in C a while ago. You can find it at [0]. It has served us well for the past five or so years, handling hundreds of GB of WAL archives each day. Maybe you find it useful, too. [0]: https://johannes.truschnigg.info/code/pg_archive_wal_segment-2.0.0/ -- with best regards: - Johannes Truschnigg ( johannes@truschnigg.info ) www: https://johannes.truschnigg.info/ phone: +43 650 2 133337 xmpp: johannes@truschnigg.info Please do not bother me with HTML-email or attachments. Thank you.
Вложения
On Wed, Apr 3, 2019 at 4:18 PM LEROY TENNISON <leroy_tennison@prodigy.net> wrote:
Postgresql documentation lists the following as an archive command for log shipping:archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'The problem is that it is very fragile. If anything breaks the copy (either system failing, network issues) then log shipping stops because the file exists in some intermediate state. The result is that logs build up on the source system filling disk space. I've had to deal with a few of these this week, in two cases the file system was 98% full. Is there a reason rsync isn't used? It has the ability to restart interrupted transfers. I don't want to use it only to find out i got myself into another predicament. Any other less-fragile options? Thanks for the help.
It is definitely recommended to use something more reliable the cp command. The docs are just an example that your own command can be used for the archive_command.
I'd recommend one of the more well maintained backup tools out there such as pgbackrest. They have very robust archive & restore command options that are made to handle failure situations much more cleanly. They also provide good automated retention options.
On 2019-04-03 22:16, LEROY TENNISON wrote: > Postgresql documentation lists the following as an archive command for > log shipping: > > archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p > /mnt/server/archivedir/%f' > > The problem is that it is very fragile. A more robust alternative is to use pg_receivewal. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services