Thread

1. Conservation of OIDs

   Berend Tober <btober@seaworthysys.com> — 2003-11-14T15:01:51Z

   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
   
   
   
   
   

2. Re: Conservation of OIDs

   Manfred Koizar <mkoi-pg@aon.at> — 2003-11-14T16:51:25Z

   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.  Something like 1000 or 10000 are reserved
   for system use.  So you still have more than 4000000000 for your
   objects.  How many objects (tables, indices, operators, functions,
   ...) are there, and how often is "periodically"?
   
   If you're really concerned, you can initdb separate clusters for QAT
   and DEV and run three postmasters using three different ports.
   
   Servus
    Manfred
   
   

3. Re: Conservation of OIDs

   Berend Tober <btober@seaworthysys.com> — 2003-11-14T17:55:16Z

   > 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
   
   
   
   
   

4. Re: Conservation of OIDs

   Berend Tober <btober@seaworthysys.com> — 2003-11-14T18:37:50Z

   >
   > If you're really concerned, you can initdb separate clusters for QAT
   > and DEV and run three postmasters using three different ports.
   >
   
   Follow-up question: Are different ports really necessary? I currently
   have the three different databases defined all in the same cluster, and
   differentiated by name, e.g., mydb, mydbqat, and mydbdev. If I have the
   postmaster start these three instances in separate clusters,
   respectively, using three different directories, such as
   
   $ postmaster -D /usr/local/pgsql/data
   $ postmaster -D /usr/local/pgsql/qat
   $ postmaster -D /usr/local/pgsql/dev
   
   and make no change regarding which port the daemon listens on, but DO
   have a single, unique database name, one per cluster (mydb, mydbqat,
   mydbdev) in the respective clusters, will connections requests to any one
   of these databases on that same port find their way to the right place
   (and back)?
   
   Or maybe would be better to not used different database names at all to
   differential PROD, QAT, and DEV, and instead differentiate solely which
   cluster is to be used by means of your suggestion of using different
   ports? I dunno. What do you think?
   
   ~Berend Tober
   
   
   
   
   

5. Re: Conservation of OIDs

   Peter Eisentraut <peter_e@gmx.net> — 2003-11-14T19:05:46Z

   btober@seaworthysys.com writes:
   
   > Follow-up question: Are different ports really necessary?
   
   Yes.
   
   > I currently have the three different databases defined all in the same
   > cluster, and differentiated by name, e.g., mydb, mydbqat, and mydbdev.
   > If I have the postmaster start these three instances in separate
   > clusters, respectively, using three different directories, such as
   >
   > $ postmaster -D /usr/local/pgsql/data
   > $ postmaster -D /usr/local/pgsql/qat
   > $ postmaster -D /usr/local/pgsql/dev
   
   ... then the second and third command will fail.
   
   -- 
   Peter Eisentraut   peter_e@gmx.net
   
   
   

6. Re: Conservation of OIDs

   Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-11-14T19:07:27Z

   On Fri, Nov 14, 2003 at 01:37:50PM -0500, btober@seaworthysys.com wrote:
   
   > > If you're really concerned, you can initdb separate clusters for QAT
   > > and DEV and run three postmasters using three different ports.
   > 
   > Follow-up question: Are different ports really necessary?
   
   No, and it will be probably a waste of shared_buffers too.  IMHO you
   should keep them under a single postmaster, and forget about the OID
   wraparound problem.  It won't be a problem for you anyway, because if
   you create the whole DB from scratch you won't have OID collision
   anyway, even on year 2030 when your OIDs really do wrap around.  Maybe by
   then we will have 8 bit OIDs anyway, and you will surely initdb way
   before that.
   
   -- 
   Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
   "Las mujeres son como hondas:  mientras más resistencia tienen,
   más lejos puedes llegar con ellas"  (Jonas Nightingale, Leap of Faith)
   
   

