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.klaver@xxxxxxxxx>
On 01/31/2013 01:48 AM, Neil Worden wrote:
Btw, ps shows:
Yes, I think you are going to have to map this out for us.
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 ?
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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
