Postgres Backup/Restore

 

pg_dump    à Creates a backup of ONE database at a time

pg_dumpall  à Can back up ALL of your databases simultaneously,

 

pg_dump -U username -W -F t database_name > c:\backup_file.tar

 
-F : specifies the output file format that can be one of the following:
    ·        c: custom-format archive file format
    ·        d: directory-format archive
    ·        t: tar
    ·        p: plain-text SQL script file (Default)
-h Specify database server host
 
-p   Specify database server port
-U  Specify the user which is used to connect to the PostgreSQL database server
-W Used to prompt for a password before connecting to the PostgreSQL server
-d   Specify the database to dump
 
 
 

SCHEMA backup
 

pg_dump --username=user --password --schema-only [schema_name] > database_schema.sql

pg_dump --schema-only DATABASE > schema.sql

 
 
EX:
pg_dump --clean --create --file /tmp/DB-$(date +%Y%m%d).pgdump --format=custom --no-unlogged-table-data  <dbname>
 
 
psql --dbname=DBNAME --command="select pg_start_backup('CurBuild');"
 

Restore

To import a single database testdb from the tar dumpfile

pg_restore -c -U username -W -F t -d testdb dump.tar

 

To import ALL databases from tar dumpfile

pg_restore -c -U username -W -F t dump.tar

To import 1 database from .sql backup

postgres=# create database

psql   -d <New_DBNAME>    < dump.sql 

Validate :

-bash-4.2$ psql

psql (15.4)

Type "help" for help.

postgres=#  \l

postgres=# \c   <new_dbname>

postgres=#  SELECT pg_size_pretty( pg_database_size(‘NEW_DBNAME’));