If I drop my heroku table, can I later restore the database with a Heroku backup?

Question

I'm having some issue with my migrations in Heroku. I've tried several things but none seem to work. I feel like I should just drop the table that's causing the issue, which will delete all the data in production.

If I drop the table, and re-created the table, will I be able to restore all of the data I lost? Because I will backup my database on Heroku before I drop the table.

Thanks!

Answer 1

You should run a backup with

pg_dump -h hostname -p 5432 -U username -F c -t mytable -f dumpfile mydatabase

Then, after you have dropped and re-created the table, you can restore the data with

pg_restore -h hostname -p 5432 -U username -a -d mydatabase dumpfile

However, this will not work if the table structure has changed.

In that case, you might want to use COPY directly to write the data to a file and restore them from there.

Let's for example assume you plan to add another column. Then you could dump with

COPY (SELECT *, NULL FROM mytable) TO '/file/on/dbserver';

After the table was created with the new column, you can

COPY mytable FROM '/file/on/dbserver';

The new column will be filled with the NULL values.

Modify this basic recipe for more fancy requirements.