Thread: naming of wal-archives
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
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
On 01/30/2013 11:16 AM, Jeff Janes wrote: > On Wed, Jan 30, 2013 at 12:58 AM, Neil Worden <nworden1234@gmail.com> wrote: > > >> 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. Would it not be easier to archive the different servers to different directories and eliminate the possibility of name collision between servers? > Cheers, > > Jeff > > -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Jan 30, 2013 at 3:13 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On 01/30/2013 11:16 AM, Jeff Janes wrote: >> >> On Wed, Jan 30, 2013 at 12:58 AM, Neil Worden <nworden1234@gmail.com> >> wrote: >> >> >>> 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. > > > Would it not be easier to archive the different servers to different > directories and eliminate the possibility of name collision between servers? Easier? I would say that that is the only sane way of doing it. I was pointing out the consequences of messing it up. A proper archive_command will save you from some self-inflicted disasters, but that does not mean I'm recommending that you should invite those disasters on yourself. If the original author is in a production environment, he desperately needs to figure out what is going on, especially so if archive_command is not tested and verified to obey its contract. Cheers, Jeff
>>> 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.
>>
>>
>> Would it not be easier to archive the different servers to different
>> directories and eliminate the possibility of name collision between servers?
>Easier? I would say that that is the only sane way of doing it. I
>was pointing out the consequences of messing it up. A proper
>archive_command will save you from some self-inflicted disasters, but
>that does not mean I'm recommending that you should invite those
>disasters on yourself.
>If the original author is in a production environment, he desperately
>needs to figure out what is going on, especially so if archive_command
>is not tested and verified to obey its contract.
>Cheers,
>Jeff
Thanks for your answers.
Yes, we are in a production environment and there are definitely no two masters writing to the wal-archive directory, at least none that i am aware of. And i can assure you that we are not intentionally working on inviting disasters either :-).
Point is, the ..6D.. line started showing up exactly after i set up the archive-command (which is btw: archive_command = 'test ! -f /data/backup/walbackup/%f && cp %p /data/backup/walbackup/%f', running on Ubuntu Linux Server)
The situation is as follows:
All concerned machines are running 9.2.2 64-bit on Ubuntu Linux Server 12.10, installed from source, all following exactly the same procedure. We have a hot-standby running to a different location over a rather thin line running since version 9.1 came out. That worked
flawlessly, we only were bitten by autovacuums to prevent XID wraparounds that generated relatively high wal-volume and we
were not sure whether the network connection could keep up with it before deleting wal-files. Since we had to physically transfer a backup once for other reasons, we set wal_keep_segments to 8192 to have enough fallback-time.
The total size of the database is currently at 313 GB and we are in the process of rethinking our backup/emergency-strategy (currently daily full dumps + hot-standby for read-only queries in the other location + one hot-standby in the same office).
So we set up another machine, intended to become a another hot_standby later, but for now just use it to experiment with pg_receivexlog. Since our current backup-strategy does not allow for PIT-recovery and we have been thinking about wal-archiving all the time, we decided to implement it and thats what we did. Now we have the two "lines" in the archive-directory.
Could the the high number of wal_keep_segments have an impact ?
Does the fact that there already were a lot of existing wal-files when i set up archiving and the archive-command have an impact ?
Jeff, you wrote:
>> 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.
When you said there would be no need for that, did you mean restoring the files for recovery or keeping a base-backup and the chain of wal-files ?
I understand that the archive-command is responsible for not overwriting wal-files. But if that situation occurs, and if i understand you correctly it will, what do i do ?
If the wal-archive files will be overwritten at some point in time, how is it possible to only have one single base-backup at time-point t and from then on only store the following wal-files ( however inconvenient that may be ) to be able to restore to any point in time after time t ?
Thanks,
Neil
Btw, ps shows:
postgres@darkblue:/data/pgdata/pg_xlog$ ps aux | grep post
postgres 11496 0.1 0.9 161018232 3696076 ? S Jan29 2:49 postmaster -i -D /data/pgdata
postgres 11499 0.0 1.6 161097088 6450616 ? Ss Jan29 1:39 postgres: checkpointer process
postgres 11500 0.0 0.3 161095036 1414612 ? Ss Jan29 0:12 postgres: writer process
postgres 11501 0.0 0.0 161095036 17328 ? Ss Jan29 0:05 postgres: wal writer process
postgres 11502 0.0 0.0 161096724 3112 ? Ss Jan29 0:11 postgres: autovacuum launcher process
postgres 11503 0.0 0.0 20136 884 ? Ss Jan29 0:10 postgres: archiver process last was 000000010000006E00000034
postgres 11504 0.0 0.0 20816 1412 ? Ss Jan29 0:53 postgres: stats collector process
postgres 11507 0.0 0.0 161096264 2652 ? Ss Jan29 1:08 postgres: wal sender process postgres 192.168.30.65(45640) streaming 8E/5544E650
postgres 11864 0.0 0.0 161096136 2656 ? Ss Jan29 0:51 postgres: wal sender process postgres 192.168.10.95(37378) streaming 8E/5544E650
...
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 ?
Thanks, Neil.
2013/1/31 Neil Worden <nworden1234@gmail.com>
>>> 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.>>>>>> Would it not be easier to archive the different servers to different>> directories and eliminate the possibility of name collision between servers?>Easier? I would say that that is the only sane way of doing it. I>was pointing out the consequences of messing it up. A proper>archive_command will save you from some self-inflicted disasters, but>that does not mean I'm recommending that you should invite those>disasters on yourself.>If the original author is in a production environment, he desperately>needs to figure out what is going on, especially so if archive_command>is not tested and verified to obey its contract.>Cheers,>JeffThanks for your answers.Yes, we are in a production environment and there are definitely no two masters writing to the wal-archive directory, at least none that i am aware of. And i can assure you that we are not intentionally working on inviting disasters either :-).Point is, the ..6D.. line started showing up exactly after i set up the archive-command (which is btw: archive_command = 'test ! -f /data/backup/walbackup/%f && cp %p /data/backup/walbackup/%f', running on Ubuntu Linux Server)The situation is as follows:All concerned machines are running 9.2.2 64-bit on Ubuntu Linux Server 12.10, installed from source, all following exactly the same procedure. We have a hot-standby running to a different location over a rather thin line running since version 9.1 came out. That workedflawlessly, we only were bitten by autovacuums to prevent XID wraparounds that generated relatively high wal-volume and wewere not sure whether the network connection could keep up with it before deleting wal-files. Since we had to physically transfer a backup once for other reasons, we set wal_keep_segments to 8192 to have enough fallback-time.The total size of the database is currently at 313 GB and we are in the process of rethinking our backup/emergency-strategy (currently daily full dumps + hot-standby for read-only queries in the other location + one hot-standby in the same office).So we set up another machine, intended to become a another hot_standby later, but for now just use it to experiment with pg_receivexlog. Since our current backup-strategy does not allow for PIT-recovery and we have been thinking about wal-archiving all the time, we decided to implement it and thats what we did. Now we have the two "lines" in the archive-directory.Could the the high number of wal_keep_segments have an impact ?Does the fact that there already were a lot of existing wal-files when i set up archiving and the archive-command have an impact ?Jeff, you wrote:>> 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.When you said there would be no need for that, did you mean restoring the files for recovery or keeping a base-backup and the chain of wal-files ?I understand that the archive-command is responsible for not overwriting wal-files. But if that situation occurs, and if i understand you correctly it will, what do i do ?If the wal-archive files will be overwritten at some point in time, how is it possible to only have one single base-backup at time-point t and from then on only store the following wal-files ( however inconvenient that may be ) to be able to restore to any point in time after time t ?Thanks,Neil
And a few minutes later the archiver-process with the same process-id has written a file from ..8.. line:
postgres 11502 0.0 0.0 161096724 3112 ? Ss Jan29 0:12 postgres: autovacuum launcher process
postgres 11503 0.0 0.0 20136 884 ? Ss Jan29 0:10 postgres: archiver process last was 000000010000008E00000058
postgres 11504 0.0 0.0 20816 1412 ? Ss Jan29 0:54 postgres: stats collector process
Am i missing something ?
Thanks, Neil
2013/1/31 Neil Worden <nworden1234@gmail.com>
Btw, ps shows:postgres@darkblue:/data/pgdata/pg_xlog$ ps aux | grep postpostgres 11496 0.1 0.9 161018232 3696076 ? S Jan29 2:49 postmaster -i -D /data/pgdatapostgres 11499 0.0 1.6 161097088 6450616 ? Ss Jan29 1:39 postgres: checkpointer processpostgres 11500 0.0 0.3 161095036 1414612 ? Ss Jan29 0:12 postgres: writer processpostgres 11501 0.0 0.0 161095036 17328 ? Ss Jan29 0:05 postgres: wal writer processpostgres 11502 0.0 0.0 161096724 3112 ? Ss Jan29 0:11 postgres: autovacuum launcher processpostgres 11503 0.0 0.0 20136 884 ? Ss Jan29 0:10 postgres: archiver process last was 000000010000006E00000034postgres 11504 0.0 0.0 20816 1412 ? Ss Jan29 0:53 postgres: stats collector processpostgres 11507 0.0 0.0 161096264 2652 ? Ss Jan29 1:08 postgres: wal sender process postgres 192.168.30.65(45640) streaming 8E/5544E650postgres 11864 0.0 0.0 161096136 2656 ? Ss Jan29 0:51 postgres: wal sender process postgres 192.168.10.95(37378) streaming 8E/5544E650...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 000000010000008C0000008FThe 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 000000010000008E00000054which seems to match the position of the streaming wal-senders .Any ideas ?Thanks, Neil.2013/1/31 Neil Worden <nworden1234@gmail.com>>>> 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.>>>>>> Would it not be easier to archive the different servers to different>> directories and eliminate the possibility of name collision between servers?>Easier? I would say that that is the only sane way of doing it. I>was pointing out the consequences of messing it up. A proper>archive_command will save you from some self-inflicted disasters, but>that does not mean I'm recommending that you should invite those>disasters on yourself.>If the original author is in a production environment, he desperately>needs to figure out what is going on, especially so if archive_command>is not tested and verified to obey its contract.>Cheers,>JeffThanks for your answers.Yes, we are in a production environment and there are definitely no two masters writing to the wal-archive directory, at least none that i am aware of. And i can assure you that we are not intentionally working on inviting disasters either :-).Point is, the ..6D.. line started showing up exactly after i set up the archive-command (which is btw: archive_command = 'test ! -f /data/backup/walbackup/%f && cp %p /data/backup/walbackup/%f', running on Ubuntu Linux Server)The situation is as follows:All concerned machines are running 9.2.2 64-bit on Ubuntu Linux Server 12.10, installed from source, all following exactly the same procedure. We have a hot-standby running to a different location over a rather thin line running since version 9.1 came out. That workedflawlessly, we only were bitten by autovacuums to prevent XID wraparounds that generated relatively high wal-volume and wewere not sure whether the network connection could keep up with it before deleting wal-files. Since we had to physically transfer a backup once for other reasons, we set wal_keep_segments to 8192 to have enough fallback-time.The total size of the database is currently at 313 GB and we are in the process of rethinking our backup/emergency-strategy (currently daily full dumps + hot-standby for read-only queries in the other location + one hot-standby in the same office).So we set up another machine, intended to become a another hot_standby later, but for now just use it to experiment with pg_receivexlog. Since our current backup-strategy does not allow for PIT-recovery and we have been thinking about wal-archiving all the time, we decided to implement it and thats what we did. Now we have the two "lines" in the archive-directory.Could the the high number of wal_keep_segments have an impact ?Does the fact that there already were a lot of existing wal-files when i set up archiving and the archive-command have an impact ?Jeff, you wrote:>> 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.When you said there would be no need for that, did you mean restoring the files for recovery or keeping a base-backup and the chain of wal-files ?I understand that the archive-command is responsible for not overwriting wal-files. But if that situation occurs, and if i understand you correctly it will, what do i do ?If the wal-archive files will be overwritten at some point in time, how is it possible to only have one single base-backup at time-point t and from then on only store the following wal-files ( however inconvenient that may be ) to be able to restore to any point in time after time t ?Thanks,Neil
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.klaver@gmail.com
On Thu, Jan 31, 2013 at 12:50 AM, Neil Worden <nworden1234@gmail.com> wrote: > > The situation is as follows: > > All concerned machines are running 9.2.2 64-bit on Ubuntu Linux Server > 12.10, installed from source, all following exactly the same procedure. We > have a hot-standby running to a different location over a rather thin line > running since version 9.1 came out. That worked > flawlessly, we only were bitten by autovacuums to prevent XID wraparounds > that generated relatively high wal-volume and we > were not sure whether the network connection could keep up with it before > deleting wal-files. Since we had to physically transfer a backup once for > other reasons, we set wal_keep_segments to 8192 to have enough > fallback-time. Ah. ... > > Could the the high number of wal_keep_segments have an impact ? > Does the fact that there already were a lot of existing wal-files when i set > up archiving and the archive-command have an impact ? Yes. It is doing something like archiving the newly-finished log files as they are completed, and interleaving that with working off the wal_keep_segments backlog. So everything seems normal. At some point they should converge without difficulty. > > Jeff, you wrote: > >>> 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. > > When you said there would be no need for that, did you mean restoring the > files for recovery or keeping a base-backup and the chain of wal-files ? No, you need both of those. There should be no need to restore the *names* of the files. It sounded like you were planning to invent some scheme to rename files and rename them back. > > I understand that the archive-command is responsible for not overwriting > wal-files. But if that situation occurs, and if i understand you correctly > it will, what do i do ? If it attempts to overwrite, refuses and returns with a non-zero status, then your server will accumulate unarchived log files in pg_xlog and you will get warnings in the log file something like: LOG: archive command failed with exit code 1 It will keep trying, but of course also keep failing, until you manually intervene. The risks are that pg_xlog might fill up, or that if the hard drive that holds pg_xlog crashes you will lose log files that were scheduled to have been archived but never made it there. But, this should be a moot point if you indeed only have one server archiving to that directory. Although this has happened to me a couple times, and I just renamed the offending archived file to something else (i.e. add .bak to the name) to unblock the process. And then compare to moved file to the newly arrived archival of it and verify that they were identical (they were). Apparently what happened was that a network glitch caused the file copy think it failed when it had not. Then future attempts failed because the file already existed. Cheers, Jeff
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@gmail.com>
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@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Yes, it does indeed interleave and it seems to archive the backlog just before the files are about to be deleted. That explains it. Thanks for your help, Neil 2013/1/31 Jeff Janes <jeff.janes@gmail.com> > On Thu, Jan 31, 2013 at 12:50 AM, Neil Worden <nworden1234@gmail.com> > wrote: > > > > The situation is as follows: > > > > All concerned machines are running 9.2.2 64-bit on Ubuntu Linux Server > > 12.10, installed from source, all following exactly the same procedure. > We > > have a hot-standby running to a different location over a rather thin > line > > running since version 9.1 came out. That worked > > flawlessly, we only were bitten by autovacuums to prevent XID wraparounds > > that generated relatively high wal-volume and we > > were not sure whether the network connection could keep up with it before > > deleting wal-files. Since we had to physically transfer a backup once for > > other reasons, we set wal_keep_segments to 8192 to have enough > > fallback-time. > > Ah. > > ... > > > > Could the the high number of wal_keep_segments have an impact ? > > Does the fact that there already were a lot of existing wal-files when i > set > > up archiving and the archive-command have an impact ? > > Yes. It is doing something like archiving the newly-finished log > files as they are completed, and interleaving that with working off > the wal_keep_segments backlog. So everything seems normal. At some > point they should converge without difficulty. > > > > > Jeff, you wrote: > > > >>> 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. > > > > When you said there would be no need for that, did you mean restoring the > > files for recovery or keeping a base-backup and the chain of wal-files ? > > No, you need both of those. There should be no need to restore the > *names* of the files. It sounded like you were planning to invent > some scheme to rename files and rename them back. > > > > > I understand that the archive-command is responsible for not overwriting > > wal-files. But if that situation occurs, and if i understand you > correctly > > it will, what do i do ? > > If it attempts to overwrite, refuses and returns with a non-zero > status, then your server will accumulate unarchived log files in > pg_xlog and you will get warnings in the log file something like: > > LOG: archive command failed with exit code 1 > > It will keep trying, but of course also keep failing, until you > manually intervene. > > The risks are that pg_xlog might fill up, or that if the hard drive > that holds pg_xlog crashes you will lose log files that were scheduled > to have been archived but never made it there. > > But, this should be a moot point if you indeed only have one server > archiving to that directory. > > Although this has happened to me a couple times, and I just renamed > the offending archived file to something else (i.e. add .bak to the > name) to unblock the process. And then compare to moved file to the > newly arrived archival of it and verify that they were identical (they > were). Apparently what happened was that a network glitch caused the > file copy think it failed when it had not. Then future attempts > failed because the file already existed. > > Cheers, > > Jeff >