8

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

17

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.

  • 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
3

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.

  • 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
2
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.

0

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
  • functions / procedures (pg_proc.proisagg probably should be honoured though)
  • 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)
  • aggregate functions
  • 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” in case someone is interested; the diff is small. Contact me if necessary.

SQL

-- Copyright © 2019
--      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
        r RECORD;
BEGIN
        -- triggers
        FOR r IN (SELECT pns.nspname, pc.relname, pt.tgname
                FROM pg_trigger pt, pg_class pc, 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_constraint pcon, pg_class pc, 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_constraint pcon, pg_class pc, 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_class pc, 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_class pc, 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_class pc, 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_class pc, 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 (see below), only if necessary
        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;
        -- functions / procedures
        FOR r IN (SELECT pns.nspname, pp.proname, pp.oid
                FROM pg_proc pp, pg_namespace pns
                WHERE pns.oid=pp.pronamespace
                    AND pns.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
            ) LOOP
                EXECUTE format('DROP FUNCTION %I.%I(%s);',
                    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_namespace pns, 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). Bugfixes and further improvements welcome!

  • 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 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 at 1:24
  • Perfect, thank you :) – Clément Prévost Jul 14 at 7:49

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.