配置 Discourse 以使用单独的 PostgreSQL 服务器

[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;

[kkk (Kunal Soni)]

we can also use Hosts ip address instead of using --net=host
172.17.0.1 is the default address for host machine from docker network on unix machines.
Using --net=host restricts us to use -p option as docker argument.

DISCOURSE_DB_HOST = 172.17.0.1

[AlexanderK]

Hi,
Thank you for really great guide.
Unfortunately I got an error while reproducing it.
Initially I created discourse using launcher for all - app/redis/postgres. And it worked fine.
But with external RDS launcher failed:

root@ip-172-31-42-129:/var/discourse# ./launcher rebuild app
Ensuring launcher is up to date
Fetching origin
Launcher is up-to-date
Stopping old container
+ /usr/bin/docker stop -t 60 app
app
cd /pups && git pull && git checkout v1.0.3 && /pups/bin/pups --stdin
docker: Error response from daemon: could not get container for discourse.xxxxxxxx.us-west-2.rds.amazonaws.com: No such container: discourse.xxxxxxxx.us-west-2.rds.amazonaws.com.
See 'docker run --help'.
cat: cids/app_bootstrap.cid: No such file or directory
"docker rm" requires at least 1 argument.
See 'docker rm --help'.

Usage:  docker rm [OPTIONS] CONTAINER [CONTAINER...]

Remove one or more containers
rm: cannot remove 'cids/app_bootstrap.cid': No such file or directory
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.

Please advice how to resolve this issue.
Thank you,
Alexander K

[dotnetnate]

Is there any way to pre-create and seed the database that doesn’t require going through the migration steps? We are running on AKS with external Postgres and the db setup seems to take what I consider to be an abnormally long amount of time (~8-9 minutes). Speeding this up would be great. Or is this a known issue in that configuration?

[pfaffman (Jay Pfaffman)]

That’s not a supported configuration.

If you’re building an image, a bunch more stuff than just the database is getting built like a bunch of templates are getting pre-compiled. I think that’s just how long it’s going to take.

[Falco]

@dotnetnate

Is there any way to pre-create and seed the database that doesn’t require going through the migration steps?

Yes, you can bootstrap it elsewhere and then move the data to your production PostgreSQL.

However this will make updates very cumbersome.

[VinodSingh]

需要注意的三件事。

一：默认情况下，PostgreSQL 监听 localhost。在 postgresql.conf 文件中更改监听地址，如下所示。

listen_addresses = ‘localhost,172.17.0.1’

二：通过在 pg_hba.conf 文件中添加以下行，建议 PostgreSQL 接受来自 Docker 镜像的连接。

host all all 172.17.0.0/16 scram-sha-256

在进行以上两项更改后，重启 PostgreSQL 服务。

三：如果仍然无法连接，请检查防火墙，它可能会阻止端口 5432 上的入站连接。

[Samir_Sabri]

@Falco 如果我使用现有数据库，是否存在擦除现有表的风险？\n另外，有没有办法为 discourse 表添加前缀？

[pfaffman (Jay Pfaffman)]

@Samir_Sabri

如果我使用现有的数据库，会不会有擦除现有表的风险？

不会，除非它们与 discourse 的名称冲突。

但我认为这是个坏主意。

@Samir_Sabri

有没有办法为 discourse 表添加前缀？

没有。我建议你使用单独的数据库，除非有某些原因需要将它们连接起来。

你通过共享数据库在解决什么问题？

[Samir_Sabri]

谢谢，我正在使用 AWS RDS，我刚发现可以在同一实例上拥有多个数据库，因此，我创建了一个带有新用户的新数据库。

[linw-pnwe]

我有一个新安装的 Discourse 实例，通过 Docker 在 Google Cloud 的 VM 上运行。我目前已启用文件上传和 Discourse 备份到 Google Cloud 的存储桶，并且在遵循 为上传配置 S3 兼容对象存储提供商 线程 中的说明后，这些功能运行正常。我可以在存储桶中看到测试上传，并且当我查看上传 URL 时，所有上传都显示来自 CDN 的正确 URL，因此它们似乎能正确地从存储桶中拉取。

然后，我在 Google Cloud 上创建了一个 PostgreSQL 15.2 实例，并执行了第一个帖子中概述的数据库设置过程，并配置了 app.yml 文件。Google Cloud 上 PostgreSQL 的默认端口是 5432，所以我省略了这些行。
如果我在 app.yml 配置中使用 postgres 实例的公共 IP 地址，当我重建应用程序时，我会得到以下结果：

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse & su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 1024 exit 1>
Location of failure: /usr/local/lib/ruby/gems/3.2.0/gems/pups-1.1.1/lib/pups/exec_command.rb:117:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]}
bootstrap failed with exit code 1
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.
a6a71b00bce378aa6334ae1c9fe103778d260bb699fe598f9685689e8b5ce450

只是为了看看发生了什么，我尝试使用 postgres 实例的其他 IP 地址。
如果我使用 postgres 实例的私有 IP 地址，我会得到以下结果：

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse & su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 1024 exit 1>
Location of failure: /usr/local/lib/ruby/gems/3.2.0/gems/pups-1.1.1/lib/pups/exec_command.rb:117:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]}
bootstrap failed with exit code 1
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.
7333126c522eb51ace4d55ea89803eea54b96704baab70c322008cf2836ba47a

如果我使用 postgres 实例的传出 IP 地址，我会得到以下结果：

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse & su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 1026 exit 1>
Location of failure: /usr/local/lib/ruby/gems/3.2.0/gems/pups-1.1.1/lib/pups/exec_command.rb:117:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migrate'"]}
bootstrap failed with exit code 1
** FAILED TO BOOTSTRAP ** please scroll up and look for earlier error messages, there may be more than one.
./discourse-doctor may help diagnose the problem.
c588d2b6977b9e7d493b0b59bc694369cb7c2219de67d5886112ed16312626ae

使用所有不同的 IP 地址，失败的消息都非常相似，并且 postgres 数据库根本没有收到任何数据或连接。有人能告诉我我哪里做错了什么吗？

另外，我的问题是否是由 VM 实例上未使用 Cloud SQL Auth Proxy 引起的？如果是，我想我必须构建一个脚本来运行代理并在应用程序重建之前对其进行计时。有人对此有任何见解吗？

感谢大家的时间。

[linw-pnwe]

我又尝试了几次，切换了 IP 地址进行重建，似乎 discourse 数据库最终还是填充了表。所以现在我更不明白到底是怎么回事了。

[maggiomj (Matt Maggiore)]

能否有人告诉我原始说明是为哪个版本的 Discourse 编写的？

[pfaffman (Jay Pfaffman)]

这应该适用于过去五年甚至更长时间的标准安装。

您遇到问题了吗？