pg_dump and pg_dumpall command helper (postgres)

• pg_dump
  pg_dump only dumps a single database. To backup global objects that are common to all databases in a cluster, such as roles and tablespaces, use pg_dumpall.Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.

   

• pg_dumpall
  pg_dumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file. The script file contains SQL commands that can be used as input to psqlto restore the databases. It does this by calling pg_dump for each database in a cluster. pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, tablespaces, and properties such as access permissions that apply to databases as a whole.Since pg_dumpall reads tables from all databases you will most likely have to connect as a database superuser in order to produce a complete dump. Also you will need superuser privileges to execute the saved script in order to be allowed to add users and groups, and to create databases.

  The SQL script will be written to the standard output. Use the [-f|file] option or shell operators to redirect it into a file.

  pg_dumpall needs to connect several times to the PostgreSQL server (once per database). If you use password authentication it will ask for a password each time. It is convenient to have a ~/.pgpass file in such cases. See Section 32.15 for more information.

pg_dump

Source: https://www.postgresql.org/docs/9.6/static/app-pgdump.html

Examples

To dump a database called mydb into a SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

To dump a database into a directory-format archive:

$ pg_dump -Fd mydb -f dumpdir
To dump a database into a directory-format archive in parallel with 5 worker jobs:

$ pg_dump -Fd mydb -j 5 -f dumpdir
To reload an archive file into a (freshly created) database named newdb:

$ pg_restore -d newdb db.dump
To dump a single table named mytab:

$ pg_dump -t mytab mydb > db.sql
To dump all tables whose names start with emp in the detroit schema, except for the table named employee_log:

$ pg_dump -t ‘detroit.emp*’ -T detroit.employee_log mydb > db.sql
To dump all schemas whose names start with east or west and end in gsm, excluding any schemas whose names contain the word test:

$ pg_dump -n ‘east*gsm’ -n ‘west*gsm’ -N ‘*test*’ mydb > db.sql
The same, using regular expression notation to consolidate the switches:

$ pg_dump -n ‘(east|west)*gsm’ -N ‘*test*’ mydb > db.sql
To dump all database objects except for tables whose names begin with ts_:

$ pg_dump -T ‘ts_*’ mydb > db.sql
To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case (see Patterns). But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like

$ pg_dump -t “\”MixedCaseName\”” mydb > mytab.sql

pg_dumpall

Source: https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html

To dump all databases:

$ pg_dumpall > db.out

To reload database(s) from this file, you can use:

$ psql -f db.out postgres

(It is not important to which database you connect here since the script file created by pg_dumpall will contain the appropriate commands to create and connect to the saved databases.)