我希望能夠備份和恢復 GitLab Mattermost，以便它保留使用者名和消息。到目前為止，我已經用它來保留消息，但它用“某人”替換了所有使用者名。

我已經查看了Mattermost 的備份文件：

我正在使用 PostgreSQL，所以我按照他們的連結到PostgreSQL 備份文件。

它建議使用 pg_dump 進行備份，使用 psql 進行恢復，這是我對我建立的 Zotonic 站點的自動備份+恢復非常熟悉的東西。

我有一個每小時執行此命令的 cron：

sudo -i -u gitlab-psql -- /opt/gitlab/embedded/bin/pg_dump -h \
 /var/opt/gitlab/postgresql mattermost_production |
 gzip > "mattermost-backup.sql.gz"

當我採取mattermost-backup.sql.gz並像這樣恢復它時：

gitlab-ctl stop mattermost
zcat mattermost-backup.sql.gz |
 sudo -i -u gitlab-psql -- /opt/gitlab/embedded/bin/psql \
   -h /var/opt/gitlab/postgresql \
   -d mattermost_production
gitlab-ctl start mattermost

gitlab-psql 記錄這個（包括很多衝突和約束錯誤）：

SET
SET
SET
SET
SET
set_config
------------

(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
SET
SET
ERROR:  relation "audits" already exists
ALTER TABLE
ERROR:  relation "bots" already exists
ALTER TABLE
ERROR:  relation "channelmemberhistory" already exists
ALTER TABLE
ERROR:  relation "channelmembers" already exists
ALTER TABLE
ERROR:  relation "channels" already exists
ALTER TABLE
ERROR:  relation "clusterdiscovery" already exists
ALTER TABLE
ERROR:  relation "commands" already exists
ALTER TABLE
ERROR:  relation "commandwebhooks" already exists
ALTER TABLE
ERROR:  relation "compliances" already exists
ALTER TABLE
ERROR:  relation "emoji" already exists
ALTER TABLE
ERROR:  relation "fileinfo" already exists
ALTER TABLE
ERROR:  relation "groupchannels" already exists
ALTER TABLE
ERROR:  relation "groupmembers" already exists
ALTER TABLE
ERROR:  relation "groupteams" already exists
ALTER TABLE
ERROR:  relation "incomingwebhooks" already exists
ALTER TABLE
ERROR:  relation "jobs" already exists
ALTER TABLE
ERROR:  relation "licenses" already exists
ALTER TABLE
ERROR:  relation "linkmetadata" already exists
ALTER TABLE
ERROR:  relation "oauthaccessdata" already exists
ALTER TABLE
ERROR:  relation "oauthapps" already exists
ALTER TABLE
ERROR:  relation "oauthauthdata" already exists
ALTER TABLE
ERROR:  relation "outgoingwebhooks" already exists
ALTER TABLE
ERROR:  relation "pluginkeyvaluestore" already exists
ALTER TABLE
ERROR:  relation "posts" already exists
ALTER TABLE
ERROR:  relation "preferences" already exists
ALTER TABLE
ERROR:  relation "publicchannels" already exists
ALTER TABLE
ERROR:  relation "reactions" already exists
ALTER TABLE
ERROR:  relation "roles" already exists
ALTER TABLE
ERROR:  relation "schemes" already exists
ALTER TABLE
ERROR:  relation "sessions" already exists
ALTER TABLE
ERROR:  relation "status" already exists
ALTER TABLE
ERROR:  relation "systems" already exists
ALTER TABLE
ERROR:  relation "teammembers" already exists
ALTER TABLE
ERROR:  relation "teams" already exists
ALTER TABLE
ERROR:  relation "termsofservice" already exists
ALTER TABLE
ERROR:  relation "tokens" already exists
ALTER TABLE
ERROR:  relation "useraccesstokens" already exists
ALTER TABLE
ERROR:  relation "usergroups" already exists
ALTER TABLE
ERROR:  relation "users" already exists
ALTER TABLE
ERROR:  relation "usertermsofservice" already exists
ALTER TABLE
COPY 160
COPY 4
COPY 126
COPY 116
COPY 29
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 4
COPY 0
COPY 0
COPY 0
COPY 0
COPY 94
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
COPY 0
ERROR:  duplicate key value violates unique constraint "pluginkeyvaluestore_pkey"
DETAIL:  Key (pluginid, pkey)=(com.mattermost.nps, ServerUpgrade-5.17.0) already exists.
CONTEXT:  COPY pluginkeyvaluestore, line 4
COPY 171
COPY 110
COPY 14
COPY 6
ERROR:  duplicate key value violates unique constraint "roles_name_key"
DETAIL:  Key (name)=(system_guest) already exists.
CONTEXT:  COPY roles, line 1
COPY 0
COPY 24
COPY 15
ERROR:  duplicate key value violates unique constraint "systems_pkey"
DETAIL:  Key (name)=(AsymmetricSigningKey) already exists.
CONTEXT:  COPY systems, line 1
COPY 29
COPY 4
COPY 0
COPY 0
COPY 1
COPY 0
ERROR:  duplicate key value violates unique constraint "users_username_key"
DETAIL:  Key (username)=(surveybot) already exists.
CONTEXT:  COPY users, line 1
COPY 0
ERROR:  multiple primary keys for table "audits" are not allowed
ERROR:  multiple primary keys for table "bots" are not allowed
ERROR:  multiple primary keys for table "channelmemberhistory" are not allowed
ERROR:  multiple primary keys for table "channelmembers" are not allowed
ERROR:  relation "channels_name_teamid_key" already exists
ERROR:  multiple primary keys for table "channels" are not allowed
ERROR:  multiple primary keys for table "clusterdiscovery" are not allowed
ERROR:  multiple primary keys for table "commands" are not allowed
ERROR:  multiple primary keys for table "commandwebhooks" are not allowed
ERROR:  multiple primary keys for table "compliances" are not allowed
ERROR:  relation "emoji_name_deleteat_key" already exists
ERROR:  multiple primary keys for table "emoji" are not allowed
ERROR:  multiple primary keys for table "fileinfo" are not allowed
ERROR:  multiple primary keys for table "groupchannels" are not allowed
ERROR:  multiple primary keys for table "groupmembers" are not allowed
ERROR:  multiple primary keys for table "groupteams" are not allowed
ERROR:  multiple primary keys for table "incomingwebhooks" are not allowed
ERROR:  multiple primary keys for table "jobs" are not allowed
ERROR:  multiple primary keys for table "licenses" are not allowed
ERROR:  multiple primary keys for table "linkmetadata" are not allowed
ERROR:  relation "oauthaccessdata_clientid_userid_key" already exists
ERROR:  multiple primary keys for table "oauthaccessdata" are not allowed
ERROR:  multiple primary keys for table "oauthapps" are not allowed
ERROR:  multiple primary keys for table "oauthauthdata" are not allowed
ERROR:  multiple primary keys for table "outgoingwebhooks" are not allowed
ERROR:  multiple primary keys for table "pluginkeyvaluestore" are not allowed
ERROR:  multiple primary keys for table "posts" are not allowed
ERROR:  multiple primary keys for table "preferences" are not allowed
ERROR:  relation "publicchannels_name_teamid_key" already exists
ERROR:  multiple primary keys for table "publicchannels" are not allowed
ERROR:  multiple primary keys for table "reactions" are not allowed
ERROR:  relation "roles_name_key" already exists
ERROR:  multiple primary keys for table "roles" are not allowed
ERROR:  relation "schemes_name_key" already exists
ERROR:  multiple primary keys for table "schemes" are not allowed
ERROR:  multiple primary keys for table "sessions" are not allowed
ERROR:  multiple primary keys for table "status" are not allowed
ERROR:  multiple primary keys for table "systems" are not allowed
ERROR:  multiple primary keys for table "teammembers" are not allowed
ERROR:  relation "teams_name_key" already exists
ERROR:  multiple primary keys for table "teams" are not allowed
ERROR:  multiple primary keys for table "termsofservice" are not allowed
ERROR:  multiple primary keys for table "tokens" are not allowed
ERROR:  multiple primary keys for table "useraccesstokens" are not allowed
ERROR:  relation "useraccesstokens_token_key" already exists
ERROR:  relation "usergroups_name_key" already exists
ERROR:  multiple primary keys for table "usergroups" are not allowed
ERROR:  relation "usergroups_source_remoteid_key" already exists
ERROR:  relation "users_authdata_key" already exists
ERROR:  relation "users_email_key" already exists
ERROR:  multiple primary keys for table "users" are not allowed
ERROR:  relation "users_username_key" already exists
ERROR:  multiple primary keys for table "usertermsofservice" are not allowed
ERROR:  relation "idx_audits_user_id" already exists
ERROR:  relation "idx_channel_search_txt" already exists
ERROR:  relation "idx_channelmembers_channel_id" already exists
ERROR:  relation "idx_channelmembers_user_id" already exists
ERROR:  relation "idx_channels_create_at" already exists
ERROR:  relation "idx_channels_delete_at" already exists
ERROR:  relation "idx_channels_displayname_lower" already exists
ERROR:  relation "idx_channels_name" already exists
ERROR:  relation "idx_channels_name_lower" already exists
ERROR:  relation "idx_channels_team_id" already exists
ERROR:  relation "idx_channels_update_at" already exists
ERROR:  relation "idx_command_create_at" already exists
ERROR:  relation "idx_command_delete_at" already exists
ERROR:  relation "idx_command_team_id" already exists
ERROR:  relation "idx_command_update_at" already exists
ERROR:  relation "idx_command_webhook_create_at" already exists
ERROR:  relation "idx_emoji_create_at" already exists
ERROR:  relation "idx_emoji_delete_at" already exists
ERROR:  relation "idx_emoji_name" already exists
ERROR:  relation "idx_emoji_update_at" already exists
ERROR:  relation "idx_fileinfo_create_at" already exists
ERROR:  relation "idx_fileinfo_delete_at" already exists
ERROR:  relation "idx_fileinfo_postid_at" already exists
ERROR:  relation "idx_fileinfo_update_at" already exists
ERROR:  relation "idx_groupchannels_channelid" already exists
ERROR:  relation "idx_groupmembers_create_at" already exists
ERROR:  relation "idx_groupteams_teamid" already exists
ERROR:  relation "idx_incoming_webhook_create_at" already exists
ERROR:  relation "idx_incoming_webhook_delete_at" already exists
ERROR:  relation "idx_incoming_webhook_team_id" already exists
ERROR:  relation "idx_incoming_webhook_update_at" already exists
ERROR:  relation "idx_incoming_webhook_user_id" already exists
ERROR:  relation "idx_jobs_type" already exists
ERROR:  relation "idx_link_metadata_url_timestamp" already exists
ERROR:  relation "idx_oauthaccessdata_client_id" already exists
ERROR:  relation "idx_oauthaccessdata_refresh_token" already exists
ERROR:  relation "idx_oauthaccessdata_user_id" already exists
ERROR:  relation "idx_oauthapps_creator_id" already exists
ERROR:  relation "idx_oauthauthdata_client_id" already exists
ERROR:  relation "idx_outgoing_webhook_create_at" already exists
ERROR:  relation "idx_outgoing_webhook_delete_at" already exists
ERROR:  relation "idx_outgoing_webhook_team_id" already exists
ERROR:  relation "idx_outgoing_webhook_update_at" already exists
ERROR:  relation "idx_posts_channel_id" already exists
ERROR:  relation "idx_posts_channel_id_delete_at_create_at" already exists
ERROR:  relation "idx_posts_channel_id_update_at" already exists
ERROR:  relation "idx_posts_create_at" already exists
ERROR:  relation "idx_posts_delete_at" already exists
ERROR:  relation "idx_posts_hashtags_txt" already exists
ERROR:  relation "idx_posts_is_pinned" already exists
ERROR:  relation "idx_posts_message_txt" already exists
ERROR:  relation "idx_posts_root_id" already exists
ERROR:  relation "idx_posts_update_at" already exists
ERROR:  relation "idx_posts_user_id" already exists
ERROR:  relation "idx_preferences_category" already exists
ERROR:  relation "idx_preferences_name" already exists
ERROR:  relation "idx_preferences_user_id" already exists
ERROR:  relation "idx_publicchannels_delete_at" already exists
ERROR:  relation "idx_publicchannels_displayname_lower" already exists
ERROR:  relation "idx_publicchannels_name" already exists
ERROR:  relation "idx_publicchannels_name_lower" already exists
ERROR:  relation "idx_publicchannels_search_txt" already exists
ERROR:  relation "idx_publicchannels_team_id" already exists
ERROR:  relation "idx_sessions_create_at" already exists
ERROR:  relation "idx_sessions_expires_at" already exists
ERROR:  relation "idx_sessions_last_activity_at" already exists
ERROR:  relation "idx_sessions_token" already exists
ERROR:  relation "idx_sessions_user_id" already exists
ERROR:  relation "idx_status_status" already exists
ERROR:  relation "idx_status_user_id" already exists
ERROR:  relation "idx_teammembers_delete_at" already exists
ERROR:  relation "idx_teammembers_team_id" already exists
ERROR:  relation "idx_teammembers_user_id" already exists
ERROR:  relation "idx_teams_create_at" already exists
ERROR:  relation "idx_teams_delete_at" already exists
ERROR:  relation "idx_teams_invite_id" already exists
ERROR:  relation "idx_teams_name" already exists
ERROR:  relation "idx_teams_update_at" already exists
ERROR:  relation "idx_user_access_tokens_token" already exists
ERROR:  relation "idx_user_access_tokens_user_id" already exists
ERROR:  relation "idx_user_terms_of_service_user_id" already exists
ERROR:  relation "idx_usergroups_delete_at" already exists
ERROR:  relation "idx_usergroups_remote_id" already exists
ERROR:  relation "idx_users_all_no_full_name_txt" already exists
ERROR:  relation "idx_users_all_txt" already exists
ERROR:  relation "idx_users_create_at" already exists
ERROR:  relation "idx_users_delete_at" already exists
ERROR:  relation "idx_users_email" already exists
ERROR:  relation "idx_users_email_lower_textpattern" already exists
ERROR:  relation "idx_users_firstname_lower_textpattern" already exists
ERROR:  relation "idx_users_lastname_lower_textpattern" already exists
ERROR:  relation "idx_users_names_no_full_name_txt" already exists
ERROR:  relation "idx_users_names_txt" already exists
ERROR:  relation "idx_users_nickname_lower_textpattern" already exists
ERROR:  relation "idx_users_update_at" already exists
ERROR:  relation "idx_users_username_lower_textpattern" already exists

完成後 GitLab Mattermost 備份並執行，它顯示“某人”而不是實際使用者名。

如何備份和恢復 GitLab Mattermost 以保留使用者名？

在您要恢復到的副本系統上，您需要：

1. 使用 Mattermost 安裝 GitLab（我使用了 Omnibus 安裝）
2. 刪除mattermost_production 數據庫
3. 創建一個空的 mattermost_production 數據庫
4. 將備份 SQL 載入到空數據庫中

為什麼原來的方法會失敗？

安裝 GitLab 後，它會使用一些記錄填充 Mattermost 數據庫。執行 psql 以在這些記錄之上傳入備份會導致數據庫具有全新安裝數據和備份數據的不一致狀態。

你如何解決這個問題？

為了更好地確保乾淨還原，您需要刪除 mattermost_production 數據庫並創建一個名為 mattermost_production 的新空數據庫，然後使用 psql 將備份 SQL 執行到其中，如下所示：

gitlab-ctl stop mattermost
sudo -i -u gitlab-psql -- /opt/gitlab/embedded/bin/dropdb \
     -h /var/opt/gitlab/postgresql \
     mattermost_production
sudo -i -u gitlab-psql -- /opt/gitlab/embedded/bin/createdb \
     -h /var/opt/gitlab/postgresql \
     mattermost_production
zcat mattermost-backup.sql.gz |
 sudo -i -u gitlab-psql -- /opt/gitlab/embedded/bin/psql \
   -h /var/opt/gitlab/postgresql \
   -d mattermost_production
gitlab-ctl start mattermost

引用自：https://serverfault.com/questions/1000172