How to pg_restore one table and its schema from a Postgres dump?

Question

I am having some difficulties with restoring the schema of a table. I dumped my Heroku Postgres db and I used pg_restore to restore one table from it into my local db (it has more than 20 tables). It was successfully restored, but I was having issues when I tried to insert new data into the table.

When I opened up my database using psql, I found out that the restored table is available with all the data, but its schema has zero rows. Is there anyway I could import both the table and its schema from the dump? Thank you very much.

This is how I restored the table into my local db:

pg_restore -U postgres --dbname my_db --table=message latest.dump

Edit:

I tried something like this following the official docs, but it just gets blocked and nothing happened. My db is small, no more than a couple of megabytes and the table's schema I am trying to restore has no more than 100 row.

pg_restore -U postgres --dbname mydb --table=message --schema=message_id_seq latest.dump

Answer 1

As a more general answer (I needed to restore a single table from a huge backup), you may want to take a look at this post: https://thequantitative.medium.com/restoring-individual-tables-from-postgresql-pg-dump-using-pg-restore-options-ef3ce2b41ab6

# run the schema-only restore as root
pg_restore -U postgres --schema-only -d new_db /directory/path/db-dump-name.dump

# Restore per table data using something like
pg_restore -U postgres --data-only -d target-db-name -t table_name /directory/path/dump-name.dump

Answer 2

From the Heroku DevCenter here

Heroku Postgres is integrated directly into the Heroku CLI and offers many helpful commands that simplify common database tasks

You can check here if your environment is correctly configured.

In this way, you can use the Heroku CLI pg:pull command to pull remote data from a Heroku Postgres database to a local database on your machine.

For example:

$ heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi