Thread: database name aliases?

database name aliases?

From

Reece Hart

Date:

06 November 2006, 22:14:00

I'd like to be able to have several versions of a database available concurrently and one database alias that refers to the most recent of these. For example:

dbname_1-1
dbname_1-2
dbname_1-3
dbname -> dbname_1-3

and
$ psql -d dbname
would connect to dbname_1-3.

Any ideas?

Thanks,
Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: database name aliases?

From

"Talha Khan"

Date:

06 November 2006, 22:54:16

Hi Reece,

You can do this by using a script. I mean whenever you are creating a new version of the database. you can do that by running a script that creates a database and then stores the name of that database in an environmental variable . Now you can run psql by connecting to a database through that variable.

Regards
Talha Khan

On 11/7/06, Reece Hart <reece@harts.net> wrote:

I'd like to be able to have several versions of a database available concurrently and one database alias that refers to the most recent of these. For example:

dbname_1-1
dbname_1-2
dbname_1-3
dbname -> dbname_1-3

and
$ psql -d dbname
would connect to dbname_1-3.

Any ideas?

Thanks,
Reece
-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: database name aliases?

From

Reece Hart

Date:

06 November 2006, 23:29:59

On Tue, 2006-11-07 at 04:54 +0500, Talha Khan wrote:

You can do this by using a script. I mean whenever you are creating a new version of the database. you can do that by running a script that creates a database and then stores the name of that database in an environmental variable . Now you can run psql by connecting to a database through that variable.

I think I understand the gist of your proposal, but psql was just one client example. In principle, I'd want the same alias to be exposed to psql, perl DBI, odbc, jdbc, etc. I really think this should be done in the database itself to ensure consistency.

-Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: database name aliases?

From

AgentM

Date:

06 November 2006, 23:41:04

On Nov 6, 2006, at 19:29 , Reece Hart wrote:

On Tue, 2006-11-07 at 04:54 +0500, Talha Khan wrote:
You can do this by using a script. I mean whenever you are creating a new version of the database. you can do that by running a script that creates a database and then stores the name of that database in an environmental variable . Now you can run psql by connecting to a database through that variable.

I think I understand the gist of your proposal, but psql was just one client example. In principle, I'd want the same alias to be exposed to psql, perl DBI, odbc, jdbc, etc. I really think this should be done in the database itself to ensure consistency.

I partition production, testing, development areas by using schemas. Using ALTER SCHEMA X RENAME TO Y, it's trivial to shuffle around the names as you like.

Cheers,

Re: database name aliases?

From

Jeff Davis

Date:

06 November 2006, 23:43:51

On Mon, 2006-11-06 at 16:29 -0800, Reece Hart wrote:
> On Tue, 2006-11-07 at 04:54 +0500, Talha Khan wrote:
> > You can do this by using a script. I mean whenever you are creating
> > a new version of the database. you can do that by running a script
> > that creates a database  and then stores the name of that database
> > in an environmental variable . Now you can run psql by connecting to
> > a database through that variable.
>
> I think I understand the gist of your proposal, but psql was just one
> client example. In principle, I'd want the same alias to be exposed to
> psql, perl DBI, odbc, jdbc, etc. I really think this should be done in
> the database itself to ensure consistency.
>
> -Reece

You can use "ALTER DATABASE name RENAME TO newname;". Does that help?

I don't think you cane have aliases to the same database, however.

Regards,
    Jeff Davis

Re: database name aliases?

From

Reece Hart

Date:

07 November 2006, 02:58:22

On Mon, 2006-11-06 at 16:43 -0800, Jeff Davis wrote:

You can use "ALTER DATABASE name RENAME TO newname;". Does that help?

This is what I do now to evolve from development to staging to production, as well as to deprecate versions. That indeed solves most of the problem.

Aliases might solve two problems. The first is to address the oft-recurring problem of wanting to be able to refer simultaneously to an instance and more generally to a concept (e.g., HEAD in cvs, or /etc/alternatives/ for system executables, etc). That is, one could refer to a specific db version/instance as well as a name for the "most recent" version (or dev, stage, prod, or whatever).

The second goal is more practical: postgres doesn't allow a database to be renamed while it's in use and that prohibition causes minor scheduling problems when rotating instances. I imagine that db aliases would affect only new connections.

Thanks,
Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: database name aliases?

From

"Albe Laurenz"

Date:

07 November 2006, 07:04:05

Reece Hart wrote:
> I'd like to be able to have several versions of a database
> available concurrently and one database alias that refers to
> the most recent of these.  For example:
>
> dbname_1-1
> dbname_1-2
> dbname_1-3
> dbname -> dbname_1-3
>
> and
> $ psql -d dbname
> would connect to dbname_1-3.
>
> Any ideas?

In 8.2 (currently beta) you can store connection data and
database name on an LDAP server and refer to it via a service name.

See http://developer.postgresql.org/pgdocs/postgres/libpq-ldap.html

You can use the service name to connect:

env PGSERVICE=dbname psql

This will work with all client interfaces that use libpq to connect.

Yours,
Laurenz Albe

Re: database name aliases?

From

"Woody Woodring"

Date:

07 November 2006, 11:49:05

We use the pg_services.conf file.

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

In the file you can point the alias to whatever db you want. To connect:

[bash]$ PGSERIVCE=dbname psql

Woody

IGLASS Networks

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Reece Hart
Sent: Monday, November 06, 2006 6:07 PM
To: pgsql-general
Subject: [GENERAL] database name aliases?

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

Re: database name aliases?

From

Jeff Davis

Date:

07 November 2006, 17:38:17

On Mon, 2006-11-06 at 19:58 -0800, Reece Hart wrote:
> On Mon, 2006-11-06 at 16:43 -0800, Jeff Davis wrote:
> > You can use "ALTER DATABASE name RENAME TO newname;". Does that
> > help?
>
> This is what I do now to evolve from development to staging to
> production, as well as to deprecate versions. That indeed solves most
> of the problem.
>
> Aliases might solve two problems. The first is to address the oft-
> recurring problem of wanting to be able to refer simultaneously to an
> instance and more generally to a concept (e.g., HEAD in cvs,
> or /etc/alternatives/ for system executables, etc). That is, one could
> refer to a specific db version/instance as well as a name for the
> "most recent" version (or dev, stage, prod, or whatever).
>

I see what you're trying to do, but PostgreSQL just doesn't have that
capability. An extra layers of indirection may be nice, but in this
case, it doesn't exist.

You should probably take a look more on the application side. You can
probably accomplish what you need with network software like PgPool. I
don't think that can currently do what you need, but that might be a
better place to implement the features you need.

Regards,
    Jeff Davis

Re: database name aliases?

From

Reece Hart

Date:

07 November 2006, 17:42:05

Woody and Albe-

I wasn't aware of pg_service -- that does solve my original problem.

Thanks for the replies.

-Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0