Un successful Restoration of DATA using WAL files

From: Mitesh51 <mit_bca1(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Un successful Restoration of DATA using WAL files
Date: 2009-10-19 14:18:42
Message-ID: 25959142.post@talk.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I am unable to restore data with the use of WAL files by following procedure.

I have done following changes in postgres.conf to enable WAL archiving...

archive_mode = on # allows archiving to be done
archive_command = 'copy "%p" "C:\\archivedir\\%f"'

I have one database(built in) postgres. I create one table student in it.
and take full backup(only of a single database & I am not copying complete
data dir..) @ 12:40 pm with the

pg_dump.exe -h localhost -p 5432 -U postgres -f C:\test.sql postgres

After taking full backup...

I create 1 table named "person" @ 12:41 pm in the postgres database(for
testing purpose only).
(Now it should be recorded in WAL files...Am I write here?)

and then l remove both tables...(to check the validity of my backups) @
12:43

I restore full backup with...

psql -U postgres postgres < test.sql

@ this stage I took the full backup @ 12:40 pm so table "student" is
restored properly & I can see it.

Now...

I made recovery.conf with contents

restore_command = 'copy c:/archivedir/%f "%p"'
recovery_target_time = '2009-10-19 12:42:00'

I put it in C:\Program Files\PostgreSQL\8.4\data

I restart the server...

recovery.conf is changed to recovery.done & getting the following log...

2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,1,,2009-10-19 18:41:04
IST,,0,LOG,00000,"database system was shut down at 2009-10-19 18:40:43
IST",,,,,,,,
2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,2,,2009-10-19 18:41:04
IST,,0,LOG,00000,"starting archive recovery",,,,,,,,
2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,3,,2009-10-19 18:41:04
IST,,0,LOG,00000,"restore_command = 'copy c:/mit/%f ""%p""'",,,,,,,,
2009-10-19 18:41:04.744 IST,,,4060,,4adc6568.fdc,4,,2009-10-19 18:41:04
IST,,0,LOG,00000,"recovery_target_time = '2009-10-19
18:38:46+05:30'",,,,,,,,
2009-10-19 18:41:04.931 IST,,,4060,,4adc6568.fdc,5,,2009-10-19 18:41:04
IST,,0,LOG,00000,"automatic recovery in progress",,,,,,,,
2009-10-19 18:41:04.963 IST,,,4060,,4adc6568.fdc,6,,2009-10-19 18:41:04
IST,,0,LOG,00000,"record with zero length at 0/C8000068",,,,,,,,
2009-10-19 18:41:04.963 IST,,,4060,,4adc6568.fdc,7,,2009-10-19 18:41:04
IST,,0,LOG,00000,"redo is not required",,,,,,,,
2009-10-19 18:41:05.135 IST,,,4060,,4adc6568.fdc,8,,2009-10-19 18:41:04
IST,,0,LOG,00000,"selected new timeline ID: 10",,,,,,,,
2009-10-19 18:41:05.478 IST,,,6032,"",4adc6569.1790,1,"",2009-10-19 18:41:05
IST,,0,LOG,00000,"connection received: host=127.0.0.1 port=4383",,,,,,,,
2009-10-19 18:41:05.494
IST,"postgres","postgres",6032,"127.0.0.1:4383",4adc6569.1790,2,"",2009-10-19
18:41:05 IST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,
2009-10-19 18:41:06.760 IST,,,5080,"",4adc656a.13d8,1,"",2009-10-19 18:41:06
IST,,0,LOG,00000,"connection received: host=127.0.0.1 port=4384",,,,,,,,
2009-10-19 18:41:06.775
IST,"postgres","postgres",5080,"127.0.0.1:4384",4adc656a.13d8,2,"",2009-10-19
18:41:06 IST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,
2009-10-19 18:41:08.838 IST,,,4060,,4adc6568.fdc,9,,2009-10-19 18:41:04
IST,,0,LOG,00000,"archive recovery complete",,,,,,,,

but I cant see the table "person" created again with the help of WAL file
restoration :(

Where I am wrong ??

--
View this message in context: http://www.nabble.com/Un-successful-Restoration-of-DATA-using-WAL-files-tp25959142p25959142.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bierbryer, Andrew 2009-10-19 14:33:30 Index Question
Previous Message Tom Lane 2009-10-19 14:09:24 Re: Function returning 2 columns evaluated twice when both columns are needed