Skip site navigation (1) Skip section navigation (2)

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

From: "L'Huillier, Jeff" <Jeff(dot)LHuillier(at)ONSTAR(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
Date: 2012-04-11 15:29:14
Message-ID: 7CF5C5C9FE3CE140AD240A391A7681FBF5CC11@USRN4EX0ONS06.onstar.ad.gm.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(dot)LHUILLIER(at)onstar(dot)com

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

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

"L'Huillier, Jeff" <Jeff(dot)LHuillier(at)ONSTAR(dot)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

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2012-04-11 15:39:01
Subject: Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
Previous:From: Kevin GrittnerDate: 2012-04-11 14:09:17
Subject: Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group