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.
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:ken@xxxxxxxxxx
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
