How to create PostgreSQL database?

Technical assistance
1

I’m installing zammad 6.3.1 on Debian 12, via package.
I’ve already PostgreSQL up and running, but after the installation I don’t see the database zammad created, and the conf/database.yml looks fine:

$ sudo cat /opt/zammad/config/database.yml 
production:
  adapter: postgresql
  database: zammad
  pool: 50
  timeout: 5000
  encoding: utf8
  username: zammad
  password: xyz

I’ve tried to run zammad reconfigure but no database is created.
If I run, as operating system zammad the command rake db:create I got a strange error:

$ ./bin/rake db:create
Could not find mysql2-0.5.6, pg-1.5.6, rszr-1.5.0, webauthn-3.1.0, openssl-3.2.0, cose-1.3.0, tpm-key_attestation-0.12.0, openssl-signature_algorithm-1.3.0 in locally installed gems
Run `bundle install --gemfile /opt/zammad/Gemfile` to install missing gems.

but then running bundle install fails spectacurarly:

$ ./bin/bundle install
Fetching gem metadata from https://rubygems.org/.......
Installing rszr 1.5.0 with native extensions
Installing mysql2 0.5.6 with native extensions
Installing openssl 3.2.0 with native extensions
Installing pg 1.5.6 with native extensions
Fetching openssl-signature_algorithm 1.3.0
Installing openssl-signature_algorithm 1.3.0
Fetching tpm-key_attestation 0.12.0
Fetching cose 1.3.0
Installing tpm-key_attestation 0.12.0
Installing cose 1.3.0
Gem::Ext::BuildError: ERROR: Failed to build gem native extension.

current directory:
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/gems/mysql2-0.5.6/ext/mysql2
/opt/zammad/vendor/ruby-3.2.4/bin/ruby extconf.rb
checking for rb_absint_size()... yes
checking for rb_absint_singlebit_p()... no
checking for rb_gc_mark_movable()... yes
checking for rb_wait_for_single_fd()... no
checking for rb_enc_interned_str() in ruby.h... no
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.  Check the mkmf.log file for more details.  You may
need configuration options.

Provided configuration options:
        --with-opt-dir
        --without-opt-dir
        --with-opt-include
        --without-opt-include=${opt-dir}/include
        --with-opt-lib
        --without-opt-lib=${opt-dir}/lib
        --with-make-prog
        --without-make-prog
        --srcdir=.
        --curdir
        --ruby=/opt/zammad/vendor/ruby-3.2.4/bin/$(RUBY_BASE_NAME)
        --with-openssl-dir
        --without-openssl-dir
        --with-mysql-dir
        --without-mysql-dir
        --with-mysql-include
        --without-mysql-include=${mysql-dir}/include
        --with-mysql-lib
        --without-mysql-lib=${mysql-dir}/lib
        --with-mysql-config
        --without-mysql-config
        --with-mysqlclient-dir
        --without-mysqlclient-dir
        --with-mysqlclient-include
        --without-mysqlclient-include=${mysqlclient-dir}/include
        --with-mysqlclient-lib
        --without-mysqlclient-lib=${mysqlclient-dir}/lib
        --with-mysqlclientlib
        --without-mysqlclientlib
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/mkmf.rb:1083:in `block in find_library': undefined
method `split' for nil:NilClass (NoMethodError)

    paths = paths.flat_map {|path| path.split(File::PATH_SEPARATOR)}
                                       ^^^^^^
        from /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/mkmf.rb:1083:in `each'
        from /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/mkmf.rb:1083:in `flat_map'
        from /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/mkmf.rb:1083:in `find_library'
        from extconf.rb:131:in `<main>'

To see why this extension failed to compile, please check the mkmf.log which can be found here:

/opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/extensions/x86_64-linux/3.2.0-static/mysql2-0.5.6/mkmf.log

extconf failed, exit code 1

