Cannot access local postgreSQL heroku, node.js

Question

Set up a node.js app with postgreSQL db, all works in production environment.

I have used the CLI heroku pg:pull ... which was successful. Am running postgres 9.4.X locally, but on starting my application locally (foreman start) I am getting an error: "Error error: relation "test_table" does not exist". I have followed instructions here: https://devcenter.heroku.com/articles/heroku-postgresql#local-setup but still I cannot seem to access my DB when running locally.

bash_profile:

export PATH="/usr/local/heroku/bin:$PATH"
export PATH=$PATH:/Applications/Postgres.app/Contents/Versions/9.4/bin
export PATH="/Applications/Postgres93.app/Contents/MacOS/bin:$PATH:$PATH"

Console error log:

15:39:48 web.1 | started with pid 18779
15:39:48 web.1 | Node app is running on port 5000
15:39:52 web.1 | { [error: relation "test_table" does not exist]
15:39:52 web.1 | name: 'error',
15:39:52 web.1 | length: 101,
15:39:52 web.1 | severity: 'ERROR',
15:39:52 web.1 | code: '42P01',
15:39:52 web.1 | detail: undefined,
15:39:52 web.1 | hint: undefined,
15:39:52 web.1 | position: '15',
15:39:52 web.1 | internalPosition: undefined,
15:39:52 web.1 | internalQuery: undefined,
15:39:52 web.1 | where: undefined,
15:39:52 web.1 | schema: undefined,
15:39:52 web.1 | table: undefined,
15:39:52 web.1 | column: undefined,
15:39:52 web.1 | dataType: undefined,
15:39:52 web.1 | constraint: undefined,
15:39:52 web.1 | file: 'parse_relation.c',
15:39:52 web.1 | line: '986',
15:39:52 web.1 | routine: 'parserOpenTable' }

Create test_table? How is the database being seeded/imported/setup with a schema? — jmunsch
– jmunsch, Commented Jul 31, 2015 at 21:49
You are probably connecting to the wrong database. Can you show your the connection string your app is configured to use, and also the complete pg:pull command you used? Also, if you connect to the DB using the postgresql 'psql' command, can you see the table then? — harmic
– harmic, Commented Jul 31, 2015 at 23:27
pg.connect(process.env.DATABASE_URL, function(err, client, done){..... I cant remember the exact pg:pull I used, but what should I use to ensure that this works? Locally psql \conninfo is You are connected to database "Oliver" as user "Oliver" via socket in "/tmp" at port "5432". — OliverJ90
– OliverJ90, Commented Jul 31, 2015 at 23:30

harmic · Accepted Answer · 2015-08-01 00:12:19Z

2

According to the heroku docs the second argument you give to pg:pull is the name of the database that will be created locally to contain a copy of your application's data. If you used the example given on the heroku web site:

 heroku pg:pull HEROKU_POSTGRESQL_MAGENTA mylocaldb --app sushi

then the data would be dumped into a local database with the name 'mylocaldb'.

According to the comments, your application is using the contents of the environment variable DATABASE_URL as the connection string.

Since you say pg:pull worked, but your application cannot find the tables that should exist in the database, I am guessing that your application is connecting to the wrong database. If you were blindly following the heroku documentation you might have done this:

export DATABASE_URL=postgres:///$(whoami)

That connection string would connect you to a database named the same as your user name. Probably this is not the name you used in the pg:pull command.

To identify what the correct database name is, either look back through your command history to find the name used in pg:pull, or use the \l command in psql to list all the available databases. Then update the environment variable to

export DATABASE_URL=postgres:///dbname

where dbname would be the actual name.

There are a lot of other possible parameters that can be used in a postgresql connection string (and in fact multiple different formats you can use). For more information on these, see the postgresql manual.

answered Aug 1, 2015 at 0:12

harmic

31k6 gold badges75 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OliverJ90 Over a year ago

Well, when you put it like that, simples. I just wasn't exporting the correct Db name. Thanks.

Collectives™ on Stack Overflow

Cannot access local postgreSQL heroku, node.js

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related