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

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
--
  • PostgreSQL documentation: pg_dump

Categories

Backup, Core utility, Data import/export

See also

pg_dumpall, pg_restore, test_pg_dump