Re: Conservation of OIDs - Mailing list pgsql-general

>
> On Sun, 16 Nov 2003, Alvaro Herrera wrote:
>
>> On Sun, Nov 16, 2003 at 10:47:22AM -0800, Joshua D. Drake wrote:
>> >
>> > >Whoa! You mean these aren't already separate database clusters or
>> even separate systems? I am very shocked, you can't do a proper
>> Dev --> QAT --> Prod environment if all three systems are run by
>> the same postmaster, or on the same host imo. But maybe I'm just
>> over
>> > >cautious, or worked on systems where access to production systems
>> is controlled.
>> >
>> > I second this. Use different databases for each. You can run them
>> on the same machine (there are some real advantages to this) but
>> create a separate initdb for each...
>>
>> What's the point?  You can keep them separate through pg_hba.conf if
>> it's really needed.  I don't see how having several clusters, one
>> database each, buys you any security.

Using a separate cluster for each, or at least one cluster for PROD and
one for both QAT and DEV does address the original question posed about
burning through OIDS during the refresh->modify->test->fix iteration.

>
> I don't think security isn't the issue but instead it's reliability. If
> you have a bug in your dev system that say causes backends to die and
> forcing full postmaster restarts, do you really want to be mucking up
> your production system as well? This isn't hard to imagine ...
>

I can see the reliability thing being a big plus, too. While I don't care
if developers accidentally drop an important table in DEV, if they hang
the production postmaster or otherwise screw up the server, then that is
undesireable.

The complications (real or imagined at this point) of using different
port numbers has me kind of stuck, however.

So, help me out with some specifics of the multi-cluster route, and lets
pretend I can't afford another machine (actually the hardware acquisition
is not a problem, but I don't want to incur the additional server setup
and maintenance responsiblity). I create a new cluster with

initdb -D /path/to/my/qat/cluster
initdb -D /path/to/my/dev/cluster


right? Then that means that I need to maintain separate pg_hba.conf and
postgresql.conf files for each (in the respective cluster directories),
right?

Then I restore into the new QAT and DEV cluster my backup from PROD, but
exactly how is that done? I guess I have to employ the -p option with
psql, something like

psql -p 5433 -U postgres -f my_prod_backup.sql myqatdb
psql -p 5433 -U postgres -f my_prod_backup.sql mydevdb

(I guess I can use the same database name if I'm using separate
clusters?) assuming I've modified the QAT and DEV postgresql.conf files
to specify that port, or possilby started postmaster with

postmaster -p 5433

for qat and maybe

postmaster -p 5434

for dev.

Actually, the two postmaster commands above are probably wrong. Instead,
is what I need

postmaster -D /path/to/my/qat/cluster

and

postmaster -D /path/to/my/dev/cluster


so that the proper port is picked up from the respectively-modified
postgresql.conf files in the separate cluster directories?

And how would I automate the QAT and DEV postmaster startup with server
startup, the way the current postmaster is started with all three
databases in a single cluster, on my RH Linux host that uses the
rc.d/init.d/postgresql script? Would it be necessary to make a qat and
dev version of that stgartup script, appropriately modified to use the
different cluster directories, or what?

~Berend Tober