Database Reference
In-Depth Information
Accessing objects in other PostgreSQL
databases
Sometimes we want to access data in other PostgreSQL databases. Reasons might be
as follows:
F
You have more than one database server, and you need to extract data from one and
load it into the other, such as reference data.
F
You want to access data that is in a different database on the same database server
which had been split up for administrative purposes.
F
You want to perform some changes that you do not wish to rollback in the event of an
error or transaction abort—known as function side-effects or autonomous transactions.
You might also be considering this because you are exploring scale out, sharding or
load-balancing approaches. If so, please read the last part of this recipe
See
Also,
and then skip to the chapter on
Replication
.
Getting ready
Install dblink, which is a contrib module for PostgreSQL.
Next, we create some access definitions. This can be done in various ways, but these
commands are SQL Standard (SQL/MED), so it seems useful to follow them:
postgres=# CREATE FOREIGN DATA WRAPPER postgresql
VALIDATOR postgresql_fdw_validator;
CREATE FOREIGN DATA WRAPPER
postgres=# CREATE SERVER otherdb
FOREIGN DATA WRAPPER postgresql
OPTIONS (host 'foo', dbname 'otherdb', port '5432');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR PUBLIC
SERVER otherdb;
CREATE USER MAPPING
You need only create one
FOREIGN
DATA
WRAPPER
, though you need to create one
SERVER
for each PostgreSQL destination database to which you may wish to connect to. This is just
the connection definition, not the connection itself.
Creating a public user mapping with no options seems strange, though it will mean that we
use the libpq default behavior. This will mean that we will connect the remote database using
the value of
PGUSER
, or if not set, use the operating system user.
