Since the backup script still doesn’t seem to work in some rare cases (I’m moving from Docker(-compose) to CentOS 7/package) I wanted to use pg_dump and pg_restore as described in the linked thread by @mbpws.
Unfortunately there’s a mismatch of the postgres versions and I’m not sure what to do about that.
Where can I find the correct database user and password for the new system? (I’ve already looked at /opt/zammad/config/database.yml, but it didn’t suffice).
(Also, why is there no database password in that database configuration file?)
Edit: When trying to import the database using the ‘zammad’ user, I keep getting several errors:
[zammad@portal zammad_backup]$ psql -d zammad -f ./zammad_new.dump
SET
psql:./zammad_new.dump:9: FEHLER: unbekannter Konfigurationsparameter »lock_timeout«
psql:./zammad_new.dump:10: FEHLER: unbekannter Konfigurationsparameter »idle_in_transaction_session_timeout«
SET
SET
set_config
------------
(1 Zeile)
SET
SET
SET
psql:./zammad_new.dump:17: FEHLER: unbekannter Konfigurationsparameter »row_security«
CREATE EXTENSION
psql:./zammad_new.dump:30: FEHLER: Berechtigung nur für Eigentümer der Erweiterung plpgsql
SET
SET
psql:./zammad_new.dump:51: FEHLER: Relation »activity_streams« existiert bereits
psql:./zammad_new.dump:54: FEHLER: Berechtigung nur für Mitglied von Rolle »postgres«
psql:./zammad_new.dump:65: FEHLER: Relation »activity_streams_id_seq« existiert bereits
psql:./zammad_new.dump:68: FEHLER: kann Eigentümer der Sequenz »activity_streams_id_seq« nicht ändern
DETAIL: Sequenz »activity_streams_id_seq« ist mit Tabelle »activity_streams« verknüpft.
ALTER SEQUENCE
EDIT (for clarity): When trying to restore using the script, I get the following error:
INIT CMD = systemctl
# Stopping Zammad
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Legitimierung ist notwendig für die Verwaltung von Systemdiensten und Einheiten
Authenticating as: root
Password:
==== AUTHENTICATION COMPLETE ===
# Restoring Files
# Restoring PostgrSQL DB
Passwort:
su: Fehler bei Authentifizierung
# Starting Zammad
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Legitimierung ist notwendig für die Verwaltung von Systemdiensten und Einheiten
Authenticating as: root
Password: Failed to start zammad.service: Die Wartezeit für die Verbindung ist abgelaufen
See system logs and 'systemctl status zammad.service' for details.
(When asked for the password after ‘# Restoring PostgrSQL DB’ the script just continues after approx. one or two seconds.)
@MrGeneration You’re recommending using psql in your post above—but whatever I’ve tried, I cannot manage to get it to work. Seems to be as @mbpws wrote in the linked thread (emphasis mine):
In the meantime I managed to get it working. I already figured out that the migration from CentOS7 to Ubuntu will not work when using the provided backup script. It will throw errors all over.
Could anyone elaborate on why this happens in the first place?
Also, he advised to use pg_restore (which is why I used for this migration from Docker/Alpine to CentOS). As I said, CentOS uses an older version of pg_restore—so this seems to be the issue here.
tl;dr / Summary:
Using the provided backup script doesn’t seem to work (I’ve tried a few things—nothing works)
@mbpws said in another thread that the backup script doesn’t work when migrating from CentOS to Ubuntu
Backup script uses ‘psql’ (which seems to be the bad guy here—for whatever reason)
pg_restore won’t work because the version in CentOS is older (9.2.24) than in Docker/Alpine (9.6.13)
Looking forward to your ideas. Maybe @martin.von.wittich ran into some sort of similar issue?
I don’t know how exactly the Zammad Debian packages work, but using postgres without passwords is a very common (and sensible) configuration. Instead, it’s most likely using PEER authentication, that is, postgres will map system users to postgres users via a config file that could say e.g. “the system user zammad is allowed to connect with the postgres user zammad”. Have a look at pg_hba.conf and pg_ident.conf to figure out how exactly this is configured. An example from a non-Zammad server:
server ~ # cat /etc/postgresql/9.6/main/pg_hba.conf
[...]
# TYPE DATABASE USER CIDR-ADDRESS METHOD
#
local all proftpd trust
local all all ident map=map1
host all rsyslogd 127.0.0.1/32 md5
host all rsyslogd ::1/128 md5
#host all all 127.0.0.1/32 md5
#host all all ::1/128 md5
server ~ # head -n 15 /etc/postgresql/9.6/main/pg_ident.conf
[...]
# MAPNAME IDENT-USERNAME PG-USERNAME
map1 postgres postgres
map1 root postgres
map1 root antivirus
map1 www-data backup
map1 www-data booking
map1 www-data booking_adm
Hmm, but this is already looking pretty good. Some errors about unknown configuration parameters are to be expected, as postgres 9.2 didn’t have these yet. If these cause the import to fail, edit the SQL dump and remove them.
Not sure why it’s complaining about tables that already exist - maybe they were created during a first import attempt, and now are causing problems on subsequent attempts? You could try to create a clean slate by dropping and recreating the database.
Is that all the output, or does it go on? I have a feeling that either the import worked, or it failed later on and the actually important error message is missing ^^
Thank you for elaborating. To be honest I just went with Debian instead of CentOS, which uses newer packages, and the import went well… I think it had something to do with the user/privileges, though.