Ubuntu 12.04 is the current LTS version of the distribution and is what I'm using on most of the servers I get to manage.

I always try to work with the packages provided by the distribution. In this case Ubuntu bundled PostgresSQL 9.1 as the most up-to-date version of the database software in the distribution (you can always use an update PPA to get the shiny new things).

In the middle of a small migration between versions of our software I needed to populate a database with information from other database. By design you can't do queries between databases in PostgreSQL because it loads database-specific system catalogs and it is uncertain how a cross-database query should even behave.

For this problem you can use dblink, it basically acts like a function that contains a query in it and returns the result of the query. dblink comes as part of the contrib package in PostgreSQL, and it's present in the postgresql-9.1-contrib package in Ubuntu.

So, supposing you already have PostgreSQL 9.1 running you can install the contrib package with:

sudo apt-get install postgresql-contrib-9.1

And to load the dblink modules in PostgreSQL you've to:

sudo su postgres -c "psql -c "CREATE EXTENSION dblink" postgres"

Supposing that "postgres" is the database where you want to use dblink. That's it, to try it:

postgres=# select username, app from dblink('dbname=booki', 'select usename, application_name from pg_stat_activity') as t(username text, app text);
 username | app
----------+------
 postgres | psql
 postgres |
(2 rows)

Magic!