Обсуждение: Restore Database

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




_______________________________________________________
Abra sua conta no Yahoo! Mail: 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz.
http://mail.yahoo.com.br/

On 7/11/06, Burbello <burbello3000@yahoo.com.br> wrote:
==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
==================================================================

Ok! Its a good tool, but for Production Database I think it is not
recommended.
Only using pg_dump for the second backup plain.
Suppose that you backed up at 6:00am and at 9am happened a crash on the
server.
In this case, I would lost data between that time, 3 hours of information.

For production databases, my plan is to do phisical backup including WAL.
If a crash happen, I can restore the datafiles and recover applying the
WAL logs until the last file was generated.
As Oracle does in this type of crash.

My doubt is that I am not getting apply the WAL files on recover stage.

Any other suggestion?

Thanks for your help




Aaron Bono escreveu:

> On 7/11/06, *Burbello* <burbello3000@yahoo.com.br
> <mailto:burbello3000@yahoo.com.br>> wrote:
>
>     I need to test and create a procedure to restore
>     databases.
>
>
> Why not just use pg_dump?
>
> See http://manual.intl.indoglobal.com/ch06s07.html
> <http://manual.intl.indoglobal.com/ch06s07.html> - it's really easy.
> This is how we copy from production to testing and development and how
> we do nightly backups.
>
>
> ==================================================================
>    Aaron Bono
>    Aranya Software Technologies, Inc.
>    http://www.aranya.com
> ==================================================================

On Wed, 2006-07-12 at 15:04, Alexander Burbello wrote:
> Ok! Its a good tool, but for Production Database I think it is not
> recommended.
> Only using pg_dump for the second backup plain.
> Suppose that you backed up at 6:00am and at 9am happened a crash on the
> server.
> In this case, I would lost data between that time, 3 hours of information.
>
> For production databases, my plan is to do phisical backup including WAL.
> If a crash happen, I can restore the datafiles and recover applying the
> WAL logs until the last file was generated.
> As Oracle does in this type of crash.
>
> My doubt is that I am not getting apply the WAL files on recover stage.
>
> Any other suggestion?

You have read this section of the manual, right?

http://www.postgresql.org/docs/8.1/interactive/backup-online.html

Yes!
I followed exactly that page to do.

Thanks



Scott Marlowe escreveu:

>On Wed, 2006-07-12 at 15:04, Alexander Burbello wrote:
>
>
>>Ok! Its a good tool, but for Production Database I think it is not
>>recommended.
>>Only using pg_dump for the second backup plain.
>>Suppose that you backed up at 6:00am and at 9am happened a crash on the
>>server.
>>In this case, I would lost data between that time, 3 hours of information.
>>
>>For production databases, my plan is to do phisical backup including WAL.
>>If a crash happen, I can restore the datafiles and recover applying the
>>WAL logs until the last file was generated.
>>As Oracle does in this type of crash.
>>
>>My doubt is that I am not getting apply the WAL files on recover stage.
>>
>>Any other suggestion?
>>
>>
>
>You have read this section of the manual, right?
>
>http://www.postgresql.org/docs/8.1/interactive/backup-online.html
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>
>