Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_dump -n schema -Fc has a DROP DATABASE command... confused

pg_dump -n schema -Fc has a DROP DATABASE command... confused

From
Wells Oliver
Date:
I did a few pg_dumps of different schemas using:

pg_dump -h host dbname -n schema -Fc > schema.dump

Opening it in VIM brings up a binary file (compressed, sure) but it looks like this:

Screen Shot 2022-01-20 at 8.40.24 PM.png

Which contains the line "DROP DATABASE". Which is weird-- it does not drop the database, I mean, I restored from the same file. Why is this in there?

Doing a text dump w/o -Fc yields a text file with no DROP statement.

More just curious, less afraid at this point.

--
Attachment

Re: pg_dump -n schema -Fc has a DROP DATABASE command... confused

From
"David G. Johnston"
Date:
On Thu, Jan 20, 2022 at 9:45 PM Wells Oliver <wells.oliver@gmail.com> wrote:
pg_dump -h host dbname -n schema -Fc > schema.dump

Opening it in VIM brings up a binary file (compressed, sure) but it looks like this:

Which contains the line "DROP DATABASE". Which is weird-- it does not drop the database, I mean, I restored from the same file. Why is this in there?

Doing a text dump w/o -Fc yields a text file with no DROP statement.
 
The whole point of the custom format - which indeed defaults to compressed mode (read the docs) - is to move any decisions about what to do during restoring to the pg_restore commands.  So, if the restore wants to drop the database it has a drop database command available which it can execute.  If it doesn't that command will simply be skipped.

-Fp is a plain text format meant to be run via psql (or any other program that can execute a basic SQL script - again, see the docs).  Such a program is not expected to specify restore conditions so whatever was defined during the pg_dump will simply take effect.

David J.

Re: pg_dump -n schema -Fc has a DROP DATABASE command... confused

From
Tom Lane
Date:
Wells Oliver <wells.oliver@gmail.com> writes:
> I did a few pg_dumps of different schemas using:
> pg_dump -h host dbname -n schema -Fc > schema.dump
> Opening it in VIM brings up a binary file (compressed, sure) but it looks
> like this:
> Which contains the line "DROP DATABASE". Which is weird-- it does not drop
> the database, I mean, I restored from the same file. Why is this in there?

pg_dump -Fc produces an archive file that pg_restore can use to
do whatever you can ask pg_restore to do.  One of those things
is the --clean switch, which says to drop every database object
before restoring.  So among the strings in the archive file you
will find DROP commands for all the dumped objects.  Those are
not issued by default, though.

(The other DROP commands might be obscured by compression.
Try --compress=0 to get a clearer view of what's in there.)

            regards, tom lane