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

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