This is my first post to a PostgreSQL mailing list, so please forgive me
if I have posted to the wrong place
Currently pg_dump has flags for dumping only table definitions and/or
data. These flags are respectively:
--schema-only
--data-only
I propose that two more be added:
--tables-only
--constraints-only
These would essentially break up the output of --schema-only into two
sections. I.e., the output of --tables-only plus the output of
--constraints-only would be identical to the output of --schema-only .
There are a number of scenarios where this may be useful, I will
describe the one that I would use it for.
When making changes to my database schema I would like to take the
schema from the newly modified database, and dump the data from the old
database into it to ensure that the new schema is able to take the data
that exists in the live database. If it isn't then I modify the new
schema or the live dataset as appropriate, and try again.
This requires the following steps:
1. Create temporary database and apply modified schema to it
2. Dump new database using --schema-only
3. Split new schema into table definitions and constraints
4. Apply new schema table definitions from step 34 to the testing database
5. Dump the existing database using --data-only
6. Apply the dataset from step 5 to the testing database
7. Apply new schema constraints from step 3 to the testing database
All of these steps are easily scriptable except step 3, which means that
making quick changes to the new schema and re-applying includes the
tedious task of opening the 5,000 line schema file in a text editor and
manually pasting the relevant sections into new files. Step 3 really
does hold up the development process with regards to testing changes to
the schema.
Generalizing the nature of this task, the pg_dump features I propose
would allow the easy scripting of dumping databases, making changes to
the dumps and then re-applying them in a non paradox-inducing order.
FWIW I thought this would be a very simple patch, and had a look at the
code for pg_dump myself, despite the fact that I've not even written a
Hello World in C as yet. That attempt failed miserably, so now I am
reduced to selling the merits of this idea to real developers.
Incidentally, --schema-only appears to break tables and constraints into
two sections already, as evidenced by the fact that all tables are
created first, and constraints afterwards.