Thread: Postgres online backup and restore

Postgres online backup and restore

From
"Chris Barnes"
Date:

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

 

Re: Postgres online backup and restore

From
Scott Mead
Date:

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:

    Note section 24.3.3.1

--Scott

Re: Postgres online backup and restore

From
Scott Mead
Date:
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:

    Note section 24.3.3.1

--Scott