pg_dump makes a dump of one PostgreSQL database. Its default format, plain,
is the SQL script described here, which is loaded back with psql. Its other
formats (custom, directory and tar) are archives
for pg_restore and are not SQL text.
The start of the file
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.4
-- Dumped by pg_dump version 15.4
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;Setting search_path to empty means every name in the file has to be
written with its schema, so the script does not depend on the settings of the session
that runs it. Recent versions also put psql’s \restrict and
\unrestrict commands around the script, as a safeguard for restoring a
dump from a server that is not trusted.
The order of the file
A comment comes before each object, with its name, type, schema and owner. The objects are in three parts:
| Part | Holds |
|---|---|
| Pre-data | Schemas, extensions, types, functions and CREATE TABLE |
| Data | A COPY block for each table, and sequence values |
| Post-data | Primary keys, indexes, triggers and foreign keys |
--
-- Name: fruit; Type: TABLE; Schema: public; Owner: app
--
CREATE TABLE public.fruit (
id integer NOT NULL,
name character varying(20) NOT NULL
);
ALTER TABLE public.fruit OWNER TO app;
COPY public.fruit (id, name) FROM stdin;
1 apple
2 pear
\.
ALTER TABLE ONLY public.fruit
ADD CONSTRAINT fruit_pkey PRIMARY KEY (id);So in a pg_dump file, the primary key is never in the CREATE TABLE. It is
added by an ALTER TABLE after the data. A sequence’s value is set with
SELECT pg_catalog.setval(…).
Options that change the file
| Option | Effect |
|---|---|
--inserts | INSERT statements instead of COPY. |
--column-inserts | The same, with the columns named. |
--rows-per-insert | Several rows in each INSERT. |
--clean | DROP statements before each object. |
--create | A CREATE DATABASE and a \connect to it. |
--no-owner | No OWNER TO statements. |
--schema-only | Definitions only. |
--data-only | Rows only. |
What only PostgreSQL has
COPY blocks carry the data. Function bodies are
dollar-quoted strings. Types can have
brackets and several words, as in timestamp without time zone. Sequences
are objects of their own, tied to a column by DEFAULT nextval(…) and
ALTER SEQUENCE … OWNED BY. Any line starting with a backslash is a psql
command.