'How to download only specific tables from heroku PorstgreSQL backups?

There is a way to exclude tables using heroku pg:pull (--exclude-table-data).

But how about backups if I need to get some data from the past?

heroku pg:backups:download does not seem to have an option to select tables to download. Is there a way to download only some part instead of having to load gigabytes of data if I only need small chunk?

herokuheroku-postgres


Solution 1:[1]

heroku pg:backups:download is nothing more complicated than a wrapper to download previous dumpfiles you've created so what you're asking isn't possible. If you need to inspect the state of a table from the past, you might have a couple options.

  1. Run heroku pg:backups:download to download the dumpfile and use pg_restore locally to rehydrate a single table. It sounds like this isn't ideal for you and you're already aware of it but I included it for the sake of completeness.

  2. If you're using a standard (or higher) database and the historical state that you'd like to inspect is within the rollback (docs here) window you could create a rollback fork of your database and psql directly to the fork and dump a single table that way.

Solution 2:[2]

Backup Heroku DB and download (see https://devcenter.heroku.com/articles/heroku-postgres-backups)

heroku pg:backups:capture
heroku pg:backups:download

Import single table from Heroku Backup into local DB via pg_restore (see https://www.postgresql.org/docs/current/app-pgrestore.html)

pg_restore --verbose --data-only --table TABLE_NAME --no-acl --no-owner 
    -h localhost -U DBUSER -d DBNAME latest.dump

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RangerRanger
Solution 2 ZzikkZzakk

Related Questions