Postgresql Integration

Feature Requests
  • 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.

  • 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.

  • @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!

  • 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.

  • @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

  • @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.

  • @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


  • SQLite Integration

    Feature Requests
    0 Votes
    12 Posts
    517 Views

    @chung-leong said in SQLite Integration:

    To emulate certain operations on sorted sets, I ended up just pulling the fully data set and processing it in JavaScript.

    Yes, this is what I was afraid of... there are certain concessions that need to be made for an SQL driver when you build your software around the expectation of a schema-less data store 😆

    Nevertheless, I am looking forward to reviewing.

  • SQLite Integration

    Feature Requests
    0 Votes
    3 Posts
    728 Views

    In case anybody is ever interested in this again, I think I could prepare it in 2 weeks up to 1 month, for 6 LTC. Just let me know.

  • 2 Votes
    9 Posts
    4k Views

    @jarey

    MongoDB being schemaless for me is an easy way to work with the javascript objects in json. I think it would be harder to work with traditional relational databases like PostgreSQL in that approach.

    I'm not talking about whatever your specific use case is, I'm only talking about how NodeBB/database works.

    If you don't see MongoDB suitable, because its collection model is not used by Nodebb, the table model around PostgreSQL wouldn't be used either with the additional problem of being tied to an specific schema if you don't model all data with key/blob.

    What I'm saying is that the relational table structure (SQL) is closer to key/value (Redis) than the document-store structure (Mongo). Obviously that doesn't matter when talking about NodeBB/database since it doesn't using any of those specific features. It essentially just emulates Redis on top of Mongo, which a Postgre adapter would also do.

  • 0 Votes
    31 Posts
    17k Views

    Shouldn't this request be directed to Taptalk Inc? They seem to charge both the app user and the forum maintainer for "pro" features.

    As this is an open source project, they're free to provide a NodeBB taptalk plugin and earn more money 😉

  • 0 Votes
    7 Posts
    3k Views

    @Giorgio-Chiodi said:

    We noticed that using the browser's notifications has its limit, since they only work when the app is open on the mobile device. Which kind of beats having the notifications altogether.

    This is where pushbullet itself would come in. Currently talking to someone at pushbullet over this, but it's lower priority at the moment, as it is not on our immediate roadmap.