Re: [bug fix] PITR corrupts the database cluster

On 2013-07-24 10:57:14 -0400, Tom Lane wrote:
> I wrote:
> > The only thing here that really bothers me is that a crash during DROP
> > DATABASE/TABLESPACE could leave us with a partially populated db/ts
> > that's still accessible through the system catalogs.  ...
> > I guess one thing we could do is create a flag file, say
> > "dead.dont.use", in the database's default-tablespace directory, and
> > make new backends check for that before being willing to start up in
> > that database; then make sure that removal of that file is the last
> > step in DROP DATABASE.
>
> After a bit of experimentation, it seems there's a pre-existing way that
> we could do this: just remove PG_VERSION from the database's default
> directory as the first filesystem action in DROP DATABASE.  If we
> crash before committing, subsequent attempts to connect to that database
> would fail like this:

Neat idea, especially as it would work on the back branches without
problems.

It doesn't address MauMau's original problem of not being able to set a
correct stop point because the datadir is being removed before the
commit record (which is the only place we currently can set a
recovery_target on in a basebackup scenario) though.

Wouldn't it make more sense to simply commit the pg_database entry
removal separately from the actual removal of the datadir? DROP DATABASE
already can't run in a transaction, so that should be easy.

>
> $ psql bogus
> psql: FATAL:  "base/176774" is not a valid data directory
> DETAIL:  File "base/176774/PG_VERSION" is missing.
>
> which is probably already good enough, though maybe we could add a HINT
> suggesting that the DB was incompletely dropped.

We could rename/fsync() the file to PG_BEING_DROPPED atomically. Then we
could give a useful error instead of an error message with a "maybe" hint.

That would require to remove the file last though, so I am not sure if
it's worth the bother.

Greetings,

Andres Freund

--Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services