Commands for backup and restore of postgresql database

psql-backup-restore-commands.md

For backup,

pg_dump -W -U <DB_USERNAME> <DB_NAME> -h <DB_HOST> -p <DB_PORT> --no-owner > <PATH_FOR_FILE>.sql

For backup of database with INSERT statements in the dump file instead of COPY statements,

pg_dump -W -U <DB_USERNAME> <DB_NAME> -h <DB_HOST> -p <DB_PORT> --no-owner --inserts > <PATH_FOR_FILE>.sql

For backup of database with multiple jobs,

pg_dump --compress <COMPRESSION_LEVEL> --jobs <NUMBER_OF_PROCESSES_TO_RUN_PARALLEL> --format=d -W -U <DB_USERNAME> -h <DB_HOST> -p <DB_PORT> --no-owner --inserts --verbose --dbname <DB_NAME> --file <DIR_WHERE_TO_PUT_DUMP_FILES>
tar -cf - <DIR_WHERE_TO_PUT_DUMP_FILES> | pigz > <DIR_WHERE_TO_PUT_DUMP_FILES>.tar.gz

For restore,

psql -U <DB_USERNAME> -d <DB_NAME> -h <DB_HOST> -p <DB_PORT> < <PATH_FOR_FILE>.sql

For restore of database with multiple jobs running using pg_restore utility,

pigz -dc <DIR_WHERE_DUMP_FILES_RESIDE>.tar.gz | tar -C <DIR_WHERE_DUMP_FILES_RESIDE> --strip-components 1 -xf -
pg_restore --jobs <NUMBER_OF_PROCESSES_TO_RUN_PARALLEL> --format=d -W -U <DB_USERNAME> -h <DB_HOST> -p <DB_PORT> --no-owner --verbose --exit-on-error --dbname <DB_NAME> <DIR_WHERE_DUMP_FILES_RESIDE>

For backup of schema of particular database,

pg_dump -W -U <DB_USERNAME> <DB_NAME> -h <DB_HOST> -p <DB_PORT> -s > <PATH_FOR_FILE>.sql

For restore of schema in particular database,

psql -U <DB_USERNAME> -d <DB_NAME> -h <DB_HOST> -p <DB_PORT> < <PATH_FOR_FILE>.sql

Drop all tables of the current schema of the current database,

DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
    EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;

pg_dump "dbname=abc host=abc user=abc password=abc port=123" --no-owner > abc.sql

SELECT sum(numbackends) FROM pg_stat_database;
SELECT datname,numbackends FROM pg_stat_database