database name aliases?

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

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

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

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

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

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?

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

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

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