Error when migrating to an external postgres instance

Product Support Self-hosting

AI-generated summary

Morpheus636 is experiencing an error when migrating to an external Postgres instance. The restore from backup fails due to the incorrect port being used (5432 instead of 9432). Despite setting the correct port in the app.yml file, the restore script seems to ignore it.

@pfaffman suggests checking if the environment variables are set correctly inside the container and if the database can be accessed manually. Morpheus636 confirms that the environment variables are set and the database can be accessed manually.

@pfaffman suspects that the restore script might not respect the environment variables. Morpheus636 attempts a restore via the UX and encounters the same error.

@supermathie suggests checking the BackupRestore.database_configuration and BackupRestore::DatabaseRestorer.psql_command values in the Rails console. Morpheus636 provides the values, which show that the port is still set to 5432.

@supermathie asks for the ActiveRecord::Base.connection_pool.db_config.configuration_hash value, which Morpheus636 provides. The value shows that the correct port (9432) is set for the connection pool, but a separate backup_port is set to 5432.

@supermathie suggests setting DISCOURSE_DB_BACKUP_PORT to 9432 to fix the issue. This solution should resolve the problem and avoid confusion in the future. A pull request has been created to address this issue.

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 Contribute > 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!