PostgreSQL pg_dump pg_dumpall and restore

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 or directory name

-F, --format=c|d|t|p output file format (custom, directory, tar,

plain text (default))

-j, --jobs=NUM use this many parallel jobs to dump

-v, --verbose verbose mode

-V, --version output version information, then exit

-Z, --compress=0-9 compression level for compressed formats

--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock

-?, --help show this help, then exit

Options controlling the output content:

-a, --data-only dump only the data, not the schema

-b, --blobs include large objects in dump

-c, --clean clean (drop) database objects before recreating

-C, --create include commands to create database in dump

-E, --encoding=ENCODING dump the data in encoding ENCODING

-n, --schema=SCHEMA dump the named schema(s) only

-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)

-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 superuser user name to use in plain-text format

-t, --table=TABLE dump the named table(s) only

-T, --exclude-table=TABLE do NOT dump the named table(s)

-x, --no-privileges do not dump privileges (grant/revoke)

--binary-upgrade for use by upgrade utilities only

--column-inserts dump data as INSERT commands with column names

--disable-dollar-quoting disable dollar quoting, use SQL standard quoting

--disable-triggers disable triggers during data-only restore

--exclude-table-data=TABLE do NOT dump data for the named table(s)

--if-exists use IF EXISTS when dropping objects

--inserts dump data as INSERT commands, rather than COPY

--no-security-labels do not dump security label assignments

--no-synchronized-snapshots do not use synchronized snapshots in parallel jobs

--no-tablespaces do not dump tablespace assignments

--no-unlogged-table-data do not dump unlogged table data

--quote-all-identifiers quote all identifiers, even if not key words

--section=SECTION dump named section (pre-data, data, or post-data)

--serializable-deferrable wait until the dump can run without anomalies

--use-set-session-authorization

use SET SESSION AUTHORIZATION commands instead of

ALTER OWNER commands to set ownership

Connection options:

-d, --dbname=DBNAME database to dump

-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, --no-password never prompt for password

-W, --password force password prompt (should happen automatically)

--role=ROLENAME do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment

variable value is used.

pg_dump is an effective tool to backup postgres database. It creates a *.sql file with CREATE TABLE, ALTER TABLE, and COPY SQL statements of source database. To restore these dumps psqlcommand is enough.

Using pg_dump, you can backup a local database and restore it on a remote database at the same time, using a single command.

Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}

Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

use pg_dump and psql backup and restore postgres database:

Backup a single postgres database

[postgres@minion2 bin]$ ./psql

psql (9.4.5)

Type "help" for help.

postgres=# l

List of databases

--------------+--------------+----------+---------+-------+-----------------------

benchmarksql | benchmarksql | UTF8 | C | C |

partition_db | postgres | UTF8 | C | C |

postgres | postgres | UTF8 | C | C |

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

test_pgdb | postgres | UTF8 | C | C |

(6 rows)

postgres=# c benchmarksql benchmarksql

You are now connected to database "benchmarksql" as user "benchmarksql".

benchmarksql=# d

List of relations

Schema | Name | Type | Owner

--------+------------+-------+--------------

public | customer | table | benchmarksql

public | district | table | benchmarksql

public | history | table | benchmarksql

public | item | table | benchmarksql

public | new_order | table | benchmarksql

public | oorder | table | benchmarksql

public | order_line | table | benchmarksql

public | stock | table | benchmarksql

public | warehouse | table | benchmarksql

(9 rows)

benchmarksql=# d+

List of relations

--------+------------+-------+--------------+----------+-------------

(9 rows)

benchmarksql=# q

[postgres@minion2bin]$./pg_dump -U benchmarksql benchmarksql -f /tmp/benchmarksql_db_backup.sql

[root@minion2 tmp]# less benchmarksql_db_backup.sql

-- PostgreSQL database dump

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: customer; Type: TABLE; Schema: public; Owner: benchmarksql; Tablespace:

CREATE TABLE customer (

c_w_id integer NOT NULL,

c_d_id integer NOT NULL,

c_id integer NOT NULL,

c_discount numeric(4,4),

c_credit character(2),

c_last character varying(16),

c_first character varying(16),

c_credit_lim numeric(12,2),

c_balance numeric(12,2),

c_ytd_payment double precision,

c_payment_cnt integer,

c_delivery_cnt integer,

c_street_1 character varying(20),

c_street_2 character varying(20),

c_city character varying(20),

c_state character(2),

c_zip character(9),

c_phone character(16),

c_since timestamp without time zone,

c_middle character(2),

c_data character varying(500)

);

