15

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server.

pg_dump -h localhost -U adminuser --data-only --column-inserts maindb > maindb.sql

How do I delete all data in the staging server database first, before restoring the data from the production dump?

I want to delete all data only so I don't have to drop and create the database and all that stuff. I just want to remove data and insert new data that is all.

I don't have the option to drop and create the database for several reasons. I will have to remove all data and just insert only, so whatever it takes to find how to do this, am willing to go for it but need help obviously to start with.

I also need to automate this process. Will automate "dumping data from production db", then "deleting data on staging db", and then "restoring data to staging db". I just need help on the "deleting data on staging db" part.

I am running on PostgreSQL 9.5.2

25

You don't have to drop the database, it should be enough to drop all the objects in the database. This can be done using

drop owned by adminuser

If you then create the SQL dump including the create table statements (so without the --data-only option) everything should be fine.

You can also remove the --column-inserts then, which will make the import a lot faster.


However, if you do want to delete everything, you can do that with a little dynamic SQL:

do
$$
declare
  l_stmt text;
begin
  select 'truncate ' || string_agg(format('%I.%I', schemaname, tablename), ',')
    into l_stmt
  from pg_tables
  where schemaname in ('public');

  execute l_stmt;
end;
$$

This will truncate all tables in the schema public with a single statement which will also work even if there are many foreign key constraints connecting all tables. If your tables are spread over multiple schemas, you need to add them in the where condition.

| improve this answer | |
  • i see...does it do same thing as @ypercube mentioned above to use this command TRUNCATE table1, table2, ... <list of all tables>;? do they both do same thing? – uberrebu Nov 2 '16 at 20:13
  • 1
    @babababa: yes, my answer simply generates and runs that statement dynamically, so you don't have to type all table names and if you add a new table it will automatically be included. – a_horse_with_no_name Nov 2 '16 at 20:22
  • nice just tried it and it works, @ypercube one works too...thanks a lot – uberrebu Nov 4 '16 at 15:08
6

pg_restore has a --clean flag (or possibly --create) which will auto delete data before running operations..

The Excellent Documentation should help you greatly...

Just to clarify, in case it's confusing:

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

This will not drop the actual database .. only the tables/views/etc.

If, for some reason, dropping and recreating the tables is not acceptable, then you're going to have to put it more work to manually create a script that creates a data only dump from the source db, issues TRUNCATE or DELETE in the target database, and then loads the data dump. There's no quick/slick way to do this, as far as I'm aware.

| improve this answer | |
  • will that --clean flag ONLY delete data and keep database and tables structures same but empty? – uberrebu Nov 2 '16 at 17:32
  • It will issue a drop table before a create table. Any tables that exist in the dump file. I would HOPE that the the dump file contains the information to recreate the table exactly as it existed before (including FKeys, etc).. But it really depends on how you created the dump file. However, since you keep mentioning "staging", it sounds like what you're really looking for is a way to populate staging tables in a data warehouse with data from a production db. If that's your goal, a dump file is probably the wrong approach.. – Joishi Bodio Nov 2 '16 at 17:48
  • that is not what am looking to do, i just want to delete data..database and tables structure will remain same and untouched...my question is pretty clear what i want to do, even from the title – uberrebu Nov 2 '16 at 17:50
  • Then, sorry to say, your solution is going to be much more difficult. – Joishi Bodio Nov 2 '16 at 17:52
3
SELECT 'TRUNCATE ' || input_table_name || ' CASCADE;' AS truncate_query FROM(SELECT table_schema || '.' || table_name AS input_table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_schema NOT LIKE 'pg_toast%') AS information;  

The above query will generate truncate queries for all tables in the database.

| improve this answer | |
0

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 <t.glaser@tarent.de>
--
-- 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!

| improve this answer | |
  • Perfect, here is my code for the extensions, it must be placed before functions / procedures: -- extensions FOR r IN (SELECT pns.nspname, pe.extname FROM pg_extension pe, 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; – Clément Prévost Jul 13 '19 at 15:51
  • @ClémentPrévost thanks, I merged your comment into the code (hope I did it right, comments lack formatting, please review). I normally leave extensions undeleted deliberately though (my use case is restoring from backups with differing schema versions, and I normally have exactly one extension, PL/pgSQL, loaded). It might be useful to some, though, so, thank you! – mirabilos Jul 14 '19 at 1:24
  • Perfect, thank you :) – Clément Prévost Jul 14 '19 at 7:49
  • doesn't work ERROR: cannot drop constraint PK_cc64c972fd68c6bce3f9a5b1273 on table product_listing because other objects depend on it DETAIL: view product_listing_with_calculations depends on constraint PK_cc64c972fd68c6bce3f9a5b1273 on table product_listing HINT: Use DROP ... CASCADE to drop the dependent objects too. CONTEXT: SQL statement "ALTER TABLE ONLY public.product_listing DROP CONSTRAINT "PK_cc64c972fd68c6bce3f9a5b1273";" PL/pgSQL function inline_code_block line 33 at EXECUTE – asumaran Jun 4 at 23:09
  • @asumaran does it work if you put the “normal and materialised views” block before the “constraints #1: foreign key” block? – mirabilos Jun 5 at 14:39
0

Another way is using a shell script:

script=$(
    psql -tAc "
            SELECT tablename
            FROM pg_tables
            WHERE schemaname = 'public'
                AND tablename != 'schema_migrations'" \
        | sed -E -- 's/^/TRUNCATE TABLE "/; s/$/";/'
)
psql -c "$script" -- "$db"

It doesn't truncate table schema_migrations in case there is one. It expects your table names to not include double quotes. The first command generates a script like:

TRUNCATE TABLE "posts";
TRUNCATE TABLE "users";
...

The second one executes it.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.