Postgres: clear entire database before re-creating / re-populating from bash script

Question

I'm writing a shell script (will become a cronjob) that will:

1: dump my production database

2: import the dump into my development database

Between step 1 and 2, I need to clear the development database (drop all tables?). How is this best accomplished from a shell script? So far, it looks like this:

#!/bin/bash
time=`date '+%Y'-'%m'-'%d'`
# 1. export(dump) the current production database
pg_dump -U production_db_name > /backup/dir/backup-${time}.sql
# missing step: drop all tables from development database so it can be re-populated
# 2. load the backup into the development database
psql -U development_db_name < backup/dir/backup-${time}.sql

• ruuter: oneliner for people in a hurry: dbname='db_name' && dropdb $dbname && createdb $dbname && psql -d $dbname -f dump.sql
• ribamar: this oneliner requires you to have permissions to create/drop the database. the approach the author is trying doesn't require special privileges.

Answer 1

I'd just drop the database and then re-create it. On a UNIX or Linux system, that should do it:

$ dropdb development_db_name
$ createdb development_db_name

That's how I do it, actually.

• pstanton: yep. this is better because there may be objects that aren't part of the dump you are restoring. in this case they will definitely be killed.
• Alvin: one trick that saves me time is $ sudo -u postgres dropdb DATABASE_NAME
• Emanuele Paolini: But... what about database permissions and ownership?
• mcalex: @EmanuelePaolini createdb --owner=db_owner [--template=template0 --encoding=UTF8] db_name i add the last two by default to all databases
• otocan: Does this automatically close all active connections? Because that's the issue I have when using DROP DATABASE within psql.

Answer 2

If you don't actually need a backup of the database dumped onto disk in a plain-text .sql script file format, you could connect pg_dump and pg_restore directly together over a pipe.

To drop and recreate tables, you could use the --clean command-line option for pg_dump to emit SQL commands to clean (drop) database objects prior to (the commands for) creating them. (This will not drop the whole database, just each table/sequence/index/etc. before recreating them.)

The above two would look something like this:

pg_dump -U username --clean | pg_restore -U username

• Hoff: i like this solution, since i do want a backup copy, i'm now doing this: pg_dump -Ft -U production_db_name > /backup/dir/backup-${time}.tar pg_restore -U development_db_name -d development_db_name -O --clean /backup/dir/backup-${time}.tar works like a charm, thanks for your help!
• ianaré: Beware: the --clean option only removes those relations found in the restore file. This means that if you add a table for testing, then want to remove it (to synchronise with the production DB for example), it will not be removed.
• Kikin-Sama: It's important to have in mind that pg_dump's --clean option only works with plain text backups. As the documentation clearly states here postgresql.org/docs/9.4/static/app-pgdump.html, you need to use --clean on pg_restore for archived backups.
• user4674453: Is there any way to include cascade in "--clean" option. As it is this option looks useless. I'm getting "ERROR: cannot drop schema public because other objects depend on it" like 100% of time using it.
• jbg: The question asked about removing all tables. This only removes tables found in the database that pg_dump is dumping from.

Answer 3

To dump:

pg_dump -Fc mydb > db.dump

To restore:

pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my_db db/latest.dump

Answer 4

If you want to clean your database named "example_db":

1) Login to another db(for example 'postgres'):

psql postgres

2) Remove your database:

DROP DATABASE example_db;

3) Recreate your database:

CREATE DATABASE example_db;

Answer 5

Clear database completely

Note: pg_dump & pg_restore don't fully clear the entire database.

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

If you are using PostgreSQL 9.3 or greater, you may also need to restore the default grants.

GRANT ALL ON SCHEMA public TO <your_db_username>;
GRANT ALL ON SCHEMA public TO public;

• Rafs: This works for postgres database on cloud PaaS offers as dropping postgres is not an option, since it requires superuser rights. For instance, on Azure, only azuresu is a superuser which end users can't switch to.

Answer 6

Although the following line is taken from a windows batch script, the command should be quite similar:

psql -U username -h localhost -d postgres -c "DROP DATABASE \"$DATABASE\";"

