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
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