Configure Discourse to use a separate PostgreSQL server

Let’s say you’re using AWS RDS, or an existing PostgreSQL server managed elsewhere. It’s fairly straightforward to get Discourse to use such a setup, without needing to grant superuser privileges to anything that Discourse itself runs. Here’s how.

Prerequisites

Discourse requires PostgreSQL 13 or later; if you try to run on an older version, it might work, but we don’t guarantee it, and if something breaks in the future, you get to keep both pieces. We also require some extensions that are distributed in the postgresql-contrib package (or its equivalent).

Randomly generate a password for the connection (20+ characters). In this guide, we’ll use s3kr1t as a placeholder.

Setup the DB

As a database superuser, run the following:

CREATE USER discourse PASSWORD 's3kr1t';
CREATE DATABASE discourse OWNER discourse;
\c discourse
CREATE EXTENSION hstore;
CREATE EXTENSION pg_trgm;

That’s all that has to be done to prepare the DB for use by Discourse.

Configure Discourse

Get Discourse setup in the usual way, and then edit containers/app.yml as follows:

  1. Remove the line - "templates/postgres.template.yml" under the templates top-level key (towards the top of the file);

  2. Under the env top-level key, set the following:

     DISCOURSE_DB_USERNAME: discourse
     DISCOURSE_DB_PASSWORD: s3kr1t
     DISCOURSE_DB_HOST: <IP or hostname>
     DISCOURSE_DB_NAME: discourse
    
  3. You also need to set the following if your database doesn’t use the default port (5432):

    DISCOURSE_DB_PORT: <port>
    DISCOURSE_DB_BACKUP_PORT: <port>
    
  4. Rebuild with ./launcher rebuild app, and you should be good to go.

30 Likes
Install the docker version with a remote postgresql
Using a PGSQL database outside of Docker?
Installation on AWS
Move from standalone container to separate web and data containers
Error when attempting install
Installation Help required
Switching to another Redis instance?
Can I install Discourse with my own Redis and Postgres?
How to provide HA
Running Discourse with pg_bouncer and a separate DB
Improving Instance Performance (Megatopics, Database Size and Extreme Load)
Database Replication
pgSQL root access - without? (docker)
pgSQL root access - without? (docker)
Simplified Discourse install?
Multiple Daily Backups
Rebuilding gives db:migrate error due to broken plugin
Can I use a later version of Postgres than Discourse?
Trust level freeze
Running discourse with Postgres running in docker
Multiple backup schedules
Any other locations where database connection IP / hostname set other than DISCOURSE_DB_HOST in app.yml?
I want to keep PostgreSQL database on separate server [standard docker install]
Move from standalone container to separate web and data containers
Is there a way to install Discourse without the provided scripts?
Why is "rebuild" so tightly coupled to container run status?
Move from standalone container to separate web and data containers
Can I install Discourse with my own Redis and Postgres?
How do you scale discourse horizontally?
Error while connecting database from aws postgresql 10.1
Relation "selected" does not exist with external Postgres
Error executing 'postInstallation': Purging temp files
Improve my DO droplet setup
Discourse Database Question/Support
Move from standalone container to separate web and data containers
Separate psql server / migrate

There is an additional setting, DISCOURSE_DB_PORT, which can, naturally, configure the TCP port number. @mpalmer please update the post?

Thanks

This isn’t supposed to be a comprehensive description of every possible way you can configure your database connection, rather it’s a quick howto to make things a bit easier.

2 Likes

Is there somewhere more authoritative for this documentation? I even tried (unsuccessfully) to track down the source code for this…!

Thanks

1 Like

The discourse_docker repo is where all that lives.

2 Likes

Thanks for the pointer, I’m embarrassed to admit I’ve gone through almost an entire setup without realising the wealth of documentation on that page.

That said, the only reference to DISCOURSE_DB_PORT is in web.template.yml: https://github.com/discourse/discourse_docker/search?utf8=✓&q=DISCOURSE_DB_PORT&type=

3 Likes

Yes, it could probably stand to be promoted into the default “web_only” template, with a suitably explanatory comment. PRs welcome!

3 Likes

Hi, first thanks for this guide. I try to setup external postgresql on the same machine, where docker installed. Do I need to bind port of my external postgresql 5432 to docker? Could anybody assist how to do it, using Discourse configuration file?

Your question doesn’t make any sense. You don’t bind external listening ports to Docker.

1 Like

I follow the instruction a the top. First I set up an ordinary docker, it works. Then I want to switch the application to external postgres. When I rebuild the app, I get an error

Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

My server has not a FQDN yet, I use 127.0.0.1 as a DB host. The database is listening on 127.0.0.1:5432, I am able to connect with psql. Where am I wrong? I tried to play with pg_hba.conf but no success, of course.

Hi guys, possible of my personal server configuration or what else… But I found a solution by running a docker in hosted mode (not bridged as default):

/var/discourse/launcher rebuild app --docker-args --net=host --skip-mac-address

After that the discourse engine could connect to external postgres database, run at 127.0.0.1:5432. Default bridged mode does not allow docker to access ports listening at the host without tricks.

Hope this might help somebody.

1 Like

When I run ./launcher rebuild app, I get this error PG::ConnectionBad: timeout expired during the bundle exec rake db:migrate. Has anyone comes across this issue? I can successfully access the database with a postgres client.

1 Like

I have not tried this yet.
Here it is suggested to remove web.template.yml but i see bunch of other stuff happening in https://github.com/discourse/discourse_docker/blob/master/templates/web.template.yml

What about that ?

No it doesn’t. 

5 Likes

Can you take the same approach for Redis?

remove “templates/redis.template.yml” & add a line in the app.yml file for DISCOURSE_REDIS_HOST

Thanks in advance

Todd

Is there an obvious benefit that I’m maybe missing?

(I assume running redis on a separate machine will just add unwanted latency if nothing else)

Just that its managed & 1 less thing to worry about

You don’t have to worry about redis that ships with discourse. I haven’t seen it failing on me in the last 1 year of self hosting discourse.

2 Likes

Thanks @itsbhanusharma,

Do you know what the process would be even if I did?

This article, albeit a little old says to add

DISCOURSE_REDIS_HOST

which is the same format as the article you so kindly mentioned, so that all makes sense.

Just not sure where to put it (maybe underneath other changes made to the app.yml file or maybe in the "templates/redis.template.yml")

and also

what if any lines or files need to be deleted

Thanks

Todd

That variable should go into the file where you declared postgres variables. Just be warned that exposing redis for a production environment is lethal. (Because of poor security practices of redis itself)