Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to recover a database using recovery.conf file for making point-in-time copy.

On recovery.conf file, I added a recovery target time as below.

recovery_target_time = '2011-06-16 04:10:00 IST'

But in the logs I can see if it advances the time by 3.5 hours to 07:40 and actual recovery is attempted for this time, not the time I given in recovery_target_time.

2011-06-16 12:53:45 IST LOG:  starting archive recovery
2011-06-16 12:53:45 IST LOG:  restore_command = '/app/postgresinstall/bin/pg_standby -l /dbdata/archive %f %p %r'
2011-06-16 12:53:45 IST LOG:  recovery_target_time = '2011-06-16 07:40:00+05:30'
cp: cannot stat `/dbdata/archive/00000001.history': No such file or directory

Why this difference of 3.5 hours and I need help in figuring out whats going on in deciding the recovery target time?

Update: Here are time details from database

postgres=# SELECT EXTRACT(timezone_hour FROM now()),EXTRACT(timezone_minute FROM now());
 date_part | date_part 
-----------+-----------
         5 |        30
(1 row)

postgres=# select now();
               now                
----------------------------------
 2011-08-12 13:08:19.333068+05:30
(1 row)

OS Date/Time:
postgres@xxxxx:~$ date
Fri Aug 12 13:10:19 IST 2011

I faced the same problem when I try to restore from WAL archives on same server and my backup server. Also I can consistently reproduce this problem on both servers.

So if I need to create a near to current time backup, I use a recovery_target_time 3.5 hours behind current time and start the DB with recovery.conf

share|improve this question
    
I added a comment with more questions –  RolandoMySQLDBA Jun 20 '11 at 15:23

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
OS time zone is correct. I will check TZ in db and report back. Thanks –  Jamess Jun 17 '11 at 17:14
    
Database timezone is also correct and matching to OS TZ. So I am still at loss why log reported a different time and recovery proceeded 3.5 hours ahead of the time I specified. Will you be able to guide me to check time zone in WAL files? –  Jamess Jun 20 '11 at 6:28
    
@Jamess : I have more questions. Where did WAL files come from ? In other words, did the WAL archiving take place on the same server as the DB is running on now, or were the WAL files copied or rsync'd to the DB Server from another DB server ? If the WAL files were created on a different DB server, is that orignal DB server in the same time zone ? –  RolandoMySQLDBA Jun 20 '11 at 15:22
    
@Jamess : I may be overthinking this problem. Another question : If every server has the same timezone (both OS and postgresql.conf), why is it necessary to specify the timezone in the recovery_target_time ? –  RolandoMySQLDBA Jun 20 '11 at 15:40
    
@Jamess : Here is another possibility. Do you have any WAL files missing or is the continuous archive backup incorrectly setup ??? –  RolandoMySQLDBA Jun 20 '11 at 18:58

In postgresql, IST may not mean 'Indian Standard Time', depending on the setting 'timezone_abbreviations' in postgresql.conf. It should be set to 'India' for IST to be interpreted as desired here.

share|improve this answer
    
Looks like this is it!. Thanks, I will check and let you know the results. –  Jamess Jul 17 '12 at 6:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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