Migration
This document describes options to migrate data from an older a9s PostgreSQL Service Instance using an older PostgreSQL version to an a9s PostgreSQL Service Instance with a higher version.
Custom Parameter copy_from
The application developer can migrate data from an older instance to a newer one using the custom parameter copy_from,
see Dump and Restore From Origin Instance to New Instance.
If an error happens during the migration and no syslog endpoint has been specified, the platform operator can take a look at the first BOSH VM of the deployment in the following files:
/var/vcap/sys/log/postgresql-ha/post-start.*/var/vcap/sys/log/postgresql-ha/copy_from.*
Ensure that all objects in all databases belong to the cfuser before running copy_from.
Leveraging maintenance_work_mem
Depending on the size of your database the execution of copy_from can take a long time to finish due to the operations
that it uses. As explained here
the copy_from custom parameter makes use of the pg_dump and pg_restore calls behind the scenes, this allows us to
leverage the value of maintenance_work_mem property to cut down on execution time, as this parameter is the maximum
amount of memory that certain (maintenance) operations, such as VACCUM or INDEX, can use.
If you choose to follow this approach, please keep in mind that the value of maintenance_work_mem must be set when
starting the copy_from process and should be set back to its previous value after the process finishes.
This means that, if you decide to increase the value of maintenance_work_mem from 64MB to 120MB, you should set it
back to 64MB at the end of the migration process.