Обсуждение: 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:
Why not just use pg_dump?
See 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
==================================================================
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 - 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
==================================================================
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 > > >