Is it possible to connect to the database directly from a separate app?

Installation
1

The app would be outside of the Discourse Docker container but on the same server. If so, would anyone be able to share some details on how to do this or point me to a guide or directions please?

Would there also be any drawbacks in doing this over using the DE plugin/API?

2

Drawbacks mostly involve the fact that such a connection could be used for write access. Is that a requirement?

If your integration needs write access to the database, consider writing a plugin that exposes just the operations you need instead.

2 Likes
3

It’s not a requirement but I am ok with that as a drawback :slight_smile:

I started using your DE plugin but unfortunately I think my use case is going to require a direct connection as I am sending too many requests through the API for some of my pages (and that’s just with me on the site). They are mostly custom queries so I’m not sure if that is having an impact. I still love the DE plugin tho!
Would you happen to know what the best way is to connect to the Postgres DB directly outside of the container? Both the forum and the site are on the same server if that’s any help.

Edit: I think I am hitting a rate limit with the DE plugin, but I’m sure I saw Sam say that if the requests are coming from the same server no rate limit applies - is that still the case?

4

While possible, the other app can acquire locks on tables and block Discourse from working as usual.

Either go for a new plugin that adds the necessary API endpoints you need or go fully into it by creating another PostgreSQL instance that replicates you Discourse and where you can plug your app.

3 Likes
5

Thanks for the info Rafael, I will be using select statements exclusively and as far as I know these don’t lock, so I’d only need to worry about table changes on the Discourse side (i.e when upgrading) and where I could switch the other app off temporarily. Would that alleviate lock concerns?

With regards to replicating the database, that seems like an interesting option - can it be done on the fly, so it’s no more than a couple of minutes out of date? (I need to fetch latest topics frequently - almost every page on the site has them, although I do cache for two minutes but they vary per page/criteria and there will be hundreds of these types of pages). Also this may stop being an option when the DB grows? (On another Discourse Forum my dB is already a couple gig.)

(I don’t think creating a plugin would be an option here as I can’t see how it would be any better than the Data Explorer plug-in, in fact the DE plugin is almost perfect - apart from this issue.)

6

Anyone have any ideas why this isn’t working please?

I followed some of the posts by @pfaffman and @Nacho_Caballero in this topic: How to make the database (or part of it) accessible to a cloud data processor? and @mpalmer’s post in this one: Accessing to the database from outside the container.

First I edited app.yml with:

expose:
  - "127.0.0.2:5432:5432"

Rebuilt container. In the container I set a password for the postgres user, and can then connect with the following from inside the container:

psql -h localhost -d discourse -U postgres

However when I exit the container and try connecting I get:

# psql -h 127.0.0.2 -p 5432 -d discourse -U postgres
psql: server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

I have also tried changing the port to something else but I get the same thing. I got the 127 IP from docker ps and inspect under Network Settings (I have three standalone Discourse instances running).

If I change the IP (to that of one of the other Discourse forums) I get a (more immediate and) different response/message, so the above does appear to be partly correct:

# psql -h 127.0.0.3 -p 5432 -d discourse -U postgres
psql: could not connect to server: Connection refused
	Is the server running on host "127.0.0.3" and accepting
	TCP/IP connections on port 5432?

Any ideas on what I’m doing wrong? Googling psql: server closed the connection unexpectedly seems to suggest a networking issue - so do I need to alter anything else inside the container?

7

The way I got this to work (from outside the container) is to use an SSH tunnel. I actually created a new user instead of using postgres and have custom ports for SSH and postgres, but this should work for your setup:

ssh -L 5432:localhost:5432 EXTERNAL_VPS_IP "psql -U postgres -d discourse -h localhost"

The EXTERNAL_VPS_IP is the IP address you use to connect to your remote server.

If it still doesn’t work, you might want to try changing the exposed IP in app.yml to use the docker bridge IP instead of the internal container IP you got from docker ps. I’m not sure if this is required, but it’s how I have it set up:

expose:
  - "127.0.0.1:5432:5432"
# or if you use a custom port:  - "127.0.0.1:CUSTOM_PORT:5432"

Remember to rebuild the container afterwards.

Let me know how this goes. It took me A LONG TIME to get everything working (and it was so simple in retrospect), so I’m happy to help out.

1 Like
8

Thanks for the info @Nacho_Caballero… especially the link about docker!

Seems like the advice in your (and the other) thread was correct, you need:

expose:
  - "127.17.0.1:5432:5432"

…as mentioned in that link, Docker will automatically forward on to the correct IP for your container (which makes sense given those IPs are dynamic - something I had wondered). I would think this is all that’s needed for most setups.

I’d already tried that though - so you may be wondering what my problem was. My firewall! (I thought I recognised the psql: server closed the connection unexpectedly error!)

All sorted now - thank you everyone :blush:

2 Likes
9

Good to know! I’m glad you figured it out. Is your firewall iptables? How did you open the port?

2 Likes
10

I use firewalld but for iptables something like this should work:

iptables -A INPUT -p tcp --dport xxxx -j ACCEPT
iptables -A OUTPUT -p tcp --dport xxxx -j ACCEPT
1 Like