Postgresql. Real-time copy of a database to another disk on the same server

DISCLAIMER : I am not a PostgreSQL DBA, though I dabble a lot with it.

You probably need to check your timezone in the OS and in psql.

In the OS run this:

[postgres@radarPG-db1 ~]$ date
Fri Jun 17 12:55:37 EDT 2011

In psql, run the following:

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part
-----------+-----------
        -4 |         0

postgres=# select now();
              now
-------------------------------
 2011-06-17 12:54:39.195291-04

Many times, some forget to have postgres default the timezone to that of the OS. If the WAL files also contain the timezone in its internal timestamps, you must align the timezone set in postgres with that of the WAL files.

Something else to consider is the following:

The default behavior of recovery is to recover along the same timeline that was current when the base backup was taken. If you want to recover into some child timeline (that is, you want to return to some state that was itself generated after a recovery attempt), you need to specify the target timeline ID in 'recovery.conf'. You cannot recover into timelines that branched off earlier than the base backup.

UPDATE 2011-06-20 15:08 EDT

This is right from the online documentation. Please look this over and see if you missed any WAL files along the way. If you waited too long to setup the WAL files on the new server, postgres may have deleted them on startup. You may need to find those WAL files. See if they reside back in pg_xlog folder from the old server.

Recovering using a Continuous Archive Backup

1. Stop the server, if it's running.

2. If you have the space to do so, copy the whole cluster data directory and any tablespaces to a temporary location in case you need them later. Note that this precaution will require that you have enough free space on your system to hold two copies of your existing database. If you do not have enough space, you need at the least to copy the contents of the pg_xlog subdirectory of the cluster data directory, as it might contain logs which were not archived before the system went down.

3. Clean out all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.

4. Restore the database files from your base backup. Be careful that they are restored with the right ownership (the database system user, not root!) and with the right permissions. If you are using tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.

5. Remove any files present in pg_xlog/; these came from the backup dump and are therefore probably obsolete rather than current. If you didn't archive pg_xlog/ at all, then recreate it, being careful to ensure that you re-establish it as a symbolic link if you had it set up that way before.

6. If you had unarchived WAL segment files that you saved in step 2, copy them into pg_xlog/. (It is best to copy them, not move them, so that you still have the unmodified files if a problem occurs and you have to start over.)

7. Create a recovery command file recovery.conf in the cluster data directory (see Recovery Settings). You might also want to temporarily modify pg_hba.conf to prevent ordinary users from connecting until you are sure the recovery has worked.

8. Start the server. The server will go into recovery mode and proceed to read through the archived WAL files it needs. Should the recovery be terminated because of an external error, the server can simply be restarted and it will continue recovery. Upon completion of the recovery process, the server will rename recovery.conf to recovery.done (to prevent accidentally re-entering recovery mode in case of a crash later) and then commence normal database operations.

9. Inspect the contents of the database to ensure you have recovered to where you want to be. If not, return to step 1. If all is well, let in your users by restoring pg_hba.conf to normal.

There isn't a magic way of doing that, the options I see is:

1. At a defined interval, do another basebackup and remove old backups. You can use pg_archivecleanup contrib to remove the old archives (read the contents of backup_label file contained in your basebackup to check which files each basebackup needs). This is a broadly used approach, and recommended for its simplicity.

2. Another approach is to extract the basebackup into a directory, add a recovery.conf file and start PostgreSQL to consume the archives.

Following an example of recovery.conf to achieve that:

# restore the archives
restore_command = 'cp /path/to/archives/%f %p'
# remove archives already restored (you must install pg_archivecleanup contrib)
archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
# select a target time of last month (you must create this by hand)
recovery_target_time = '2014-01-10 00:00'
# make PostgreSQL pause when it reaches the target time
pause_at_recovery_target = true
# make the PostgreSQL stop itself
recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'

The problem is that you'll need to do it all by hand. I had no time to test it myself, but you can try and see if the following shell script works for such thing:

#!/bin/sh
# create data dir
mkdir -p /path/to/data
cd /path/to/data
# extract base backup
tar xvf /path/to/base1.tar.gz
# create recovery.conf (as the above)
cat > recovery.conf <<EOL
    restore_command = 'cp /path/to/archives/%f %p'
    archive_cleanup_command = 'pg_archivecleanup /path/to/archives %r'
    recovery_target_time = '`date -d '1 month ago'`'
    pause_at_recovery_target = true
    recovery_end_command = 'pg_ctl -w -D /path/to/data stop -mf'
EOL
# start PostgreSQL
pg_ctl -D . start

You can also use the recovery_end_command to recreate the .tar.gz file or just keep the backup as a directory.

Sorry for the not tested solution, but you can try it and I can edit if any mistake is found.