Re: pg_dump'ed file contains "DROP DATABASE"

On 2/20/23 11:36, pf(at)pfortin(dot)com wrote:
> On Mon, 20 Feb 2023 11:06:34 -0800 Adrian Klaver wrote:
>
>> 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;
>>> ^^^^^^^^^^^^^^^^^^^^^
>
> I thought the --clean applied to the table being restored. The man page
> reads:
>
> -c |||||||
> --clean VVVVVVV
> Clean (drop) database objects before recreating them. (Unless
> --if-exists is used, this might generate some harmless error
> messages, if any objects were not present in the destination
> vs => database.)
>
> so I took that to mean table; not the entire DB.

1) If you clean(drop) all the objects in a database you have effectively
got to the same point as dropping the database.

>
> Notwithstanding the man page, my take is that the DROP DATABASE statement
> needs to be eliminated at pg_dump creation by pgAdmin4. Taking this to
> that mailing list.

This is not on pgAdmin4 If the dump is done with the custom format the
-c and -C can be done on the pg_restore end per:

https://www.postgresql.org/docs/current/app-pgdump.html

-c

-C

This option is ignored when emitting an archive (non-text) output file.
For the archive formats, you can specify the option when you call
pg_restore.

So this:

pg_restore --host "localhost" --port "5432" --username "postgres"
--no-password --dbname "myname" --create --clean --verbose "dumpfile"

is on you not pgAdmin4.

Spend some time in the pg_dump and pg_restore docs, there is a lot going
on in there.
>
> Thanks Tom & Adrian!
>

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