Re: pg_restore -1 vs -C and -c

On 12 jan 2009, at 17.46, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> It should be possible to make it compatible with -C by moving the
>> CREATE
>> DATABASE command to outside of the transaction. I have only had a
>> quick
>> look at the code wrt how much work this would be. One thing that
>> hit me
>> quickly: do we support multiple CREATE DATABASE statements in a
>> single
>> dump file? I think not, but the format seems to allow it? If not, it
>> should be "fairly simple" to do.
>
> We don't, and a single transaction couldn't restore into multiple
> databases anyway. So in principle there's no reason we shouldn't
> allow
> it to do the CREATE DATABASE, switch into the new DB, and then start
> the
> transaction.
>
> However, one of the properties -1 is supposed to have is that any
> failure aborts the whole restore; it's not immediately clear how to
> preserve that with CREATE DATABASE issued separately.

Good point. Declare as incompatible it is, then :) it's not like it's
hard do create the database before restoring.

> Also you need to think about whether this might impact pg_dumpall.

Dumps from pg_dumpall aren't restored through pg_restore...

>
>> As for -c, the solution would be to issue DROP IF EXISTS
>> statements. Is
>> there any particular reason why we don't?
>
> I think we did that to avoid damaging portability and backwards
> compatibility of the dump files. The backwards compatibility argument
> is pretty weak by now, but the "it's not standard SQL" argument still
> has force.
>

IIRC the drop statements are generated by pg_restore and not stored in
the archive. So we could do the if exists by default and have a switch
to turn it off for a compatible dump, perhaps?

/Magnus