pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedarchive_command options?
5+ messages / 5 participants
[nested] [flat]
* archive_command options?
@ 2019-04-03 20:16 LEROY TENNISON <leroy_tennison@prodigy.net>
2019-04-04 00:18 ` Re: archive_command options? Jerry Sievers <gsievers19@comcast.net>
2019-04-04 11:36 ` Re: archive_command options? Johannes Truschnigg <johannes@truschnigg.info>
2019-04-04 13:27 ` Re: archive_command options? Keith Fiske <keith.fiske@crunchydata.com>
2019-04-04 21:14 ` Re: archive_command options? Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 4 replies; 5+ messages in thread
From: LEROY TENNISON @ 2019-04-03 20:16 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: archive_command options?
2019-04-03 20:16 archive_command options? LEROY TENNISON <leroy_tennison@prodigy.net>
@ 2019-04-04 00:18 ` Jerry Sievers <gsievers19@comcast.net>
3 siblings, 0 replies; 5+ messages in thread
From: Jerry Sievers @ 2019-04-04 00:18 UTC (permalink / raw)
To: LEROY TENNISON <leroy_tennison@prodigy.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: archive_command options?
2019-04-03 20:16 archive_command options? LEROY TENNISON <leroy_tennison@prodigy.net>
@ 2019-04-04 11:36 ` Johannes Truschnigg <johannes@truschnigg.info>
3 siblings, 0 replies; 5+ messages in thread
From: Johannes Truschnigg @ 2019-04-04 11:36 UTC (permalink / raw)
To: LEROY TENNISON <leroy_tennison@prodigy.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: archive_command options?
2019-04-03 20:16 archive_command options? LEROY TENNISON <leroy_tennison@prodigy.net>
@ 2019-04-04 13:27 ` Keith Fiske <keith.fiske@crunchydata.com>
3 siblings, 0 replies; 5+ messages in thread
From: Keith Fiske @ 2019-04-04 13:27 UTC (permalink / raw)
To: LEROY TENNISON <leroy_tennison@prodigy.net>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.
https://pgbackrest.org/
--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: archive_command options?
2019-04-03 20:16 archive_command options? LEROY TENNISON <leroy_tennison@prodigy.net>
@ 2019-04-04 21:14 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
3 siblings, 0 replies; 5+ messages in thread
From: Peter Eisentraut @ 2019-04-04 21:14 UTC (permalink / raw)
To: LEROY TENNISON <leroy_tennison@prodigy.net>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-04-04 21:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-04-03 20:16 archive_command options? LEROY TENNISON <leroy_tennison@prodigy.net>
2019-04-04 00:18 ` Jerry Sievers <gsievers19@comcast.net>
2019-04-04 11:36 ` Johannes Truschnigg <johannes@truschnigg.info>
2019-04-04 13:27 ` Keith Fiske <keith.fiske@crunchydata.com>
2019-04-04 21:14 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
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