I am trying to restore a database dump in postgreSQL. The database dump has been produced using pg_dump. The command that I am using is

pg_restore -C -d postgres -v -h localhost -p 5432 -U postgres "C:\[file_path]\filename.dmp"

Unfortunately, I do not get any error message and I do not see any database created.

Please note that I have already checked the pg_restore documentation and a relevant post in stackoverflow (pg_restore with -C option does not create the database).

Please also note that I use postgreSQL 16 on Windows 11 Pro.

I am trying to restore a database dump in postgreSQL. The database dump has been produced using pg_dump. The command that I am using is

pg_restore -C -d postgres -v -h localhost -p 5432 -U postgres "C:\[file_path]\filename.dmp"

Unfortunately, I do not get any error message and I do not see any database created.

Please note that I have already checked the pg_restore documentation and a relevant post in stackoverflow (pg_restore with -C option does not create the database).

Please also note that I use postgreSQL 16 on Windows 11 Pro.

• postgresql

Share Follow asked Mar 14 at 14:03 Pantelis NatsiavasPantelis Natsiavas 5,38955 gold badges2323 silver badges4040 bronze badges 4

• Make sure this hits the right database cluster/server. You could have some tunnels set up, a docker container running, something redirecting the connection elsewhere. Does your command complete or just hang, doing nothing? If you connect to that db and observe pg_stat_activity do you see any new connections&queries when you start the command? Do you run this directly/manually, or as a part of some script? In some scenarios if there's no .pgpass file, it could be hanging on password prompt unless you add -w or --no-password. – Zegarek Commented Mar 14 at 14:18
• Check whether pg_restore executed successfully by inspecting its exit code echo %ERRORLEVEL%. An error code of 0 indicates successful execution. Non-zero values indicate a failure – Szymon Roziewski Commented Mar 14 at 14:32
• @Zegarek thank you very much for your insights. However, this is not the case. My setup is totally "clear". No dockering, no tunnels, no networking. – Pantelis Natsiavas Commented Mar 14 at 14:46
• @SzymonRoziewski echo %ERRORLEVEL% also produces no output – Pantelis Natsiavas Commented Mar 14 at 14:51

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

I am not really sure why this works but I could actually do my work using the command

psql -U postgres -d [databasename] < "C:\[filepath]\[filename].dmp"

Please note that this command was executed from windows command line directly and not the psql command prompt.

If you use the option -C (or --create) to dump the database postgres (do you really keep your data in that database?), the dump is going to look like that:

/* some SET commands */

CREATE DATABASE postgres WITH TEMPLATE = template0 ...

ALTER DATABASE postgres OWNER TO postgres;

\connect postgres

/* the rest of the dump */

Now when you try to restore that dump, the CREATE DATABASE statement will certainly fail and produce an error message.

So I'd say that the -C option is a mistake to begin with.

But your fundamental problem is that you are trying to use pg_restore to restore a plain format (SQL file) dump. That just won't work. You need psql to restore a plain format dump.