Heroku pg:pull failing to populate schema

Question

So I've read the documentation on pg:pull, but can't seem to get it to work. When I run the command with the correct parameters, my Heroku DB is fetched and a local database is created, but the schema is not filled in locally; e.g., no tables are created, no triggers are set up, no constraints, no data, etc.

Here's the heroku pg:info log for my database:

=== HEROKU_POSTGRESQL_ORANGE_URL (DATABASE_URL)
Plan:        Hobby-dev
Status:      Available
Connections: 1
PG Version:  9.3.3
Created:     2014-04-02 19:24 UTC
Data Size:   7.4 MB
Tables:      6
Rows:        1376/10000 (In compliance)
Fork/Follow: Unsupported
Rollback:    Unsupported

Here is the command that I'm using:

PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_ORANGE mylocaldb --app myappname

I couldn't figure out how to set the environment variables for PGUSER and PGPASSWORD (if anyone can point me in the right direction there, you'll get a +1. I read this question and answer to no avail).

mylocaldb is created, but the schema is not filled in.

Here is the pg_dump once the command was run:

pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading extensions
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension members
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "phrases"
pg_dump: finding default expressions of table "phrases"
pg_dump: finding the columns and types of table "users"
pg_dump: finding default expressions of table "users"
pg_dump: finding the columns and types of table "favorite_phrases"
pg_dump: finding the columns and types of table "favorite_users"
pg_dump: finding the columns and types of table "phrasebooks"
pg_dump: finding default expressions of table "phrasebooks"
pg_dump: finding the columns and types of table "phrasebooks_phrases"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "phrases"
pg_dump: reading indexes for table "users"
pg_dump: reading indexes for table "favorite_phrases"
pg_dump: reading indexes for table "favorite_users"
pg_dump: reading indexes for table "phrasebooks"
pg_dump: reading indexes for table "phrasebooks_phrases"
pg_dump: reading constraints
pg_dump: reading foreign key constraints for table "phrases"
pg_dump: reading foreign key constraints for table "users"
pg_dump: reading foreign key constraints for table "favorite_phrases"
pg_dump: reading foreign key constraints for table "favorite_users"
pg_dump: reading foreign key constraints for table "phrasebooks"
pg_dump: reading triggers
pg_dump: reading triggers for table "phrases"
pg_dump: reading triggers for table "users"
pg_dump: reading triggers for table "favorite_phrases"
pg_dump: reading triggers for table "favorite_users"
pg_dump: reading triggers for table "phrasebooks"
pg_dump: reading rewrite rules
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving database definition
pg_restore: [archiver] did not find magic string in file header
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte
ll used
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte
ll used
pg_dump: dumping contents of table favorite_phrases
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte
ll used
pg_dump: dumping contents of table favorite_users
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte
ll used
pg_dump: dumping contents of table phrasebooks
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte
ll used
pg_dump: dumping contents of table phrasebooks_phrases
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- fte
ll used
pg_dump: dumping contents of table phrases
pg_dump: [custom archiver] could not write to output file: Invalid argument

Is there some kind of further configuration that I need to do locally for this to work? If it matters, I am using a custom stop-word dictionary for tsquery which isn't set up locally (yet) on this machine... I doubt that would be a problem in the pg_dump though... Any help is much appreciated!

Edit: I just tried running this command on my desktop, the other local station that has everything configured. No bones. I'm getting the exact same results. Below is the output of which psql on said local machine, just in case:

$ which psql
/c/Program Files/PostgreSQL/9.3/bin/psql

Answer 1

I wrote the pg:pull and pg:push commands.

pg:pull and pg:push both use the pg_dump command and pipe that directly to pg_restore (source code). Unfortunately, there are problems with pg:push,pull on windows as of the time of this answer, September 2014. These problems are not insurmountable, so if you are coming to this answer sometime later, please check to see if it might have been fixed.

The env command used here and here I don't think is on windows, which is why you're having to do the PGUSER and PGPASSWORD stuff. However the reason for the env is that pg_dump needs different environment variables than pg_restore.

I took a stab at fixing that once, by using popen to set up the commands independently, then wire them together instead of using a pipe. However, I couldn't quite get it working, and had to stop.

I'd be very happy to review any patches that go all the way and fix this issue, just @ mention me on a pull request to the heroku/heroku project.

Sorry for my failing to fix this issue :(

Instead, and until this is fixed, you can use the pg_dump and pg_restore commands separately, directly. It's more cumbersome as a 2 step process, and having to look up the remote credentials, but it would get the job done. The linked source in the gen_pg_*_command methods show how to use pg_dump and pg_restore.

Answer 2

The answer of @will is perfect in case of regular Heroku development, but fail in case of Private Space. As mentioned at Heroku Postgres and Heroku Private Spaces :: External connections:

Unlike the Heroku Postgres databases in our hobby, standard, and premium tiers, private databases cannot be accessed via a local computer.

As a workaround, you may duplicate the DB to non private DB, and then you can dump the DB your local comp.