pg_restore failed for foreign key constraint

Started by Neha Sharmaover 8 years ago2 messages
#1Neha Sharma
neha.sharma@enterprisedb.com

Hi,

I am getting the below error while restoring data having foreign key
constraint.

./psql postgres
psql (10beta2)
Type "help" for help.

postgres=# create user test;
CREATE ROLE
postgres=# create schema test;
CREATE SCHEMA
postgres=# grant all on SCHEMA test to test;
GRANT
postgres=# set search_path to 'test';
SET
postgres=# CREATE TABLE test_tbl_fk (c1 INTEGER PRIMARY KEY);
CREATE TABLE
postgres=# CREATE TABLE test_tbl (c1 INTEGER PRIMARY KEY, c2 INTEGER, c3
VARCHAR,
postgres(# CONSTRAINT test_tbl_fk_c1 FOREIGN KEY (c1) REFERENCES
test_tbl_fk(c1));
CREATE TABLE
postgres=# insert into test_tbl_fk values (1),(2),(3);
INSERT 0 3
postgres=# INSERT INTO test_tbl VALUES (1,1,'p11');
INSERT 0 1
postgres=# INSERT INTO test_tbl VALUES (2,2,'p11');
INSERT 0 1
postgres=# INSERT INTO test_tbl VALUES (3,3,'p11');
INSERT 0 1
postgres=# show search_path ;
search_path
-------------
test
(1 row)

postgres=# \q
[edb@localhost bin]$ ./pg_dump -f a.dump -Fc postgres
[edb@localhost bin]$ ./createdb test1
[edb@localhost bin]$ ./pg_restore -d test1 -c -e a.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2903; 2606 16399 FK
CONSTRAINT test_tbl test_tbl_fk_c1 edb
pg_restore: [archiver (db)] could not execute query: ERROR: schema "test"
does not exist
Command was: ALTER TABLE ONLY test.test_tbl DROP CONSTRAINT
test_tbl_fk_c1;

Is this an expected behaviour?

--
Regards,
Neha Sharma

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Neha Sharma (#1)
Re: pg_restore failed for foreign key constraint

Neha Sharma <neha.sharma@enterprisedb.com> writes:

I am getting the below error while restoring data having foreign key
constraint.

[edb@localhost bin]$ ./createdb test1
[edb@localhost bin]$ ./pg_restore -d test1 -c -e a.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2903; 2606 16399 FK
CONSTRAINT test_tbl test_tbl_fk_c1 edb
pg_restore: [archiver (db)] could not execute query: ERROR: schema "test"
does not exist
Command was: ALTER TABLE ONLY test.test_tbl DROP CONSTRAINT
test_tbl_fk_c1;

Is this an expected behaviour?

Yes, it is. Either don't use -c (it's entirely useless when restoring
into an empty database), or specify --if-exists, or ignore the errors.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers