Validated on 9 June 2021 • Posted on 24 May 2021
PostgreSQL is an open source, object-relational database built with a focus on extensibility, data integrity, and speed. Its concurrency support makes it fully ACID compliant, and it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.
Depending on your database’s write workload, you can choose to redirect its traffic to the new target database using one of these two strategies.
Transition by Updating Connection Parameters
If your database’s workload is read-heavy, such as hosting blog entries or other static files, changing the connection parameters in the applications and clients accessing the source database is a simple strategy that results in some minor downtime but a small amount of work.
Update the connection parameters in your applications and clients to the new database’s parameters.
Restart all applications and clients that are using the new database connection parameters.
Transition Using a Proxy
If your database’s workload is write-heavy and requires you to minimize downtime as much as possible, you can use a proxy software, such as ProxySQL, to reroute the source database’s queries to the target database after the migration is complete. This allows you to reroute the source database’s queries by updating only one set of connection parameters as opposed to updating the connection parameters for all applications accessing the database.
To transition using this strategy:
Download and install your proxy software on a Droplet or other webserver. We recommend ProxySQL for MySQL, pgpool for PostgreSQL, and Sentinal for Redis.
Configure the proxy to connect to the source database.
Update all applications and clients connecting to the database to use the proxy’s hostname instead of the source database’s hostname.