ALTER TABLE customer OWNER TO benchmarksql;

……

[postgres@minion2 bin]$ ./psql

psql (9.4.5)

Type "help" for help.

postgres=# drop database benchmarksql;

DROP DATABASE

postgres=# create database benchmarksql owner benchmarksql;

CREATE DATABASE

postgres=# q

[postgres@minion2 bin]$ ./psql -U benchmarksql -d benchmarksql -f /tmp/benchmarksql_db_backup.sql

SET

CREATE EXTENSION

COMMENT

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COPY 3000000

COPY 1000

COPY 3035734

COPY 100000

COPY 903653

COPY 3036923

COPY 30375276

COPY 10000000

COPY 100

ALTER TABLE

ERROR: canceling autovacuum task

CONTEXT: automatic analyze of table "benchmarksql.public.order_line"

ALTER TABLE

ERROR: canceling autovacuum task

CONTEXT: automatic analyze of table "benchmarksql.public.stock"

ALTER TABLE

CREATE INDEX

REVOKE

GRANT

postgres=# c benchmarksql benchmarksql

You are now connected to database "benchmarksql" as user "benchmarksql".

benchmarksql=# d+

List of relations

--------+------------+-------+--------------+----------+-------------

(9 rows)

Backup all postgres databases

Backup all postgres databases using pg_dumpall:

pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:

pg_dumpall [OPTION]...

General options:

-f, --file=FILENAME output file name

-V, --version output version information, then exit

--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock

-?, --help show this help, then exit

Options controlling the output content:

-a, --data-only dump only the data, not the schema

-c, --clean clean (drop) databases before recreating

-g, --globals-only dump only global objects, no databases

-o, --oids include OIDs in dump

-O, --no-owner skip restoration of object ownership

-r, --roles-only dump only roles, no databases or tablespaces

-s, --schema-only dump only the schema, no data

-S, --superuser=NAME superuser user name to use in the dump

-t, --tablespaces-only dump only tablespaces, no databases or roles

-x, --no-privileges do not dump privileges (grant/revoke)

--binary-upgrade for use by upgrade utilities only

--column-inserts dump data as INSERT commands with column names

--disable-dollar-quoting disable dollar quoting, use SQL standard quoting

--disable-triggers disable triggers during data-only restore

--if-exists use IF EXISTS when dropping objects

--inserts dump data as INSERT commands, rather than COPY

--no-security-labels do not dump security label assignments

--no-tablespaces do not dump tablespace assignments

--no-unlogged-table-data do not dump unlogged table data

--quote-all-identifiers quote all identifiers, even if not key words

--use-set-session-authorization

use SET SESSION AUTHORIZATION commands instead of

ALTER OWNER commands to set ownership

Connection options:

-d, --dbname=CONNSTR connect using connection string

-h, --host=HOSTNAME database server host or socket directory

-l, --database=DBNAME alternative default database

-p, --port=PORT database server port number

-U, --username=NAME connect as specified database user

-w, --no-password never prompt for password

-W, --password force password prompt (should happen automatically)

--role=ROLENAME do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard

output.

[postgres@minion2 bin]$ ./psql -l

List of databases

--------------+--------------+----------+---------+-------+-----------------------

benchmarksql | benchmarksql | UTF8 | C | C |

partition_db | postgres | UTF8 | C | C |

postgres | postgres | UTF8 | C | C |

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

test_pgdb | postgres | UTF8 | C | C |

(6 rows)

[postgres@minion2 bin]$ ./pg_dumpall > /tmp/all_pgdatabase_backup.sql

[root@minion2 tmp]# less all_pgdatabase_backup.sql

-- PostgreSQL database cluster dump

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

-- Roles

CREATE ROLE benchmarksql;

ALTER ROLE benchmarksql WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md540a665cab5a6807638fdd6b60b1eaf44';

CREATE ROLE postgres;

ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md53175bce1d3201d16594cebf9d7eb3f9d';

-- Database creation

CREATE DATABASE benchmarksql WITH TEMPLATE = template0 OWNER = benchmarksql;

CREATE DATABASE partition_db WITH TEMPLATE = template0 OWNER = postgres;

REVOKE ALL ON DATABASE template1 FROM PUBLIC;

