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  gt   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  lt  backup dir backup-  time  sql

User · Accepted Answer

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 developmnent db name   That s how I do it  actually

User · Answer

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

User · Answer

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

User · Answer

I ve used   pg restore -c -d database name filename dump

User · Answer

To dump   pg dump -Fc mydb  gt  db dump   To restore   pg restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d my db db latest dump

User · Answer

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

User · Answer

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  indices VIEWs  normal or materialised  tables sequences routines  aggregate functions  functions  procedures  all non-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  lt t glaser tarent de gt  -- -- 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 lt  gt  f                LOOP                 EXECUTE format  ALTER TABLE ONLY  I  I DROP CONSTRAINT  I                        r nspname  r relname  r conname           END LOOP          -- indices         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          -- non-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

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

Examples related to database

Examples related to backup

Examples related to postgresql