SEO

Hi pgsql-list-members,
I currently write a small script that deletes outdated xlog-files from
my backup-location.
Because I do not want to rely on creation-date, I found it usable to use
the result of
ln | sort -g -r
Thus the newest WAL xlog-file is on top and I can delete all not needed
files at the bottom of the list.

My question: Is it for ALL cases guaranteed, that the naming of the
WAL-files in $PGDATA/pg_xlog always produces a "higher" number for a
newer file?
What happens if the 24hexdigits reach upper bound?

Thank your for your replies on that issue of postgresql inner working model.
Regards Johannes

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Johannes Konert wrote:
> Hi pgsql-list-members,
> I currently write a small script that deletes outdated xlog-files from
> my backup-location.
> Because I do not want to rely on creation-date, I found it usable to use
> the result of
> ln | sort -g -r
> Thus the newest WAL xlog-file is on top and I can delete all not needed
> files at the bottom of the list.

Warning, this is NOT SAFE to do. You should NEVER delete "outdated"
xlog files, unless you appreciate RANDOM CORRUPTION of your data.


Not sure how those caps sneaked in there, sorry about that.

Have a nice day,

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera wrote:
> Johannes Konert wrote:
>
>> Hi pgsql-list-members,
>> I currently write a small script that deletes outdated xlog-files from
>> my backup-location.
>> Because I do not want to rely on creation-date, I found it usable to use
>> the result of
>> ln | sort -g -r
>> Thus the newest WAL xlog-file is on top and I can delete all not needed
>> files at the bottom of the list.
>>
>
> Warning, this is NOT SAFE to do. You should NEVER delete "outdated"
> xlog files, unless you appreciate RANDOM CORRUPTION of your data.
>
I think he's talking about deleting pg_xlog files that are being used
for PITR from the backup machine after they've been applied.

But I'm not sure that's really what he meant or not.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Wed, 13 Jun 2007, Alvaro Herrera wrote:

> Johannes Konert wrote:
>> I currently write a small script that deletes outdated xlog-files from
>> my backup-location.
>
> Warning, this is NOT SAFE to do. You should NEVER delete "outdated"
> xlog files, unless you appreciate RANDOM CORRUPTION of your data.

He's talking about wiping out the ones on the backup server, so I think
Johannes means erasing the old archived logs on the secondary here. That
can screw up your backup if you do it wrong, but it's not an all-caps
worthy mistake.

On Wed, 13 Jun 2007, Johannes Konert wrote:
> Because I do not want to rely on creation-date,

No, you want to rely on creation date, because then this problem goes
away. The idea you should be working toward is that you identify when
your last base backup was started after it's copied to the secondary, and
then you can safely delete any archived logs file on the secondary from
before that time. Instead of doing "ls | sort -g -r" you should be doing
something like looping over the files in a bash shell script and using
[ -ot <first xlog in base backup> ] to determine which files to delete.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Greg Smith wrote:
> He's talking about wiping out the ones on the backup server, so I
> think Johannes means erasing the old archived logs on the secondary
> here. That can screw up your backup if you do it wrong, but it's not
> an all-caps worthy mistake.
yes, that's what I am talking about related to
http://www.postgresql.org/docs/8.2/i...archiving.html.
Sorry, if that did not came out clearly enough.
>
> On Wed, 13 Jun 2007, Johannes Konert wrote:
>> Because I do not want to rely on creation-date,
>
> No, you want to rely on creation date, because then this problem goes
> away.
Truely right...if I can gurantee, that the file-dates of my archived
WAL-files do have proper timestamps. If the timestamps once are messed
up and all have the same timestamp (due to a Windows-copy or something
else foolish), then the delete-script might delete the wrong files...
> The idea you should be working toward is that you identify when your
> last base backup was started after it's copied to the secondary, and
> then you can safely delete any archived logs file on the secondary
> from before that time. Instead of doing "ls | sort -g -r" you should
> be doing something like looping over the files in a bash shell script
> and using
> [ -ot <first xlog in base backup> ] to determine which files to delete.
right; but as I said, then I rely on file-dates.
But during the day I came out with an solution: I store the WAL-files
with the time-stamp of archiving in their file-name. Thus I can order
and delete them safely.
Your hint was the one, that helped me to find that solution - so thanks
for that, Greg.....and the others.

Regards,
Johannes


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Johannes Konert wrote:
> But during the day I came out with an solution: I store the WAL-files
> with the time-stamp of archiving in their file-name. Thus I can order
> and delete them safely.
> Your hint was the one, that helped me to find that solution - so
> thanks for that, Greg.....and the others.
That solution has still a problem: It workes fine in case that the
WAL-naming restarts with 000000000000000000000001, because the attached
timestamp in name would still make it possible to identify the file as
being a newer one as FFFFFFFFFFFFFFFFFFFFFFFF, but there is still the
problem with shifts in time itself.
If someone corrects the servers computer-time/date to a date before
current time (e.g. set the clock two hours back), then the newer WAL
files will have an older timestamp and will be deleted by accident.

Thus now I increase the number of characters of the filename to infinite
and the last 24 characters are the WAL file name. Thus the archived
filenames ~always~ increase in naming and all backup files before the
last base backup can be safely identified not relying on computer
timestamps or with the risk of a restart in naming by postgresql.
I hope this solutions only border is the 255 character restriction of
file-name length....but if that one will be reached in future times I am
sure longer names are possible

Regards Johannes


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Wed, 13 Jun 2007, Johannes Konert wrote:

> If someone corrects the servers computer-time/date to a date before current
> time (e.g. set the clock two hours back), then the newer WAL files will have
> an older timestamp and will be deleted by accident.

