The first requirement of data federation is the ability to connect to remote databases. With this capability, we can read or write to a remote PostgreSQL database table as if it were local. By doing so, certain query elements can be offloaded to the other server. We can also access metadata that is stored in some central location that acts as a shared resource for all database servers.
This recipe will describe how to create a foreign PostgreSQL server and will be the basis for several of the upcoming segments.
Before we can use the PostgreSQL foreign data wrapper functionality, we need to add the postgres_fdw extension to the database that will use it. Execute this SQL statement as the postgres user in the database that will be contacting foreign servers (pgbench, for example):
CREATE EXTENSION postgres_fdw;
For this recipe, we have two servers: pg-primary as our main data source and pg-report as a reporting server. As with the previous recipe, we will use pgbench as our sample database. Follow these steps to create a connection from pg-report to pg-primary within pgbench.
- Connect to
pgbenchon thepg-reportPostgreSQL server as thepostgresuser. - Execute the following SQL statement:
CREATE SERVER primary_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'pg-primary', dbname 'pgbench'), - Execute this SQL statement to check for the foreign server entry:
SELECT srvname, srvoptions FROM pg_foreign_server;
We start by connecting to the database where we will be accessing remote data. As our test database is pgbench, this is where the foreign server will reside.
The server creation itself consists of a server name, a foreign data wrapper, and options to the foreign data wrapper. For the server name, we used primary_db to keep things simple, but anything relatively descriptive is a good choice.
The CREATE SERVER statement can use several available foreign data wrappers, but to contact a PostgreSQL server, we need postgres_fdw. This data wrapper will accept many standard PostgreSQL connection parameters, including host, dbname, port, and so on.
We only used the dbname and host settings because we don't want to force this server connection to always use any specific user or password combination. This allows us to map one or more local users to users on the remote database. When new connections are created to the foreign server, each user will access the remote data as themselves. This is a much more secure usage pattern.
Finally, we check the pg_foreign_server view to make sure PostgreSQL registered it with the options we specified. Once this is verified, we can move on to the next step. Here is our test server's output:
Foreign data servers have a couple more pieces of functionality that we should discuss.
Assume for a moment that we need the definition of the primary_db foreign server to change. For instance, what if we integrated pgBouncer to reduce user contention and we need to use a nondefault port of 5433? Here's how we would add the port option:
ALTER SERVER primary_db OPTIONS (ADD port '5433'),
If we need to change this again later, we would use this syntax instead:
ALTER SERVER primary_db OPTIONS (SET port '5444'),
We must admit that this difference in syntax is something of an oddity. To PostgreSQL, SET only modifies the settings that were specified when we called CREATE SERVER. We must use ADD to override a default, even though SET could have been overloaded to perform both actions. This merely means ADD might fail with an error, noting that the option isn't found. If this happens, simply use SET instead.
The PostgreSQL foreign data wrapper has quite a bit of documentation available. The CREATE SERVER statement has its own entry as well. Please refer to these URLs for more information: