Here’s the situation : With Heroku & Postgres, you can have automatically generated backups dump file. But what can you do with it?

1. Dump it on your database, if you want to fully go back to the backup state
2. Dump it locally to “have a look”, or to use production data in development environment
3. Set back specific rows of your database in a previous state (eg. restore accidentally deleted rows)

I found myself so much struggling about latter point that I wanted to share how I have done it.

Summary / TL;DR

In 3 steps you’ll be able to execute very simply:

INSERT INTO production_db.table_name
SELECT * FROM backup_db.table_name -- backup_db being remote

First install the backup locally, second get a SQL script, third open your localhost to the outside world with ngrok.

Let’s go?

1. Download your dump file on Heroku and dump it somewhere:

• You can do that on a remote database if you have some servers available. But if like me you don’t want to provision another production database on Heroku or somewhere else, locally will totally do.
• I like to use PGAdmin (available on Linux, Mac and Windows), but using command line and psql will also do (by reading this post by example)
• In PGAdmin, you’d do Create a database. Then right click on it and use the restore function. Select your dump file, click Restore and you’re all set : your backup data is available locally! Good job!

2. Access it from your remote database

I wanted to do the following:

SELECT * FROM backup_db.table_name
-- So I could then do
INSERT INTO production_db.table_name
SELECT * FROM backup_db.table_name

And I would be all set. Super easy, right? Pretty obvious? This must have been done hundreds of times already. Well, no!

There is a utility called db_link in Postgres 9.1+, but it is pretty constraining as the following syntax applies:

SELECT fname, lname FROM db_link('host=localhost dbname=backup-28-08', 'SELECT fname, lname FROM users') AS remote (varchar255 fname varchar255 lname)

Every column name needs to be repeated twice including its type. Pretty heavy, we are far from the simple SELECT * FROM backup_db.table_name

So the idea here is to use the information_schema table content, which describes each table with its column names, its types etc. I found this question on SO: http://stackoverflow.com/questions/18317216/specify-dblink-column-definition-list-from-a-local-existing-type which helped me a lot (Thanks bentrm).

But its solution was a two steps process, first generating a function, then querying it:

SELECT dblink_star_func('dbname=ben', 'public', 'test');
SELECT * FROM star_test() WHERE data = 'success';

And I was still aiming at a 1 liner. After some little pain (not being a SQL Guru), here is the Gist:

CREATE OR REPLACE FUNCTION remote_db(_table anyelement)
RETURNS SETOF anyelement
AS $func$
DECLARE
    _host                text := 'ngrok.com'
    _port                text := '53813'
    _user                text := 'postgres'
    _password            text := 'postgres'
    _db_name             text := 'backup-28-08'
    _server              text := format('host=%s port=%s user=%s password=%s dbname=%s', _host, _port, _user, _password, _db_name);
    _table_name          text := pg_typeof(_table);
    _dblink_schema       text;
    _cols_names          text;
    _remote_schema_query text;
    _information_schema  text;

BEGIN
    -- Still unclear about what this does, what a schema is etc.
    SELECT nspname INTO _dblink_schema
    FROM pg_namespace n, pg_extension e
    WHERE e.extname = 'dblink' AND e.extnamespace = n.oid;

    -- Fetch the remote table information_schema to get column names and types
    EXECUTE format(
     'SELECT array_to_string(array_agg(remote_cols.column_name || '' '' || remote_cols.udt_name), '', '')
      FROM %I.dblink(%L, %L) AS remote_cols (column_name text, udt_name text)',
      _dblink_schema,
      _server,
      format('SELECT column_name, udt_name FROM information_schema.columns WHERE table_name = ''%s'' ORDER BY ordinal_position', _table_name)
    ) INTO _cols2;

    -- Execute the query itself and returns it
    RETURN QUERY EXECUTE format('SELECT (remote::%I).* FROM %I.dblink(%L, %L) AS remote (%s)',
      _table_name,
      _dblink_schema,
      _server,
      format('SELECT * FROM %I', _table_name),
      _cols_names
    );


END;
$func$ LANGUAGE plpgsql;

I now can do:

SELECT * FROM remote_db(NULL::users) -- (Still not 100% about why I need the NULL::)
-- And also
INSERT INTO users
SELECT * FROM remote_db(NULL::users)

Awesome, right?

3. Access localhost remotely

If your remote database is already available from the internet = has an IP address, a domain name (Eg. for Heroku it will look like: ec2-54-217-229-169.eu-west-1.compute.amazonaws.com:5672/df68cfpbufjd9p) you can skip this step. But if you use your local database, you need to make it available from the outside world (so that the Heroku database can access it).

For this, I use the wonderful ngrok.

Once installed I only need to enter the following command:

ngrok -proto=tcp 5432 #5432 being the default port for Postgresql. (Adapt if necessary)
Tunnel Status                 online
Version                       1.7/1.6
Forwarding                    tcp://ngrok.com:51727 -> 127.0.0.1:5432
Web Interface                 127.0.0.1:4040
# Conn                        0
Avg Conn Time                 0.00ms

And you’d only need to plug db_link (in the gist) to host=ngrock.com port=51727 and you are good to go!

4. Going further

There are many possible improvements to this. Here are some I see already:

• Considering the script as a default feature to db_link function
• Being more error-proof if database structures are different in backup and production
• Making comparison tool between database results and backup results (to only return diffing lines)
• Handle simple joins
• And even further would be to have an application level adapter (Eg. ActiveRecord in Rails) that could allow manipulation of backend objects instead of raw SQL like now

Hope I was clear! Please ask for more details otherwise.