Generally speaking, the PostgreSQL documentation is great, but every so often, I come across some glaring omissions that cost me 20 minutes or so of poking around and guessing before I find the answer.
For those who don’t know about dblink, it’s a function in PostgreSQL that allows you to connect to a different database and execute queries. You can set up a persistent connection to query through or you can do a one-off connection to pull data. I’m migrating tables from a development environment to a production environment and needed to pull a couple of tables over instead of loading them from the raw data.
The examples provided for dblink only offer you the connection arguments “dbname” (database name). Well that’s fine if you’re connecting to a different database on the same host, but if you have to reach out to another host, you have to provide the host name, as well as user authentication parameters (username and password).
The text description turfs us over to dblink_connect for a description of the connection parameters, but the only examples provided use <hostaddr>, for an IP address, but if you have a hostname instead of an IP, this will fail. You have to skip over to the “Database Connection Control Functions” section to find the parameter name as simply “host” (I would’ve preferred “hostname” but whatever works). Likewise, the arguments for user authentication are “user” and “password”.
So, if you want to make the one-off connection using dblink, the syntax would be:
FROM dblink( ‘host=host.com port=5432 dbname=pg_db_name user=pg_user password=pg_password’, ‘SELECT * FROM foo’) as bar( col1 <datatype>, col2 <datatype>,…);
In this case, the alias is important for setting up the datatypes for the query result. Since I have access to the table definitions in pgAdmin, I just copy them and paste them into the alias definition so the table comes over with the same data types and column names as the original.
For all I’d heard about how difficult dblink was to use, I didn’t find it that bad. I wasn’t setting up a persistent connection, but for establishing a connection for a one-off query, after I got the connection parameters set, the queries were quick and painless to execute. Definitely a nice technique to have in the DBA toolbox.