Cannot restore postgres dump file

Question

When i try to restore a database from a dump file using the following command:

 \i C:/pathname/dumpfile.txt

from within my sql shell, it runs the first few lines of the dump file and then I get the following error:

ERROR: permission denied to create "pg_catalog.databasename"
DETAIL: System catalog modifications are currently disallowed.

This shows the beginning of the dump file. I think the CREATE TABLE command triggers the error.

enter image description here

I'm using postgreSQL Version 11.1. Any ideas how to solve this?

• Vérace: Have you created an empty schema for your data? Maybe you could try that?
• user169425: But then how do I restore the dump into this empty schema?
• jjanes: Do you still have access to the old database? if so, redo the dump using the pg_dump from 11.1, not from 9.6.0.
• jjanes: Unless you changed the setting of "log_min_error_statement", you shouldn't have to guess what statement triggered the ERROR. The ERROR message will include the triggering statement. Using the default log format, it will be flagged with "STATEMENT:"
• user169425: I don't have access to the old database so I can't redo the dump. I tried to restore it using Version 9.6.0 though, but that didn't work either.

Answer 1

Ok I solved it: in the following line:

SET search_path = car, pg_catalog;

apparantly, car is the name of the schema. As that schema didn't exist, it tried to create a table in the pg_catalog schema. I manually created a schema called car, and, tada.. Thanks to everybody for their support.

Answer 2

First check if you need to set the search_path (see the other answer to the question).

SET search_path = car, pg_catalog;

Then check if the schema exists. If not, then add this to your SQL dump file:

CREATE SCHEMA car;
ALTER SCHEMA car OWNER TO car;