Contents
pg_dump
A core utility for backing up a single PostgreSQL database
pg_dump
is a core utility for backing up a single PostgreSQL database as an SQL script, TAR archive or custom archive file.
pg_dump
has been part of PostgreSQL since it was first released.
Change history
Work-in-progress
- PostgreSQL 15
- PostgreSQL 14
- PostgreSQL 13
- option
--include-foreign-data
added to dump data from foreign tables (commit 2f9eb313)
- option
- PostgreSQL 11
- PostgreSQL 10
- PostgreSQL 9.5
- redundant
-i
/--ignore-version
option removed (commit 232cd63b)
- redundant
- PostgreSQL 9.4
- PostgreSQL 9.3
- PostgreSQL 9.2
- option
--section
added (commit a4cd6abc)
- option
- PostgreSQL 9.1
- PostgreSQL 8.2
- PostgreSQL 8.0
- start/stop times shown when
-v
/--verbose
option used (commit 91366138)
- start/stop times shown when
- PostgreSQL 7.3
primary keys
will be restrospectively added to tables withALTER TABLE ... ADD PRIMARY KEY
to avoid index creation penalty during a later restore (commit 5b5cef9a)- various output portability improvements (commit c828ec88)
- support for objects larger than 2GB (commit 38e444aa)
- PostgreSQL 7.2
- use
pg_get_indexdef()
function to generateCREATE INDEX
commands and avoid emitting default opclasses (1929a90b) - NLS support added (commit b5593821)
- use
- PostgreSQL 7.1
- support for TAR output (commit c3e18804)
- support for dumping large objects (commit e8f69be0)
- PostgreSQL 7.0
- PostgreSQL 6.5
- use single serializable transaction, meaning database can stay online while a dump is made (commit df9e539e)
- dump ACLs by default (commit 615e77ed)
- option
-x
added to suppress dumping ACLs (commit 615e77ed) - option
-c
added to drop objects before creation (commit 289a826d) - dump output format improved (commit 84107b41)
- PostgreSQL 6.4
- PostgreSQL 6.3
-H
option renamed to-h
(commit c35d7fb8)
Examples
Sample output of a plaintext dump for a single table (including both definition and contents):
$ pg_dump -Fp -d 'host=localhost dbname=app user=appuser' --table=app.language -- -- PostgreSQL database dump -- -- Dumped from database version 12.5 -- Dumped by pg_dump version 14devel SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: language; Type: TABLE; Schema: app; Owner: appuser -- CREATE TABLE app.language ( lang_id character(2) NOT NULL, name_english character varying(64), name_native character varying(64), CONSTRAINT language_lang_id_check CHECK ((lang_id ~ '^[a-z]{2}$'::text)) ); ALTER TABLE app.language OWNER TO app; -- -- Data for Name: language; Type: TABLE DATA; Schema: app; Owner: appuser -- COPY app.language (lang_id, name_english, name_native) FROM stdin; en English English de German Deutsch ja Japanese 日本語 th Thai ไทย \. -- -- Name: language language_pkey; Type: CONSTRAINT; Schema: app; Owner: appuser -- ALTER TABLE ONLY app.language ADD CONSTRAINT language_pkey PRIMARY KEY (lang_id); -- -- PostgreSQL database dump complete --
References
- PostgreSQL documentation: pg_dump