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.a...@wien.gv.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 >