Mattermost Migration Postgres restore fails

I am following the migration guide to move my mattermost server from one instance (u18.04+postgres10) to a new instance (u20.04+postgres13). Both installations are on the same server and schema version (5.36.1).

I am stuck on the DB migration part. I am using pg_dumpall --user=postgres > mattermost_db on the old box and psql -f mattermost_db mattermost on the new box but it fails with messages like:

psql:mattermost_db:40807: ERROR: relation "table_name" already exists, and
psql:mattermost_db:14: ERROR: role "mmuser" already exists

I know this is probably more of a postgres question than a mattermost question, but after a week of trying all sorts of things, I am really stuck.

Thank you so much in advance for any advice!

Hi, @mmadmin

Since you mentioned that you are currently stuck at the Postgres migration part specifically, may I know if you are dumping the data into a brand new database since you mentioned that you are getting the relation XXXX and role XXX already exists?

If not, were you trying to merge the Mattermost server together?

Thank you for your response. The new server is a brand new mattermost installation, I am not trying to merge two servers.

I have considered deleting all the tables on the new server, but did not know the potential [future] risks related to that… (Also, if I went that route, I would be unsure which tables not to delete, etc.).

My assumption is that there is a postgres command that would overwrite the tables on the target instance. [But I’ve had trouble finding the right postgres command(s) to do that.]

Thank you again! :slight_smile:

Hi, @mmadmin

So, that means that you are not dumping it into a fresh database. Am I getting the right idea?

I came across the article that suggests the following:

To rewrite data, you should either drop tables first or pg_dump -F c and pg_restore -c .

I’d recommend testing it on a staging instance first if you have any.

Hi @ahmaddanial Correct, I’m testing a new instance with the omni installation method, which of course sets up the target DB with the schema for a fresh install.

I will try your postgres commands and report back. Thank you so very much!!!

p.s. yes I’m testing with a cloned prod instance, and a new target instance. Thank you again. :slight_smile:

1 Like