Postgresql Integration

julian

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

JJSagan

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.

JJSagan

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.

Ben Lubar

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

JJSagan

@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

JJSagan

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.

JJSagan

Made some progress, but hit a small bump

For those who are converting from Redis:

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
DL the converter and follow the github instructions using the new database name
https://github.com/BenLubar/nodebb-postgres-converter
npm install continuation-local-storage
npm install pg
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"
}
}
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

Ben Lubar

@JJSagan said in Postgresql Integration:

Run ./nodebb setup.

You shouldn't need to do this step.

JJSagan

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

JJSagan

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

JJSagan

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.

Ben Lubar

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

JJSagan

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.

JJSagan

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.