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, from the Application Developer's perspective.
Dump and Restore From Origin Instance to New Instance
This feature is only available for migrating to a9s PostgreSQL 11 and newer.
If you have an existing a9s PostgreSQL Service Instance, you can create a copy of its data by creating a new a9s Service
Instance with the custom parameter copy_from
.
The custom parameter copy_from
requires in the default case an object with the following keys:
host
: The hostname of the existing instance.username
: The username of the existing instance.password
: The password of the existing instance.
The values for the required keys can be derived from any service binding (either cf service-key
or from
cf env APP_NAME
when bound to an a9s Service Instance).
When the parameter role_privileges
with CREATEDB
has been used and there are additional databases next to the main
database, the copy_from
parameter requires the key main_database
that specifies what is the main database on the
origin a9s Service Instance. The main database name can also be derived from the binding information in the field
name
.
Be aware that when using the additional databases, you must take responsibility for handling manually created databases
during the migration once only the main_database
is fully migrated.
An example call to create a new a9s Service Instance and initiate the database with data from another a9s Service Instance looks the following way:
cf create-service a9s-postgresql postgresql-single-small new-instance-name -c '{"copy_from": {"host": "rgd25db46-psql-master-alias.node.dc1.consul.dsf2", "username": "a9s9d374017060a7d4573bc61baf714500f4d948631", "password": "a9se2fdcf291672e4555293b13c4b7119ba2f77312f"} }'
The create-service
call will validate whether the given credentials are correct. In the case of multiple databases,
the presence of a value for the main_database
will also be validated.
The databases postgres
, template0
and template
will not be copied over.
Behind the scenes, the new a9s Service Instance will copy over the data using pg_dump
and pg_restore
calls. The
pg_dump
call receives the command line arguments
-O -x --quote-all-identifiers --lock-wait-timeout=300s --verbose --format=custom
.
If an error happens during the time the a9s Service Instance copies the data from the old a9s Service Instance to the
new a9s Service Instance, Cloud Foundry will just report back the instance failed. To receive more information why the
copy process failed, it is recommended to specify a syslog endpoint via custom parameter to receive the logs from the
copy process. The copy process reports progress and error lines with the text copy_from
included.
The credentials stored from the origin a9s Service Instance on the new a9s Service Instance will disappear the next time the instance gets updated.
Migrating to a9s PostgreSQL 15 and Above
With PostgreSQL 15, the creation permission was removed from the public
schema for regular users, and the owner was
changed to the pg_database_owner
role. Therefore, users under the cfuser
role can no longer create tables or other
objects on the public
schema. Due to this change, a new schema called shared
was introduced to ensure the same
behavior as before.
When copy_from
is used to migrate from an older version of PostgreSQL to version 15 or greater, all data from the
public
schema will be automatically migrated to the new shared
schema. This is done by renaming the restored schema
public
to shared
.
Caveats
- This feature is only supported when used to copy over data from a9s Service Instances that have either an older or the
same major version as the new a9s Service Instance. This means that
11 -> 13
,13 -> 15
or11 -> 11
for example are supported. Backwards migration such as13 -> 11
is not supported by this feature. This is due limitations in the PostgreSQLpg_dump
utility. - This feature only supports instances where PITR is disabled.
- It is recommended to try out the
copy_from
procedure and to validate all expected data has been restored before doing the real switch. - If possible, this feature should be executed at a time the database has not much load.
- This feature has been tested with example feature sets for the following supported extensions:
'postgis', 'uuid-ossp', 'ltree','pgcrypto', 'citext', 'vector'
. Your experience may differ when using advanced features of these extensions. - When migrating data you might need more space than the old a9s Service Instance currently requires due to WAL files occupying additional space. For this reason, we recommend always copying to a single, and then updating from single to cluster. You need to pay special attention to this if using continuous archiving since uploading WAL files takes a long time compared to WAL files being consumed by replication.
- All objects in all databases being copied MUST be owned by the
cfuser
role.
Keeping Track of the Migration Progress
This monitoring approach is given for informational purposes only. Please keep in mind that the total processing time and the progress' pace might fluctuate depending on various factors, such as:
- Your environment.
- Your Service Plan.
- The size of the records.
- The presence of indices and constraints.
You can estimate your current progress by making use of the metric_pg_database_size
on the target a9s PostgreSQL Service Instance and by observing its available metrics, which allows you to extrapolate in
order to have an approximate processing time.
For example, let us assume that you have a database that is 100GB, and you decide to monitor after the progress after 10
minutes and notice that the progress is currently at 120MB, after 20 mins 250MB; with this you can initially estimate a
duration for the whole process by comparing the current size to the known size of the source a9s PostgreSQL Service
Instance's original database, with the possibility of recalculating when it slows down due to expensive transactions
(e.g REINDEX
).