4

I want to use pg_dump on a Heroku multi-schema pgSQL app using the apartment gem, but am afraid I have too many schemas to make it work.

I looked into it and found the following from the creator of the apartment gem at the following link

I'm the author of the Apartment gem that's referenced here. We've been using it for over a year on Heroku and it utilizes schemas.

It's definitely fine to use, we still get great performance with well over 100 schemas in an application with 50+ tables per schema.

The article mentioned by @4ware talks about issues with heroku's pg:backups command. (I'm pretty sure that article came about from our support queries to them)

It DEFINITELY has issues, but this is not a shortcoming of Postgresql, schemas or multi-tenancy with schemas, but rather the heroku tool itself. Now that Postgresql has ingres support on their dbs, you don't really need to use their built in tools. We just pg_dump when we need to and it works just as fast as one would expect.

How can I use external tools? Can someone elaborate on this?

David Mckee
  • 1,100
  • 3
  • 19
  • 35

1 Answers1

5

I don't know what that person means by "ingres"- certainly not the product, Ingres. Perhaps "ingress", i.e. "inbound"?

In any case: You can make a direct SSL connection to a PostgreSQL database on Heroku using any tool that supports the libpq protocol. That includes psql ... and pg_dump.

Simply:

pg_dump -Fc -f mydb.dbbackup "sslmode=require host=my.heroku.host.name port=5432 dbname=my.heroku.db.name user=my.heroku.user"

(with any other options you want).

See the Heroku documentation for more details.

Note that Heroku database credentials may change, per the docs link above. So if you're automating this or doing it regularly you should use your Heroku account to dynamically fetch the database credentials per the Heroku manual:

pg_dump -Fc -f mydb.dbbackup $(heroku pg:credentials DATABASE)
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    I get `Error: You must install at least one postgresql-client- package.`. Using `pg (0.18.1)`, but is there something else that needs to be installed? – B Seven Mar 10 '15 at 17:15
  • @BSeven Er, yes. That's what the error message says. Install `postgresql-client-9.3` or whatever your PostgreSQL version is. Given the error it sounds like you're on Debian or Ubuntu so that'll be using `aptitude install`. – Craig Ringer Mar 11 '15 at 02:57
  • @CraigRinger, `pg_dump -Fc -f ../backups/my_app_db.dbbackup $(heroku pg:credentials DATABASE --app my_app_name)` is this line really works? in my case it says 'too many arguments for pg_dump' – gaussblurinc May 08 '15 at 12:38
  • @gaussblurinc Heroku must've changed the format of the output of the `pg:credentials` command. Try wrapping the `$(...)` in double quotes, i.e. `"$(heroku ...)"` – Craig Ringer May 11 '15 at 02:47
  • 1
    It's no longer a simple one-liner Heroku updated their output and the command itself (add a `:url` subcommand). You now need to use `heroku pg:credentials:url DATABASE` and manually copy/paste from the string it returns. Or of course some crazy ruby/perl command to extract the output for you. But not too crazy because heroku will just change it again. – macc Mar 09 '20 at 17:29