Re: Conservation of OIDs - Mailing list pgsql-general

> On Fri, 14 Nov 2003 10:01:51 -0500 (EST), <btober@seaworthysys.com>
> wrote:
>>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.
>
>> 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.
>
> OIDs are unsigned 32 bit...you still have more than 4000000000 for your
> objects.  How many objects (tables, indices, operators, functions, ...)

Only several thousand, so as a practical matter I realize in this case it
realistically is not a problem, but for the sake of completenss in
understanding how I *could* do this, I'd like to learn.

> ...and how often is "periodically"?

I guess periodically isn't really the right word. We may not refresh QAT
or DEV at all for weeks. But when a change is required, there may be
many, say dozens, of iterations on some days when testing is in full
operation because we want to be sure to have QAT in the same state as
PROD before applying a sequence of changes for QA testing that are
proposed for PROD before implementing in PROD. So an iterative cycle
happens where a proposed change is implemented in QAT, problems are
discovered in QAT, we try to fix the problems, then refresh QAT,
re-apply, test again, etc., until we get it right and are confident that
we need do it only once in PROD.

Another reason we may do a refresh, against PROD even, is to change a
column width, since, as numerous list participants here have realized, is
not really very easy otherwise, if you want to be sure to get all
dependent views, rules, and triggers to not break.


> ...you can initdb separate clusters for QAT
> and DEV and run three postmasters using three different ports.

This is what I was thinking but didn't really understand fully what I
would have to do. So your suggestion involves

initdb -D my_QAT_path

then

psql -f my_PROD_backup.sql ... [?]

Can you help me out with a little more detail and tell me where does the
"different port" assignement come into play? I know I have the default
port 5432 in my postgresql.conf file, but how do I associate different
ports with the different postmasters? I guess the -h option on the start
command line, but my RH Linux machine is setup up start the postmaster
automatically on startup, using the /etc/init.d/postgresql script file.
How would I get the postmaster to auto start multiple instances using the
different port configurations?

~Berend Tober