Alain O'Dea

Asked: 2020-01-24 13:30:08 +0800 CST2020-01-24 13:30:08 +0800 CST 2020-01-24 13:30:08 +0800 CST

How do I backup and restore GitLab Mattermost so it preserves usernames?

I would like to be able to backup and restore GitLab Mattermost so it preserves the usernames along with the messages. So far I've got it to preserve the messages, but it replaces all the usernames with "Someone".

I have reviewed Mattermost's backup documentation:

I'm using PostgreSQL, so I followed their link to the PostgreSQL backup documentation.

It recommends pg_dump for backup and psql for restore, something I'm quite familiar with from automating backup+restore for Zotonic sites I've built.

I have a hourly cron that runs this command:

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

When I take mattermost-backup.sql.gz and restore it like this:

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 logs this (including a lot of conflict and constraint errors):

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

After that completes and GitLab Mattermost is back up and running, it shows "Someone" instead of actual usernames.

How do I backup and restore GitLab Mattermost so it preserves usernames?

1 Answers

Voted

Best Answer

Alain O'Dea
2020-02-07T11:28:28+08:002020-02-07T11:28:28+08:00
On the replica system you are restoring to, you will need to:

Install GitLab with Mattermost (I used the Omnibus install)

Delete the mattermost_production database

Create an empty mattermost_production database

Load the backup SQL into the empty database

Why does the original approach fail?

When GitLab is installed, it populates the Mattermost database with some records. Running psql to load the backup on top of those records leads to an inconsistent state where the database has both the fresh install data and the backup data.

How do you fix this?

In order to better ensure a clean restore, you'll need to delete the mattermost_production database and create a new empty database called mattermost_production, then run the backup SQL into it with psql as follows:

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
2

How do I backup and restore GitLab Mattermost so it preserves usernames?

Why does the original approach fail?

How do you fix this?

Can you pass user/pass for HTTP Basic Authentication in URL parameters?

Ping a Specific Port

Check if port is open or closed on a Linux server?

How to automate SSH login with password?

How do I tell Git for Windows where to find my private RSA key?

What's the default superuser username/password for postgres after a new install?

What port does SFTP use?

Command line to list users in a Windows Active Directory group?

What is a Pem file and how does it differ from other OpenSSL Generated Key File Formats?

How to determine if a bash variable is empty?