Backup (Logical)

Nov 20, 2016

Dump a database into a file

Using SQL statements

_$: pg_dump -U <user> -F p <database> > database-sql-pfile
_$: /usr/lib/postgresql/9.3/bin/pg_dump -U <user> -p 5433 database > database.sql    # With a particular version

Using PostgreSQL’s custom format

_$: pg_dump -U <user> -F c <database> > database-sql-cfile

Dump a table into a file

_$: pg_dump -U <user> -F p -t <table>                <database> > database-sql-pfile
_$: pg_dump -U <user> -F p -t <table_1> -t <table_2> <database> > database-sql-pfile
_$: pg_dump -U <user> -F p -t <table> --data-only --column-inserts <database> > database-sql-pfile

Dump a schema into a file

Using SQL statements

_$: pg_dump --schema-only -U <user> -F p <database> > database-sql-pfile-schema

Using PostgreSQL’s custom format

_$: pg_dump --schema-only -U <user> -F c <database> > database-sql-pfile-schema

Restore a database from a file

Using SQL statements

_$: psql -U <user> -d <database> -f database-sql-pfile
_$: psql -U <user> -d <database> -f database-sql-pfile --single-transaction     # One transaction

Using PostgreSQL’s custom format

_$: pg_restore -U <user> -d <database> -j 4 database-sql-cfile