Thread: DB alias ?

Gauthier, Dave wrote:
> Problem:  Some users (scripts actually) try to connect to a DB who's name is derived from environmental variables.
TheDB doesn't exist (yet), and I want them to connect to a different DB for the time being.  Is there a way to define
analias for the existing DB that = the db name that doesn't exist?    Or is there a way to have PG fail over to a
defaultDB should a DB connect fail?  I can implement the fail over outside PG, but those users who make a direct
connectto the DB don't use that code. 

This has been requested previously.  Probably the only reason it hasn't
gotten done yet is that nobody has cooked up a patch.  Searching the
archives for "database synonyms" might be fruitful.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 01/23/2013 12:45 PM, Gauthier, Dave wrote:
> Problem:  Some users (scripts actually) try to connect to a DB who's
> name is derived from environmental variables.  The DB doesn't exist
> (yet), and I want them to connect to a different DB for the time being.
> Is there a way to define an alias for the existing DB that = the db name
> that doesn't exist?    Or is there a way to have PG fail over to a
> default DB should a DB connect fail?  I can implement the fail over
> outside PG, but those users who make a direct connect to the DB don't
> use that code.

Pass the database name when you connect?

JD


>
> Thanks in Advance
>


--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC
@cmdpromptinc - 509-416-6579

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
wantto create the FEB DB until Feb 15th.  In the meantime, I want those who try to connect to FEB to connect to JAN
(forexample).   

-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, January 23, 2013 4:04 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] DB alias ?


On 01/23/2013 12:45 PM, Gauthier, Dave wrote:
> Problem:  Some users (scripts actually) try to connect to a DB who's
> name is derived from environmental variables.  The DB doesn't exist
> (yet), and I want them to connect to a different DB for the time being.
> Is there a way to define an alias for the existing DB that = the db name
> that doesn't exist?    Or is there a way to have PG fail over to a
> default DB should a DB connect fail?  I can implement the fail over
> outside PG, but those users who make a direct connect to the DB don't
> use that code.

Pass the database name when you connect?

JD


>
> Thanks in Advance
>


--
Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and
DevelopmentHigh Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 

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
wantto create the FEB DB until Feb 15th.  In the meantime, I want those who try to connect to FEB to connect to JAN
(forexample). 
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Wednesday, January 23, 2013 4:04 PM
> To: Gauthier, Dave
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] DB alias ?
>
>
> On 01/23/2013 12:45 PM, Gauthier, Dave wrote:
>> Problem:  Some users (scripts actually) try to connect to a DB who's
>> name is derived from environmental variables.  The DB doesn't exist
>> (yet), and I want them to connect to a different DB for the time being.
>> Is there a way to define an alias for the existing DB that = the db name
>> that doesn't exist?    Or is there a way to have PG fail over to a
>> default DB should a DB connect fail?  I can implement the fail over
>> outside PG, but those users who make a direct connect to the DB don't
>> use that code.
>
> Pass the database name when you connect?
>
> JD
>
>
>>
>> Thanks in Advance
>>
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and
DevelopmentHigh Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579 
>
>
alter database JAN rename to FEB;

On 1/23/2013 1: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
wantto create the FEB DB until Feb 15th.  In the meantime, I want those who try to connect to FEB to connect to JAN
(forexample). 

proposed new SQL command:

READ USERS MIND;

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

Then someone who wants to look at old JAN data will have the same problem :-(

If I recall, Oracle enables something like this.  Multiple tnsfilenames (or something like that).  There was a connect
layeron the server side that the DBA had access to where you could do stuff like this. 

>> proposed new SQL command:
>>READ USERS MIND;

:-)
Actually, read the DBA's mind.

How about...

postgres=# create db_alias FEB to db JAN;
postgres=# drop db_alias FEB;



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

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
wantto create the FEB DB until Feb 15th.  In the meantime, I want those who try to connect to FEB to connect to JAN
(forexample). 
>
> -----Original Message-----
> From: Joshua D. Drake [mailto:jd@commandprompt.com]
> Sent: Wednesday, January 23, 2013 4:04 PM
> To: Gauthier, Dave
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] DB alias ?
>
>
> On 01/23/2013 12:45 PM, Gauthier, Dave wrote:
>> Problem:  Some users (scripts actually) try to connect to a DB who's
>> name is derived from environmental variables.  The DB doesn't exist
>> (yet), and I want them to connect to a different DB for the time being.
>> Is there a way to define an alias for the existing DB that = the db name
>> that doesn't exist?    Or is there a way to have PG fail over to a
>> default DB should a DB connect fail?  I can implement the fail over
>> outside PG, but those users who make a direct connect to the DB don't
>> use that code.
>
> Pass the database name when you connect?
>
> JD
>
>
>>
>> Thanks in Advance
>>
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL
> Support, Training, Professional Services and Development High
> Availability, Oracle Conversion, Postgres-XC @cmdpromptinc -
> 509-416-6579
>
>
alter database JAN rename to FEB;


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

