Error with pg_restore on new host (version mismatch)

Continuing the discussion from Migrating Zammad environment to new server:

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.

Old host (docker container ‘zammad-postgres’:

bash-4.4# pg_restore --version
pg_restore (PostgreSQL) 9.6.13

New host:

[root@portal ~]# pg_restore --version
pg_restore (PostgreSQL) 9.2.24


  • Used Zammad version: 2.9 (newest)
  • Used Zammad installation source: package
  • Operating system: CentOS 7
  • Browser + version: Safari (newest)

Expected behavior:

  • Successful import of the database dump on new host

Actual behavior:

  • Error: pg_restore: [Archivierer] nicht unterstützte Version (1.13) im Dateikopf

According to this: it’s better to run “pgsql” for restoring. This should also solve your problem with restoring.

In that context: The changes between 9.2 and 9.6 shouldn’t affect you during import.

Thank you. Now I’m having another (I hope small) issue: The DB user within docker-compose was ‘postgres’, while on CentOS it’s ‘Zammad’.

[root@portal ~]# psql -h localhost -U zammad -d zammad_production -f /var/tmp/zammad_backup/zammad_new.dump
psql: FATAL:  Ident-Authentifizierung für Benutzer »zammad« fehlgeschlagen

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 
psql:./zammad_new.dump:9: FEHLER: unbekannter Konfigurationsparameter »lock_timeout«
psql:./zammad_new.dump:10: FEHLER: unbekannter Konfigurationsparameter »idle_in_transaction_session_timeout«
(1 Zeile)
psql:./zammad_new.dump:17: FEHLER: unbekannter Konfigurationsparameter »row_security«
psql:./zammad_new.dump:30: FEHLER: Berechtigung nur für Eigentümer der Erweiterung plpgsql
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.

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
# Restoring Files
# Restoring PostgrSQL DB
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?

Yes, using pg_dump with the plaintext format and then importing this via psql is in this case definitely preferrable over using pg_dump with the custom format and pg_restore. The custom format is simply not backwards compatible:

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                  md5
host    all             rsyslogd                ::1/128         md5
#host   all             all               md5
#host   all             all             ::1/128                 md5

server ~ # head -n 15 /etc/postgresql/9.6/main/pg_ident.conf
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 ^^