Postgresql Integration



  • Dear NodeBB Team,

    May I humbly request to have Postgresql supported?

    I understand that I am asking a lot, I mean, when you commit a feature, you have to support it going forward, so this may become a liability and a big headache, especially given the number of forums you must have on your back.

    The upside is that Postgresql integration will allow simple and cost effective setup/hosting (for example in Amazon/Heroku, where Postgresql is provided as a service with automated backups/restore).

    I believe (my humble personal opinion), that with Postgresql support, NodeBB will become more popular, especially with the teams that have less IT experience (yes, I admit I am one of them 😉 ).

    It seems that @Ben-Lubar completed the Postgresql driver and has a pending pull request: https://github.com/NodeBB/NodeBB/pull/5861. It looks like the relevant DB tests passed and the driver is ready for action. Furthermore, from what I read the driver has similar performance to Mongo/Redis. I would love to see it committed to master so I could give it a test run.

    Lastly, I realize that you must be extremely busy, so I totally understand if Postgresql is put on the back burner. I have yet to see a similar team that is so helpful. Thus so far you immensely helped my efforts and I am most appreciative.

    Thanks again for your kind consideration,
    JJ.


  • Global Moderator



  • Thank you @PitaJ .

    I fully realize Postgres driver is at work, and I quoted the very same pull request you did in my original message.

    It is just that @Ben-Lubar 's pool request has been collecting dust for quite some time, while all test seems to perform as expected. Looks like the code is baked and ready for prime-time, at least from where I am sitting 😉

    Again, I fully realize you guys are busy!
    I sure hope you'll find some time to merge the pull request into master soon.

    All the best,
    JJ.


  • Global Moderator

    @jjsagan we're in direct communication with him, and it's almost done



  • Wow, this is superb! @PitaJ , I don't think I can thank you and @Ben-Lubar enough for this effort!


  • Admin

    Definitely appreciative of @Ben-Lubar 's efforts to bring pgSQL support to NodeBB. Looking forward to merging it in. The main blocker is that we are not too familiar with pgSQL here at NodeBB HQ, and so we'd need someone (in addition to Ben) to maintain it and debug any issues/inconsistencies that arise



  • Hi @julian , I have basic/limited knowledge of postgres and js, I'll be happy to help, but kindly note I am no-way near the level of @Ben-Lubar.

    If I may suggest, it seems that hmqgg https://github.com/NodeBB/NodeBB/pull/5861 (see Apr-10 comments) may have sufficient knowledge, and is keen on improving the code. Perhaps he may be open to help-out as well.



  • Hi @julian,
    First allow me to thank you and the team for all the improvements made recently with 1.10+. As always you guys are amazing!
    Is there any chance to see @Ben-Lubar 's postgresql pull request integrated soon?
    Thank you!
    JJ.


  • Gamers

    @jjsagan said in Postgresql Integration:

    Hi @julian,
    First allow me to thank you and the team for all the improvements made recently with 1.10+. As always you guys are amazing!
    Is there any chance to see @Ben-Lubar 's postgresql pull request integrated soon?
    Thank you!
    JJ.

    We are currently running the PR on https://what.thedailywtf.com/, and it's working without any new problems.



  • @Ben-Lubar,

    Wow amazing! how long have you been running? How's the performance in comparison to redis/mongo? When will this gem get integrated into master?

    My apology for the many questions, just a bit excited to see this code finally on prime time 🙂



  • Hi All,

    I decided to look at the recent commits, and..., well, could not believe my eyes, PostgreSQL just got integrated!

    PostgreSQL opens the door for (novice) admins to create forums based on NodeBB, on cloud platforms that don't support MongoDB or Redis, or where such support is too expensive.

    With PostgreSQL support, the admin does not need to create and maintain a database on an instance - instead for a similar cost he may opt for PostgreSQL as service. In Amazon AWS for example, the service comes with automated backup and restore. The integration is quite speedy with low to moderate effort (basically reading few AWS documents and following examples).

    Besides that, PostgreSQL offers additional features over key-value database, so future releases may benefit as well.

    This amazing achievement was made possible by @Ben-Lubar, with help from @julian and the rest of the NodeBB team.

    Congratulations on this fine release.
    I am grateful for your work and dedication!
    JJ.



  • Made some progress, but hit a small bump 🙂

    For those who are converting from Redis:

    1. Open pgAdmin and create a new user/pass (default username is nodebb). Create a new database, and grant login permission to the just created new user

    2. DL the converter and follow the github instructions using the new database name
      https://github.com/BenLubar/nodebb-postgres-converter

    3. npm install continuation-local-storage

    4. npm install pg

    5. Change the Config file:
      {
      "url": "XYZ",
      "secret": "XYZ",
      "database": "postgres",
      "port": 5432,
      "postgres": {
      "host": "just_the_host_address",
      "password": "supersecret_password",
      "port": "5432",
      "database": "my_cool_db"
      }
      }

    6. Run ./nodebb setup.

    That's how far I got, I am now getting the following error:
    Now configuring postgres database:
    warn: NodeBB Setup Aborted.
    error: type "legacy_object_type" already exists

    The legacy_object_type was created by the converter, so something must have gone wrong.
    @Ben-Lubar can you please help?

    Thanks,
    JJ


  • Gamers

    @JJSagan said in Postgresql Integration:

    1. Run ./nodebb setup.

    You shouldn't need to do this step.



  • Thanks @Ben-Lubar! I tried running (,/nodebb build ), but got the below. Any idea?

    • error: [build] Encountered error preparing for build message=type "legacy_object_type" already exists, stack=error: type "legacy_object_type" already exists
      at Connection.parseE (nodebb/node_modules/pg/lib/connection.js:553:11)
      at Connection.parseMessage (nodebb/node_modules/pg/lib/connection.js:378:19)
      at Socket.<anonymous> (nodebb/node_modules/pg/lib/connection.js:119:22)
      at emitOne (events.js:116:13)
      at Socket.emit (events.js:211:7)
      at addChunk (_stream_readable.js:263:12)
      at readableAddChunk (_stream_readable.js:250:11)
      at Socket.Readable.push (_stream_readable.js:208:10)
      at TCP.onread [as _originalOnread] (net.js:597:20)
      at TCP.onread (nodebb/node_modules/async-listener/glue.js:188:31), name=error, length=98, severity=ERROR, code=42710, detail=undefined, hint=undefined, position=undefined, internalPosition=undefined, internalQuery=undefined, where=undefined, schema=undefined, table=undefined, column=undefined, dataType=undefined, constraint=undefined, file=typecmds.c, line=1135, routine=DefineEnum


  • Perhaps the issue sourced from the parameters I provided the converter?
    Kindly note, I use Redis, on SCHEMA=1.

    nodebb-postgres-converter \
    --type redis \
    --input 'redis://REDIS_SERVER_ADDR:6379/1' \
    --sessionType redis \
    --sessionInput 'redis://REDIS_SERVER_ADDR:6379/1' \
    --outputType postgres \
    --output 'postgresql://username:password@PSQL_SERVER_ADDR:5432/MY_NEW_DATABASE' \
    --memory 4GB



  • Hi @Ben-Lubar ,

    I am still stuck at the same spot, no matter what I do.

    I guess the converter and the pg driver are a bit incompatible, at least on my database.
    However my DB is very small, and quite standard...

    Any suggestion how to resolve?

    Thank you very much in advance!
    JJ.


  • Gamers

    @JJSagan said in Postgresql Integration:

    my_cool_db

    @JJSagan said in Postgresql Integration:

    MY_NEW_DATABASE

    Are these the same database or a different one?



  • Hi @Ben-Lubar ,
    Yes they are the same, my apology for the naming confusion.
    Kindly note the "/1" indicating SCHEMA=1 - I believe it is required but I was not sure it should appear in both Input and sessionInput,
    Thanks for looking into this!
    JJ.



  • Hi @Ben-Lubar .
    No matter what I do, I can't bring up the converted forum generated by nodebb-postgres-converter.
    Honestly I am not sure what to do next... Any idea what I should check?
    Thanks!
    JJ.


 

Suggested Topics

| |