7. Re: Conservation of OIDs

   Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-11-15T22:57:10Z

   On Fri, 14 Nov 2003 btober@seaworthysys.com wrote:
   
   > 
   > > 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.
   > >
   > > 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.
   > 
   >...
   > 
   > > ...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?
   
   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 can see the advantages in that Dev and QAT environments are automatically the
   same as Prod but in general Dev can be a law unto itself almost and QAT
   reflects the environment of Prod, e.g. Prod is Solaris 5.9 so QAT is Solaris
   5.9, with the only differences being changes applied to QAT that have not yet
   been applied to Prod, and Dev could be Windows if that can provide everything
   needed to develop for the end product.
   
   At the very least I think your three database should be run as separate
   clusters, indeed reading the section I edited out from your email about the
   usage pattern on QAT and Dev my first thought was "Well if you think oid wrap
   around would be a problem just throw an initdb into your rebuild cycle."
   
   I've seen some useful replies on how to run these separately but am I the only
   one shocked that the whole process is happening on a production system?
   
   
   --
   Nigel Andrews
   
   
   

8. Re: Conservation of OIDs

   Berend Tober <btober@seaworthysys.com> — 2003-11-16T01:50:02Z

   > 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.
   
   I can see having separate clusters would save me the unnecessary
   expenditure of OID',s, and I can see access to the production data being
   restricted (from developers?) in most cases as a good thing, but for this
   environment the set of end-users and developers is actually quite small,
   they are in close proximity and have ready communication, and so I don't
   see why it would be a problem to have the same postmaster running all
   three databases for this case. I would be interested in hearing more
   detail as to why it is a bad idea in general.
   
   ~Berend Tober
   
   
   
   
   

9. Re: Conservation of OIDs

   Joshua D. Drake <jd@commandprompt.com> — 2003-11-16T18:47:22Z

   >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... Then run PostgreSQL on its own
   port for each.
   
   If you really want to make it structured create virtual IP addresses
   for each so that you never think about it...
   
   dev.database.com
   qat.database.com
   prod.database.com
   
   
   
   
   >I can see the advantages in that Dev and QAT environments are automatically the
   >same as Prod but in general Dev can be a law unto itself almost and QAT
   >reflects the environment of Prod, e.g. Prod is Solaris 5.9 so QAT is Solaris
   >5.9, with the only differences being changes applied to QAT that have not yet
   >been applied to Prod, and Dev could be Windows if that can provide everything
   >needed to develop for the end product.
   >
   >At the very least I think your three database should be run as separate
   >clusters, indeed reading the section I edited out from your email about the
   >usage pattern on QAT and Dev my first thought was "Well if you think oid wrap
   >around would be a problem just throw an initdb into your rebuild cycle."
   >
   >I've seen some useful replies on how to run these separately but am I the only
   >one shocked that the whole process is happening on a production system?
   >
   >
   >--
   >Nigel Andrews
   >
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 9: the planner will ignore your desire to choose an index scan if your
   >      joining column's datatypes do not match
   >  
   >
   
   -- 
   Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC
   Postgresql support, programming, shared hosting and dedicated hosting.
   +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
   PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org
   
   
   
   

10. Re: Conservation of OIDs

    Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-11-16T19:50:55Z

    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.
    
    -- 
    Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
    "Cuando mañana llegue pelearemos segun lo que mañana exija" (Mowgli)
    
    

11. Re: Conservation of OIDs

    Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-11-16T22:22:35Z

    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.
    
    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 if you have
    extension functions that you're loading as part of your dev system that
    are different from production.
    
    

12. Re: Conservation of OIDs

    Berend Tober <btober@seaworthysys.com> — 2003-11-17T14:02:45Z

    >
    > 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
    
    
    
    
    

13. Re: Conservation of OIDs

    Douglas McNaught <doug@mcnaught.org> — 2003-11-17T14:09:12Z

    <btober@seaworthysys.com> writes:
    
    > The complications (real or imagined at this point) of using different
    > port numbers has me kind of stuck, however.
    
    It's really not that hard.
    
    > initdb -D /path/to/my/qat/cluster
    > initdb -D /path/to/my/dev/cluster
    
    Right.
    
    > 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?
    
    Exactly.  Setting the 'port' parameter in postgresql.conf is probably
    preferable to doing it on the command line.
    
    > 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
              ^^^^5434
    Yup.
    
    > 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?
    
    This is a possible approach, but there is a lot of hair in the RH
    startup that makes it less than conducive to running multiple
    postmasters.  In particular, it looks for (or used to; I haven't
    looked at it recently since RH 7.x) a process named 'postmaster' to
    see if the server is currently running, which doesn't work very well
    when you have multiple postmasters.
    
    I'd probably replace the existing init.d script with three copies of
    something simpler that just calls 
    
    'pg_ctl <start|stop> -D /the/proper/place -l /log/file/name'
    
    -Doug