This should never happen; no one should ever touch the clock by hand on a
production system. The primary and backup server should both be
syncronized via NTP. If you're thinking about clock changes for daylight
savings time, those shouldn't have any effect on timestamps, which should
be stored in UTC. If you're on Windows, I recommend reading
http://searchwinit.techtarget.com/ti...241193,00.html and
http://www.wilsonmar.com/1clocks.htm if you're not familiar with how
UTC/NTP insulate you from this issue. On many types of systems that
process time-sensitive data, an administrator adjusting the clock manually
is considered a dangerous event that is specificly scheduled so issues
like you're concerned about don't happen--and someone who tinkers with the
clock without following that procedure would be in serious trouble.

You're working hard to worry about problems that should be eliminated by
the overall design of your system. If you can't trust your system clocks
and that files are being copied with their attributes intact, you should
consider thinking about how to resolve those problems rather than working
around them. It's not just PostgreSQL that will suffer from weird,
unpredictable behavior in a broken environment like that. Giving a
Windows example, if you're running in a Windows Domain configuration, if
the client time drifts too far from the server you can get "The system
cannot log you on due to the following error: There is a time difference
between the Client and Server." when trying to login.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Hello Johannes,

Johannes Konert schrieb:
> Thus the newest WAL xlog-file is on top and I can delete all not needed
> files at the bottom of the list.

You're using pg_controldata to figure out which file's serial is older
than the latest redo checkpoint.
In case of restart of the slave server PgSQL needs all files that were
archived beginning with the one right after the latest redo checkpoint
or it will fail to sync to its master.


> What happens if the 24hexdigits reach upper bound?

Did you calculate you question? I assume no.

24 Hex digits means 24^16 unique file names. Assuming your server saves
a WAL file each second (you should review your config it it does) it
takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper
bound. (Plase forgive me ignoring leap years )
I assume that there will be a system change before that date so counting
will start over again.

Greetings,
Frank Wittig


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQFGcDjcx6tzaD9P7mkRApeNAJ49L13c/f9dvUydI1rkN2BnPEe+2ACbBqFt
xWY9+v4wR9aeKaiZHM+KowU=
=HuHB
-----END PGP SIGNATURE-----

Frank Wittig schrieb:

> 24 Hex digits means 24^16 unique file names. Assuming your server saves
> a WAL file each second (you should review your config it it does) it
> takes (24^16)/(60*60*24*365)=3.84214066×10^14 years to reach the upper
> bound.

How embarrassing - I messed up the calculation. It has to be 16^24.
But pg does forge filenames other that that. It uses 2 hex digits to
count segments. After 256 segments counting starts over and the serial
is increased by one. The first 8 positions are the time line which I
will ignore for my new calculation.

So there is an eight hex digits serial for each time line which takes
256 segments. So there are 16^8*256 unique file names. If I assume one
WAL file a second this would reach upper bound (for a single time line)
after slightly more than 136 years.

Please correct me if my assumptions are wrong. But I would say one can
rely on serial file names to increase steadily.
The attached restore.pl uses this assumption to delete all files which
are older than the last redo checkpoint.

Greetings,
Frank Wittig

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQFGcEf4x6tzaD9P7mkRArkFAJ965Io8SNK6wr7IzITnyI bz08FTngCfUnM2
6oRvQc8AlI1cn3MIYXLQXdw=
=Srjb
-----END PGP SIGNATURE-----

Greg Smith wrote:
> On Wed, 13 Jun 2007, Johannes Konert wrote:
>
>> If someone corrects the servers computer-time/date to a date before
>> current time (e.g. set the clock two hours back), then the newer WAL
>> files will have an older timestamp and will be deleted by accident.
>
> This should never happen; no one should ever touch the clock by hand
> on a production system. The primary and backup server should both be
> syncronized via NTP. If you're thinking about clock changes for
> daylight savings time, those shouldn't have any effect on timestamps,
> which should be stored in UTC. If you're on Windows,
Its not Windows; it will be Debian Linux.
I completely agree with you that of course our servers synchronize
themselve via NTP with global time, but we already had the case that -
for some reasons - NTP did not work and times drift away from each
other. If you have to manage some servers you might not recognize that a
NTP daemon does not work anymore or that a new firewall prohibits these
TCP packages now....and time goes by, because everything seem to work
just fine.
Then one nice day you realize, that one, two or many of your servers
just have their own time and you need to bring them back to synchronized
time while they are online. If you made your applications be aware of
such effects and use system-nanotime or global counters where possible,
then even these time-corrections can be handled.
But I agree with you: of course normally this will never happen...but it
happened once.
>
> You're working hard to worry about problems that should be eliminated
> by the overall design of your system. If you can't trust your system
> clocks and that files are being copied with their attributes intact,
> you should consider thinking about how to resolve those problems
> rather than working around them.
yes, but still there is a remaining risk in my opinion.
> It's not just PostgreSQL that will suffer from weird, unpredictable
> behavior in a broken environment like that. Giving a Windows example,
> if you're running in a Windows Domain configuration, if the client
> time drifts too far from the server you can get "The system cannot log
> you on due to the following error: There is a time difference between
> the Client and Server." when trying to login.
If we add a new server to the cluster, the application will check times
as it is in oyur Windows-example, but if it is allready in and working,
then it cannot simply shutdown in case of time-diffs.

Greg, thanks for your sophisticated hints.
But the thread is going a little off-topic now, I guess
The issue with the time-dependency of WAL archiving and deletion
issolved for me by using a global infinite counter to rely on by now.
I am sure next questions will come before long and I look forward to
read any hints then, if you and others have time to read them.
Regards Johannes

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq