# Error with pg\_restore on new host (version mismatch)

**URL:** <https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575>\
**Category:** Technical assistance\
**Created:** [June 1, 2019, 8:12pm UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575 "2019-06-01T20:12:12Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![herzkerl](https://community.zammad.org/user_avatar/community.zammad.org/herzkerl/32/8167_2.png) [@herzkerl](https://community.zammad.org/u/herzkerl)\
**Post date:** [June 1, 2019, 8:12pm UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/1 "2019-06-01T20:12:12Z")

</div>

Continuing the discussion from [Migrating Zammad environment to new server](https://community.zammad.org/t/migrating-zammad-environment-to-new-server/1526/11):

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

```

### Infos:

- 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`

---

<div class="post-metadata">

**Author:** ![MrGeneration](https://community.zammad.org/user_avatar/community.zammad.org/mrgeneration/32/9260_2.png) [@MrGeneration](https://community.zammad.org/u/MrGeneration)\
**Post date:** [June 1, 2019, 11:28pm UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/2 "2019-06-01T23:28:04Z")

</div>

According to this: [https://stackoverflow.com/questions/4599696/use-pg-restore-to-restore-from-a-newer-version-of-postgresql](https://stackoverflow.com/questions/4599696/use-pg-restore-to-restore-from-a-newer-version-of-postgresql) 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.

---

<div class="post-metadata">

**Author:** ![herzkerl](https://community.zammad.org/user_avatar/community.zammad.org/herzkerl/32/8167_2.png) [@herzkerl](https://community.zammad.org/u/herzkerl)\
**Post date:** [June 2, 2019, 5:30am UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/3 "2019-06-02T05:30:08Z")

</div>

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 
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.)

---

<div class="post-metadata">

**Author:** ![herzkerl](https://community.zammad.org/user_avatar/community.zammad.org/herzkerl/32/8167_2.png) [@herzkerl](https://community.zammad.org/u/herzkerl)\
**Post date:** [June 2, 2019, 10:56am UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/4 "2019-06-02T10:56:49Z")

</div>

@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?

---

<div class="post-metadata">

**Author:** ![martin.von.wittich](https://community.zammad.org/letter_avatar_proxy/v4/letter/m/e480ec/32.png) [@martin.von.wittich](https://community.zammad.org/u/martin.von.wittich)\
**Post date:** [June 4, 2019, 2:58pm UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/5 "2019-06-04T14:58:49Z")

</div>

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: [Dump and restore of PostgreSQL: version compatibility FAQ – Pasha Golub's Blog](https://pgolub.wordpress.com/2013/11/19/dump-and-restore-of-postgresql-version-compatibility-faq/)

> [@herzkerl](#):
>
> 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?)

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](https://www.postgresql.org/docs/9.2/auth-methods.html#AUTH-PEER), 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:

```auto
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

```

> [@herzkerl](#):
>
> Edit: When trying to import the database using the ‘zammad’ user, I keep getting several errors:

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 ^^

---

<div class="post-metadata">

**Author:** ![herzkerl](https://community.zammad.org/user_avatar/community.zammad.org/herzkerl/32/8167_2.png) [@herzkerl](https://community.zammad.org/u/herzkerl)\
**Post date:** [June 25, 2019, 6:12am UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/6 "2019-06-25T06:12:35Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://community.zammad.org/uploads/default/original/1X/5c5afaea2d23f811dbec33cd4edb02fa29d3262f.png) [@system](https://community.zammad.org/u/system)\
**Post date:** [October 23, 2019, 6:12am UTC](https://community.zammad.org/t/error-with-pg-restore-on-new-host-version-mismatch/2575/7 "2019-10-23T06:12:36Z")

</div>

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