For each phase of a project, a new DB is created.  The project phase is identified in a linux environment variable
(letscall it $PHASE).  The DB name that is used in the connect string of the perl/DBI scripts they run is derived from
thatin 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
(Iwon'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@postgresql.org [mailto:pgsql-general-owner@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
identicalschemas, are you replacing the old or are you keeping the old, etc.). Assuming you have some flexibility in
howyou 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
thereal 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
pointto 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
connectioninformation. 

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

On Wed, Jan 23, 2013 at 10:08:05PM +0000, Gauthier, Dave wrote:
> For each phase of a project, a new DB is created.  The project phase is identified in a linux environment variable
(letscall it $PHASE).  The DB name that is used in the connect string of the perl/DBI scripts they run is derived from
thatin the perl/DBI script, maybe something like this... $db = $ENV{PHASE}."_DB", followed by the db connect string.
  
>

It seems that this is your problem.  What you need to do is something more like

    $db = $ENV{PROJDB}."_DB" || $db = $ENV{PHASE}."_DB"

Then have (only) the people who are supposed to be working on the non-standard database name set PROJDB in their
environment,and your problem is solved.  No? 

(I have reservations about this entire thing anyway.  It feels to me you really want to be using schemas here, but
that'sa different discussion.) 

Best,

A

--
Andrew Sullivan
ajs@crankycanuck.ca

On 01/23/2013 02:08 PM, Gauthier, Dave wrote:
> For each phase of a project, a new DB is created.... But in the meantime, I have to redirect them to P2_DB without
changinganything 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
(Iwon't have direct access to it for manual edit or anything like that).  But a service file concept sounds intriguing. 
>
>

First, the convention on this mailing list is to bottom-post so people
can follow threads. Top-posting is frowned upon.

Given your expanded description I think you should look at pgBouncer.
Although it is intended as a connection pooler, the configuration allows
you to set a database name on the client-facing side that is different
than the actual name of the database the pooler connects to. So both
p2_db and p3_db could point to real_p2_db until you update the pgBouncer
config.

Depending on how you authenticate/authorize you may have to fuss with
some of the password settings and to minimize client-side changes you
will probably have to change PostgreSQL to listen on a different port
then have pgBouncer listen on the standard 5432 so things appear
unchanged to the clients.

As a bonus, the new databases can be on different machines if you choose.

http://pgfoundry.org/projects/pgbouncer/

Cheers,
Steve

On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote:

> Then someone who wants to look at old JAN data will have the same problem

> :-(

>

> If I recall, Oracle enables something like this. Multiple tnsfilenames (or

> something like that). There was a connect layer on the server side that

> the DBA had access to where you could do stuff like this.

> >> proposed new SQL command:

> >>READ USERS MIND;

> :

> :-)

>

> Actually, read the DBA's mind.

>

> How about...

>

> postgres=# create db_alias FEB to db JAN;

> postgres=# drop db_alias FEB;

I would have suggested to use pg_services file as documented at

http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html

http://www.postgresql.org/docs/9.1/static/libpq-connect.html

You can think of this as tnsnames replacement.

but I am unable to make it work. I don't know what is wrong with this.

shridhar@bheem ~$ cat ~/.pg_service.conf

[test1]

host=localhost

dbname=test

shridhar@bheem ~$ strace -o psql.strace psql test1

psql: FATAL: database "test1" does not exist

shridhar@bheem ~$ grep -i pg_service psql.strace

shridhar@bheem ~$ psql test

psql (9.2.2)

Type "help" for help.

test=# \q

shridhar@bheem ~$ psql --version

psql (PostgreSQL) 9.2.2

--

Regards

Shridhar

On Thu, 2013-01-24 at 09:01 +0530, Shridhar Daithankar wrote:
> On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote:
> > Then someone who wants to look at old JAN data will have the same problem
> > :-(
> >
> > If I recall, Oracle enables something like this.  Multiple tnsfilenames (or
> > something like that).  There was a connect layer on the server side that
> > the DBA had access to where you could do stuff like this.
> > >> proposed new SQL command:
> > >>READ USERS MIND;
> > :
> > :-)
> >
> > Actually, read the DBA's mind.
> >
> > How about...
> >
> > postgres=# create db_alias FEB to db JAN;
> > postgres=# drop db_alias FEB;
>
> I would have suggested to use pg_services file as documented at
>
> http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html
> http://www.postgresql.org/docs/9.1/static/libpq-connect.html
>
> You can think of this as tnsnames replacement.
>
> but I am unable to make it work. I don't know what is wrong with this.
>
> shridhar@bheem ~$ cat ~/.pg_service.conf
> [test1]
> host=localhost
> dbname=test
>
> shridhar@bheem ~$ strace -o psql.strace psql test1
> psql: FATAL:  database "test1" does not exist
>

Well, you need to tell psql to use a service:

psql service=test1

or

PGSERVICE=test1
psql


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Guillaume Lelarge wrote:
> On Thu, 2013-01-24 at 09:01 +0530, Shridhar Daithankar wrote:
> > On Wednesday, January 23, 2013 09:39:43 PM Gauthier, Dave wrote:
> > > Then someone who wants to look at old JAN data will have the same problem
> > > :-(
> > >
> > > If I recall, Oracle enables something like this.  Multiple tnsfilenames (or
> > > something like that).  There was a connect layer on the server side that
> > > the DBA had access to where you could do stuff like this.

> > I would have suggested to use pg_services file as documented at
> >
> > http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html
> > http://www.postgresql.org/docs/9.1/static/libpq-connect.html
> >
> > You can think of this as tnsnames replacement.
> >
> > but I am unable to make it work. I don't know what is wrong with this.
> >
> > shridhar@bheem ~$ cat ~/.pg_service.conf
> > [test1]
> > host=localhost
> > dbname=test
> >
> > shridhar@bheem ~$ strace -o psql.strace psql test1
> > psql: FATAL:  database "test1" does not exist
> 
> Well, you need to tell psql to use a service:
> 
> psql service=test1
> 
> or
> 
> PGSERVICE=test1
> psql

In addition, to return to the example from
http://www.postgresql.org/message-id/0AD01C53605506449BA127FB8B99E5E16112D04F@FMSMSX105.amr.corp.intel.com
you can centralize the name resolution on an LDAP server:
http://www.postgresql.org/docs/current/static/libpq-ldap.html

That saves you from having to change the pg_service.conf
file on each client if you have a lot of clients.

Yours,
Laurenz Albe

Dave Gauthier wrote:
> I would have suggested to use pg_services file as documented at
>
> http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html
> http://www.postgresql.org/docs/9.1/static/libpq-connect.html
>
> You can think of this as tnsnames replacement.
>
> but I am unable to make it work. I don't know what is wrong with this.
>
> shridhar@bheem ~$ cat ~/.pg_service.conf
> [test1]
> host=localhost
> dbname=test
>
> shridhar@bheem ~$ strace -o psql.strace psql test1

That's wrong.  It should have been
  psql "service=test1"
as has been pointed out.

> psql: FATAL: database "test1" does not exist
>
> shridhar@bheem ~$ grep -i pg_service psql.strace
>
> shridhar@bheem ~$ psql test
> psql (9.2.2)
> Type "help" for help.
>
> test=# \q
>
> shridhar@bheem ~$ psql --version
> psql (PostgreSQL) 9.2.2

> The services file looked/looks interesting, but there are far too many clients, and at multiple sites,
> to manage this.  I really need something on the server side, a single place to manage this for all
> connections regardless of where they are coming from.  It also looks like a C lib based file,
> something I won't have access to except, maybe, through an app designed for DBAs to edit this file.
> If I am mistaken, and the services file is in the DB root area (the area specified after the "-D" in
> commands like pg_ctl), then maybe this is still viable.  But I don't see a services file there :-(

The service file is on the client side.

In a scenario like yours, use LDAP lookup:
http://www.postgresql.org/docs/current/static/libpq-ldap.html

Yours,
Laurenz Albe


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

Dave Gauthier wrote:
> I would have suggested to use pg_services file as documented at
>
> http://www.postgresql.org/docs/9.1/static/libpq-pgservice.html
> http://www.postgresql.org/docs/9.1/static/libpq-connect.html
>
> You can think of this as tnsnames replacement.
>
> but I am unable to make it work. I don't know what is wrong with this.
>
> shridhar@bheem ~$ cat ~/.pg_service.conf
> [test1]
> host=localhost
> dbname=test
>
> shridhar@bheem ~$ strace -o psql.strace psql test1

That's wrong.  It should have been
  psql "service=test1"
as has been pointed out.

> psql: FATAL: database "test1" does not exist
>
> shridhar@bheem ~$ grep -i pg_service psql.strace
>
> shridhar@bheem ~$ psql test
> psql (9.2.2)
> Type "help" for help.
>
> test=# \q
>
> shridhar@bheem ~$ psql --version
> psql (PostgreSQL) 9.2.2

> The services file looked/looks interesting, but there are far too many clients, and at multiple sites,
> to manage this.  I really need something on the server side, a single place to manage this for all
> connections regardless of where they are coming from.  It also looks like a C lib based file,
> something I won't have access to except, maybe, through an app designed for DBAs to edit this file.
> If I am mistaken, and the services file is in the DB root area (the area specified after the "-D" in
> commands like pg_ctl), then maybe this is still viable.  But I don't see a services file there :-(

The service file is on the client side.

In a scenario like yours, use LDAP lookup:
http://www.postgresql.org/docs/current/static/libpq-ldap.html

Yours,
Laurenz Albe


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

On 03/23/2014 06:22 PM, Tim Uckun wrote:
> How can this be combined with other commands. For example pgsql doesn't
> like this
>
> psql service=test1 -d test_database -c "some command"

But it would like this:

http://www.postgresql.org/docs/9.3/static/app-psql.html

"An alternative way to specify connection parameters is in a conninfo
string or a URI, which is used instead of a database name. This
mechanism give you very wide control over the connection. For example:

$ psql "service=myservice sslmode=require"

"

>
> the PGSERVICE=test1 psql blah blah works but seems cumbersome.  Why
> isn't there a psql --service=blah option?
>
>
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com