This command is used to clear the whole database, by actually dropping it. The $DATABASE (in Windows should be %DATABASE%) in the command is a windows style environment variable that evaluates to the database name. You will need to substitute that by your development_db_name.

• Mike 'Pomax' Kamermans: then why not use the already available dropdb and createdb commands? If you can run psql, you can run those as well.

Answer 7

Note: my answer is about really deleting the tables and other database objects; for deleting all data in the tables, i.e. truncating all tables, Endre Both has provided a similarily well-executed (direct execution) statement a month later.

For the cases where you can’t just DROP SCHEMA public CASCADE;, DROP OWNED BY current_user; or something, here’s a stand-alone SQL script I wrote, which is transaction-safe (i.e. you can put it between BEGIN; and either ROLLBACK; to just test it out or COMMIT; to actually do the deed) and cleans up "all" database objects... well, all those used in the database our application uses or I could sensibly add, which is:

• triggers on tables
• constraints on tables (FK, PK, CHECK, UNIQUE)
• indicēs
• VIEWs (normal or materialised)
• tables
• sequences
• routines (aggregate functions, functions, procedures)
• all nōn-default (i.e. not public or DB-internal) schemata "we" own: the script is useful when run as "not a database superuser"; a superuser can drop all schemata (the really important ones are still explicitly excluded, though)
• extensions (user-contributed but I normally deliberately leave them in)

Not dropped are (some deliberate; some only because I had no example in our DB):

• the public schema (e.g. for extension-provided stuff in them)
• collations and other locale stuff
• event triggers
• text search stuff, ... (see here for other stuff I might have missed)
• roles or other security settings
• composite types
• toast tables
• FDW and foreign tables

This is really useful for the cases when the dump you want to restore is of a different database schema version (e.g. with Debian dbconfig-common, Flyway or Liquibase/DB-Manul) than the database you want to restore it into.

I’ve also got a version which deletes "everything except two tables and what belongs to them" (a sequence, tested manually, sorry, I know, boring) in case someone is interested; the diff is small. Contact me or check this repo if interested.

SQL

-- Copyright © 2019, 2020
-- mirabilos <[email protected]>
--
-- Provided that these terms and disclaimer and all copyright notices
-- are retained or reproduced in an accompanying document, permission
-- is granted to deal in this work without restriction, including un‐
-- limited rights to use, publicly perform, distribute, sell, modify,
-- merge, give away, or sublicence.
--
-- This work is provided "AS IS" and WITHOUT WARRANTY of any kind, to
-- the utmost extent permitted by applicable law, neither express nor
-- implied; without malicious intent or gross negligence. In no event
-- may a licensor, author or contributor be held liable for indirect,
-- direct, other damage, loss, or other issues arising in any way out
-- of dealing in the work, even if advised of the possibility of such
-- damage or existence of a defect, except proven that it results out
-- of said person’s immediate fault when using the work as intended.
-- -
-- Drop everything from the PostgreSQL database.
DO $$
DECLARE
 q TEXT;
 r RECORD;
