Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    Hong Kong
    Posts
    10

    Unanswered: How can I restore from WAL log? [PG 7.3]

    Hi,

    I am new to PostgreSQL and now want to know how can I recover from a database crash.

    I know pg_dump and pg_restore but with pg_dump all transactions between every pg_dump will be lost. I found WAL in the doc and seems with it I can archieve point-in-time recovery. But I don't know the exact steps of doing this.

    Any input, link or tutorials will be greatly appreciated.

    Thanks in advance.

    Michael

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    • if possible, rename the damaged cluster/copy the WAL files to a different location
    • restore the cluster from the last backup
    • clean up the restored cluster (remove pg_xlog files and postmaster.pid)
    • If possible copy the WAL files from the damaged cluster to the freshly restored cluster
    • create a recovery.conf file that at minimum contains a restore_command. If you wish, you can restore up to a point in time prior to the actual crash time by specifying the recovery_target_time.
    • Start the postmaster process at the server
    • Verify thet the data you expect is really there
    I recommend that you get a copy of the second edition of PostgreSQL, by Douglas & Douglas. The book explains this process in a great deal more detail.

    Also, ref http://www.postgresql.org/docs/8.0/s...up-online.html
    Last edited by loquin; 09-28-06 at 18:38.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Sep 2004
    Location
    Hong Kong
    Posts
    10
    Hi loquin,

    Thanks for your input.

    Your reference points to PG 8.0, is your procedure for PG 7.3 (sorry I haven't tried it out as I'm afarid I may mess up my PostgreSQL)?

    I am a bit confused now. If I can achieve Point-In-Time Recovery (PITR) in PG 7.3, then why it is a new feature in 8.0? What is the difference between

    PG 7.3 Restore from WAL log and PG 8.0 PITR?

    Thanks again for your help.

    Michael
    Last edited by michael_hk; 09-28-06 at 23:53.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Sorry, Michael - I missed the 7.3 in the thread title.

    recovery from the write-ahead log began with version 8. Prior to this (beginning with v7.1) WALs were used to ensure database integrity in the event of a system failure.

    You should verify this at the PostgreSQL mailing list archives
    Last edited by loquin; 09-29-06 at 03:01.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •