I run three instances of a database, in a typical change-control scenario:
Production, QAT, and DEV.
The Production database is the "real" data, and we periodically take a
back up from Prod and re-instantiate QAT and DEV by dropping them and
then restoring from the Prod backup.
The development (DEV) instance is used by application developers and
DBA's to "play" with, when initially trying out or developing new
features. It gives them completely realistic sample data to work with,
and I don't care if they screw up the data or even accidentally delete
all of it, because I can easily re-create DEV from PROD.
QAT is somewhat similar to DEV, but it is intended to be the proving
ground for newly implemented features: we start with QAT in the same,
(presumably stable) state as Prod, apply any required changes, test the
client application and data, repeat as necessary until it works right,
then process the same changes against Prod.
My question relates to how to avoid expending OID's unnecessarily. I have
declared all my tables as WITHOUT OIDS, but my understanding is that
applies only to the actual table data and that database objects, like
tables, view, procedures, etc, still have a uniquie OID assigned at
instantiation. So every time I refresh QAT and DEV from the production
database, I burn up lots of OID's. Not that OID's are in short supply,
but I'm anal retentive about these things and so if there is a
straight-forward way to avoid unnecesary OID consumption it would help me
sleep better.
~Berend Tober