BEGIN
 -- triggers
 FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
 FROM pg_catalog.pg_trigger pt, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace AND pc.oid=pt.tgrelid
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pt.tgisinternal=false
 ) LOOP
 EXECUTE format('DROP TRIGGER %I ON %I.%I;',
 r.tgname, r.nspname, r.relname);
 END LOOP;
 -- constraints #1: foreign key
 FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
 FROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pcon.contype='f'
 ) LOOP
 EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
 r.nspname, r.relname, r.conname);
 END LOOP;
 -- constraints #2: the rest
 FOR r IN (SELECT pns.nspname, pc.relname, pcon.conname
 FROM pg_catalog.pg_constraint pcon, pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace AND pc.oid=pcon.conrelid
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pcon.contype<>'f'
 ) LOOP
 EXECUTE format('ALTER TABLE ONLY %I.%I DROP CONSTRAINT %I;',
 r.nspname, r.relname, r.conname);
 END LOOP;
 -- indicēs
 FOR r IN (SELECT pns.nspname, pc.relname
 FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pc.relkind='i'
 ) LOOP
 EXECUTE format('DROP INDEX %I.%I;',
 r.nspname, r.relname);
 END LOOP;
 -- normal and materialised views
 FOR r IN (SELECT pns.nspname, pc.relname
 FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pc.relkind IN ('v', 'm')
 ) LOOP
 EXECUTE format('DROP VIEW %I.%I;',
 r.nspname, r.relname);
 END LOOP;
 -- tables
 FOR r IN (SELECT pns.nspname, pc.relname
 FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pc.relkind='r'
 ) LOOP
 EXECUTE format('DROP TABLE %I.%I;',
 r.nspname, r.relname);
 END LOOP;
 -- sequences
 FOR r IN (SELECT pns.nspname, pc.relname
 FROM pg_catalog.pg_class pc, pg_catalog.pg_namespace pns
 WHERE pns.oid=pc.relnamespace
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pc.relkind='S'
 ) LOOP
 EXECUTE format('DROP SEQUENCE %I.%I;',
 r.nspname, r.relname);
 END LOOP;
 -- extensions (only if necessary; keep them normally)
 FOR r IN (SELECT pns.nspname, pe.extname
 FROM pg_catalog.pg_extension pe, pg_catalog.pg_namespace pns
 WHERE pns.oid=pe.extnamespace
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 ) LOOP
 EXECUTE format('DROP EXTENSION %I;', r.extname);
 END LOOP;
 -- aggregate functions first (because they depend on other functions)
 FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
 FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns, pg_catalog.pg_aggregate pagg
 WHERE pns.oid=pp.pronamespace
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
 AND pagg.aggfnoid=pp.oid
 ) LOOP
 EXECUTE format('DROP AGGREGATE %I.%I(%s);',
 r.nspname, r.proname,
 pg_get_function_identity_arguments(r.oid));
 END LOOP;
 -- routines (functions, aggregate functions, procedures, window functions)
 IF EXISTS (SELECT * FROM pg_catalog.pg_attribute
 WHERE attrelid='pg_catalog.pg_proc'::regclass
 AND attname='prokind' -- PostgreSQL 11+
 ) THEN
 q := 'CASE pp.prokind
 WHEN ''p'' THEN ''PROCEDURE''
 WHEN ''a'' THEN ''AGGREGATE''
 ELSE ''FUNCTION''
 END';
 ELSIF EXISTS (SELECT * FROM pg_catalog.pg_attribute
 WHERE attrelid='pg_catalog.pg_proc'::regclass
 AND attname='proisagg' -- PostgreSQL ≤10
 ) THEN
 q := 'CASE pp.proisagg
 WHEN true THEN ''AGGREGATE''
 ELSE ''FUNCTION''
 END';
 ELSE
 q := '''FUNCTION''';
 END IF;
 FOR r IN EXECUTE 'SELECT pns.nspname, pp.proname, pp.oid, ' || q || ' AS pt
 FROM pg_catalog.pg_proc pp, pg_catalog.pg_namespace pns
 WHERE pns.oid=pp.pronamespace
 AND pns.nspname NOT IN (''information_schema'', ''pg_catalog'', ''pg_toast'')
 ' LOOP
 EXECUTE format('DROP %s %I.%I(%s);', r.pt,
 r.nspname, r.proname,
 pg_get_function_identity_arguments(r.oid));
 END LOOP;
 -- nōn-default schemata we own; assume to be run by a not-superuser
 FOR r IN (SELECT pns.nspname
 FROM pg_catalog.pg_namespace pns, pg_catalog.pg_roles pr
 WHERE pr.oid=pns.nspowner
 AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast', 'public')
 AND pr.rolname=current_user
 ) LOOP
 EXECUTE format('DROP SCHEMA %I;', r.nspname);
 END LOOP;
 -- voilà
 RAISE NOTICE 'Database cleared!';
END; $$;

Tested, except later additions (extensions contributed by Clément Prévost), on PostgreSQL 9.6 (jessie-backports). Aggregate removal tested on 9.6 and 12.2, procedure removal tested on 12.2 as well. Bugfixes and further improvements welcome!

Answer 8

I've used:

pg_restore -c -d database_name filename.dump