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

On 12/02/2014 03:50 PM, Joshua Boyd wrote:
> 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 not necessary, you are going to empty the pg_xlog directory
later anyway and you want the archive contents from the later date when
you do below.

>
> 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,

See, you empty the pg_xlog dir you saved.

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..

Well those where the WALs from the time of the backup and should already
be archived, assuming sufficient time/activity between backup and recovery.

>
> 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.

Yes from the answers it would seem the solution is to push your
recovery_target_time further into the future. If I am following the
discussion DROP DATABASE removes the files from the file system first
and then clears the system catalog. So by ending the recovery at the
moment of DROP DATABASE you are getting to the point that it drops the
files but before it removes the entry. Try letting the recovery run a
little longer to see if that works. Also seems there is a patch in the
works to correct this:)

>
>

>
> --
> Joshua Boyd
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>
>
> --
> Joshua Boyd

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com