pg_restore complains both if the database exists or not when importing a backup

Question

Postgresql version: 15 Backup was made from version 12.

(I don't know if --schema-only is part of the problem.)

I have a single backup tar file of a database that I'm trying to restore, but I keep running into what feels like weird conflicts.

If I pre-create the database weatherdata and try to use the --create option, I get the expected error that the database already exists.

postgres@logs:~$ pg_restore -e --create --schema-only -U postgres -d weatherdata /home/geuis/postgres-bak-3-31-22.tar 
pg_restore: error: could not execute query: ERROR: database "weatherdata" already exists
Command was: CREATE DATABASE weatherdata WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

But if I drop the database and then run the same command, it complains that weatherdata DOESN'T exist

postgres=# DROP DATABASE weatherdata;
DROP DATABASE
postgres=# exit
postgres@logs:~$ pg_restore -e --create --schema-only -U postgres -d weatherdata /home/geuis/postgres-bak-3-31-22.tar 
pg_restore: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "weatherdata" does not exist

I've been back and forth in the pg_restore docs https://www.postgresql.org/docs/15/app-pgrestore.html and some other questions that seem related https://stackoverflow.com/questions/58970102/how-to-restore-a-postgres-backup-error-cannot-drop-the-currently-open-databas but I'm at a bit of a loss as to what I'm doing wrong.

Answer 1

From the docs you linked:

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.

If the database already exists, you can't create it. If it doesn't already exist, you can't connect to it in order to create itself. So connect to a different database (traditionally 'postgres' is used) in order to create the new 'weatherdata' database.

• Geuis: Thanks for clearing that up. Soon as I changed that, the restore worked without a problem.