How To Create Dump File In Postgres

To Generate a Dump file use the pg_dump tool to create a dump file from a database/tables from a PostgreSQL database, pg_dump dumps a database as a text file or to other formats.

Usage:

              pg_dump [OPTION]... [DBNAME]

General options:

              -f, --file=FILENAME      output file name  -F, --format=c|t|p       output file format (custom, tar, plain text)  -i, --ignore-version     proceed even when server version mismatches                           pg_dump version  -v, --verbose            verbose mode  -Z, --compress=0-9       compression level for compressed formats  --help                   show this help, then exit  --version                output version information, then exit

Options controlling the output content:

              -a, --data-only          dump only the data, not the schema  -c, --clean              clean (drop) schema prior to create  -C, --create             include commands to create database in dump  -d, --inserts            dump data as INSERT, rather than COPY, commands  -D, --column-inserts     dump data as INSERT commands with column names  -E, --encoding=ENCODING  dump the data in encoding ENCODING  -n, --schema=SCHEMA      dump the named schema only  -o, --oids               include OIDs in dump  -O, --no-owner           skip restoration of object ownership                           in plain text format  -s, --schema-only        dump only the schema, no data

              -S, --superuser=NAME     specify the superuser user name to use in                           plain text format  -t, --table=TABLE        dump the named table only  -x, --no-privileges      do not dump privileges (grant/revoke)  -X disable-dollar-quoting, --disable-dollar-quoting                           disable dollar quoting, use SQL standard quoting  -X disable-triggers, --disable-triggers                           disable triggers during data-only restore  -X use-set-session-authorization, --use-set-session-authorization                           use SESSION AUTHORIZATION commands instead of                           OWNER TO commands

Connection options:

              -h, --host=HOSTNAME      database server host or socket directory  -p, --port=PORT          database server port number  -U, --username=NAME      connect as specified database user  -W, --password           force password prompt (should happen automatically)

If no database name is supplied, then the PGDATABASE environment
Example:

pg_dump --username=postgres mertech --table=CHPROD --file=chprod.sql --inserts

To load the dump file use the psql program:

Usage:

              psql [OPTIONS]... [DBNAME [USERNAME]]

General options:

              -d DBNAME       specify database name to connect to (default: "Marcello")  -c COMMAND      run only single command (SQL or internal) and exit  -f FILENAME     execute commands from file, then exit  -l              list available databases, then exit  -v NAME=VALUE   set psql variable NAME to VALUE  -X              do not read startup file (~/.psqlrc)  --help          show this help, then exit  --version       output version information, then exit

Input and output options:

              -a              echo all input from script  -e              echo commands sent to server  -E              display queries that internal commands generate  -q              run quietly (no messages, only query output)  -o FILENAME     send query results to file (or |pipe)  -n              disable enhanced command line editing (readline)  -s              single-step mode (confirm each query)  -S              single-line mode (end of line terminates SQL command)  -L FILENAME     send session log to file

Output format options:

              -A              unaligned table output mode (-P format=unaligned)  -H              HTML table output mode (-P format=html)  -t              print rows only (-P tuples_only)  -T TEXT         set HTML table tag attributes (width, border) (-P tableattr=)  -x              turn on expanded table output (-P expanded)  -P VAR[=ARG]    set printing option VAR to ARG (see pset command)  -F STRING       set field separator (default: "|") (-P fieldsep=)  -R STRING       set record separator (default: newline) (-P recordsep=)

Connection options:

              -h HOSTNAME     database server host or socket directory (default: "local socket")

              -p PORT         database server port (default: "5432")  -U NAME         database user name (default: "Marcello")  -W              prompt for password (should happen automatically)

Example: