Validated on 10 Feb 2022 • Last edited on 29 Nov 2023
PostgreSQL is an open source, object-relational database built for 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 one of the following strategies to redirect its traffic to the new target database:
Updating Connection Parameters
If your database’s workload is read-heavy, such as hosting blog entries or other static files, change the connection parameters in the applications and clients accessing the source database. This strategy results in downtime while you update connection parameters for all applications and clients, but does not require proxy configuration to implement.
Restart all applications and clients that are using the new database connection parameters.
Using a Proxy
If your database’s workload is write-heavy and requires minimal downtime, you can use a proxy software, such as Pgpool, to reroute the source database’s queries to the target database after the migration is complete. This strategy updates only one set of connection parameters instead of updating the connection parameters for all applications accessing the database.
Warning
If the migration suffers from replication lag, disable writing to the source database. This lag can result in data loss when you redirect queries to your target database, since it may not be entirely up to date.
To migrate using this strategy:
Download and install your proxy software on a Droplet or other web server. We recommend Pgpool for PostgreSQL.
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.
Once the target database is up to date, update the proxy with the target database’s connection parameters to redirect queries from the source database to the target database. The method to do this can vary depending on your proxy, so reference its documentation for more detailed steps.