pg_restore --clean is not dropping and clearing the database

Question

I am having an issue with pg_restore --clean not clearing the database.

Or do I misunderstand what the --clean does, I am expecting it to truncate the database tables and reinitialize the indexes/primary keys.

I am using 9.5 on rds

This is the full command we use

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U superuser -d mydatabase backup.dump

Basically what is happening is this.

I do a nightly backup of my production db, and restore it to an analytics db for the analyst to churn and run their reports.

I found out recently that the rails application used to view the reports was complaining that the primary keys were missing from the restored analytics database.

So I started investigating the production db, the analytics db etc. Which was when I realized that multiple rows with the same primary key existed in the analytics database.

I ran a few short experiments and realized that every time the pg_restore script is run it inserts duplicate data into the tables, this leads me to think that the --clean is not dropping and restoring the data. Because if I were to drop the schema beforehand, I don't get duplicate data.

Answer 1

To remove all tables from a database (but keep the database itself), you have two options.

Option 1: Drop the entire schema You will need to re-create the schema and its permissions. This is usually good enough for development machines only.

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

Applications usually use the "public" schema. You may encounter other schema names when working with a (legacy) application's database.

Note that for Rails applications, dropping and recreating the database itself is usually fine in development. You can use bin/rake db:drop db:create for that.

Option 2: Drop each table individually Prefer this for production or staging servers. Permissions may be managed by your operations team, and you do not want to be the one who messed up permissions on a shared database cluster.

The following SQL code will find all table names and execute a DROP TABLE statement for each.

DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE'; -- DROP TABLE IF EXISTS instead DROP TABLE - thanks for the clarification Yaroslav Schekin
  END LOOP;
END $$;

Original:

https://makandracards.com/makandra/62111-how-to-drop-all-tables-in-postgresql