Thread: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename

"L'Huillier, Jeff" <Jeff.LHuillier@ONSTAR.com> writes:
> When enabling WAL archiving and setting up the archive_command, is it
> possible to add the date & time as an extension to the %f copied to the
> archive directory in order to avoid overwriting a file of the same name?

Why would you think that's necessary?  Please see the recommendations
for archive scripts, which tell you to simply refuse to copy in such a
case.

            regards, tom lane

"L'Huillier, Jeff" <Jeff.LHuillier@ONSTAR.com> wrote:

> When enabling WAL archiving and setting up the archive_command, is
> it possible to add the date & time as an extension to the %f
> copied to the archive directory in order to avoid overwriting a
> file of the same name?

The recommended behavior is to exit the recovery command (or the
script run as the command) with an exit code of 255 when the target
file already exists.  This should never happen unless you
accidentally point two different database clusters to the same
target directory.

> In adding a date-time stamp to the archived file name, would this
> adversely affect the restore_command and render the %f file coming
> back unusable?

Well, you could use a wildcard in the restore command to find a
matching file, but if there is more than one because of the
timestamps added to the filename, which one do you pick?

-Kevin

The scenario I'm looking at...

* Every night the database is able to be taken down for full backup ..
* During the runtime, the WAL files would be copied to an NFS mount
archivedir
* Let's say I keep a week of daily full backups and a week of archived
WAL files
-- Over time, there may be the case where archive dir could have a
duplicate name due to WAL name reuse
-- to avoid this, putting a timestamp on the filename in archivedir
would allow the archive WALS to accumulate without overwrite

I propose this scenario due to a past experience of table-level data
corruption that was not noticed by the Subject Matter Experts for over a
week and it was only by having multiple weeks worth of backups &
redo-logs that the database was able to be corrected...



Would a basic restore_command (restore_command = 'cp
/mnt/server/archivedir/%f %p' ) care if the ../archivedir/%f  filenames
are "00000001000000010000000D.2012-0411.021245" (.YYYY-MMDD.HHMMSS) vs.
just   "00000001000000010000000D"

I'm assuming the restore process works with the files at hand, sorted by
file create/modify timestamp, and not explicit filename...



Thanx!
Jeff

Jeff L'Huillier ( La-Hill-Yer )
JEFF.LHUILLIER@onstar.com

....Snappy little phrases here just get me in trouble ....

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 11, 2012 10:09 AM
To: L'Huillier, Jeff
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 9.0.4/7 WAL Archiving: archive_command add
date-time to cp of filename

"L'Huillier, Jeff" <Jeff.LHuillier@ONSTAR.com> writes:
> When enabling WAL archiving and setting up the archive_command, is it
> possible to add the date & time as an extension to the %f copied to
the
> archive directory in order to avoid overwriting a file of the same
name?

Why would you think that's necessary?  Please see the recommendations
for archive scripts, which tell you to simply refuse to copy in such a
case.

            regards, tom lane

"L'Huillier, Jeff" <Jeff.LHuillier@ONSTAR.com> writes:
> -- Over time, there may be the case where archive dir could have a
> duplicate name due to WAL name reuse

But WAL file names aren't reused.

            regards, tom lane

Then that is the ticket ... I mis-interpreted the Postgres documentation
on the segment files and naming ...

Thank you for the feedback ... It was a very big help and I appreciated
it!!


Thanx!
Jeff

Jeff L'Huillier ( La-Hill-Yer )

....Snappy little phrases here just get me in trouble ....


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 11, 2012 11:39 AM
To: L'Huillier, Jeff
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 9.0.4/7 WAL Archiving: archive_command add
date-time to cp of filename

"L'Huillier, Jeff" <Jeff.LHuillier@ONSTAR.com> writes:
> -- Over time, there may be the case where archive dir could have a
> duplicate name due to WAL name reuse

But WAL file names aren't reused.

            regards, tom lane