Skip site navigation (1) Skip section navigation (2)

pg_dump --split patch

From: Joel Jacobson <joel(at)gluefinance(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: pg_dump --split patch
Date: 2010-12-28 16:00:35
Message-ID: AANLkTikLHA2x6U=q-t0j0YS78txHFmdtyxJfsrsRcLqN@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Dear fellow hackers,

Problem: A normal diff of two slightly different schema dump files (pg_dump
-s), will not produce a user-friendly diff, as you get all changes in the
same file.

Solution: I propose a new option to pg_dump, --split, which dumps each
object to a separate file in a user friendly directory structure:

[-f filename] : main dump file, imports each splitted part using \i
[-f filename]-split/[desc]/[tag]/[oid].sql : dump of the oid

Example: If the filename (-f) is "pg.dump", the following directory
structure would be created:

$ ./pg_dump -f /crypt/pg.dump --split -F p -s glue
/crypt/pg.dump-split/VIEW/
/crypt/pg.dump-split/TYPE/
/crypt/pg.dump-split/TRIGGER/
/crypt/pg.dump-split/TABLE/
/crypt/pg.dump-split/SEQUENCE/
/crypt/pg.dump-split/SCHEMA/
/crypt/pg.dump-split/PROCEDURAL_LANGUAGE/
/crypt/pg.dump-split/INDEX/
/crypt/pg.dump-split/FUNCTION/
/crypt/pg.dump-split/FK_CONSTRAINT/
/crypt/pg.dump-split/CONSTRAINT/
/crypt/pg.dump-split/AGGREGATE/

In each such directory, one directory per object name is created.
If we would have a function "foobar" with oid "12345" it would be saved to:
/crypt/pg.dump-split/FUNCTION/foobar/12345.sql

In the "pg.dump" plain text file, the files are "linked in" using the "\i"
psql command, e.g.:
\i /crypt/pg.dump-split/FUNCTION/foobar/12345.sql

Potential use-case scenarios:

*) Version control your database schema, by exporting it daily (using
--split) and commiting the differences.

*) Compare differences of schema dumps created in different points in time.
Since objects are stored in separate files, it is easier to see what areas
were modified, compared to looking at the diff of two entire schemas.

*) Restore only some objects, based on type (e.g., only the functions) or
name (e.g. only fucntions of certain name/names).

I've tested the patch for both the latest HEAD (9.1devel) as well as 8.4.6.

Feedback welcome.

-- 
Best regards,

Joel Jacobson
Glue Finance

Attachment: pg-dump-split-plain-text-files-9.1devel.patch
Description: application/octet-stream (4.9 KB)
Attachment: pg-dump-split-plain-text-files-8.4.6.patch
Description: application/octet-stream (5.4 KB)

Responses

pgsql-hackers by date

Next:From: Peter GeogheganDate: 2010-12-28 16:04:18
Subject: Re: "writable CTEs"
Previous:From: tvDate: 2010-12-28 15:55:12
Subject: Re: estimating # of distinct values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group