I'm working with a copy (DB-B) of a database (DB-A) I keep up to date by running daily:

pg_restore -U postgres -d someDB --clean -j 2 -L WhatToImportList admin.dump

But I started noticing duplicate records in DB-B tables. Upon further investigation seems the --clean is not dropping the tables in DB-B because it would require a cascade drop on other tables and views that are in my DB-B but NOT in the origin DB-A.

  1. Is it possible to force the import of data WITHOUT doing a cascade drop? I want to keep all my custom tables, views and functions!
  2. If not, what would be a good duplication strategy where I import the data from DB-A to DB-B but keep all my functions, views and tables I need for my analysis in DB-B?


Edit: possible work around: truncating each table and then importing them... but i'd have to include in the script EACH TABLE.

Edit2: To help future readers, with this managed to generate a list of all the tables that had to be truncated:

SELECT 'TRUNCATE ' || table_name || ';'
  FROM information_schema.tables
 WHERE table_schema='my_schema'
   AND table_type='BASE TABLE';
  • I cannot think of a better way either. – Laurenz Albe Dec 14 '20 at 9:21
  • @LaurenzAlbe , if YOU do not know other way, is because there is NO other way! (when using pg_restore, that is). Thanks for checking my post. – Gabriel A. Zorrilla Dec 14 '20 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.