Hi all, i am not sure whether i have fully understood the implications of archiving wal-files and i have a few questions.
We currently keep a rather long backlog of wal-files, since we have a few hot-standbys over slow and unreliable lines that might fall back. So this is an extract from my current pg_xlog directory: -rw------- 1 postgres postgres 16777216 Jan 29 16:03 000000010000008C0000008E -rw------- 1 postgres postgres 16777216 Jan 29 16:32 000000010000008C0000008F -rw------- 1 postgres postgres 16777216 Jan 29 16:35 000000010000008C00000090 -rw------- 1 postgres postgres 16777216 Jan 4 15:02 000000010000008C00000091 -rw------- 1 postgres postgres 16777216 Jan 4 15:02 000000010000008C00000092 As you can see it recycles existing files by using them with their exact name as they already exist (next file to be overwritten is the ..91-file). So far so good. I have, just a few minutes ago, set archive_mode to "on" and set an archive_command and restarted the server. Now files start appearing in the wal-archive directory. But i seem to have two "lines" of wal archives in my wal-storage directory, as the following extract shows: -rw------- 1 postgres postgres 16777216 Jan 30 09:03 000000010000008D00000036 -rw------- 1 postgres postgres 16777216 Jan 30 09:05 000000010000008D00000037 -rw------- 1 postgres postgres 16777216 Jan 30 09:09 000000010000008D00000038 -rw------- 1 postgres postgres 16777216 Jan 30 09:16 000000010000008D00000039 ... -rw------- 1 postgres postgres 16777216 Jan 30 09:09 000000010000006D00000016 -rw------- 1 postgres postgres 16777216 Jan 30 09:09 000000010000006D00000017 -rw------- 1 postgres postgres 16777216 Jan 30 09:24 000000010000006D00000018 -rw------- 1 postgres postgres 16777216 Jan 30 09:24 000000010000006D00000019 These files are current. But one is the ..8D.. line and the other one is the ..6D.. line and both lines are being added to. I also have another server that connects to the master via pg_receivexlog and that one only shows the ...8D.. line. My questions are as follows: Why is postgres reusing the old files with the old names ? Is there a difference in the naming of wal-files if archive_mode = on compared to archive_mode = off ? Does archive_mode = on combined with the use of a proper archive-command ensure that the naming of the wal-files will never collide with existing files ? If not, how do i prevent files from being overwritten when using an archive_command ? And how would i restore the needed file names for recovery if i decide to keep one base-backup und then a very long chain of wal-files ? Is storing the output of pg_receivexlog equivalent to using archive_mode = on and using a proper archive_command ? I have spent a more than a few hours on this, but am currently somewhat lost. Thanks in advance, Neil
