Re: pg_dump'ed file contains "DROP DATABASE"

On 2/20/23 10:27, pf(at)pfortin(dot)com wrote:
> [Still a newbie; but learning fast...]
>
> Hi,
>
> A remote team member is helping out by dumping some of his tables via
> pgAdmin4 on Windows. My DB is on Linux.
>
> The other day, I restored his first file with:
> pg_restore --host "localhost" --port "5432" --username "postgres"
> --no-password --dbname "myname" --create --clean --verbose "dumpfile"
>
> when I saw this:
>
> pg_restore: dropping DATABASE myname
> Command was: DROP DATABASE myname;
>
> pg_restore: error: could not execute query:
> ERROR: cannot drop the currently open database
>
> Digging into the pg_dump'ed files, I see:
>
> CREATE DATABASE myname WITH TEMPLATE = template0 ENCODING = 'UTF8'
> LOCALE_PROVIDER = libc LOCALE = 'English_United States.1252';
> DROP DATABASE myname;
> ^^^^^^^^^^^^^^^^^^^^^
>
> Was my 134 table[1] myname DB saved because it was open? If the dump file
> contains the above statements, how can I be absolutely certain I won't
> lose the DB? I'm obviously quite paranoid now...

You will lose the database if you do as the docs specify for -C:

"
-C

...

When this option is used, the database named with -d is used only to
issue the initial DROP DATABASE and CREATE DATABASE commands. All data
is restored into the database name that appears in the archive.
"

It will then be recreated with whatever information is in "dumpfile". If
that is the same data or new data you want then you are fine. Otherwise
you will need to be more specific about what you are trying to achieve.

>
> [1] 3 types of tables: ~40%=8.5M rows; ~40%=33M rows; ~20%=varying sizes
>
> Thanks,
> Pierre
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com