Access Discourse DB from host


(Maestro Magnifico) #1

Hey, I started to build main page for my site. It’s on same server as Discourse of course. I need to access container’s PostgreSQL server from host to pull out Discourse posts from category “News” on my main page. How do I connect to it?


How to get current user's session id?
(Khoa Nguyen) #2

expose PostgreSQL port from docker image and you can access it.


(Maestro Magnifico) #3

Sounds easy. Is it safe though? Won’t this expose port for external connections too?


(Khoa Nguyen) #4

Yes. It will too. But you can prevent it by using iptables or firewall (I don’t really know, still learning about linux)


(Sam Saffron) #5

Or expose the port on 127.0.0.1, instead of 0.0.0.0.


(Maestro Magnifico) #6

You mean, I can do like this in app.yml?

expose:
  - "127.0.0.1:4002:5432" # fwd host port 4002   to container port 5432 (postgres)
  - "127.0.0.1:4001:80" # fwd host port 80   to container port 80 (http)
  - "127.0.0.1:2222:22" # fwd host port 2222 to container port 22 (ssh)

Discourse API Documentation
(Sam Saffron) #7

not 100% sure but we copy it to docker run so in theory that may work, try it … you can do a docker ps to see if it worked as expected

then report back.


(Maestro Magnifico) #8

I didn’t tried it with Postgres yet, but I just hooked http like this: 127.0.0.1:4001:80 and it works. Now I can’t connect to Discourse via http://mysite:4001. It should work with everything else, thanks!


(Maestro Magnifico) #9

Hey. Two quick questions:

  1. If I’ll create my own tables inside of Discourse DB, will they survive rebuilding of container?
  2. Will Discourse backup my tables inside of Discourse DB?

I think it’s a waste of resourses to host another PostgreSQL server or something else if there’s perfectly fine PostgreSQL server already runing in Discourse container. So I want to store some of my stuff in same DB, with some fancy prefix to avoid conflict.


(Ilya Kuchaev) #10

You could just separate the Discourse and PostgreSQL containers.


(Maestro Magnifico) #11

I tried to setup separated containers right from begining, but I failed hard. This setup turned out too complicated for me. Maybe because I tried to setup multisite configuration at the same time. Anyway, I don’t think this answers any of my questions.


(Jens Maier) #12

The answer to the first question is “yes”, to the second “yes, unless you’re being sneaky”. The PostgreSQL database lives somewhere in /var/discourse/shared/; the full path is defined in the volumes section of your YAML config file. (If you’ve copied the standalone sample, the full path of the database will be /var/discourse/shared/standalone/postgres_data.)

This tree exists separately from the Docker containers; it sticks around even if you destroy the container and if you re-bootstrap the container the database will be unchanged (altho it may be migrated to a newer schema version if Discourse was upgraded in the meantime).
Thus, you can create databases, roles, tables etc. as you see fit and these, too, will stick around. However, if you modify the public schema in the discourse database, your stuff can obviously clash with later official changes and you’ll have a hard upgrading.

Finally, the backup process dumps the entire public schema. If you’ve added a custom table, it will get dumped and restored along with Discourse’s own data just fine.

So, bottomline:

  1. Yes, absolutely, you can even create a new schema to separate your data from Discourse and avoid naming conflicts, create new roles, databases, and so on.
  2. Yes, but only as long as you do not store your custom tables in a separate database or schema, thus risking naming conflicts when Discourse’s schema changes.

Pick your poison. :slight_smile:


(Pavel Chuchuva) #13

Another approach is to use domain socket in /var/discourse/shared/standalone/postgres_run/ folder:

sudo -u root psql -h /var/discourse/shared/standalone/postgres_run/

You would need to create ‘root’ user in Postgresql in container first:

createuser root