Heroku pg: pull failed to populate schema
So I 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 populated locally; e.g. no tables are created, no triggers are configured, no constraints, no data, etc.
Here's the log heroku pg:info
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 I'm using:
PGUSER=postgres PGPASSWORD=password heroku pg:pull HEROKU_POSTGRESQL_ORANGE mylocaldb --app myappname
I couldn't figure out how to set environment variables for PGUSER
and PGPASSWORD
(if someone can point me in the right direction, you get +1. I read this question and answer to no avail).
mylocaldb
is created, but the diagram is not populated.
Below is the command pg_dump
after running the command:
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 additional configuration I need to do locally for this to work? If it matters, I am using a custom stopword dictionary for tsquery
which is not configured locally (yet) on this computer ... I doubt this is the problem pg_dump
though ... Any help is greatly appreciated!
Edit . I just tried running this command on my desktop, on another local station where everything is set up. No bones. I am getting exactly the same results. Below is the output which psql
on the specified local machine, just in case:
$ which psql
/c/Program Files/PostgreSQL/9.3/bin/psql
source to share
I wrote commands pg:pull
and pg:push
.
pg:pull
and pg:push
use a command pg_dump
and channel that is directly linked to pg_restore
( source code ). Sorry, there are problems with pg:push,pull
on windows at the time of reply, September 2014. These issues are not insurmountable, so if you come to this answer after a while, please check to see if this has been fixed.
env
the command is here and here I don't think it is on windows, which is why you should be doing things PGUSER and PGPASSWORD. However, the reason env
is that pg_dump
different environment variables are needed than pg_restore
.
I took one time to fix this , using popen to configure the commands myself, then piping them together instead of using a pipe. However, I couldn't get it to work and had to stop.
I would be very happy to consider any fixes that go all the way and fix this issue, just @mention me on request for the heroku / heroku project.
Sorry that I was not able to fix this problem :(
Instead, and until this is fixed, you can use the commands directly pg_dump
and pg_restore
separately. It's more cumbersome as a two step process and has to look for deleted credentials, but that would do the job. The linked source in methods gen_pg_*_command
shows how to use pg_dump
and pg_restore
.
source to share
@Will's answer is perfect in case of regular Heroku development, but failure in case of Private Space. As mentioned in Heroku Postgres and Heroku Private Spaces :: External Connections :
Unlike Heroku Postgres databases in our hobby, standard and premium tier, private databases can not be accessed from the local machine .
As a workaround, you can duplicate the DB to a non-private DB and then you can dump the database to your local machine.
source to share