How to replay xlogs from the beginning of time? (postgresql) 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?
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


Set $PGDATA and create its directory and new database therein

[postgres]$ export PGDATA=/var/lib/pgsql.$$
[postgres]$ mkdir -p $PGDATA
[postgres]$ initdb 

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



Create recovery.conf file and start the database.

 [postgres]$ cat &gt;recovery.conf &lt;&lt; END
 restore_command='cp "$OLD_PGDATA/pg_xlog/%f" "%p"'
 END
 [postgres]$ pg_ctl start



Note: the space between &lt;&lt; and END is very important. 


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.