Error when starting with recovered data directory

[Andrew Swerlick]

I'm having some issues restoring from a recovered data directory. Let me start be explaining our setup.

Currently I have a primary server and a replica server replicated using repmgr. Barman is installed on the replicated server. I've configured barman to talk to the replica and run the barman backup command against that server. This is working fine, and both the barman check and barman backup commands work great.

I've also configured wal archiving on the primary server to ship the logs to the incoming wal directory defined by barman. I have tested this by adding records in the primary server and then calling "select pg_switch_xlog();" to flush the current WAL so that it can be archved. I see the logs appear briefly in the incoming folder before disappearing when the backup cron job runs.

I've also been able to successfully run barman recover with the --target-time command for PITR recovery. After the command runs I edit postgresql.conf to change the data directory, pg_hba, and ident_file files to point to those in the current data directory. I stop my currently running postgres server and then start a new one pointing to the new data directory. This is the error message I get

2015-04-10 03:54:38 UTC LOG: database system was interrupted while in recovery at log time 2015-04-10 03:26:17 UTC

2015-04-10 03:54:38 UTC HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.

2015-04-10 03:54:38 UTC FATAL:  invalid input syntax for type timestamp with time zone: "20150409232800"

2015-04-10 03:54:38 UTC LOG:  startup process (PID 3322) exited with exit code 1

2015-04-10 03:54:38 UTC LOG:  aborting startup due to startup process failure

One thing that's particularly interesting is that the timestamps shown in that message are actually from the future. I suspect that's part of the issue, but don't know how to go about fixing things.

[Andrew Swerlick]

One other details that might be important, the "20150409232800" in the error message is the time I'm trying to recover to

[Andrew Swerlick]

Just figured it out. Apparently the --target-time argument has to be in the format YYY-MM-DD HH:MM:SS. I figured since the recover command worked I was out of the woods in that respect. This post was particularly helpful https://groups.google.com/forum/#!topic/pgbarman/FRBy1Wnot0E. Might be worth updating the documentation to make this more clear though.

[harish guntur]

In my case there is timezone mismatch b/w barman and postgres, so I have to use the GMT time with --target-time option!
barman recover app_db_server latest --remote-ssh-command "ssh postgres@<targer_server>" /var/lib/pgsql/9.5/data --target-time "2020-02-27 21:10:28.599024+00:00"
"2020-02-27 21:10:28.599024+00:00" this is GMT time