Pivotal Knowledge Base

Follow

gptransfer Fails with "relation does not exist" Error when Transferring Tables with Foreign Keys

Environment

  • Pivotal Greenplum Database (GPDB) 4.3.x
  • Tables with the foreign keys

Symptom

Running gptransfer over a database with user tables with foreign keys fails with the following error:

20171201:10:18:05:430513 gptransfer:mdw:gpadmin-[DEBUG]:-[worker0] finished cmd: transfer of foo.bar cmdStr='None' had result: cmd had rc=1 completed=False halted=False 
stdout='error 'ERROR: relation "example" does not exist

Cause

In the example above, the table foo.bar has a foreign key that references the table example. In the gptransfer run, the table example is transferred before the table foo.bar, therefore the foreign key reference fails and the table foo.bar can't be created.

This issue is reported to the Research and Development team and it will be fixed in a future release.

Resolution

  1. Take a schema-only dump of the source database
  2. Restore the dump in the target database
  3. Run gptransfer with option --truncate

Example of the commands:

pg_dump -F c -f source-db.dump --schema-only source

createdb destination
pg_restore -d source --schema-only source-db.dump

gptransfer -d source --dest-database=destination --truncate

The --truncate option will truncate the tables in the target system, which are empty already because we just restored the schema. The tables in the source system are not affected.

Comments

Powered by Zendesk