Thread: pg_dumpall --clean is completely broken

A thread over in -admin has made me realize the truth of $SUBJECT.
With --clean, pg_dumpall does indeed emit a DROP command for each
role, tablespace, or database ... just before recreating it.  This
takes no account of dependencies and so the role and tablespace
drops are pretty much guaranteed to fail due to databases still
depending on them.

I'm not sure if we need any real dependency analysis.  It seems
like it would be sufficient to issue the drops in a separate
pass:- drop all the databases- drop all the tablespaces- drop all the roles- go on with creation

The roles might still have references to each other in step 3,
but the DROP ROLE docs claim that's okay (I haven't tested).

Comments?
        regards, tom lane

Tom Lane wrote:
> A thread over in -admin has made me realize the truth of $SUBJECT.
> With --clean, pg_dumpall does indeed emit a DROP command for each
> role, tablespace, or database ... just before recreating it.  This
> takes no account of dependencies and so the role and tablespace
> drops are pretty much guaranteed to fail due to databases still
> depending on them.
> 
> I'm not sure if we need any real dependency analysis.  It seems
> like it would be sufficient to issue the drops in a separate
> pass:
>     - drop all the databases
>     - drop all the tablespaces
>     - drop all the roles
>     - go on with creation
> 
> The roles might still have references to each other in step 3,
> but the DROP ROLE docs claim that's okay (I haven't tested).

Does your recently-applied patch address any of these TODO items?
Stop dumping CASCADE on DROP TYPE commands in clean modeAllow pg_dump --clean to drop roles that own objects orhave
privileges
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:
> Does your recently-applied patch address any of these TODO items?

>     Stop dumping CASCADE on DROP TYPE commands in clean mode

That has nothing to do with pg_dumpall.
>     Allow pg_dump --clean to drop roles that own objects or
>     have privileges 

Hmm ... pg_dump never drops roles at all, and shouldn't.  Is this
a garbled reference to pg_dumpall?  If so I might've fixed it.
It's not entirely clear what the item is about though.
        regards, tom lane