2

Let's say I'm keeping all the xlog's for a cluster instance since the time of cluster initialization (ie, initdb). Let's call that time T0. All xlog's are retained/archived through T1 and continuing through time T2, at which point a backup is made. But now, I need to recover to time T1. I know how to do PIT to a time after T2, but I'm not sure how to do it to time T1. Do I re-initialize the database and replay the logs? Will that actually work, or will I have issues with timelines?

  • When I try this idea (initdb in new directory, copy over all pg_xlogs), I get DETAIL: WAL file database system identifier is 6221786353392811102, pg_control database system identifier is 6252279422905597461. Is there a way to hack this? – Otheus Feb 17 '16 at 15:29
1

I was able to perform a complete recovery from T0 to T1 using these steps. We do it with a completely newly initialized database. We need the original database and all its xlogs. The original database is in $OLD_PGDATA and the new one is in $PGDATA. Make sure you get these straight before you proceed.

Steps

  1. Set $PGDATA and create its directory and new database therein

    [postgres]$ export PGDATA=/var/lib/pgsql.$$
    [postgres]$ mkdir -p $PGDATA
    [postgres]$ initdb 
    
  2. Modify the control file using a little trick I gleaned from stackexchange and other sites:

    [postgres]$ cut -c 1-8 $OLD_PGDATA/global/pg_control | tr -d '\n' |
     dd of=$PGDATA/global/pg_control count=1 skip=0 conv=notrunc
    [postgres]$ pg_resetxlog -f $PGDATA
    

You should see something like the following warning and output, which can be ignored:

     pg_resetxlog: pg_control exists but has invalid CRC; proceed with caution
     Transaction log reset
  1. Create recovery.conf file and start the database.

     [postgres]$ cat >recovery.conf << END
     restore_command='cp "$OLD_PGDATA/pg_xlog/%f" "%p"'
     END
     [postgres]$ pg_ctl start
    

Note: the space between << and END is very important.

  1. According to pg_resetxlog manual, afterwards, do a full dump, initdb, restore to be sure the DB is in a consistent state.

Note: if you are running the OLD_PGDATA instance on the same host, you'll just add -o "-p 5433" to use a port #5433 instead of the default port.

It took less than 15 minutes to recover 1700+ or 28 GB of xlog files.

  • Once your PITR to time T1 was completed, did you do a dump, initdb, and restore as comes highly recommended in the manual and in other dba.se questions? – Kassandry Feb 18 '16 at 4:58
  • No I did not, but this was a test case. I'll add it to the answer. Thank you! – Otheus Feb 18 '16 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.