Обсуждение: [HACKERS] pg_restore failed for foreign key constraint

Поиск
Список
Период
Сортировка

[HACKERS] pg_restore failed for foreign key constraint

От
Neha Sharma
Дата:
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

Re: [HACKERS] pg_restore failed for foreign key constraint

От
Tom Lane
Дата:
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