Databases & Data Management › Heroku Postgres › Postgres Data Transfer & Preservation › Heroku Data Links

Heroku Data Links

Last updated 17 August 2018

Heroku Data Links allows you to connect disparate data sources, such as Heroku Postgres, to a Heroku Postgres database. This allows you to use SQL semantics to read and write data, regardless of where the data lives.

Heroku Data Links is only available on production tier Heroku Postgres databases (standard, premium, and enterprise) that are on Postgres version 9.4 and above. Data Links is not available on hobby tier databases.

Overview

Heroku Data Links uses a native feature of Postgres called Foreign Data Wrappers. With Foreign Data Wrappers, it affords a developer the ability to specify a foreign server and the tables in a remote database that map to federated tables in the local database.

When you write a query against data in a remote database, Postgres will automatically query the remote data transparently to the developer, allowing you to join it with local 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.

Terminology

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.
  • Local - The Heroku Postgres database that’s accepting the connection.
  • Data Link - The name of connection between the remote and local databases.

All of the Data Links that have been created can be listed via the Heroku CLI:

$ heroku pg:links -a sushi
=== DATABASE_URL (warming-calmly-5763)
==== postgres_yellow
Created:     2015-06-23 20:58 UTC
Remote:      HEROKU_POSTGRESQL_BLUE_URL (swimming-newly-4067)
Remote Name: swimming_newly_4067

=== HEROKU_POSTGRESQL_CERULEAN_URL (drifting-coyly-8373)
==== reading_deeply_5049
Created:     2015-06-21 08:15 UTC
Remote:      HEROKU_POSTGRESQL_BLUE_URL (reading-deeply-5049)
Remote Name: reading_deeply_5049

=== HEROKU_POSTGRESQL_PINE_URL
No data sources are linked into this database.

=== HEROKU_POSTGRESQL_BURGUNDY
This operation is not supported by hobby-dev databases

The command will list all of the databases attached to an application. Within each database, each link that exists in the associated database will then be listed. In the above example, DATABASE_URL is the local database, also known by it’s resource add-on name warming-calmly-5763.

The Remote: line references the config var and the resource name of the database that’s connected to DATABASE_URL. Remote Name is the server reference inside of the DATABASE_URL database.

Linking Heroku Postgres to Heroku Postgres

You can also link Heroku Postgres to another Heroku Postgres database:

$ heroku pg:links:create HEROKU_POSTGRESQL_BLUE HEROKU_POSTGRESQL_RED --as datalink_test -a sushi

When Heroku Postgres instances are linked, a new schema is added to Postgres based on the link name (in this case, datalink_test schema). If you have the people table in the remote data store HEROKU_POSTGRESQL_BLUE, you can query with that table like this, from the HEROKU_POSTGRESQL_RED database:

SELECT * FROM datalink_test.people;

Only the tables that exist at the time of the link creation will be available. If you create tables after you created the link, they will not show up in the local database. If you want to include newly created tables, you will need to remove the link and create it again.

When you delete the remote data store, or the remote date store moves to another host (e.g. Postgres failover), the data link will not be updated. Also, when you reset your local database with the command like heroku pg:reset, the data link information will also be reset. You will need to recreate the data link for these cases.

Linking Heroku Redis to Heroku Postgres

Creation of Heroku Data Links from Heroku Redis to Heroku Postgres has been deprecated as of January 30, 2018.

A good practice when creating links is to give it a meaningful name. The Data Links create command has the ability to override the link name using the --as flag:

$ heroku pg:links:create HEROKU_POSTGRESQL_RED HEROKU_POSTGRESQL_CERULEAN --as cerulean_red_data -a sushi

The name given for the --as flag should only use lower case alphanumeric characters and underscores and be at most 63 characters long. Data Links will reject any name that does not follow that rule.

When a Data Link has been named, the remote Postgres table should be queried using the same name in the --as flag:

SELECT key, value FROM cerulean_red_data.redis;

To remove your Data Link via the CLI:

$ heroku pg:links:destroy HEROKU_POSTGRESQL_CERULEAN session_data -a sushi
 !    WARNING: Destructive Action
 !    This command will affect the database: DATABASE
 !    This will delete session_data along with the tables and views created within it.
 !    This may have adverse effects for software written against the session_data schema.
 !    To proceed, type "sushi" or re-run this command with --confirm sushi
> sushi
Deleting link session_data in DATABASE... done

If any views or code have been written against a Postgres table that comes from a Data Link, no checks will be made before deletion occurs.

Feedback

Log in to submit feedback.