Heroku Data Links
Last updated February 15, 2023
Table of Contents
Heroku Data Links helps you connect a Heroku Postgres database to another Heroku Postgres database. After connecting, you can use SQL semantics to read and write data from them, regardless of where the data lives.
Heroku Data Links isn’t available on Essential-tier databases.
Heroku Data Links uses a native feature of Postgres called Foreign Data Wrappers. A Foreign Data Wrapper maps the logical representation of foreign tables on a foreign server to tables in the local database.
When you write a query against data in a remote database, Postgres transparently queries the remote data. The details of where the data lives and how it’s abstracted in Postgres are pushed down into the Postgres extension that connects the two data stores.
Heroku Data Links takes care of the difficult parts of managing Foreign Data Wrappers, including creating the server specification within the local database and creating tables to query against in the remote system.
The documentation on Heroku Data Links uses a strict set of terms to define all of the interactions.
- Remote: The data store that is being connected to a Heroku Postgres database. For the examples in this article,
HEROKU_POSTGRESQL_BLUE_URLis the config var designating the URL of the remote database and
postgres-colorful-33594is the add-on name for the remote database.
- Local: The Heroku Postgres database that’s accepting the connection. This term corresponds to the foreign server in the Foreign Data Wrapper context. For the examples in this article,
DATABASE_URLis the config var designating the URL of the local database and
postgres-animate-91581is the add-on name for the local database.
- Data Link: The name of the connection between the remote and local databases. For the examples in this article,
DATA_LINK_NAMEis a placeholder name and
datalink_testis the name for the data link.
Listing Data Links
List all the existing Data Links for an application with the
heroku pg:links CLI command:
$ heroku pg:links -a example-app
remote: HEROKU_POSTGRESQL_BLUE_URL (postgresql-colorful-33594)
No data sources are linked into this database.
This operation is not supported by mini databases
The command lists all the databases attached to an application and existing Data Links within each database. In the previous example,
postgresql-animate-91581 is the local database for the
datalink_test Data Link.
Remote: line references the config var and the resource name of the database that’s connected to the local database.
Remote Name is the foreign server reference inside of the local database.
Linking Heroku Postgres to Heroku Postgres
Create a Data Link between two Heroku Postgres databases with the
heroku pg:links:create [REMOTE] [LOCAL] --as [DATA_LINK_NAME] command:
$ heroku pg:links:create HEROKU_POSTGRESQL_BLUE_URL DATABASE_URL --as datalink_test -a example-app
Names must consist of lower case alphanumeric characters and underscores and be at most 63 characters.
When Heroku Postgres instances are linked via Data Links, a new schema is added to Postgres based on the link name (in this example, the schema is named
Setting up a Data Link creates a new foreign schema in your local database, named as the Data Link. All the tables in the
public schema of your remote database are created as foreign tables within this schema.
heroku pg:psql to inspect all the schemas of your database with the
\dn command and find the new schema:
$ heroku pg:psql postgresql-animate-91581 -a example-app
psql (13.2, server 13.5 (Ubuntu 13.5-2.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
List of schemas
Name | Owner
datalink_test | u12344l89vh890
public | u12344l89vh890
You can list all the foreign tables created by the Data Link by running
example-app::DATABASE=> \det datalink_test.*
List of foreign tables
Schema | Table | Server
datalink_test | people | postgresql_colorful_33594
If you have the
people table in the remote data store
HEROKU_POSTGRESQL_BLUE, you can query that table from the local database like this:
SELECT * FROM datalink_test.people;
Only the tables that exist at the time of the link creation are available. If you create tables after you created the link, they won’t appear in the local database. If you want to include newly created tables, you must remove the link and create it again.
When you delete the remote data store, or the remote data store moves to another host (for example, Postgres failover), the data link doesn’t get updated. Also, when you reset your local database with the command like
heroku pg:reset, the Data Link and the internal Foreign Data Wrapper configuration are also reset. You must recreate the Data Link for these cases.
Linking Heroku Data for Redis to Heroku Postgres
Creation of Heroku Data Links from Heroku Data for Redis to Heroku Postgres has been deprecated as of January 30, 2018.
Removing a Data Link
Remove a Data Link with the
heroku pg:links:destroy [LOCAL] [DATA_LINK_TEST] CLI command:
$ heroku pg:links:destroy DATABASE_URL datalink_test -a example-app
! WARNING: Destructive Action
! This command will affect the database: DATABASE
! This will delete datalink_test along with the tables and views created within it.
! This may have adverse effects for software written against the datalink_test schema.
! To proceed, type "example-app" or re-run this command with --confirm example-app
Deleting link datalink_test in DATABASE... done
If any views or code have been written against a Postgres table that comes from a Data Link, no checks are made before the Data Link is deleted.
Caveats and Considerations
- Data Link creation fails if the tables in your remote database have columns with custom types that don’t exist in the local database. Create any required custom types in your local database in advance before creating the Data Link.
- Data Links isn’t supported on Shield Heroku Postgres plans, even if these exist in the same Shield Space, due to ingress and egress network traffic restrictions.