pg_dump custom format file contains 'DROP DATABASE'

Question

I wish to pg_dump a specific schema from one database and pg_restore it into an already existing database without dropping it.

The command I have been using for the pg_dump is as follows:

pg_dump -n mySchema -Z 9 -b -f mySchema.sql.gz -F c -U ${db_user} -h ${db_host} ${db_name}

-F c generates a custom format file suitable for pg_restore.

I'm aware that if I included the --clean flag it would 'clean (drop) database objects prior to outputting the commands for creating them.' according to the documentation here.

I did not include this flag.

However, when I run head on the generated file, I can see the following within it: DROP DATABASE myDatabase. Why is it here? I'm afraid that if I do a pg_restore with this file that it will drop my existing database.

Here is what head returns:

    PGDMP
        ymyDatabase11.8"11.10 (Ubuntu 11.10-1.pgdg18.04+1)�0ENCODINENCODINGSET client_encoding = 'UTF8';
false�00
STDSTRINGS
STDSTRINGS(SET standard_conforming_strings = 'on';
false�00
SEARCHPATH
SEARCHPATH8SELECT pg_catalog.set_config('search_path', '', false);
false�126221356smarDATABASEwCREATE DATABASE myDatabase WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
DROP DATABASE myDatabase;
myUserfalse�00DATABASE smartACL/GRANT CONNECT ON DATABASE myDatabase TO readaccess;

Laurenz Albe · Accepted Answer · 2021-02-22 15:07:17Z

3

A custom format dump includes the DDL to drop the listed objects.

That is because with a custom format dump, you can specify the --clean option with pg_restore, that is, you don't need if you need the DROP statements until the dump is restored.

However, if you don't use --clean with pg_restore, the DROP statements won't be executed, so you don't have to worry.

answered Feb 22, 2021 at 15:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pg_dump custom format file contains 'DROP DATABASE'

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related