我希望能够备份和恢复 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-备份.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 安装）
- 删除 mattermost_production 数据库
- 创建一个空的 mattermost_production 数据库
- 将备份 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