Sample Header Ad - 728x90

pg_restore --clean not working because cascade drop

10 votes
1 answer
3859 views
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**? **Possible work around**: truncating each table and then importing them... but I'd have to include in the script EACH TABLE. **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';
Asked by Gabriel (387 rep)
Dec 11, 2020, 09:52 PM
Last activity: Jun 22, 2025, 04:26 AM