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