Database Migration Strategies

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.

To transition 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. Stop the migration within the DigitalOcean Control Panel.
  4. Update the connection parameters in your applications and clients to the new database’s parameters.
  5. 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:

  1. Download and install your proxy software on a Droplet or other webserver. We recommend ProxySQL for MySQL, pgpool for PostgreSQL, and Sentinal for Redis.
  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. When you’re ready to redirect queries to the target database, stop the migration in the DigitalOcean Control Panel.
  6. Update the proxy with the target database’s connection parameters to redirect queries from the source database to the target database.
Note
If you notice replication lag during the migration process, you should ensure nothing is writing to the database before pointing the proxy at the target database, otherwise data loss may occur.