Migrating from PostgreSQL in Cluster
Some versions of ESS previously included a built-in PostgreSQL server for quick proof of concept setups. However, this option is not suitable for production due to limitations in performance, scalability, and maintenance.
We strongly recommend using your own PostgreSQL server for production environments to ensure optimal reliability and security.
For customers who have already deployed using the PostgreSQL in Cluster, please follow this guide to migrate.
Setting up the PostgreSQL Server
Before you begin, you will need to deploy an external (to ESS) PostgreSQL Server.
How you deploy PostgreSQL is out-of-scope for this document, please refer to the PostgreSQL Documentation.
Dumping and Restoring the Database
A number of components require a PostgreSQL Database to function, for each, you will need to create the new target databases on your external PostgreSQL server that you can then restore to.
You will need to use two commands:
Warning
It is strongly advised to stop your ESS deployment to dump each database, this will prevent rooms becoming out-of-sync upon restore.
pg_dump
Connect to each PostgreSQL pod, and use the pg_dump command to create a dump of the database that can be used to restore into your external PostgreSQL server.
Tip
It is highly advised to use the official PostgreSQL pg_dump Documentation for the most up-to-date information.
Connecting to a Pod
You can use a command like so, to connect to a PostgreSQL pod:
# kubectl --namespace <namespace> exec --container <container> --stdin --tty <pod> -- bash
kubectl --namespace element-onprem exec --container postgres --stdin --tty synapse-postgres-0 -- bash
Once you have a shell on the pod, you can use the following to connect to it's PostgreSQL:
The variables POSTGRES_DB, POSTGRES_USER, and POSTGRES_PASSWORD are already set on the PostgreSQL pod, so you do not need to know or find the values. Just paste the psql command as it is above and press enter.
Once connected via psql you can use \l to list the Databases on the pod.
Take note of the Encoding, Collate and Ctype. Per the Synapse Documentation the database must use UTF8 encoding and C locale. You will need to ensure the nw target database on your external PostgreSQL server is configured to use the same.
Dumping the Database
Connect to the PostgreSQL pod, then use pg_dump to create a dump of the database.
You can then use the kubectl cp command, to copy the dump out of the pod onto your local machine, ready to transfer to your external PostgreSQL server.
# kubectl cp <namespace>/<pod>:<path-in-pod> <local-path>
kubectl cp element-onprem/synapse-postgres-0:/postgresdump ./postgresdump -c postgres
pg_restore
Connect to your external PostgreSQL server, transfer over the dump of the database that will be used to restore the database from the PostgreSQL in Cluster.
Tip
It is highly advised to use the official PostgreSQL pg_restore Documentation for the most up-to-date information.
You will need to use the following command to perform the restore:
Configuring ESS to use an external PostgreSQL Server
You will find the option to configure the use of PostgreSQL in Cluster towards the bottom of the Host section:
If opting to update the cluster.yml config directly, you can switch to using an external PostgreSQL server by simply setting the postgresInCluster config option under the microk8s section to {}.
-
When PostgreSQL in Cluster is enabled, the config for this will look something like:
Once changed, you will need to ensure you update any components relying on a PostgreSQL database are configured to point at you external PostgreSQL server (and their respective restored databases).
Info
Changing this setting will not delete the existing Postgres in Cluster pods / databases. This is to ensure no data is lost, and allows you to partially migrate from Postgres in Cluster. See Partially migrating from PostgreSQL in Cluster for more details.
Within affected components / integrations, you will find an empty PostgreSQL section that should be completed:
If opting to update the deployment.yml config directly, you should find the associated PostgreSQL section under the component.
spec:
components:
compent_name:
config:
postgresql: # (1)!
database: db_name
host: host
passwordSecretKey: postgresPassword
user: username
- Some components may structure the PostgreSQL section differently, always refer to the documentation for each section to confirm exact placement.
Migrating GroupSync from Matrix Room to PostgreSQL
The GroupSync originally stored it's configuration within a dedicated Matrix room, however support to store this data within PostgreSQL has been added and storing runtime data within Matrix room is considered legacy.
Warning
You should always re-enable Dry Run mode whenever making significant changes to GroupSync configuration. This will allow you to verify any changes made are as intended (via the pod logs) before it makes any active changes to users' / room membership.
Switching from Matrix storage to PostgreSQL storage will enable an automatic data migration during the next startup. You should wait for this process to complete successfully - once completed, if still using PostgreSQL in Cluster, you can migrate this database using the guide above.
Note
The Matrix room which currently holds the GroupSync configuration data will not be automatically deleted, as as such works as a backup should the migration not complete successfully. However you may wish to delete this room following a successful migration to save database space, you can do this by using the Synapse Admin Delete Room API, see Getting Started Using the Admin API for help using the API.
You must not delete the Matrix room until after a successful migration, otherwise you risk data loss, and the migration failing with GroupSync failing as it cannot find the room.
You will find the option to configure how GroupSync stores it's realtime data at the top of the GroupSync integration section:
If opting to update the deployment.yml config directly, you can enable this by simply adding the postgresql config option under the groupsync components' config.
- When PostgreSQL in Cluster is enabled, the config for this will simply be
{}.
In the event you are switching whilst using an external PostgreSQL server, you must fill out the PostgreSQL config section:
spec:
components:
groupsync:
config:
postgresql:
database: db_name
host: host
passwordSecretKey: postgresPassword
user: username
Once you have deployed with updated GroupSync config to use PostgreSQL, the automatic migration will occur.
If the migration fails, the GroupSync pod will begin CrashLooping - to resolve this, you can immediately redeploy back to GroupSync using the Matrix room for configuration. Before doing so, please export the full logs from the pod and contact support to identify the issue impacting migration.
Partially migrating from PostgreSQL in Cluster
Warning
Whilst partial migration is possible, it is highly recommended to ensure full migration away from Postgres in Cluster shortly after any partial migration. You should ensure all data is migrated and each component is correctly configured to use an external PostgreSQL server.
In the event you wish to only migrate some components to an external PostgreSQL server, but are not ready to migrate all others, you will need to disable PostgreSQL in Cluster and update all components to point to either the PostgreSQL in Cluster deployed databases or the external PostgreSQL server.
Follow Configuring ESS to use an external PostgreSQL Server to configure your deployment to use an external PostgreSQL server.
Then update all components' PostgreSQL sections not migrating to refer to the PostgreSQL in Cluster databases', the simplest way to do this is to use the following command to get a copy of your existing deployment:
kubectl get elementdeployment first-element-deployment -n element-onprem -o yaml > element-deployment.yaml
Then find each component using PostgreSQL in Cluster, and pull the configuration to use, i.e. for Synapse the config may look like:
spec:
components:
synapse:
config:
postgresql:
allowUnsafeLocale: false
connectionPool:
maxConnections: 10
minConnections: 5
database: synapse
host: synapse-postgres.element-onprem.svc.cluster.local
passwordSecretKey: postgresPassword
port: 5432
sslMode: disable
user: synapse_user
Tip
For passwords, you can use the following command on each PostgreSQL Secret to extract the password:
# kubectl get secrets -n <name_space> | grep postgres # Get all postgres secrets in the namespace
# kubectl get secret <secret_name> -n <name_space> -o jsonpath="{.data.POSTGRES_PASSWORD}" | base64 --decode # Get a secrets' password
kubectl get secret integrator-postgres -n element-onprem -o jsonpath="{.data.POSTGRES_PASSWORD}" | base64 --decode
You can get all PostgreSQL Secrets' passwords using the following:
for secret in $(kubectl get secrets -n element-onprem -o jsonpath='{.items[*].metadata.name}' | tr ' ' '\n' | grep postgres); do
echo "Secret: $secret"
kubectl get secret "$secret" -n element-onprem -o jsonpath="{.data.POSTGRES_PASSWORD}" | base64 --decode
echo -e "\n"
done
Which will output something like:
After updating all non-migrating components to use the Postgres in Cluster databases, you can then configure the remaining components to use your external PostgreSQL server via PostgreSQL config: