pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
6+ messages / 3 participants
[nested] [flat]
* 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
@ 2012-04-11 13:54 L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 14:08 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 14:09 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 6+ messages in thread
From: L'Huillier, Jeff @ 2012-04-11 13:54 UTC (permalink / raw)
To: pgsql-admin
Hello -
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?
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?
Thanx!
Jeff
Jeff L'Huillier ( La-Hill-Yer )
JEFF.LHUILLIER@onstar.com
....Snappy little phrases here just get me in trouble ....
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
2012-04-11 13:54 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
@ 2012-04-11 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 15:29 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-04-11 14:08 UTC (permalink / raw)
To: L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
2012-04-11 13:54 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 14:08 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-04-11 15:29 ` L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 15:39 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: L'Huillier, Jeff @ 2012-04-11 15:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
2012-04-11 13:54 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 14:08 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 15:29 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
@ 2012-04-11 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 15:44 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-04-11 15:39 UTC (permalink / raw)
To: L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
2012-04-11 13:54 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 14:08 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 15:29 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 15:39 ` Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-04-11 15:44 ` L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
0 siblings, 0 replies; 6+ messages in thread
From: L'Huillier, Jeff @ 2012-04-11 15:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename
2012-04-11 13:54 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
@ 2012-04-11 14:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2012-04-11 14:09 UTC (permalink / raw)
To: Jeff L'Huillier <Jeff.LHuillier@ONSTAR.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-04-11 15:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-04-11 13:54 9.0.4/7 WAL Archiving: archive_command add date-time to cp of filename L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 15:29 ` L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-04-11 15:44 ` L'Huillier, Jeff <Jeff.LHuillier@ONSTAR.com>
2012-04-11 14:09 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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