REVOKE ALL ON DATABASE template1 FROM postgres;

GRANT ALL ON DATABASE template1 TO postgres;

GRANT CONNECT ON DATABASE template1 TO PUBLIC;

CREATE DATABASE test_pgdb WITH TEMPLATE = template0 OWNER = postgres;

connect benchmarksql

SET default_transaction_read_only = off;

-- PostgreSQL database dump

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: customer; Type: TABLE; Schema: public; Owner: benchmarksql; Tablespace:

CREATE TABLE customer (

c_w_id integer NOT NULL,

c_d_id integer NOT NULL,

c_id integer NOT NULL,

c_discount numeric(4,4),

c_credit character(2),

c_last character varying(16),

c_first character varying(16),

c_credit_lim numeric(12,2),

c_balance numeric(12,2),

c_ytd_payment double precision,

c_payment_cnt integer,

c_delivery_cnt integer,

c_street_1 character varying(20),

c_street_2 character varying(20),

c_city character varying(20),

c_state character(2),

c_zip character(9),

c_phone character(16),

c_since timestamp without time zone,

c_middle character(2),

c_data character varying(500)

);

ALTER TABLE customer OWNER TO benchmarksql;

[postgres@minion2 bin]$ ./psql -d template1

psql (9.4.5)

Type "help" for help.

template1=# l

List of databases

-----------+----------+----------+---------+-------+-----------------------

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

(2 rows)

[postgres@minion2 bin]$ ./psql -f /tmp/all_pgdatabase_backup.sql

SET

ERROR: role "benchmarksql" already exists

STATEMENT: CREATE ROLE benchmarksql;

psql:/tmp/all_pgdatabase_backup.sql:14: ERROR: role "benchmarksql" already exists

ALTER ROLE

ERROR: role "postgres" already exists

STATEMENT: CREATE ROLE postgres;

psql:/tmp/all_pgdatabase_backup.sql:16: ERROR: role "postgres" already exists

ALTER ROLE

CREATE DATABASE

REVOKE

GRANT

CREATE DATABASE

You are now connected to database "benchmarksql" as user "postgres".

SET

CREATE EXTENSION

COMMENT

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COPY 3000000

COPY 1000

COPY 3035734

COPY 100000

COPY 903653

COPY 3036923

COPY 30375276

COPY 10000000

COPY 100

ALTER TABLE

ERROR: canceling autovacuum task

CONTEXT: automatic analyze of table "benchmarksql.public.order_line"

ALTER TABLE

CREATE INDEX

REVOKE

GRANT

You are now connected to database "partition_db" as user "postgres".

SET

CREATE EXTENSION

COMMENT

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COPY 0

CREATE INDEX

REVOKE

GRANT

You are now connected to database "postgres" as user "postgres".

SET

COMMENT

CREATE EXTENSION

COMMENT

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COPY 0

REVOKE

GRANT

You are now connected to database "template1" as user "postgres".

SET

COMMENT

CREATE EXTENSION

COMMENT

REVOKE

GRANT

You are now connected to database "test_pgdb" as user "postgres".

SET

CREATE EXTENSION

COMMENT

SET

CREATE TABLE

ALTER TABLE

COPY 1

REVOKE

GRANT

Backup a specific postgres table

To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option.

$ pg_dump -t tablename -U username dbname -f onlytable.sql

[postgres@minion2 bin]$ ./pg_dump -t customer -U benchmarksql benchmarksql -f /tmp/benchmarksql_customer.sql

[root@minion2 tmp]# less benchmarksql_customer.sql

-- PostgreSQL database dump

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'UTF8';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: customer; Type: TABLE; Schema: public; Owner: benchmarksql; Tablespace:

CREATE TABLE customer (

c_w_id integer NOT NULL,

c_d_id integer NOT NULL,

c_id integer NOT NULL,

c_discount numeric(4,4),

c_credit character(2),

c_last character varying(16),

c_first character varying(16),

c_credit_lim numeric(12,2),

c_balance numeric(12,2),

c_ytd_payment double precision,

c_payment_cnt integer,

c_delivery_cnt integer,

c_street_1 character varying(20),

c_street_2 character varying(20),

c_city character varying(20),

c_state character(2),

c_zip character(9),

c_phone character(16),

c_since timestamp without time zone,

c_middle character(2),

c_data character varying(500)

);

ALTER TABLE customer OWNER TO benchmarksql;

