Fwd: naming of wal-archives

Sorry, this one should have been sent to the group.

---------- Forwarded message ----------

Hi,

Master M -> streaming via pg_receivexlog -> TEST R (same location,
currently for testing and experimenting)
-> streaming to hot standby via dsl -> HOT1 (other location,
hot-standby and used for read-queries)
-> archiving to /data/backup/walbackup on Master M via
archive_command (as of 2 days ago)

Up until 2 days ago, archiving on the master was not enabled, but wal-files
were streamed to the hot-standby HOT1 and TESTR and wal_keep_segments was
all the time set to 8192 (which currently amounts to a timeframe of about 3
weeks). Everything was fine. Then, two days ago, i set archive_mode = on
and archive_command to 'test ! -f /data/backup/walbackup/%f && cp %p
/data/backup/walbackup/%f'.

This is a current extract from the master pg_xlog directory (with ls -la |
less):

-rw------- 1 postgres postgres 16777216 Jan 6 00:46
000000010000006E00000061
-rw------- 1 postgres postgres 16777216 Jan 6 00:46
000000010000006E00000062
-rw------- 1 postgres postgres 16777216 Jan 6 00:46
000000010000006E00000063
-rw------- 1 postgres postgres 16777216 Jan 6 00:46
000000010000006E00000064
...
-rw------- 1 postgres postgres 16777216 Jan 31 15:06
000000010000008E0000007F
-rw------- 1 postgres postgres 16777216 Jan 31 15:30
000000010000008E00000080
-rw------- 1 postgres postgres 16777216 Jan 31 15:59
000000010000008E00000081
-rw------- 1 postgres postgres 16777216 Jan 31 16:13
000000010000008E00000082
-rw------- 1 postgres postgres 16777216 Jan 5 21:01
000000010000008E00000083
-rw------- 1 postgres postgres 16777216 Jan 5 21:01
000000010000008E00000084
-rw------- 1 postgres postgres 16777216 Jan 5 21:01
000000010000008E00000085
-rw------- 1 postgres postgres 16777216 Jan 5 21:01
000000010000008E00000086

As you can see, the oldest wal-files in pg_xlog on the master date back to
the 5th of january and are subsequently overwritten. Wal-files from the
...6E... range were last modified around 6th of january.

Now this is a current extract from masters-wal-archive directory
/data/backup/walbackup, where the archive-command is copying files to:

...
-rw------- 1 postgres postgres 16777216 Jan 31 15:21
000000010000006E0000005F
-rw------- 1 postgres postgres 16777216 Jan 31 15:30
000000010000008E00000080
-rw------- 1 postgres postgres 16777216 Jan 31 15:32
000000010000006E00000060
-rw------- 1 postgres postgres 16777216 Jan 31 15:59
000000010000008E00000081
-rw------- 1 postgres postgres 16777216 Jan 31 16:03
000000010000006E00000061
-rw------- 1 postgres postgres 16777216 Jan 31 16:20
000000010000008E00000082

As you can see, the filenames alternate between ...6E... and ...8E...(in
the middle) but they are created by the same archiver-process. Why are
files name ...6E... appearing in my wal-archive when the last ...6E.. file
in my pg_xlog was modified more than 3 weeks ago ? Also the ...6E...
wal-files never appear on the pg_receivexlog-machine.

Some of the comments suggested there were multiple masters writing to the
same archive-directory. That is not the case, there is only one master. The
receivers and standbys are all on different physical machines or even in
other locations.

I put the ps-output in my two previous mails to show that it is indeed the
same archiver process (same process-id in both mails) that is writing the
files

postgres 11503 0.0 0.0 20136 884 ? Ss Jan29 0:10 postgres:
archiver process last was 000000010000006E00000034

and a few seconds later the same process is writing:

postgres 11503 0.0 0.0 20136 884 ? Ss Jan29 0:10 postgres:
archiver process last was 000000010000008E00000058

(note the 6E vs the 8E in the middle)

So it seems to be archiving two "lines" or sequences of wal-archives, which
i do not understand. All this started exactly 3 minutes after i set the
archive_command and enabled archiving.

Is this normal behaviour, or am i getting something fundamentally wrong ?

Thanks, Neil

ps.: These things cast aside, i still do not understand how to implement
the "One base-backup + an endless stream of wal files" backup-strategy if
the files in my wal archive get overwritten and i don´t "save" them to
another place before that happens.

2013/1/31 Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>

> On 01/31/2013 01:48 AM, Neil Worden wrote:
>
>>
>> Btw, ps shows:
>>
>>
>
>> The archiver process says "last was 000000010000006E00000034" and when i
>> look into my wal-archive-directory i see:
>>
>> -rw------- 1 postgres postgres 16777216 Jan 31 10:24
>> 000000010000006E00000033
>> -rw------- 1 postgres postgres 16777216 Jan 31 10:24
>> 000000010000006E00000034
>> -rw------- 1 postgres postgres 16777216 Jan 29 16:03
>> 000000010000008C0000008E
>> -rw------- 1 postgres postgres 16777216 Jan 29 16:32
>> 000000010000008C0000008F
>>
>> The 6E..34 file was just written by the archiver process. But further
>> down at the same time this file was written:
>>
>> ...
>> -rw------- 1 postgres postgres 16777216 Jan 31 10:24
>> 000000010000008E00000054
>>
>> which seems to match the position of the streaming wal-senders .
>>
>> Any ideas ?
>>
>
> Yes, I think you are going to have to map this out for us.
>
> Something like:
>
> pg_receivexlog to ?
> /
> Machine A (Master) Archiving on ----> /some_archive_dir
> \
> Machine B (standby)
> \
> Machine C (standby)
>
> I cannot follow what is going on from the written description.
>
>
>> Thanks, Neil.
>>
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>