For each phase of a project, a new DB is created.  The project phase is 
identified in a linux environment variable (lets call it $PHASE).  The DB name 
that is used in the connect string of the perl/DBI scripts they run is derived 
from that in the perl/DBI script, maybe something like this... $db = 
$ENV{PHASE}."_DB", followed by the db connect string.      

When phase 2 comes along, the DBA would typically create a new DB (P2_DB) so 
that the users with their $PHASE set to "P2" would find the correct DB to 
connect to.  In the meantime, other P1_DB users can still work with the P1_DB 
database.

Now phase 3 comes along.  Management tells the DBA to NOT create a P#_DB just 
yet.  They want the P3 users ($PHASE = "P3") to actually work on the P2 DB.  
The $PHASE env var cannot be modified as it is used by other tools in the work 
environment.  The tell the DBA (me) to have them work on the P2_DB database as 
if it was the P3_db database.  Eventually, they'll tell me to create the P3_DB 
database and the problem will go away at that point.  But in the meantime, I 
have to redirect them to P2_DB without changing anything in the linux 
environment.  I need to have the DB itself "know" that the dbname "P3_DB" 
really = "P2_DB" for the time being.  A simple mapping capability could do it.  

I'm googling around for the connection service stuff, but its really sparse.  
And its not clear where, in my linux install, I'm supposed to find the config 
file.  Even if I find it, I'll need a utility that the DBA can use to modify it 
(I won't have direct access to it for manual edit or anything like that).  But 
a service file concept sounds intriguing.

Thanks Steve.    

-----Original Message-----
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steve Crawford
Sent: Wednesday, January 23, 2013 4:38 PM
To: Rob Sargent
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DB alias ?

On 01/23/2013 01:16 PM, Rob Sargent wrote:
> On 01/23/2013 02:10 PM, Gauthier, Dave wrote:
>> Nope.  Think of it this way, a new DB is created on day 1 of every 
>> month.  So there's a DB called JAN, another called FEB, etc... .  The 
>> DB name used in the connect is picked up from the current date/time.
>> But January is oevr and I don't want to create the FEB DB until Feb 
>> 15th.  In the meantime, I want those who try to connect to FEB to 
>> connect to JAN (for example).
Perhaps it would be better if you more fully explained the problem you are 
trying to solve (i.e. is it updated data but identical schemas, are you 
replacing the old or are you keeping the old, etc.). Assuming you have some 
flexibility in how you solve your actual issue, there are some options.

1. Use pgBouncer so that all users connect to the pooler - perhaps using a 
standard database like "current" and update the real database to which that 
connects when it is ready.

2. Use schemas in a database instead of separate databases and update the role 
information to set the search path to point to the appropriate schema. Perhaps 
always call the most recent schema "current" then rename schemas as/when needed.

3. Use a connection service file
http://www.postgresql.org/docs/current/static/libpq-pgservice.html that is 
pushed/pulled/shared somehow with updated connection information.

Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make 
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to