I try dumping tables from a production environment to a dev one. However, when dumping and restoring this table, using the following command:

pg_restore --no-owner --no-acl --clean --if-exists -d database dump_file.dump

I get an error stating that I can't drop that table unless I use something like CASCADE (i.e. dropping all other tables that depend on that one). Is there a way to determine the tables to be dropped? is there a way of maybe state in the pg_dump command to dump the table I'm looking to dump and all related tables ?

Here's the error raised:

pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 4066; 2606 30526 CONSTRAINT table1 pkey user pg_restore: error: could not execute query: ERROR: cannot drop constraint pkey on table public.table1 because other objects depend on it DETAIL: constraint id_fkey on table public.dag depends on index public.pkey constraint id_fkey on table public.dag depends on index public.pkey HINT: Use DROP ... CASCADE to drop the dependent objects too...

CodePudding user response：

You have a table on the dev database that has a pkey that is dependent and therefore can not be dropped before the restore. This is proper behavior.

CodePudding user response：

1. I am not seeing dumping/restoring a particular table. You are dumping/restoring the entire database.

2. If you want recreate the production database as a dev one then do:

pg_restore -C --no-owner --no-acl --clean --if-exists -d postgres dump_file.dump

The -C with --clean will DROP DATABASE db_name and then rebuild it from scratch by connecting to the database postgres to do the DROP/CREATE db_name and then connect to db_name to load the rest of the objects.

This is the best way to clean out cruft and start at a consistent state.