How to recover data from a previous backup in Postgres Heroku? (For example, accidentally deleted lines)

Question

Here's the situation: with Heroku and Postgres, you can create an automatically created backup copy file. But what can you do with it?

• Dump it in your database if you want to fully return to the backup state
• Dump locally to "take a look" or use production data in a development environment.
• Set specific rows of your database in a previous state (for example, restore accidentally deleted rows)

I found myself fighting very hard about how I wanted to share how I did it.

How to recover certain data from a previous backup in Postgres Heroku?

Answer 1

Summary / TL; DR

In 3 stages you can perform very simply:

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

First, install the backup locally, the second get the SQL script, the third open your local host to the outside world using ngrok .

Let go?

1. Download the Heroku dump file and upload it somewhere:

• You can do this in a remote database if you have available servers. But, if I, like me, you do not want to provide another production database on Heroku or elsewhere, it will be completely local.
• I like to use PGAdmin (available on Linux, Mac and Windows), but using the command line and psql will also do (after reading this post example)
• In PGAdmin, you would do Create a database . Then right-click it and use the restore function. Select the dump file, click restore , and everything will be installed: 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 tuned. Super easy, right? Pretty obvious? It must have been hundreds of times already. Oh no!

Postgres 9.1+ has a utility called db_link , but it is quite limited, because the following syntax is used:

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

Each column name must be repeated twice, including its type. Pretty heavy, we are far from simple SELECT * FROM backup_db.table_name

So, the idea here is to use the contents of the information_schema table, which describes each table with column names, its types, etc. I found this question in SO: Indicate the dblink column a list of definitions from a local existing type that helped me a lot (thanks bentrm ).

But his solution was a two-step process, first creating a function and then calling it:

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

And I was still aiming for 1 liner. After a little pain (not a SQL guru), here is the Gist: https://gist.github.com/aug-riedinger/d30973ea8b5bf0067841

Now I 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) 

Amazing right?

3. Remote access to localhost

If your remote database is already accessible from the Internet (= has an IP address, the Eg. Domain name for Heroku will look like this: 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 accessible from the outside world (so that the Heroku database can access it).

For this I use the wonderful ngrok .

After installation, 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 only need to connect db_link (in line) to host=ngrock.com port=51727 , and you're good to go !

4. Next

There are many possible improvements. Here are some of them that I see:

• Given the script as the default function for the db_link function
• Be more error resistant if the database structures are different during backup and production.
• Creating a comparison tool between database results and backup results (only for row returns)
• Handle simple connections
• And even further there will be an application-level adapter (for example, ActiveRecord in Rails) that could allow manipulating backend objects instead of raw SQL, as it is now.

I hope I get it! Please contact for more information.