Re: naming of wal-archives - Mailing list pgsql-general

On Wed, Jan 30, 2013 at 12:58 AM, Neil Worden <nworden1234@gmail.com> wrote:
>
> 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.

It sure sounds like you have two independent masters that are writing
to the same archive location.  That is not good.

Shut down what you believe to be your only master, and see if both
streams stop.  Then start it up and see if both streams start again.


> 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 ?

No.

> If not, how do i prevent files from being overwritten when using an
> archive_command ?

It is the archive_command's job to refuse to overwrite.  From the docs:

"It is advisable to test your proposed archive command to ensure that
it indeed does not overwrite an existing file, and that it returns
nonzero status in this case"

Hopefully you have done this, otherwise bad things may happen when the
6D line collides with the 8D line.

If your command does overwrite, then the server currently emitting the
8D files will become unrecoverable once those files start getting
overwritten.  If it refuses to overwrite, but returns a zero status,
then the server currently emitting 6D would become unrecoverable once
it reaches 8D and its "archived" files are not actually being archived
but are getting deleted from the local pg_xlog anyway.

> 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
> ?

There should be no need for that.

Cheers,

Jeff