How to import an existing PostgreSQL database with matrix-docker-ansible-deploy

Have you tried to import an existing Postgres database with the matrix-docker-ansible-deploy playbook, but stumbled upon errors such as ’ERROR: role ”synapse_user” does not exist’, or ’ERROR: relation ”access_tokens” already exists’? Then this guide is for you! (I hope)

The cause of the errors above is that the database has already been populated when Synapse started (or if the database lacks data), which results in not being able to import the existing database into the populated one.

To solve this you need to do the following:

Stop all Matrix services (with a command similar to),

ansible-playbook -i inventory/hosts setup.yml --tags=stop

then delete the existing Postgres data,

rm -rf /matrix/postgres/data/*

then reconfigure Postgres (without starting any services!),

ansible-playbook -i inventory/hosts setup.yml --tags=setup-postgres

Now you should be ready to import the existing database. Run the import command,

ansible-playbook -i inventory/hosts setup.yml \
--extra-vars='server_path_postgres_dump=<server-path-to-postgres-dump.sql>' \
--tags=import-postgres

and if it succeeds you should be ready to start all services again with,

ansible-playbook -i inventory/hosts setup.yml --tags=start

and you should be good to go!

I hope it works, and thanks for reading! 🙂

Sauce:
https://github.com/spantaleev/matrix-docker-ansible-deploy
https://github.com/spantaleev/matrix-docker-ansible-deploy/blob/master/docs/importing-postgres.md
and the lovely folks at #matrix-docker-ansible-deploy:devture.com