pg_restore ERROR: “Relation does not exist” and creating new database

Question

I have made a backup of my specific tables that I want to restore into a new database using:

call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public.table2

And I have no problems.

I then want to restore the dump by creating a new database with pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This gives me a "database temp does not exist" error. This should have created the database and restored it as far as I understand.

I However then create the "temp" database manually and run:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

This follows through, but does not create the tables and gives me an error "relation table1" and "relation table2" does not exist and only creates the corresponding id_sequences for the two tables.

What I actually want is to not have to manually create the new database and that all tables in the backup is restored into a brand new database via pg_restore using:

call pg_restore --clean --create -d temp -h server -p password -U user dump.sql

as I understand it.

Laurenz Albe · Accepted Answer · 2018-04-06 06:50:09Z

3

In order to create the database temp, pg_restore needs to be connected to a different database first.

So it won't do to use -d temp; you must specify an existing database, typically postgres.

pg_restore will connect to that database, issue CREATE DATABASE temp, connect to temp and proceed to restore the data.

answered Apr 6, 2018 at 6:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yuqiu G. · Accepted Answer · 2023-04-13 08:26:51Z

-1

for me, I installed pgAdmin 4 and did the restore via pgAdmin 4 UI, instead of doing it via cmd:

pg_restore -d <database_name> -p 5432 -Fc -O -a --no-tablespaces --no-privileges -v -U postgres -j 8 <database_name>.dmp

answered Apr 13, 2023 at 8:26

Yuqiu G.

3584 silver badges8 bronze badges

2 Comments

Alexandros Papadopoulos Over a year ago

This does not answer the question

Yuqiu G. Over a year ago

restore over the pgAdmin 4 UI

Collectives™ on Stack Overflow

pg_restore ERROR: “Relation does not exist” and creating new database

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related