X-Original-To: pgsql-admin-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 099429FAE4E
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Thu, 13 Jul 2006 08:28:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 72975-02 for <pgsql-admin-postgresql.org@postgresql.org>;
	Thu, 13 Jul 2006 08:28:43 -0300 (ADT)
X-Greylist: delayed 23:56:56.90407 by SQLgrey-
Received: from mail.cresoltec.com.br (unknown [200.103.143.8])
	by postgresql.org (Postfix) with SMTP id 0D83D9FAD5A
	for <pgsql-admin@postgresql.org>; Thu, 13 Jul 2006 08:28:43 -0300 (ADT)
Received: (qmail 701 invoked from network); 11 Jul 2006 12:55:59 -0000
Received: from unknown (HELO ?10.90.2.162?) (10.90.2.162)
	by mail.cresoltec.com.br with SMTP; 11 Jul 2006 12:55:59 -0000
Message-ID: <44B39FD6.7080800@cresoltec.com.br>
Date: Tue, 11 Jul 2006 09:55:50 -0300
From: Alexander Burbello <alexander@cresoltec.com.br>
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20060423)
X-Accept-Language: pt-br, pt
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Restore database
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200607/130
X-Sequence-Number: 22376

I need to test and create a procedure to restore databases.
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 cresoldev -c "SELECT pg_start_backup('/pg/backup/');"
     tar -cvf /pg/backup/bk_base.tar /pg/data/base/*
     /pg/bin/psql cresoldev -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.
     What's the procedure that I doing mistakes????????


Thanks for helping me, in advance.

Regards
Alexander