Ken Winter wrote:
>Tom et al ~
>
>I understand that this is all a risky business. So maybe someone can tell
>me a safer way to accomplish what I was trying to accomplish with pg_dump
>and data-only pg_restore.
>
>It's a basic bit of database administration work. I'm trying to establish
>two (or if necessary more) instances of the same database - a production
>instance and a development instance - and a change management process for
>coordinating them. As you can probably guess:
>
>1. The production instance is the one the users are actually using. Its
>data are The Truth.
>
>2. The development instance is where design changes (to tables, procedures,
>and all other database objects) are developed, tested, and readied to go
>into production. Its data are of no value except for testing purposes.
>
>3. The crucial purpose of the change management process is to put into
>production each new release of the database design.
>
Ken,
The approach that has worked very well for me over the years is to use a
data dictionary outside of the system that is used to build the tables.
A large part of the development process is making changes to this
dictionary. You "post" a unit of work to the dev system by running a
diff builder that updates the dev system. When the work is deemed
acceptable, you post the same alterations to the production server, or
you copy the spec in total from dev to production and run a build
there. A good system lets you provide values for system tables as part
of the spec.
This approach is far more general-purpose than what you are attempting.
It may be too general-purpose, and your backup/restore system may be
just fine, but the backup/restore system will not scale well.