Moving postgres db to central db server: build error

Hi,
I am currently migrating from a classical two container setup (separated web_only and data containers) to a setup where the database is hosted on a central database server (not inside a docker container).

The central database was created from the dump.sql which is part of the backup file. The docker compose web_only.yaml uses

connect to central postgres database

DISCOURSE_DB_SOCKET: ‘’
DISCOURSE_DB_USERNAME: discourse
DISCOURSE_DB_PASSWORD: xxxx
DISCOURSE_DB_HOST: 10.10.10.xx
DISCOURSE_DB_NAME: discourse

During the build of web_only I get the following error:

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 741 exit 1>
Location of failure: /usr/local/lib/ruby/gems/3.2.0/gems/pups-1.2.1/lib/pups/exec_command.rb:132: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.

The database host is accessible with these data. Any ideas whats happening here? The same build with the standard database container finishes successfully.

Are you sure that the remote database is available from the server? It looks like it’s not. Can you telnet to the database port from the web server machine?

Hi Jay,

I followed the thread of Falco at Configure Discourse to use a separate PostgreSQL server The database server is available from the VM:

root@docker2:/var/discourse# pg_isready -d discourse -h 10.10.10.18 -p 5432 -U discourse    
10.10.10.18:5432 - Verbindungen werden angenommen

(connections are accepted)

The DB name, user and password in web_only.yml are also correct. The build error is

FAILED
--------------------
Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process:
:Status: pid 829 exit 1>
Location of failure: /usr/local/lib/ruby/gems/3.2.0/gems/pups-1.2.1/lib/pups/exec_command.rb:132:in `spawn'
exec failed with the params {"cd"=>"$home", "hook"=>"db_migrate", "cmd"=>["su discourse -c 'bundle exec rake db:migra
te'"]}
bootstrap failed with exit code 1

I am not a ruby expert, so I would need some help with the debugging starting from the error which appears here.

Bye, Thommie

IN that case:

What plugins do you have? Do you have the AI plugin?

What version of postgres are you running?

  • PostgreSQL 13.12
  • no AI plugin
  • plugins:
cmd:
          - git clone https://github.com/discourse/docker_manager.git
          - git clone https://github.com/discourse/discourse-shared-edits.git
          - git clone https://github.com/discourse/discourse-chat-integration
          - git clone https://github.com/discourse/wp-discourse
          - git clone https://github.com/discourse/discourse-openid-connect
          - git clone https://github.com/discourse/discourse-calendar
          - git clone https://github.com/discourse/discourse-data-explorer
          - git clone https://github.com/paviliondev/discourse-events
          - git clone https://github.com/paviliondev/discourse-locations
          - git clone https://github.com/discourse/discourse-reactions
          - git clone https://github.com/discourse/discourse-chat
          - git clone https://github.com/discourse/discourse-voting.git
          - git clone https://github.com/discourse/discourse-user-notes.git
          - git clone https://github.com/discourse/discourse-solved.git
          - git clone https://github.com/discourse/discourse-footnote.git
          - git clone https://github.com/discourse/discourse-docs.git
          - git clone https://github.com/discourse/discourse-docs-card-filter.git
          - git clone https://github.com/discourse/discourse-assign.git
          - git clone https://github.com/discourse/discourse-templates.git
          - git clone https://github.com/discourse/discourse-saved-searches.git
          - git clone https://github.com/discourse/discourse-tooltips
          - git clone https://github.com/nathan-nz/discourse-wikified-posts
          - git clone https://github.com/discourse/discourse-post-voting.git
          - git clone https://github.com/discourse/discourse-encrypt.git
          - git clone https://github.com/discourse/discourse-zoom.git
          - git clone https://github.com/discourse/discourse-spoiler-alert.git
          - git clone https://github.com/discourse/discourse-category-experts.git

Can you

?

The exact error message in the backtrace will tell us exactly what is the problem :smile:

Is your plan to ditch the data container?

1 Like

error code:

> FAILED
> --------------------
> Pups::ExecError: cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate' failed with return #<Process::Status: pid 816 exit 1>
> Location of failure: /usr/local/lib/ruby/gems/3.2.0/gems/pups-1.2.1/lib/pups/exec_command.rb:132: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

On the new system the data shall come from the central db instead of the docker db if possible.

I wonder if having this set to a blank value rather than unset will cause problems.

In any case, please post the complete output.

1 Like

the complete build log after

./launcher rebuild web_only

web_only_build.zip (9.4 KB)

the yaml

web_only.zip (2.4 KB)

Concerning the DISCOURSE_DB_SOCKET: ‘’: as we use a remote connection, I would guess that the socket value is not relevant in both cases (dockered data or remote data)

1 Like

The error was farther up in the build log:

…
docker_manager is already at latest compatible version
wp-discourse is already at latest compatible version

I, [2023-11-10T21:08:17.388213 #1]  INFO -- : > cd /var/www/discourse && su discourse -c 'bundle exec rake db:migrate'
Plugin name is 'discourse-topic-voting', but plugin directory is named 'discourse-voting'
rake aborted!
ActiveRecord::StatementInvalid: PG::InsufficientPrivilege: ERROR:  permission denied for table users (ActiveRecord::StatementInvalid)

So good news! It’s connecting fine, it’s that DB permissions need adjusting.

2 Likes

Hmmmm, strange, the discourse database user has

postgres=# GRANT ALL ON DATABASE discourse TO discourse;

on the central postgres server. Do we need anything else for this role?

That doesn’t affect existing tables.

Offhand, perhaps:

ALTER DATABASE "discourse" OWNER TO "discourse";

or:

ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT ALL ON TABLES TO "discourse";

The postgres logs should also tell you in more detail what operations are failing.

4 Likes

… ok ok, irt looks as if I have some more permission issues here, see /var/log/postgresql/postgresql-13-main.log:

2023-11-10 22:07:58.371 UTC [196127] postgres@postgres STATEMENT:  ALTER DEFAULT PRIVILEGES IN SCHEMA 'public' GRANT ALL ON TABLES TO 'discourse';
2023-11-10 22:10:18.270 UTC [196160] discourse@discourse ERROR:  permission denied for table site_settings
2023-11-10 22:10:18.270 UTC [196160] discourse@discourse STATEMENT:  SELECT name, data_type, value FROM site_settings
2023-11-10 22:10:18.313 UTC [196160] discourse@discourse ERROR:  permission denied for table users
2023-11-10 22:10:18.313 UTC [196160] discourse@discourse STATEMENT:  SELECT COUNT(*) FROM (SELECT 1 AS one FROM "users" LIMIT 20) subquery_for_count

OK, as always, it was a “layer 8 problem” :smiling_face_with_tear:

I already had a discourse database in the postgres but the discourse user/role had not all required privileges on it. And I did miss some major “behavioural” differences between mariadb and postgres …

Now I re-created all: user, database, permissions for the user/role (including ALTER DEFAULT PRIVILEGES FOR ROLE discourse IN SCHEMA public GRANT ALL ON TABLES TO “discourse”; to match tables created by this user in the future)

The build finished and the instance runs fine with the remote postgres server, so its ready for importing the backup from the older instance.

4 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.