Restore and Recover Database

From: Alexander Burbello <burbello3000(at)yahoo(dot)com(dot)br>
To: pgsql-admin(at)postgresql(dot)org
Subject: Restore and Recover Database
Date: 2006-07-25 20:16:27
Message-ID: 44C67C1B.3050809@yahoo.com.br
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Sorry insist in this question, but did someone try to restore and
recover the database, and check if no data is lost??

I tryed to do some steps following the Postgres documentation, but ... I
couldn't recover.
Anybody has some tips or suggestion?

Thanks in advance.

I followed the steps based on the site, but I couldn't finish succesfully.

I did:

1. Put the database on Backup Mode and copy datafiles.
/pg/bin/psql dbdev -c "SELECT pg_start_backup('/pg/backup/');"
tar -cvf /pg/backup/bk_base.tar /pg/data/base/*
/pg/bin/psql dbdev -c "SELECT pg_stop_backup();"

File .conf: archive_command = 'cp -i %p /pg/backup/xlog/%f </dev/null'

2. Created a new table and populated with data, to simulate the recovery:
create table test (
aa integer,
bb varchar(50)
);

insert into test values (1,'aaa');
...
insert into test values (5,'aaa');

Data inserted successfully!!!

3. Shutdown on database;

Last log transactions copied to the directory archived log;
cp /pg/data/pg_xlog/* /pg/backup/xlog/

4. Configuring the recovery.conf file:

restore_command = 'cp /pg/backup/xlog/%f %p'
recovery_target_time = '2006-07-06 16:33:52 BRT'

5. Simulate the lost directories, deleting... :

rm -r /pg/data/base/*

6. Recreating the directories exploding the tar file:

tar -xvf bkp_base... .tar

7. Starting the database for applying the log transactions.
Supposing recove the table "test" located on log transactions.

LOG: database system was shut down at 2006-07-06 16:47:18 BRT
LOG: starting archive recovery
LOG: restore_command = "cp /pg/backup/xlog/%f %p"
LOG: recovery_target_time = 2006-07-06 16:33:52-03
cp: cannot stat `/pg/backup/xlog/00000001.history': No such file or directory
LOG: restored log file "000000010000000000000001" from archive
LOG: record with zero length at 0/1122880
LOG: invalid primary checkpoint record
LOG: restored log file "000000010000000000000001" from archive
LOG: record with zero length at 0/1122844
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 3989) was terminated by signal 6
LOG: aborting startup due to startup process failure

8. There was an error and the table was lost!!!!!!!!!!!!!!!!!

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Arthurs 2006-07-26 01:05:41 Re: Restore and Recover Database
Previous Message Ivo Rossacher 2006-07-25 19:58:47 Re: invalid UTF-8 byte sequences and iconv