Обсуждение: Postgres online backup and restore has errors that are concerning

  I started an online backup of postgres, tar’d my data folder, copy to usb drive in production
and restored it into my RC environment. Have I missed something important?

Online Backup

psql
checkpoint;
SELECT pg_switch_xlog();
SELECT pg_start_backup('postres_full_backup_June222009');
tar -czvf pgprd01_June22_2009_production.dmp.tar.gz data/
SELECT pg_stop_backup();

Restore
tar –xzvf pgprd01_June22_2009_production.dmp.tar.gz

When starting the database I receive many errors that look like the backup was corrupted.

2009-06-23 08:29:15 EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not satisfied --- flushed only to 10D/510C4FB8
2009-06-23 08:29:15 EDT:@:7614CONTEXT:  writing block 2 of relation 16403/16884/2696
2009-06-23 08:29:16 EDT:@:7614ERROR:  xlog flush request 10D/590D6578 is not satisfied --- flushed only to 10D/510C4FB8
2009-06-23 08:29:16 EDT:@:7614CONTEXT:  writing block 2 of relation 16403/16884/2696
2009-06-23 08:29:16 EDT:@:7614WARNING:  could not write block 2 of 16403/16884/2696

pgdb001=> select criteria_rank from client001.t1020screen where criteria_rank like '%TR009%';
ERROR:  missing chunk number 0 for toast value 738680

Version
[postgres@pgprd01 data]$ cat PG_VERSION
8.3

Chris Barnes

On Thursday 25 June 2009, Chris Barnes <compuguruchrisbarnes@hotmail.com>
wrote:
>   I started an online backup of postgres, tar’d my data folder, copy to
> usb drive in production
> and restored it into my RC environment. Have I missed
> something important?
>

You need the transaction logs archived during and immediately after the
backup, and you need to setup a recovery.conf for the initial startup that
can access those files. See the PITR backup notes in the documentation,
they're really quite complete.

--
WARNING:  Do not look into laser with remaining eye.