Postgres on Heroku and dumping single table to dump file

postgresql heroku heroku-toolbelt
dennismonsewicz picture dennismonsewicz · Apr 3, 2013 · Viewed 9.7k times · Source

I am using Postgres on Heroku and am needing to dump one table from my production DB and dump it into my staging DB. I have the heroku tool belt installed, but am not sure how to dump a single db table to import into my staging db.

Answer

catsby picture catsby · Apr 22, 2013

You can dump a single table of data like so:

$ pg_dump --no-acl --no-owner -h [host ip].compute-1.amazonaws.com -U [user name] -t [table name] --data-only [database name] > table.dump

You can get all of the values needed with this:

$ heroku pg:credentials:url [DATABASE] -a [app_name]
Connection info string:
   "dbname=[database name] host=[host ip].compute-1.amazonaws.com port=5432 user=[user name] password=[password] sslmode=require"
Connection URL:
    postgres://[username]:[password]@[host ip].compute-1.amazonaws.com:5432/[database name]

This will prompt you for your password. Enter it, and you should then proceed to get a file table.dump on your local drive.

You probably want to truncate the table on staging:

$ echo "truncate [table];" | heroku pg:psql [DATABASE] -a staging_app

With that file, you can use psql with the Connection URL:output of a new call to pg:credentials for the staging app and restore just that table. 

$ psql "[pasted postgres:// from pg:credentials:url of staging app]" < table.dump
SET
SET
...
...
...
...
$

Related questions

Heroku "psql: FATAL: remaining connection slots are reserved for non-replication superuser connections"

I'm developing an app on Heroku with a Postgresql backend. Periodically, I get this error message when trying to access the database, both from the CLI and from loading a page on the server: psql: FATAL: remaining connection slots are …

Read More
Getting "[archiver] unsupported version (1.13) in file header" when running pg_restore

I just upgraded to postgres 10.2 on mac os which matches 10.2 on heroku. I'm trying to download a copy of the database and restore it locally. Before the upgrade the restore would work fine. I run pg_restore --verbose --clean --no-acl …

Read More
Connect to a heroku database with pgadmin

I would like to manage my heroku database with pgadmin client. By now, I've been doing this with psql. When I use data from heroku pg:credentials to connect de DB using pgadmin, I obtain: An error has occurred: Error …

Read More