Running Discourse with a separate PostgreSQL server

[Ivan_Rapekas]

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?

[mpalmer (Matt Palmer)]

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

[Ivan_Rapekas]

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.

[Ivan_Rapekas]

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.

[davedogs]

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.

[jpsayshello (Jaimin)]

I have not tried this yet.
Here it is suggested to remove web.template.yml but i see bunch of other stuff happening in discourse_docker/web.template.yml at master · discourse/discourse_docker · GitHub

What about that ?

[schleifer (Andrew Schleifer)]

Here it is suggested to remove web.template.yml

No it doesn’t. 

Remove the line - "templates/postgres.template.yml"

[Tango]

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

[itsbhanusharma (Bhanu Sharma)]

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)

[Tango]

Just that its managed & 1 less thing to worry about

[itsbhanusharma (Bhanu Sharma)]

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.

[Tango]

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

[itsbhanusharma (Bhanu Sharma)]

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)

[Tango]

Thanks

Anu reference or file need to be removed?

[hydrandt]

I needed to run development discourse (set up following Beginners Guide to Install Discourse for Development using Docker ) using database from another container. To do so, I had to modify the installation steps as follows:

1. git clone https://github.com/discourse/discourse.git
2. cd discourse
3. vim config/database.yml , on the top of the file, make it into:

development:
  prepared_statements: false
  adapter: postgresql
  #database: <%= ENV['DISCOURSE_DEV_DB'] || 'discourse_development' %>
  database: discourse
  username: discourse
  password: yourdbpassword
  host: postgres
  min_messages: warning
  pool: 5
  timeout: 5000
  checkout_timeout: <%= ENV['CHECKOUT_TIMEOUT'] || 5 %>
  host_names:
    ### Don't include the port number here. Change the "port" site setting instead, at /admin/site_settings.
    ### If you change this setting you will need to
    ###   - restart sidekiq if you change this setting
    ###   - rebake all to posts using: `RAILS_ENV=production bundle exec rake posts:rebake`
    - "localhost"

4. vim bin/docker/boot_dev, find the line starting with docker run, and add a network definition matching the docker network to which your postgres container is attached to: docker run --network my-docker_network-name -d -p 4305:...
5. ./bin/docker/boot_dev
6. ./bin/docker/unicorn
7. you may need to run migrations: docker exec -it discourse_dev /bin/bash -c "cd /src; ./bin/rails db:migrate RAILS_ENV=development"
8. visit http://localhost:9292/ and log in with credentials you set up earlier on that database

Is there a simpler way to do it, just with environment variables?

[aliyosefzai (Ali)]

This failed for me. Any suggestions on how to fix it ?
I am able to access DB discourse remotely from command line so the connection to DB looks good

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 274 exit 1>
Location of failure: /pups/lib/pups/exec_command.rb:112:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]}
578a8ec702d0025b01a0b8396985b8bfc25c7029769c2960b58693c64609a62a
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one

[Ivan_Rapekas]

I got the same error with the following lines in the log above:

rake aborted!
PG::ConnectionBad: could not connect to server: Connection refused
        Is the server running on host "127.0.0.1" and accepting
        TCP/IP connections on port 5432?

Currently I am trying to find a fix.

[Ivan_Rapekas]

The only way for me to rebuild with external postgres was the command mentioned earlier:

 rebuild app --docker-args --net=host --skip-mac-address

But in this case unicorn is started with default port 3000. Exposing ports is disabled as well. I cannot explain exactly, but something has changed since Sep '17, probably in launcher code.

[Wurzelseppi]

Hey guys,

what´s the best practice to migrate from docker based postgre db to dedicated?
The setup of the dedicated is described farther up this thread, right, but how can I then move the data from docker to dedicated db ?
Probably with backup & restore, but is there a tutorial for that available ?

Thanks and greetings,

Julian

[thabbs (Thomas Abraham)]

Ideally, you have discourse stopped.
Dump your database with pg_dump and restore with pg_restore.
see PostgreSQL Restore Database

Before starting up Discourse using the new database, issue as admin:

grant all privileges on database discourse to discourse;
alter schema public owner to discourse;
create extension if not exists hstore;
create extension if not exists pg_trgm;