Re: Postgres base Backup fails to recover all logs fails from archive - Mailing list pgsql-admin

From Magnus Hagander
Subject Re: Postgres base Backup fails to recover all logs fails from archive
Date
Msg-id CABUevEyFDGijpFVyUonR9uisd3hyyezYRXE9OrP7SD_PKrq4pg@mail.gmail.com
Whole thread Raw
In response to Re: Postgres base Backup fails to recover all logs fails from archive  (Lars Aksel Opsahl <lop@skogoglandskap.no>)
List pgsql-admin
On Tue, Jan 8, 2013 at 11:00 AM, Lars Aksel Opsahl
<lop@skogoglandskap.no> wrote:
> Hi again
>
> I have tried to check different /var/log/messages on the servers but they only go back 1 month. I also had quick
glanceat  Nagios and Munin logs but I could not find anything there. 
>
> This database is mainly used for reading data as it is now, but it's a couple of applications which also store data.
The2 days with out activity in the archive logs is strange when I checked back on the activity reports for
applications.I will set the archive_timeout, but the problem  is that we have small updates and the archive files has
thesame size and is not depending on the amount of changed data. 

Yeah, that is indeed a problem. pg_receivexlog can help you arund that
issue, but then your receiving end needs to be a "real box" and not
just an NFS share.


> We use NFS to copy archive  files to the backup server because that is simple, but maybe we should have used rsyn or
somethingelse, but independent off how we do it, we need a method to check that the archive file is OK after it's copy. 

I would generally recommend using somehting that writes the file
atomically. For example, rsync. But you can use rsync with your target
being the NFS share - you don't need an rsync server. Or just a manual
copy+rename. As long as your NFS isn't mounted asynchronously, that
should be safe.


> If it's not OK the files must be transfers once more from the database server. I assume the archive always will be OK
whenit's created on the database server. 
>
> Is the the way off doing this to make a script that copies the archive file to backup server cheeks that the files
areequal before before it is deleted on the database server ? 

Copy+rename should take care of it for you - I doubt it's an actual
failure in contents wrong, it's more that something crashed in the
middle of a file and therefor it ended up being corrupt. If that was
written to a temp file and then renamed into place, that should not
happen.

> Is there any simple utility program that can check if the archive file is OK or do I have to do a restore to check if
thefile is OK  ? 

I don't believe there is such a tool. You could make some very most
basic verification manually, but to verify the full contents you
really need to fully parse them - and the tool to do that is
"postgres".

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


pgsql-admin by date:

Previous
From: "Kevin Grittner"
Date:
Subject: Re: pg_dump and restore
Next
From: Casey Allen Shobe
Date:
Subject: Re: Mixing an SSD and Spinning drive for best performance