Examples

Reading

First, create a dump of your database using pg_dump using the custom format:

pg_dump -Fc -d [YOUR] dump.out

The following example shows how to get the data and the table definition from a dump:

import pgdumplib
from pgdumplib import constants

dump = pgdumplib.load('dump.out')
for row in dump.table_data('public', 'table-name'):
    print(row)
print(dump.lookup_entry(constants.TABLE, 'public', 'table-name').defn)

Writing

To create a dump, you need to add sections. The following example shows how to create a dump with a schema, extension, comment, type, tables, and table data:

import datetime
import uuid

import pgdumplib
from pgdumplib import constants

dump = pgdumpib.new('example')

schema = dump.add_entry(
    desc=constants.SCHEMA,
    tag='test',
    defn='CREATE SCHEMA test;',
    drop_stmt='DROP SCHEMA test;')

dump.add_entry(
    desc=constants.ACL,
    tag='SCHEMA test',
    defn='GRANT USAGE ON SCHEMA test TO PUBLIC;',
    dependencies=[schema.dump_id])

uuid_ossp = dump.add_entry(
    desc=constants.EXTENSION,
    tag='uuid-ossp',
    defn='CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;',
    drop_stmt='DROP EXTENSION "uuid-ossp";')

dump.add_entry(
    desc=constants.COMMENT,
    tag='EXTENSION "uuid-ossp"',
    defn="""COMMENT ON EXTENSION "uuid-ossp" IS generate universally unique identifiers (UUIDs)'""",
    dependencies=[uuid_ossp.dump_id])

addr_type = dump.add_entry(
    desc=constants.TYPE,
    namespace='test',
    tag='address_type',
    owner='postgres',
    defn="""\
    CREATE TYPE test.address_type AS ENUM ('billing', delivery');""",
    drop_stmt='DROP TYPE test.address_type;',
    dependencies=[schema.dump_id])

test_addresses = dump.add_entry(
    desc=constants.TABLE,
    namespace='test',
    tag='addresses',
    owner='postgres',
    defn="""\
    CREATE TABLE addresses (
        id               UUID                     NOT NULL DEFAULT uuid_generate_v4() PRIMARY KEY,
        created_at       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
        last_modified_at TIMESTAMP WITH TIME ZONE,
        user_id          UUID                     NOT NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
        type             address_type             NOT NULL,
        address1         TEXT                     NOT NULL,
        address2         TEXT,
        address3         TEXT,
        locality         TEXT                     NOT NULL,
        region           TEXT,
        postal_code      TEXT                     NOT NULL,
        country          TEXT                     NOT NULL
    );""",
    drop_stmt='DROP TABLE test.addresses;',
    dependencies=[schema.dump_id, addr_type.dump_id, uuid_ossp.dump_id])

example = dump.add_entry(
    constants.TABLE,
    'public', 'example', 'postgres',
    'CREATE TABLE public.example (\
        id UUID NOT NULL PRIMARY KEY,\
        created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,\
        value TEXT NOT NULL);',
    'DROP TABLE public.example')

with dump.table_data_writer(example, ['id', 'created_at', 'value']) as writer:
    writer.append(uuid.uuid4(), datetime.datetime.utcnow(), 'row1');
    writer.append(uuid.uuid4(), datetime.datetime.utcnow(), 'row2');
    writer.append(uuid.uuid4(), datetime.datetime.utcnow(), 'row3');
    writer.append(uuid.uuid4(), datetime.datetime.utcnow(), 'row4');
    writer.append(uuid.uuid4(), datetime.datetime.utcnow(), 'row5');

dump.save('custom.dump')