0

I have a relatively large data table (~4m rows) that has been imported to a locally hosted postgresql database. (As it happens it's a ruby on rails app database, but that shouldn't be important for the purposes of the question - unless it helps)

I want to take that table and add it into an identical table in a heroku postgresql database (the table is currently empty).

How would I do that quickly and efficiently?

I found this Copy a table from one database to another in Postgres but I'm struggling with the syntax for the heroku end, i.e. how do I connect to both at the same time? Which database am I connecting to originally?

Carpela
  • 2,155
  • 1
  • 24
  • 55

1 Answers1

1

In that answer, you are originally connected to the database "source_db" or "my_db" (depending on which line in the answer you are looking at). Presumably that database is on the instance running locally on port 5432, unless unshown environment variables (or non-default compilation) have changed that. And the destination database is named "target_db", running in the same instance.

The pg_dump and psql are independent commands and each takes all the connection options that they would take if run in isolation. So you would probably want something like:

pg_dump -t table_to_copy source_db | psql target_db -h you.heroku.hostname_or_ip

A problem could be if both commands prompt for a password, it might make a mess. Which password do you need to enter first? And whichever order, will they read them correctly? If both need passwords, it is best to arrange that at least one of them be supplied by ~/.pgpass.

jjanes
  • 37,812
  • 5
  • 27
  • 34