Postgres :: CREATE VIEW ? suggestions

sONOCOOLO

Hi all,

I have to databases (Postgres) which have the same tables, with the same field names and field types. Only the data stored in the 2 databases is different.

I want to be able to search in both databases at once. E.g. I would like to be able to search in database1.agents and database2.agents as if it was one table.

So i guess I need to create a view(s), but since I never did this, I'm not sure how I should do this.

I would be most thankfull if people can give me some ideas with respect to how I should realize this.

thanks in advance

Sxooter

Originally posted by sONOCOOLO
Hi all,

I have to databases (Postgres) which have the same tables, with the same field names and field types. Only the data stored in the 2 databases is different.

I want to be able to search in both databases at once. E.g. I would like to be able to search in database1.agents and database2.agents as if it was one table.

So i guess I need to create a view(s), but since I never did this, I'm not sure how I should do this.

I would be most thankfull if people can give me some ideas with respect to how I should realize this.

thanks in advance

Out of the box, postgresql does not support cross database joins / unions etc...

However, there is a contrib module called dblink that will let you do this.

A better idea would be to upgrade to 7.3 (it JUST came out) and get the equivalent by putting all your data into one database and seperating it based on schema.

sONOCOOLO

Thanks for the advise Sxooter.

Since I'm totally new to Postgres I'm a little bit worried about upgrading to postgres 7.3 (I'm using 7.1.3 at the moment).

Is it easy to upgarde?

I read that I only had to upgrade the initdb, but I'm not sure if that's correct.

Sxooter

Originally posted by sONOCOOLO
Thanks for the advise Sxooter.

Since I'm totally new to Postgres I'm a little bit worried about upgrading to postgres 7.3 (I'm using 7.1.3 at the moment).

Is it easy to upgarde?

I read that I only had to upgrade the initdb, but I'm not sure if that's correct.

Upgrading Postgresql is one of those things that sounds hard but is pretty easy. Which, come to think of it, is better than the other way around...

Anyway, to upgrade, you basically pg_dumpall your database with the old engine (i.e. 7.1.x for you) then shut down the database, delete the data in $PGDATA, install the new version, run initdb, then restore your data with pg_restore.

Note that upgrading from one bug fix rev to the next does not require the whole dump/initdb/restore boogie. I.e. going from 7.3.0 to 7.3.1 requires only shutting down the database and installing the new 7.3.1 then restarting.

There are a couple of minor bug fixes that should be out in a week or so for 7.3.1, so you might wanna wait til then to install it, just to save the headache of upgrading twice.

Note that 7.2 was a big improvement over 7.1 in terms of the query planner optimization, and also vacuuming was cleaned up a bunch, making 7.2 more robust when being vacuumed.

7.3 is more of a feature release, and while there are a bunch of improvements for performance wrapped into it, the biggest gains are features like schemas and finer grained access controls.

If you've got the spare time, grab the 7.3 tar balls and install them on a spare box and see if you can migrate your data into the new database on the spare box just to get familiar with upgrading.

The instructions for upgrading are here:

http://developer.postgresql.org/docs/postgres/install-upgrading.html

Good luck, and enjoy.