Restore failing from same new system

[Lapotor]

Infos:

• Used Zammad version: 5.0.2
• Installation method (source, package, …): package
• Operating system: ubuntu 20.04
• Database + version: postgres 12.9
• Elasticsearch version: elasticsearch 7.15.2

Expected behavior:

• Install zammad (hetzner cloud)
• After install create backup (with #2508)
• After backup restore same backup directly
• Restoreing successful

Actual behavior:

• Backup folder set as in #2508
• try restore this backup
  • when restoreing with zammad user:

$ sudo -u zammad -H /opt/zammad/contrib/backup/zammad_restore.sh

# Zammad restored started - Sat 20 Nov 2021 01:45:26 PM UTC!

The restore will delete your current config and database!
Be sure to have a backup available!

Enter 'yes' if you want to proceed!
Restore?: yes
Enter file date to restore:
20211120134351
File date: 20211120134351
Enter db date to restore:
20211120134351
DB date: 20211120134351
# Stopping Zammad
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to stop 'zammad.service'.
Authenticating as: root
Password: Failed to stop zammad.service: Connection timed out
See system logs and 'systemctl status zammad.service' for details.
# Checking requirements
polkit-agent-helper-1: pam_authenticate failed: Authentication failure
# ... Dropping current database zammad
PG::ObjectInUse: ERROR:  database "zammad" is being accessed by other users
DETAIL:  There are 6 other sessions using the database.
Couldn't drop database 'zammad'
rake aborted!
ActiveRecord::StatementInvalid: PG::ObjectInUse: ERROR:  database "zammad" is being accessed by other users

• When restoring with root user:

$ /opt/zammad/contrib/backup/zammad_restore.sh

# Zammad restored started - Sat 20 Nov 2021 01:48:10 PM UTC!

The restore will delete your current config and database!
Be sure to have a backup available!

Enter 'yes' if you want to proceed!
Restore?: yes
Enter file date to restore:
20211120134351
File date: 20211120134351
Enter db date to restore:
20211120134351
DB date: 20211120134351
# Stopping Zammad
# Checking requirements
# ... Dropping current database zammad
Dropped database 'zammad'
# ... Creating database zammad for owner zammad
CREATE DATABASE
# Restoring PostgreSQL DB
psql: error: FATAL:  Peer authentication failed for user "zammad"
# Restoring Files
# Ensuring correct file rights ...
# Clearing Cache ...
# Starting Zammad

# Zammad restored successfully - Sat 20 Nov 2021 01:48:36 PM UTC!

Steps to reproduce the behavior:

• Install zammad (hetzner cloud)
• After install create backup (with #2508)
• After backup restore same backup directly

[sebastianlenz]

Hi there,
i detected there is an error in /opt/zammad/contrib/backup/functions

I changed

zcat < ${BACKUP_DIR}/${RESTORE_DB_DATE}_zammad_db.${DB_FILE_EXT}.gz \
      | psql ${DB_HOST:+--host $DB_HOST} ${DB_PORT:+--port $DB_PORT} ${DB_USER:+--username $DB_USER} --dbname ${DB_NAME}

to

zcat < ${BACKUP_DIR}/${RESTORE_DB_DATE}_zammad_db.${DB_FILE_EXT}.gz \
      | su -c "psql ${DB_HOST:+--host $DB_HOST} ${DB_PORT:+--port $DB_PORT} ${DB_USER:+--username $DB_USER} --dbname ${DB_NAME}" zammad

and everything works like expected.

Greetings
Sebastian

[MrGeneration]

This is not an error but working as designed.
You’re supposed to restore as root user.

[torsten]

Hi @MrGeneration

I am trying to restore a backup on a different server as root user - as you recommended - but I get the same psql: error: FATAL: Peer authentication failed for user "zammad" error:

root@zammad-test:/opt/zammad/contrib/backup# ./zammad_restore.sh

# Zammad restored started - Mon 07 Feb 2022 12:28:40 PM UTC!

The restore will delete your current config and database!
Be sure to have a backup available!

Enter 'yes' if you want to proceed!
Restore?: yes
Enter file date to restore:
20220204002101
File date: 20220204002101
Enter db date to restore:
20220204002101
DB date: 20220204002101
# Stopping Zammad
# Checking requirements
# ... Dropping current database zammad
Dropped database 'zammad'
# ... Creating database zammad for owner zammad
CREATE DATABASE
# Restoring PostgreSQL DB
psql: error: FATAL:  Peer authentication failed for user "zammad"
# Restoring Files
# Ensuring correct file rights ...
# Clearing Cache ...
# Starting Zammad

# Zammad restored successfully - Mon 07 Feb 2022 12:28:58 PM UTC!

@sebastianlenz solution seems to work.

[MrGeneration]

Update your installation to last possible and read the documentation:
https://docs.zammad.org/en/latest/appendix/backup-and-restore/index.html

Please also try to not mention me directly.
I am receiving a huge amount of notifications.