Heroku setup error "NodeBB could not connect to your PostgreSQL database"


  • @pitaj Yes, i checked the config.json and the option was still there. I also tried different other params found at stackoverflow and elsewhere but none worked.

    I am also not sure if the ssl-param has the right notation and place. In the heroku docs they mention the connection string should contain an additional ssl-param and give an example of the Client class of the pg module (see here). In the NodeBB version (12) i want to use the Pool class of the module is adressed for the postgres connection (as far as i understand this).

    To get ./nodebb setup running i added the ssl-param to the connection options in postgres.js (connOptions.ssl = {rejectUnauthorized: false};) but this is not working after pushing it to heroku. So i am asking myself what part of the code where connections are set up i have missed.


  • @gotwf Thanks for your suggestions. The strange thing is that i can access the database when running ./nodebb setup and successfully populate it from my local device but not connect from the heroku app.

    Sounds like there are two different ways connections are established when ./nodebb setup and ./nodebb start are called but i only found one file till now where this is handled (postgres.js in nodebb version 12).

    I will have a deeper look into the postgres documentation - thanks for the link.

  • Global Moderator Plugin & Theme Dev

    @nodebber just to confirm, it works when you do

    local nodebb -> heroku pg
    

    But not when you do

    heroku nodebb -> heroku pg

  • @pitaj Yes i can run ./nodebb setup on my local device but when i push to heroku and run ./nodebb start there the app is crashing.

  • Global Moderator Plugin & Theme Dev

    @nodebber what error do you see on startup?


  • @pitaj After running ./nodebb start the app crashes and i find in logs/output.log the message

    node:40) UnhandledPromiseRejectionWarning: error: no pg_hba.conf entry for host "34.XXX.XXX.XXX", user "XXX", database "XXX", SSL off
        at Connection.parseE (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:555:11)
        at Connection.parseMessage (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:380:19)
        at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:120:22)
        at Socket.emit (events.js:198:13)
        at addChunk (_stream_readable.js:288:12)
        at readableAddChunk (_stream_readable.js:269:11)
        at Socket.Readable.push (_stream_readable.js:224:10)
        at TCP.onStreamRead [as _originalOnread] (internal/stream_base_commons.js:94:17)
        at TCP.onread (/app/node_modules/async-listener/glue.js:188:31)
    (node:40) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
    (node:40) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
    2021-03-09T22:48:23.264Z [48283/40] - error: uncaughtException: Connection terminated unexpectedly
    Error: Connection terminated unexpectedly
        at Connection.con.once (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js:200:9)
        at Object.onceWrapper (events.js:286:20)
        at Connection.emit (events.js:198:13)
        at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:130:10)
        at Socket.emit (events.js:203:15)
        at endReadableNT (_stream_readable.js:1145:12)
        at /app/node_modules/async-listener/glue.js:188:31
        at process._tickCallback (internal/process/next_tick.js:63:19) {"error":{},"stack":"Error: Connection terminated unexpectedly\n    at Connection.con.once (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js:200:9)\n    at Object.onceWrapper (events.js:286:20)\n    at Connection.emit (events.js:198:13)\n    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:130:10)\n    at Socket.emit (events.js:203:15)\n    at endReadableNT (_stream_readable.js:1145:12)\n    at /app/node_modules/async-listener/glue.js:188:31\n    at process._tickCallback (internal/process/next_tick.js:63:19)","exception":true,"date":"Tue Mar 09 2021 22:48:23 GMT+0000 (Coordinated Universal Time)","process":{"pid":40,"uid":52322,"gid":52322,"cwd":"/app","execPath":"/app/.heroku/node/bin/node","version":"v10.16.3","argv":["/app/.heroku/node/bin/node","/app/app.js"],"memoryUsage":{"rss":153505792,"heapTotal":114143232,"heapUsed":80573096,"external":435084}},"os":{"loadavg":[1.85888671875,1.759765625,2.30126953125],"uptime":1139164},"trace":[{"column":9,"file":"/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js","function":"Connection.con.once","line":200,"method":"once","native":false},{"column":20,"file":"events.js","function":"Object.onceWrapper","line":286,"method":"onceWrapper","native":false},{"column":13,"file":"events.js","function":"Connection.emit","line":198,"method":"emit","native":false},{"column":10,"file":"/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js","function":null,"line":130,"method":null,"native":false},{"column":15,"file":"events.js","function":"Socket.emit","line":203,"method":"emit","native":false},{"column":12,"file":"_stream_readable.js","function":"endReadableNT","line":1145,"method":null,"native":false},{"column":31,"file":"/app/node_modules/async-listener/glue.js","function":null,"line":188,"method":null,"native":false},{"column":19,"file":"internal/process/next_tick.js","function":"process._tickCallback","line":63,"method":"_tickCallback","native":false}]}.....
    

    `

  • Community Rep

    @nodebbr On a related note: I am curious as to the motivation behind your decision to use Postgres rather than MongoDB. Kind of in same boat myself but not able to do testing at present.


  • @gotwf said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

    I am curious as to the motivation behind your decision to use Postgres rather than MongoDB. Kind of in same boat myself but not able to do testing at present.

    Just because i had less experience with nosql databases.


  • @nodebber I contacted herokus support regarding my the connection problem and they found a solution. I had to set a new environment variable named "PGSSLMODE" to the value "require" and afterwards to restart my app / dyno.

    As it took me some days to find this out i would suggest an update of the heroku install documentation to prevent future suffering.

    Is there a official way to make documentation prosals - maybe as github issue?

  • Global Moderator Plugin & Theme Dev

    @nodebber yeah an issue on NodeBB/docs

  • GNU/Linux Admin

    @nodebber said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

    Is there a official way to make documentation prosals - maybe as github issue?

    Indeed! If you want to make a pull request to the repo @PitaJ linked to, then you will be contributing back and ensuring other users will not experience similar troubles 😁


  • The problem resulted from a change on side of heroku that now (early 2021) require ssl for all heroku-postgres addons.

    To solve the problem for nodeBB 1.12 i had to do the following:

    • set a environment variable at heroku - an advice i got from the heroku support team and found (in herokus documentation it is noted as "PGSSLMODE" = "no-verify" which did not worked for me)
    "PGSSLMODE" = "require"
    
    • add a ssl param to the postgres connection in src/database/postgres.js before creating a new Pool (two appearances in nodeBB 1.12)
    +	connOptions.ssl =  {"rejectUnauthorized": false}
    	const db = new Pool(connOptions);
    
    
    • add ssl param as json object to the postgres json object in the config.json
    "postgres" {
        ....
        "database": "DBNAME",
    +   "ssl" { "rejectUnauthorized": false }
    }
    ...
    
    • set my local node version to 10.16.3 for populating the database running ./nodebb setup locally like described in nodebb's documentation
    • set the node version to 10.16 in package.json before pushing to heroku

    To solve the problem for nodeBB 1.16 i had to do the following:

    • set a environment variable at heroku
    "PGSSLMODE" = "require"
    
    • add a ssl param to the postgres connection in src/database/postgres.js and src/database/postgres/connection.js before creating a new Pool
    +	connOptions.ssl =  {"rejectUnauthorized": false}
     	const db = new Pool(connOptions);
    
    • add ssl param as json object to the postgres json object in the config.json
    "postgres" {
        ....
        "database": "DBNAME",
    +   "ssl" { "rejectUnauthorized": false }
    }
    
    • set my local node version to 14.16.0 for populating the database running ./nodebb setup locally like described in nodebb's documentation
    • set the node version to 14.16 in package.json before pushing to heroku

  • @julian Thanks for the invitation to contribute. Is there something i should consider regarding the issue / pull request - markup, document structure,...?

Suggested Topics

  • 2
  • 1
  • 3
  • 16
  • 2
| |