From: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | Alan Hodgson <ahodgson(at)simkin(dot)ca>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: : PostgreSQL Online Backup |
Date: | 2011-10-03 12:33:35 |
Message-ID: | CAFrxt0gx7q5JNVK79X611g6rdPoy0c9SFDy4xO3CMFmHyMsp6g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Another problem in recovery (probably because of "rsync") -
As said earlier, we are taking a production backup everyday incrementally
using "rsync".
But, Postgres some how misses to sync few files in between and keeps on
asking the back dated archive files (more than 1 week ago).
I restored October 2nd backup and PG is asking for September 26th archive
file with the last known time as 26th Sep, 2011.
2011-10-03 07:17:12 CDT [12705]: [1-1] LOG: database system was
interrupted; last known up at 2011-09-26 09:01:36 CDT
2011-10-03 07:17:12 CDT [12705]: [2-1] LOG: starting archive recovery
cp: cannot stat
`/usr/local/pgsql9.0.1/obtdata/data/pg_xlog/000000010000053900000076': No
such file or directory
2011-10-03 07:17:12 CDT [12705]: [3-1] LOG: could not open file
"pg_xlog/000000010000053900000076" (log file 1337, segment 118): No such
file or directory
2011-10-03 07:17:12 CDT [12705]: [4-1] LOG: invalid checkpoint record
2011-10-03 07:17:12 CDT [12705]: [5-1] PANIC: could not locate required
checkpoint record
2011-10-03 07:17:12 CDT [12705]: [6-1] HINT: If you are not restoring from
a backup, try removing the file
"/usr/local/pgsql9.0.1/obtdata/data/backup_label".
2011-10-03 07:17:12 CDT [12702]: [1-1] LOG: startup process (PID 12705) was
terminated by signal 6: Aborted
2011-10-03 07:17:12 CDT [12702]: [2-1] LOG: aborting startup due to startup
process failure
I always see pg_clog files and some base files not getting synced.
Below is what we are doing -
pg_start_backup()
rsync the data directory
pg_stop_backup()
The first time "rsync" is fine, but, the subsequent runs are generating
in-consistency.
We do the same every day to backup the data directory incrementally.
What i observed is PG records the TXN id when ever backup starts and stops +
backup label. The next day when PG records the start backup time and TXN id,
i think some of the TXN ids and pg_clog files generated between last stop
time and the next start time are missed.
Did anyone observe this behavior ?? Please help !
This is critical for us. I want to recommend not to use "rsync" (use cp or
scp instead) for production backup.
Thanks
VB
On Tue, Sep 27, 2011 at 2:36 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>wrote:
> Venkat Balaji wrote:
> > Our problem is -
> >
> > We had mistakenly executed "rsync" on the running PostgreSQL data
> directory (production) and we did
> > not run "pg_start_backup()".
> >
> > Will this harm production ? can this lead to corruption ?
>
> I assume that you used rsync to copy *from* the data directory.
>
> This cannot lead to data corruption.
> Only performance might suffer temporarily due to the additional I/O.
>
> The backup made with rsync will be unusable without pg_start_backup().
>
> Yours,
> Laurenz Albe
>
From | Date | Subject | |
---|---|---|---|
Next Message | Karl Wright | 2011-10-03 12:48:34 | Re: [GENERAL] One-click installer, Windows 7 32-bit, and icacls.exe |
Previous Message | Alban Hertroys | 2011-10-03 12:18:56 | Re: I don't understand something... |