-- Data for Name: customer; Type: TABLE DATA; Schema: public; Owner: benchmarksql

COPY customer (c_w_id, c_d_id, c_id, c_discount, c_credit, c_last, c_first, c_credit_lim, c_balance, c_ytd_payment, c_payment_cnt, c_d

elivery_cnt, c_street_1, c_street_2, c_city, c_state, c_zip, c_phone, c_since, c_middle, c_data) FROM stdin;

[postgres@minion2 bin]$ ./psql -U benchmarksql benchmarksql

psql (9.4.5)

Type "help" for help.

benchmarksql=# drop table customer ;

DROP TABLE

benchmarksql=# d

List of relations

Schema | Name | Type | Owner

--------+------------+-------+--------------

public | district | table | benchmarksql

public | history | table | benchmarksql

public | item | table | benchmarksql

public | new_order | table | benchmarksql

public | oorder | table | benchmarksql

public | order_line | table | benchmarksql

public | stock | table | benchmarksql

public | warehouse | table | benchmarksql

(8 rows)

[postgres@minion2 bin]$ ./psql -U benchmarksql -d benchmarksql -f /tmp/benchmarksql_customer.sql

SET

CREATE TABLE

ALTER TABLE

COPY 3000000

ALTER TABLE

CREATE INDEX

[postgres@minion2 bin]$ ./psql -U benchmarksql benchmarksql

psql (9.4.5)

Type "help" for help.

benchmarksql=# d+

List of relations

--------+------------+-------+--------------+----------+-------------

(9 rows)

Backup a local postgres database and restore to remote server using single command:

$ pg_dump dbname | psql -h hostname dbname

The above dumps the local database, and extracts it at the given hostname.

dump locale database benchmarksql from 192.168.0.15 to remote server 192.168.0.71 and extracts it at database postgres

[postgres@minion2 bin]$ ./pg_dump -U benchmarksql benchmarksql |./psql -h 192.168.0.71 -p 6432 -U benchmarksql postgres

SET

CREATE EXTENSION

COMMENT

SET

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

CREATE TABLE

ALTER TABLE

COPY 3000000

COPY 1000

COPY 3035734

COPY 100000

COPY 903653

COPY 3036923

COPY 30375276

COPY 10000000

COPY 100

ALTER TABLE

CREATE INDEX

REVOKE

GRANT

shown at 192.168.0.17 database postgres

[postgres@DELL-R720 bin]$ ./psql -p 6432

psql (9.4.5)

Type "help" for help.

postgres=# d+

List of relations

--------+------------+-------+--------------+----------+-------------

(9 rows)

dump specified table of specified database to specified database

[postgres@minion2 bin]$ ./pg_dump -p 5432 -U postgres pgbench5432 -t pgbench_branches |./psql -p 6432 -U postgres pgbench6432
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 1428
ALTER TABLE
[postgres@minion2 bin]$ ./pg_dump -p 5432 -U postgres pgbench5432 -t pgbench_history |./psql -p 6432 -U postgres pgbench6432
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 0
[postgres@minion2 bin]$ ./pg_dump -p 5432 -U postgres pgbench5432 -t pgbench_tellers |./psql -p 7432 -U postgres pgbench7432
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 14280
ALTER TABLE

backup

[postgres@minion1 bin]$ ./pg_dump -h 192.168.0.14 -p 5432 -U postgres -d pgbench5432 -C -Fp -f /mnt/data/pgbench5432.dump

restore

[postgres@minion1 bin]$ ./psql -h localhost -p 5432 -U postgres -f /mnt/data/pgbench5432.dump

backup

[postgres@minion1 bin]$ ./pg_dump -h 192.168.0.14 -p 5432 -U postgres -d pgbench5432 -C -Fc -f /mnt/data/pgbench.dump

restore

[postgres@minion1 bin]$ ./pg_restore -h 192.168.0.14 -p 5432 -U postgres -C -d postgres /mnt/data/pgbench.dump

#pg_restore: options -d/--dbname and -f/--file cannot be used together.

backup

[postgres@minion1 bin]$ ./pg_dump -h 192.168.0.14 -p 5432 -U postgres -d pgbench5432 -F d -f /mnt/data/backup/

restore
[postgres@minion1 bin]$ ./pg_restore -h 192.168.0.14 -p 5432 -U postgres -C -d postgres /mnt/data/backup/