Postgres online backup and restore
Sorry if posting twice, wasn't part of general when sent and didn't see it
received by group.
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 Thu, Jun 25, 2009 at 9:40 AM, Chris Barnes <cbarnes@recognia.com> wrote:
Sorry if posting twice, wasn’t part of general when sent and didn’t see
it received by group.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.
You missed the all-important step of also taking your archived log files to
the remote environment and creating the recovery.conf file:
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html
Note section 24.3.3.1
--Scott
On Thu, Jun 25, 2009 at 9:40 AM, Chris Barnes <cbarnes@recognia.com> wrote:
Sorry if posting twice, wasn’t part of general when sent and didn’t see
it received by group.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.
You missed the all-important step of also taking your archived log files to
the remote environment and creating the recovery.conf file:
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html
Note section 24.3.3.1
--Scott