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

Mana

By "schema", do you mean "table data", a sequence, or the object that is created by CREATE SCHEMA? – Male

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

Freesia

Answer 2

Restore a single schema qualified table

Building on @Haroldo_OK's answer; to specify the schema that the table belongs to use the --schema option when restoring the data:

# Restore only the schema (data definitions), not data into the database new_db
# which is assumed to already exist
pg_restore -U postgres --schema-only -d new_db /directory/path/db-dump-name.dump

# Restore the data for the schema_name.table_name table in the new_db database
pg_restore -U postgres --data-only -d new_db --schema schema_name --table table_name /directory/path/dump-name.dump

If the Postgres cluster that you are restoring into is a different cluster from the one the backup was made from then the cluster you are restoring into might not have the group/login roles of the one the backup was made from. In this instance you can choose to specify the --no-owner and --no-privileges options which prevent restoration of object ownership and access privileges (grant/revoke commands).

pg_restore documentation.

Gisele

Answer 3

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

Landis

This doesn't answer the specific question. Using pg:pull will restore the entire database locally whereas OP wants to restore a single table. – Arnulfo

@Arnulfo It’s true, but I’ve suggested using directly the Heroku tools for two reason: 1) the DB is only a “couple of MB” then basically there are no differences - I wouldn’t have suggested otherwise 2) if an user does not have much experience on PG dump/restore this could be an easier and safer way for him – Landis

@Andrea, I have already pulled my remote database into my local one; and I have pg_restored the desired table, the problem is with the sequence table, they don't get restored with their tables automatically. I had to edit the sequence table to match the data table manually. Not the best approach but worked. Thanks for your answer. – Mana