Re: DB alias ?

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-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Steve Crawford
Sent: Wednesday, January 23, 2013 4:38 PM
To: Rob Sargent
Cc: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general