Gem files will remain installed in
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/gems/mysql2-0.5.6 for inspection.
Results logged to
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/extensions/x86_64-linux/3.2.0-static/mysql2-0.5.6/gem_make.out

  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:119:in `run'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/ext_conf_builder.rb:28:in `build'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:187:in `build_extension'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:221:in `block in
build_extensions'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:218:in `each'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:218:in `build_extensions'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/installer.rb:846:in `build_extensions'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/rubygems_gem_installer.rb:72:in
`build_extensions'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/rubygems_gem_installer.rb:28:in `install'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/source/rubygems.rb:202:in `install'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/gem_installer.rb:54:in `install'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/gem_installer.rb:16:in
`install_from_spec'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/parallel_installer.rb:156:in
`do_install'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/parallel_installer.rb:147:in `block
in worker_pool'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:62:in `apply_func'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:57:in `block in process_queue'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:54:in `loop'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:54:in `process_queue'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:90:in `block (2 levels) in
create_threads'

An error occurred while installing mysql2 (0.5.6), and Bundler cannot continue.

In Gemfile:
  mysql2


Gem::Ext::BuildError: ERROR: Failed to build gem native extension.

    current directory: /opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/gems/rszr-1.5.0/ext/rszr
/opt/zammad/vendor/ruby-3.2.4/bin/ruby extconf.rb
checking for Imlib2.h... no
imlib2 development headers are missing
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.  Check the mkmf.log file for more details.  You may
need configuration options.

Provided configuration options:
        --with-opt-dir
        --without-opt-dir
        --with-opt-include
        --without-opt-include=${opt-dir}/include
        --with-opt-lib
        --without-opt-lib=${opt-dir}/lib
        --with-make-prog
        --without-make-prog
        --srcdir=.
        --curdir
        --ruby=/opt/zammad/vendor/ruby-3.2.4/bin/$(RUBY_BASE_NAME)
        --with-imlib2-dir
        --without-imlib2-dir
        --with-imlib2-include
        --without-imlib2-include=${imlib2-dir}/include
        --with-imlib2-lib
        --without-imlib2-lib=${imlib2-dir}/lib
        --with-imlib2-config
        --without-imlib2-config
        --with-pkg-config
        --without-pkg-config

To see why this extension failed to compile, please check the mkmf.log which can be found here:

/opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/extensions/x86_64-linux/3.2.0-static/rszr-1.5.0/mkmf.log

extconf failed, exit code 1

Gem files will remain installed in /opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/gems/rszr-1.5.0
for inspection.
Results logged to
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/gems/3.2.0/extensions/x86_64-linux/3.2.0-static/rszr-1.5.0/gem_make.out

  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:119:in `run'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/ext_conf_builder.rb:28:in `build'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:187:in `build_extension'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:221:in `block in
build_extensions'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:218:in `each'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/ext/builder.rb:218:in `build_extensions'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/rubygems/installer.rb:846:in `build_extensions'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/rubygems_gem_installer.rb:72:in
`build_extensions'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/rubygems_gem_installer.rb:28:in `install'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/source/rubygems.rb:202:in `install'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/gem_installer.rb:54:in `install'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/gem_installer.rb:16:in
`install_from_spec'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/parallel_installer.rb:156:in
`do_install'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/installer/parallel_installer.rb:147:in `block
in worker_pool'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:62:in `apply_func'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:57:in `block in process_queue'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:54:in `loop'
  /opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:54:in `process_queue'
/opt/zammad/vendor/ruby-3.2.4/lib/ruby/3.2.0/bundler/worker.rb:90:in `block (2 levels) in
create_threads'

An error occurred while installing rszr (1.5.0), and Bundler cannot continue.

In Gemfile:
  rszr

but I suspect I don’t need to get into this, and dependencies should be already installed.
Am I missing something?

2

While installing zammad, I got the following and a lot more similar errors:

# apt install zammad
....
# Clear cache...
# database.yml found. Updating db...
rake aborted!
ActiveRecord::NoDatabaseError: We could not find your database: zammad. Which can be found in the database configuration file located at config/database.yml. (ActiveRecord::NoDatabaseError)

To resolve this issue:

- Did you create the database for this app, or delete it? You may need to create your database.
- Has the database name changed? Check your database.yml config has the correct database name.

To create your database, run:

        bin/rails db:create

Am I wrong but the package should create the database itself, or do I have to create it in advance?

3

I’ve tried to manually create the PostgreSQL database:

postgres=# create role zammad with login password '<the password from database.yml>';

postgres=# create database zammad with owner zammad;

and verified that the zammad operating system user can connect:

ammad@zammad-test:~$ id
uid=999(zammad) gid=996(zammad) gruppi=996(zammad)
zammad@zammad-test:~$ psql -c 'select current_date; ' zammad
 current_date 
--------------
 2024-06-24
(1 riga)

With the database in place, things go a little better but the installation still complains:

# apt install zammad
...
== 20240612071836 CleanupObsoleteTranslations: migrating ======================
== 20240612071836 CleanupObsoleteTranslations: migrated (0.0004s) =============

# Updating translations...
/opt/zammad/vendor/bundle/ruby/3.2.0/gems/activerecord-7.0.8.4/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_params': PG::ForeignKeyViolation: ERROR:  insert or update on table "translations" violates foreign key constraint "fk_rails_d6e6d9635d" (ActiveRecord::InvalidForeignKey)
DETAIL:  Key (created_by_id)=(1) is not present in table "users".

Just for the record:

# apt search zammad
Ordinamento... Fatto
Ricerca sul testo... Fatto
zammad/sconosciuto,now 6.3.1-1719208095.8ca3e48a.bookworm amd64 [installato]
  Zammad is a web based open source helpdesk/customer support system
4

Seems I’ve solved the issue.
Not sure this is either a Debian or Zammad issue, but here is the problem.

After purging the package apt --purge remove zammad the directory /opt/zammad and the zammad PostgreSQL database remain in place.
Removing manually the database does not suffice:

% sudo -u postgres psql -c 'drop database zammad;'
% sudo -u postgres psql -c 'drop role zammad;'

and there is also the need to remove (after accurate backup) /opt/zammad.

Therefore, after having stopped the daemon, purged the package, removed the directory and the database, running a fresh install apt instlal zammad concludes with success creating the database.

5

We intentionally do not remove content so you don’t have an accidental data loss. You have to clean up yourself.

6

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