Error when migrating to an external postgres instance

Support Self-hosting
1

I set up my forums using discourse-docker with the default self-contained config. I need to switch over to using a separate postgres server.

I ran a backup, then modified app.yml, rebuilt and attempted to restore from the backup via the CLI. The restore fails, and it appears that the restore was using the correct host to access postgres, but it was using the default port (5432) rather than the one I configured (9432).

Error:

psql: error: connection to server at "[REDACTED HOST]" ([REDACTED IP]), port 5432 failed: Connection refused
Is the server running on that host and accepting TCP/IP connections?
EXCEPTION: psql failed: 	Is the server running on that host and accepting TCP/IP connections?
/var/www/discourse/lib/backup_restore/database_restorer.rb:92:in `restore_dump'
/var/www/discourse/lib/backup_restore/database_restorer.rb:26:in `restore'
/var/www/discourse/lib/backup_restore/restorer.rb:51:in `run'
script/discourse:149:in `restore'
/var/www/discourse/vendor/bundle/ruby/3.2.0/gems/thor-1.3.0/lib/thor/command.rb:28:in `run'
/var/www/discourse/vendor/bundle/ruby/3.2.0/gems/thor-1.3.0/lib/thor/invocation.rb:127:in `invoke_command'
/var/www/discourse/vendor/bundle/ruby/3.2.0/gems/thor-1.3.0/lib/thor.rb:527:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/3.2.0/gems/thor-1.3.0/lib/thor/base.rb:584:in `start'
script/discourse:290:in `<top (required)>'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/cli/exec.rb:58:in `load'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/cli/exec.rb:58:in `kernel_load'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/cli/exec.rb:23:in `run'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/cli.rb:451:in `exec'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/vendor/thor/lib/thor/command.rb:28:in `run'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/vendor/thor/lib/thor/invocation.rb:127:in `invoke_command'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/vendor/thor/lib/thor.rb:527:in `dispatch'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/cli.rb:34:in `dispatch'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/vendor/thor/lib/thor/base.rb:584:in `start'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/cli.rb:28:in `start'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/exe/bundle:28:in `block in <top (required)>'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/lib/bundler/friendly_errors.rb:117:in `with_friendly_errors'
/usr/local/lib/ruby/gems/3.2.0/gems/bundler-2.5.3/exe/bundle:20:in `<top (required)>'
/usr/local/bin/bundle:25:in `load'
/usr/local/bin/bundle:25:in `<main>'
Trying to rollback...
Rolling back...
Cleaning stuff up...
Dropping functions from the discourse_functions schema...
Removing tmp '/var/www/discourse/tmp/restores/default/2024-01-17-201724' directory...
Unpausing sidekiq...
Marking restore as finished...
Notifying 'system' of the end of the restore...
Finished!
[FAILED]

Config:
In app.yml, under templates:, I commented out - "templates/postgres.template.yml"

I also added the following under env:

  DISCOURSE_DB_USERNAME: [REDACTED]
  DISCOURSE_DB_PASSWORD: [REDACTED]
  DISCOURSE_DB_HOST:  [REDACTED]
  DISCOURSE_DB_PORT: 9432
  DISCOURSE_DB_NAME: [REDACTED]
2

When you rebuilt did it generate a new blank site? If not, then you’ve got some problem there.

Can you access the database from inside the container with psql <some switches to point to the database>)

Inside the container, if you do a set|grep DB do you see your ENV variables?

3

Yes to all three. It generates a new, blank site, connected to the correct database. The environment variables are set when I grep for them, and I can manually connect to the db from within the container.

4

There is a remote chance that somehow the restore script does not respect the ENV variables (but everything else does?). The ways to check that are to look at the code and to try do do a restore from the UX. If you can restore from the UX then I think you could move this to Bug.

5

I’ve attempted a restore via the UX as well, and got the same error.

6

Then I guess what I’d do next is look at the code.

It’s especially strange that it’s working for everything else.

7

Can you get into a rails console and then show us the following values:

BackupRestore.database_configuration
BackupRestore::DatabaseRestorer.psql_command

e.g.:

→ rails c
Loading development environment (Rails 7.0.8)
[1] pry(main)> BackupRestore.database_configuration
=> #<struct BackupRestore::DatabaseConfiguration
 host=nil,
 port=nil,
 username="michael",
 password=nil,
 database="discourse_development">

[2] pry(main)> BackupRestore::DatabaseRestorer.psql_command
=> "psql --dbname='discourse_development' --single-transaction --variable=ON_ERROR_STOP=1 --username=michael"
8

Here you go! Everything I redacted is correct. The port is the only thing that is wrong.

[1] pry(main)> BackupRestore.database_configuration
=> #<struct BackupRestore::DatabaseConfiguration
 host="[REDACTED[",
 port=5432,
 username="[REDACTED]",
 password="[REDACTED]",
 database="[REDACTED]">
[2] pry(main)> BackupRestore::DatabaseRestorer.psql_command
=> "PGPASSWORD='[REDACTED]' psql --dbname='[REDACTED]' --single-transaction --variable=ON_ERROR_STOP=1 --host=[REDCATED] --port=5432 --username=[REDACTED]"

I’ll also reiterate that the blank instance is able to connect to the database for everything except restoring from the backup.

9

What is

ActiveRecord::Base.connection_pool.db_config.configuration_hash
10 
[3] pry(main)> ActiveRecord::Base.connection_pool.db_config.configuration_hash
=> {:adapter=>"postgresql",
 :pool=>8,
 :connect_timeout=>5,
 :socket=>"/var/run/postgresql",
 :host=>"[REDACTED]",
 :port=>9432,
 :backup_port=>5432,
 :username=>"[REDACTED]",
 :password=>"[REDACTED]",
 :host_names=>["[REDACTED]"],
 :database=>"[REDACTED]",
 :prepared_statements=>false,
 :idle_timeout=>30,
 :reaping_frequency=>30,
 :advisory_locks=>true}
11

Ah! You’ll also need to set

DISCOURSE_DB_BACKUP_PORT: 9432

and then things should work.

EDIT: this default has been in place for a while but seems to have caused more difficulty than good.

FIX: clear db_backup_port default value by Supermathie · Pull Request #25316 · discourse/discourse · GitHub also should resolve this and avoid this confusion for future travellers

12

Much appreciated! Thanks!