Migration Strategies

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.

To migrate using this strategy:

1. Start migrating your data from the source database to the target database.
2. When you are ready to cut over, stop all writes to the source database.
3. Update the connection parameters in your applications and clients to the new database’s parameters.
4. Stop the migration in the DigitalOcean Control Panel.
5. 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:

1. Download and install your proxy software on a Droplet or other web server. We recommend Pgpool for PostgreSQL.
2. Configure the proxy to connect to the source database.
3. Update all applications and clients connecting to the database to use the proxy’s hostname instead of the source database’s hostname.
4. Start migrating your data from the source database to the target database.
5. 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.
6. Stop the migration in the DigitalOcean Control Panel.