Re: pg_dump'ed file contains "DROP DATABASE"

On 2/20/23 11:36, pf@pfortin.com wrote:
> On Mon, 20 Feb 2023 11:06:34 -0800 Adrian Klaver wrote:
> 
>> On 2/20/23 10:27, pf@pfortin.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.klaver@aklaver.com