Re: Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files

[Joshua Boyd <joishi@xxxxxxxxx>]

Having continued my research, the problem I encountered is the exact same that's been recorded here: 

https://www.marshut.net/kstxxk/pitr-failing-to-stop-before-drop-database.html

I took the backup by following the procedures as laid forth in the continuous archiving document (http://www.postgresql.org/docs/9.2/static/continuous-archiving.html)

I configured the archiving to gzip the wal files to pgdata/archive

Then I started the backup process by issuing "select pg_start_backup('mylabel')"

next tarball'd the contents of pgdata, excluding the pgdata/archive dir and the pgdata/pg_xlog dir (although preserving the directory structure)

after that I issued "select pg_stop_backup()"

then I added the contents of pgdata/archive and pgdata/pg_xlog to the tarball above

then I gzipped the tarball.

The above is how I archived and backed up..

To restore the backup, I shut down the server, moved pgdata to pgdata_backup, untarballed the backup tarball, removed all the files in the new pgdata/pg_xlog dir, copied the files from pgdata_backup/archive and pgdata_backup/pg_xlog into the new pgdata dir, set up the recovery.conf file giving it a timestamp gathered from the pgdata_backup/pg_log/<> log files..  I copied ALL the pg_xlog files ... not simply the "unarchived ones". All of the unarchived ones should have been removed when I removed the contents of the pg_xlog dir after restoring the tarball..

I think I answered all the questions - please let me know if I missed some.  Based on the url I pasted at the top, though, it appears I'm not the only one who's encountered this problem.

On Tue, Dec 2, 2014 at 3:39 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 11/28/2014 02:29 PM, Joshua Boyd wrote:

I am testing out point in time recovery from a hot physical backup in a
disaster recovery situation - I turned on archiving of files, created a
hot physical backup,

How did you take the backup?

Archiving how and to where?

then (after letting it run for a few days) issued a

"DROP DATABASE".  The pg_log file shows the DROP DATABASE command was
issued at '2014-11-28 10:20:00.010 PST'.  I shut down the server, moved
the pgdata directory to pgdata_backup ... restored the files in the hot
physical backup I made, copied the wal archive files from pgdata_backup
to the (new) pgdata archive,

The above I do not understand.
You where archiving the WALs in your pgdata directory?

Restored the backup how?

 cleared out the new pg_xlog dir and copied

the files from the old pg_xlog into the new..  Set up a recovery.conf

All the files or only the unarchived ones?

file as such:

restore_command = 'gunzip -c /home/pg2dev/joshtest/pgdata/archive/%f.gz
 > %p'
recovery_target_time = '2014-11-28 10:20:00.010 PST'
recovery_target_inclusive = false

then I started the server up.  the pg_log shows the following:

And then I look in pgdata/base .. and sure enough, that directory is
missing.  I examine my hot physical backup file and that directory
exists within it.

So .... even though the recovery SAYS "recovery stopping before commit
of transaction 235078" ... it doesn't appear that it's 100% accurate.
It didn't commit the transaction, clearly, because the database is still
listed in the data dictionary ... however, the filesystem files are
gone.  Please - am I doing something wrong, or would this be considered
a bug?

--
Joshua Boyd

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

--

Joshua Boyd