Comments on End Point Blog: Splitting Postgres pg_dump into pre and post data ...

@ Richard Huxton: Richard, can you give some more ...

2010-02-26T09:12:02.851-05:00

@ Richard Huxton:
Richard, can you give some more details of the scripts your are using to do that kind of 'schema explode'? It sounds like this is actually something I was looking for since years to integrate postgresql development into an SVN.

Andrew: Not to minimize my own efforts, but this ...

2010-01-25T11:24:27.596-05:00

Andrew:

Not to minimize my own efforts, but this script *is* "a tiny bit of perl (or sed) glue" :) I'm all for custom formats: the process just becomes pg_format --schema-only dumpfile > newdumpfile; ./split_postgres_dump.pl newdumpfile. Of course, pg_dumpall doesn't do custom formats...

Splitting a dump is totally trivial with a custom ...

2010-01-23T18:13:17.846-05:00

Splitting a dump is totally trivial with a custom format dump, with or without data members. You use the --list and --use-list pieces of pg_restore plus a tiny bit of perl (or sed) glue. You're not using custom format dumps? Well, quite apart from this you're also depriving yourself of the possibility to use parallel pg_restore.

I've got a similar gadget I use here, but it&#...

2010-01-21T17:02:28.124-05:00

I've got a similar gadget I use here, but it's a schema exploder. You run a --schema-only dump through it and get a directory structure:
[schema]/[object-type]/[name]
e.g.
lookups/table/country_names

Each file has creation / adjustment sections. Hadn't thought about having an option to separate them.

I mainly use it so I have something diff/grep friendly to play with when looking at schema changes.

Okay, now in git at github: http://github.com/buc...

2010-01-21T11:31:43.669-05:00

Okay, now in git at github:

http://github.com/bucardo/Split_postgres_dump

Grzegorz J- That doesn't really help for the ...

2010-01-21T11:01:41.675-05:00

Grzegorz J-

That doesn't really help for the use cases the script was written for.

Jeff Davis- Good ideas. I guess maybe I should pu...

2010-01-21T11:01:05.129-05:00

Jeff Davis-

Good ideas. I guess maybe I should put this script into git after all. :)

just dump it in tar format, and you'll get eve...

2010-01-21T07:13:46.389-05:00

just dump it in tar format, and you'll get everything chopped apart.

That's very useful. Other things that might b...

2010-01-20T19:10:03.833-05:00

That's very useful.

Other things that might be helpful would be to:
* Optionally restore indexes using CREATE INDEX CONCURRENTLY.
* Optionally separate constraints into a separate phase. This may be useful when you want to restart the application ASAP, and you need the constraints to be active, but you may be able to live without non-constraint indexes for a while.

This is very useful, especially for setting up slo...

2010-01-20T13:10:38.200-05:00

This is very useful, especially for setting up slony log shipping. The slony1_dump.sql script does a data dump, which needs to be applied against a schema dump. However, `pg_dump --schema-only` produces the pre- and post-data commands in one file, the foreign keys are applied immediately, causing the re-load of data to fail. I've been doing schema split by hand up until now (split the schema file at the first "ALTER TABLE .* ADD CONSTRAINT" command, but I think this script will make things go a lot faster.