0

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!

Ozge Cokyasar
  • 301
  • 1
  • 4
  • 16

1 Answers1

0

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Hi Laurenz, Thanks for the answer. I will backup my production database on Heroku. Do you know if I will be able to restore the Heroku database if I delete the table and create exactly the same one again? – Ozge Cokyasar Sep 16 '20 at 10:06
  • No idea about Heroku's services, perhaps someone else can fill in. But my answer should work with hosted databases just as well as with others. – Laurenz Albe Sep 16 '20 at 10:20
  • Databases aren't directly hosted on a Heroku dyno. Furthermore you can pick your own database server and reference that server in your code/environment variables. [Heroku has a tutorial for their Postgres add-on](https://devcenter.heroku.com/articles/heroku-postgres-backups). – Tin Nguyen Sep 16 '20 at 11:46