32

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.

dennismonsewicz
  • 25,132
  • 33
  • 116
  • 189

3 Answers3

71

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
...
...
...
...
$
Sergei Minyukevich
  • 48
  • 5
catsby
  • 11,276
  • 3
  • 37
  • 37
  • 7
    If you need multiple tables add the -t switch for each table. So, `pg_dump --no-acl --no-owner -h [host ip].compute-1.amazonaws.com -U [user name] -t [table name 1] -t [table name 2] -t [table name 3] --data-only [database name] > table.dump` – vansan Nov 18 '13 at 15:34
  • 2
    This worked great, thanks. For others that do this: After completing the above steps, don't forget to reset your PG primary key sequence, so that it knows which primary key to start with. http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync – umezo Jul 30 '14 at 02:12
  • Great info but I'm a bit stumped with a dumb question: can you confirm which user/password to use? I've tried the user provided in the connection info string, which looks like a hashed value, along with the database password defined in `config/database.yml`; have also tried the user defined in that file (`railsapp`), with the password. I've tried my heroku password, with various username combinations (the hash provided, the cleartext heroku username, the dB username). No success. Get a `FATAL: password authenticate failed for user [user]` in all cases. – Scro Jul 30 '15 at 13:01
  • Maybe the "[DATABASE]" should be without square bracket in the pg:credential command – Rn2dy Mar 24 '16 at 17:36
  • In my case it was necessary to add the port number to the `pg_dump` command, like so: `-p [port number]` – mrt Jul 12 '16 at 10:11
  • It's saying the the commaned \N is incorrect again and again. the output file is in text with "\N" everywhere so it makes me wonder if the file output is wrong or if it needs a switch while importing to interpret properly? – Nick Res Mar 16 '17 at 02:23
4

@catsbys answer

I needed to add the port as well

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

Nicolai
  • 321
  • 3
  • 7
1

Take a look at taps (db:pull), your use case is covered by this answered question, I believe.

Community
  • 1
  • 1
friism
  • 19,068
  • 5
  • 80
  • 116