PostgreSQL Migration and Update
PUBLIC SECTOR
August 2020 – Today (4 years and 1 month)
France, France
Public Territorial Establishment of the Dordogne basin EPIDOR
EPIDOR contacted us for performance issues when a large number of agents connect to the PostgreSQL feed from their QGIS software, particularly during field survey operations.
The client first considered upgrading Postgresql (to version 9) to resolve these performance issues.
In order to determine the exact cause(s) of the performance issues experienced by EPIDOR, we performed an audit of their databases and QGIS configuration. At the same time, a test environment identical to their infrastructure was created, in our offices, as a basis for comparison.
The audit and comparisons carried out revealed that the performance issues were not a result of database performance issues but of low bandwidth at their site in Dordogne. Given that it will be some time before they have access to fiber, we proposed a migration to AWS with a mirror copy of the databases: copy available on a server in their offices.
The proposal also included upgrading Postgres from 9 to 13 and migrating to a high availability environment including access to daily backups on their local server. In the meantime, their cloud provider experienced a catastrophic outage and was unable to provide copies of their databases for several weeks. Thus, our proposal now includes locally available backups and replication between two data centers in Paris.
This proposal was accepted and within three months all Postgres databases were migrated to AWS and upgraded to the latest version 13.
Finally, using pgpool, all read traffic is now redirected to their local Postgres server, significantly improving overall performance.