Thread: [GENERAL] Logging the fact that a log was shipped
Hi, How is this done in v8.4? (I tried adding "date; rsync ..." but pg didn't like that *at all*.) Thanks
## Ron Johnson (ron.l.johnson@cox.net): > How is this done in v8.4? (I tried adding "date; rsync ..." but pg > didn't like that *at all*.) There's a DEBUG1-level log message on successful archive_command completion - that would give you a lot of other low-prio log messages wich you probably don't care about. I'd put a wrapper around your rsync (a short shell script would be sufficient) in the lines of rsync ... && logger "done", that way you'd get the information via syslog. On the other hand, do you really need the details about each WAL segment? Since 9.4 there's pg_stat_wal_archiver... You're really making your job harder than it needs to be with that ancient PostgreSQL... Regards, Christoph -- Spare Space.
On 08/28/2017 06:06 AM, Christoph Moench-Tegeder wrote: > ## Ron Johnson (ron.l.johnson@cox.net): > >> How is this done in v8.4? (I tried adding "date; rsync ..." but pg >> didn't like that *at all*.) > There's a DEBUG1-level log message on successful archive_command > completion - that would give you a lot of other low-prio log > messages wich you probably don't care about. > I'd put a wrapper around your rsync (a short shell script > would be sufficient) in the lines of rsync ... && logger "done", > that way you'd get the information via syslog. And if logging to stderr? > On the other hand, do you really need the details about each WAL > segment? Since 9.4 there's pg_stat_wal_archiver... You're really > making your job harder than it needs to be with that ancient > PostgreSQL... That's far beyond my control. -- World Peace Through Nuclear Pacification
* Christoph Moench-Tegeder (cmt@burggraben.net) wrote: > ## Ron Johnson (ron.l.johnson@cox.net): > > > How is this done in v8.4? (I tried adding "date; rsync ..." but pg > > didn't like that *at all*.) > > There's a DEBUG1-level log message on successful archive_command > completion - that would give you a lot of other low-prio log > messages wich you probably don't care about. > I'd put a wrapper around your rsync (a short shell script > would be sufficient) in the lines of rsync ... && logger "done", > that way you'd get the information via syslog. > On the other hand, do you really need the details about each WAL > segment? Since 9.4 there's pg_stat_wal_archiver... You're really > making your job harder than it needs to be with that ancient > PostgreSQL... Worse, such scripts run the serious risk of losing WAL if a crash happens because nothing is ensuring that the WAL has been sync'd to disk before returning from the archive_command. Most of the existing tools for dealing with WAL archival (pgbackrest, barman and WAL-E, at least) already log successful and unsuccessful archive command runs. I'm pretty sure barman supports back to 8.4 and I know pgbackrest does. Thanks! Stephen
Attachment
On 08/28/2017 08:22 AM, Stephen Frost wrote: > * Christoph Moench-Tegeder (cmt@burggraben.net) wrote: >> ## Ron Johnson (ron.l.johnson@cox.net): >> >>> How is this done in v8.4? (I tried adding "date; rsync ..." but pg >>> didn't like that *at all*.) >> There's a DEBUG1-level log message on successful archive_command >> completion - that would give you a lot of other low-prio log >> messages wich you probably don't care about. >> I'd put a wrapper around your rsync (a short shell script >> would be sufficient) in the lines of rsync ... && logger "done", >> that way you'd get the information via syslog. >> On the other hand, do you really need the details about each WAL >> segment? Since 9.4 there's pg_stat_wal_archiver... You're really >> making your job harder than it needs to be with that ancient >> PostgreSQL... > Worse, such scripts run the serious risk of losing WAL if a crash > happens because nothing is ensuring that the WAL has been sync'd to disk > before returning from the archive_command. > > Most of the existing tools for dealing with WAL archival (pgbackrest, > barman and WAL-E, at least) already log successful and unsuccessful > archive command runs. I'm pretty sure barman supports back to 8.4 and I > know pgbackrest does. Thanks for the info on pgbackrest. -- World Peace Through Nuclear Pacification
## Stephen Frost (sfrost@snowman.net): > Worse, such scripts run the serious risk of losing WAL if a crash > happens because nothing is ensuring that the WAL has been sync'd to disk > before returning from the archive_command. That risk already exists when using rsync/scp/scp/... and should be mitigated by filesystem settings on the receiving side. > Most of the existing tools for dealing with WAL archival (pgbackrest, > barman and WAL-E, at least) already log successful and unsuccessful > archive command runs. I'm pretty sure barman supports back to 8.4 and I > know pgbackrest does. Barman would have to use rsync for the archiving with PostgreSQL 8.4 (pg_receivexlog only came along in 9.2, replication slots in 9.4). Logging will not happen on completion of archive_command (barman doesn't know much about that) but only when the xlog segment is being processed by barman - which could be quite some time later. Regards, Christoph -- Spare Space.
## Ron Johnson (ron.l.johnson@cox.net): > > I'd put a wrapper around your rsync (a short shell script > > would be sufficient) in the lines of rsync ... && logger "done", > > that way you'd get the information via syslog. > > And if logging to stderr? In that case your original approach could have worked - after all, archive_command is run via a simple system() call. It might depend on the logging collector and how you started PostgreSQL in the first place... I'm not sure why it didn't work for you, but your original mail didn't have much detail in that area. Perhaps just add a -v to the rsync command line and see what happens? Regards, Christoph -- Spare Space.
* Christoph Moench-Tegeder (cmt@burggraben.net) wrote: > ## Stephen Frost (sfrost@snowman.net): > > > Worse, such scripts run the serious risk of losing WAL if a crash > > happens because nothing is ensuring that the WAL has been sync'd to disk > > before returning from the archive_command. > > That risk already exists when using rsync/scp/scp/... and should be > mitigated by filesystem settings on the receiving side. I was including rsync/scp/similar based tools, yes, just pointing out that such tools should be avoided when doing PG backups and WAL archiving. I have a hard time seeing "require filesystems be mounted as sync" to really be a viable solution, though I suppose it would be technically correct. Thanks! Stephen