How to replay xlogs from the beginning of time? (postgresql)

Question

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?

• Otheus: 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?

Answer 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

3. 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.

4. 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.

• Kassandry: 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?
• Otheus: No I did not, but this was a test case. I'll add it to the answer. Thank you!