Skip to main content

PostgreSQL

PostgreSQL is an object-relational database management system. It is open source and designed to be extensible.

You can access metrics and logs for this backing service.

Set up the service

Set up a PostgreSQL service

To set up a PostgreSQL service:

  1. Run the following code in the command line to see what plans are available for PostgreSQL:

    cf marketplace -e postgres
    

    Here is an example of the output you will see (the exact plans will vary):

    service plan             description                                                                                                                                                       free or paid   costs   available
    small-ha-12              100GB Storage, Dedicated Instance, Highly Available, Storage Encrypted, Max 200 Concurrent Connections. Postgres Version 12. DB Instance Class: db.t3.small.      paid                   yes
    medium-12                100GB Storage, Dedicated Instance, Storage Encrypted, Max 500 Concurrent Connections. Postgres Version 12. DB Instance Class: db.m5.large.                        paid                   yes
    ...
    large-12                 512GB Storage, Dedicated Instance, Storage Encrypted, Max 5000 Concurrent Connections. Postgres Version 12. DB Instance Class: db.m5.2xlarge.                     paid                   yes
    

    The syntax in this output is explained in the following table:

    Syntax Meaning
    ha High availability
    small / medium / large / xlarge Size of instance
    size-X Version number

    More information can be found in the PostgreSQL plans section.

  2. Run the following code in the command line:

    cf create-service postgres PLAN SERVICE_NAME
    

    where PLAN is the plan you want, and SERVICE_NAME is a unique descriptive name for this service instance. For example:

    cf create-service postgres small-12 my-pg-service
    

    You should use a high-availability (ha) encrypted plan for production apps.

  3. It will take between 5 and 10 minutes to set up the service instance. To check its progress, run:

    cf service SERVICE_NAME
    

    for example:

    cf service my-pg-service
    

    The service is set up when the cf service SERVICE_NAME command returns a create succeeded status. Here is an example of the output you will see:

    Service instance: my-pg-service
    Service: postgres
    Bound apps:
    Tags:
    Plan: small-12
    Description: AWS RDS PostgreSQL service
    Documentation url: https://aws.amazon.com/documentation/rds/
    Dashboard:
    
    Last Operation
    Status: create succeeded
    Message: DB Instance 'rdsbroker-9f053413-97a5-461f-aa41-fe6e29db323e' status is 'available'
    Started: 2016-08-23T15:34:41Z
    Updated: 2016-08-23T15:42:02Z
    

Bind a PostgreSQL service to your app

You must bind your app to the PostgreSQL service so you can access the database from the app.

  1. Use the app’s manifest to bind the app to the service instance. It will bind automatically when you next deploy your app. An example manifest:

    --
    applications:
    - name: my-app
      services:
      - my-pg-service
    
  2. Deploy your app in line with your normal deployment process.

This binds your app to a service instance called my-pg-service.

Refer to the Cloud Foundry documentation on deploying with app manifests for more information.

Use the cf bind-service command

Alternatively, you can manually bind your service instance to your app.

  1. Run the following:

    cf bind-service APP_NAME SERVICE_NAME
    

    where APP_NAME is the exact name of a deployed instance of your application and SERVICE_NAME is the name of the service instance you created. For example:

    cf bind-service my-app my-pg-service
    
  2. Deploy your app in line with your normal deployment process.

Bind your app with a read-only user

You can also bind your service instance with a read-only user to your app.

  1. Run the following:

    cf bind-service APP_NAME SERVICE_NAME -c '{"read_only": true}'
    

    where APP_NAME is the exact name of a deployed instance of your application and SERVICE_NAME is the name of the service instance you created. For example:

    cf bind-service my-app my-pg-service -c '{"read_only": true}'
    
  2. Deploy your app in line with your normal deployment process.

Warning By default Postgres uses a schema called “public”, on which all permissions are granted to all.

We disable use of the public schema to read-only users via an event trigger. As a precaution, if you are very concerned about permissions, you should create a new schema and use it, instead of relying on the default “public” schema.

Connect to a PostgreSQL service from your app

Your app must make a Transport Layer Security (TLS) connection to the service. Some libraries use TLS by default, but others will need to be manually configured.

GOV.UK PaaS will automatically parse the VCAP_SERVICES environment variable to get details of the service and then set the DATABASE_URL variable to the first database found.

If your app writes database connection errors to STDOUT or STDERR, you can view recent errors with cf logs APP_NAME --recent. See the section on logs for details.

Connect to a PostgreSQL service from your local machine

We have created the Conduit plugin to simplify the process of connecting your local machine to a PostgreSQL service. To install this plugin, run the following code from the command line:

cf install-plugin conduit

Once the plugin has finished installing, run the following code in the command line to access an SQL shell for your backing service:

cf conduit SERVICE_NAME -- psql

where SERVICE_NAME is a unique descriptive name for this service instance.

Conduit can be used with a read-only binding using the -c flag:

cf conduit SERVICE_NAME -c '{"read_only": true}' -- psql

Run cf conduit --help for more options, and refer to the Conduit README file for more information on how to use the plugin.

The read_only flag is a new feature and may require one-time user action to work with databases that were created before the feature was introduced.

If you encounter an error message like ERROR: permission denied for table my_table when running SELECT * FROM my_table from a read-only conduit, then you should run SELECT make_readable_generic() from a non-read-only conduit.

Permissions are automatically converged for read-only users when database objects are created, for example CREATE TABLE my_table, so the above action may not be necessary.

Amend the service

Import and export bulk data to and from a PostgreSQL database

Prerequisites

You must:

Non-PaaS to PaaS

To move data from a non-PaaS PostgreSQL database to a PaaS PostgreSQL database:

  1. Run the following command in the CLI to export data from the non-PaaS database to an SQL data file:

    pg_dump --host HOST_NAME --file DATA_FILE_NAME DATABASE_NAME
    

    where:

    • HOST_NAME is the name of your host
    • DATA_FILE_NAME is the SQL data file
    • DATABASE_NAME is the name of the non-PaaS source database
  2. Use the Conduit plugin to import the data file into the PaaS database by running:

    cf conduit SERVICE_NAME -- psql < DATA_FILE_NAME
    

    where SERVICE_NAME is a unique descriptive name for this service instance, and DATA_FILE_NAME is the SQL file created in the previous step.

You can only use certain PostgreSQL extensions.

PaaS to PaaS

To move data between two PaaS-hosted PostgreSQL databases:

  1. Use the Conduit plugin to connect to the source database and export the data into an SQL file by running:

    cf conduit SERVICE_NAME -- pg_dump --file DATA_FILE_NAME --no-acl --no-owner
    

    where SERVICE_NAME is a unique descriptive name for this service instance and DATA_FILE_NAME is the SQL data file created by the pg_dump command.

  2. Run the following command to import the data file into the target database:

     cf conduit DESTINATION_SERVICE_NAME -- psql < DATA_FILE_NAME
    

    where DESTINATION_SERVICE_NAME is the name of the target database.

Contact the PaaS team at gov-uk-paas-support@digital.cabinet-office.gov.uk if you have any questions.

Upgrade PostgreSQL service plan

Same encryption type

If your new plan uses the same encryption type as your current plan, you can upgrade your plan using the cf update-service command. Run the following in the command line:

cf update-service SERVICE_NAME -p NEW_PLAN_NAME

where SERVICE_NAME is a unique descriptive name for this service instance, and NEW_PLAN_NAME is the name of your new plan. For example:

cf update-service my-pg-service -p small-ha-12

The plan upgrade will start immediately and finish within an hour. You can check the status of the upgrade by running cf services.

You can also queue a plan upgrade to happen during a maintenance window to minimise service interruption.

Amazon automatically patch your database during the configured maintenance window. Updates are usually released more regularly than they are automatically applied. You can use the update_minor_version_to_latest configuration parameter to update your database to the latest available minor version:

cf update-service SERVICE_NAME -c '{"update_minor_version_to_latest": true}'

Different encryption type

You cannot upgrade your service plan using the cf update-service command if the new plan uses a different encryption type to your current plan.

To upgrade, you must set up a new service and migrate your app data.

  1. Set up a new PostgreSQL service with a new plan that has a different encryption type to your current plan.
  2. Move your app data from the current service to the new service by following the import and export bulk data documentation.

Downgrade PostgreSQL service plan

You cannot currently downgrade your service plan.

Reboot a PostgreSQL service instance

You can reboot your PostgreSQL service instance to:

  • try to fix a problem with your service instance
  • test how your app behaves during a service instance failure

To reboot your service instance, you must have the space developer role and permissions in the space that hosts your service instance.

Rebooting your service instance will cause a temporary database outage. The length of this outage depends on your service instance’s complexity and configuration.

You should tell your users and your team when a reboot is scheduled.

Run the following code in the command line to reboot your service instance:

cf update-service SERVICE_NAME -c '{"reboot": true}'

where SERVICE_NAME is a unique descriptive name for this service instance.

Force a failover

If you have a highly available service instance, you can force a failover when you reboot that service instance. You can use this to test how your app behaves when a failover happens.

Run the following code to reboot your highly available service and force a failover:

cf update-service SERVICE_NAME -c '{"reboot": true, "force_failover": true}'

When you force a failover, your PostgreSQL database IP address will change. The database’s hostname will not change. You must configure your app to close all database connections to the previous IP address after forcing a failover.

Add or remove extensions for a PostgreSQL service instance

The following extensions are always enabled for PostgreSQL service instances:

 
Extension PostgreSQL version
uuid-ossp 10, 11, 12
citext 10, 11, 12
 

You cannot remove these mandatory extensions from a PostgreSQL service instance.

GOV.UK PaaS supports the following optional extensions, but they are not enabled by default:

 
Extension PostgreSQL version
address_standardizer 10, 11, 12
address_standardizer_data_us 10, 11, 12
bloom 10, 11, 12
btree_gin 10, 11, 12
btree_gist 10, 11, 12
chkpass 10
cube 10, 11, 12
dblink 10, 11, 12
dict_int 10, 11, 12
dict_xsyn 10, 11, 12
earthdistance 10, 11, 12
fuzzystrmatch 10, 11, 12
hstore 10, 11, 12
hstore_plperl 10, 11, 12
intagg 10, 11, 12
intarray 10, 11, 12
ip4r 10, 11, 12
isn 10, 11, 12
libprotobuf 10, 11, 12
log_fdw 10, 11, 12
ltree 10, 11, 12
orafce 10, 11, 12
pageinspect 10, 11, 12
pg_buffercache 10, 11, 12
pg_freespacemap 10, 11, 12
pg_hint_plan 10, 11, 12
pg_prewarm 10, 11, 12
pg_repack 10, 11, 12
pg_similarity 10, 11, 12
pg_stat_statements 10, 11, 12
pgtap 11, 12
pg_transport 11, 12
pg_trgm 10, 11, 12
pg_visibility 10, 11, 12
pgaudit 10, 11, 12
pgcrypto 10, 11, 12
pglogical 10, 11, 12
pgrouting 10, 11, 12
pgrowlocks 10, 11, 12
pgstattuple 10, 11, 12
plcoffee 10, 11, 12
plls 10, 11, 12
plperl 10, 11, 12
plpgsql 10, 11, 12
pltcl 10, 11, 12
plv8 10, 11, 12
postgis 10, 11, 12
postgis_tiger_geocoder 10, 11, 12
postgis_topology 10, 11, 12
postgres_fdw 10, 11, 12
postgresql-hll 10, 11, 12
prefix 10, 11, 12
sslinfo 10, 11, 12
tablefunc 10, 11, 12
test_parser 10, 11, 12
tsearch2 10
tsm_system_rows 10, 11, 12
tsm_system_time 10, 11, 12
unaccent 10, 11, 12
 

You can enable or disable optional extensions for a PostgreSQL service instance when:

  • creating a new service instance
  • updating an existing service instance
  • restoring a PostgreSQL service instance snapshot

Enable an optional extension when creating a service instance

You create a new PostgreSQL service instance by running cf create-service. You can enable optional extensions in this new service instance by running:

cf create-service SERVICE_NAME -c '{"enable_extensions": ["EXTENSION_1","EXTENSION_2",...,"EXTENSION_N"]}'

where:

  • SERVICE_NAME is a unique descriptive name for this service instance
  • EXTENSION_1...N are the names of the optional extensions you want to enable

Enable an optional extension in an existing service instance

When you enable optional extensions in an existing service instance, you must also reboot that service instance.

Run the following to enable optional extensions in an existing service instance:

cf update-service SERVICE_NAME -c '{"enable_extensions": ["EXTENSION_1","EXTENSION_2",...,"EXTENSION_N"], "reboot": true}'

where:

  • SERVICE_NAME is a unique descriptive name for this service instance
  • EXTENSION_1...N are the names of the optional extensions you want to enable

For example, your PostgreSQL service instance is named my-pg-service and has 2 mandatory extensions enabled, uuid-ossp and citext. Run the following to enable pg_stat_statements:

cf update-service my-pg-service -c '{"enable_extensions": ["pg_stat_statements"], "reboot": true}'

Rebooting a service instance will cause some service downtime. The length of this outage depends on your service instance’s complexity and configuration.

You should schedule this downtime to minimise service disruption. Refer to the documentation on rebooting a service instance for more information.

Disable an optional extension in an existing service instance

When you disable optional extensions in a service instance, you must also reboot that service instance.

Run the following to disable optional extensions from a service instance:

cf update-service SERVICE_NAME -c '{"disable_extensions": ["EXTENSION_1","EXTENSION_2",...,"EXTENSION_N"], "reboot": true}'

where:

  • SERVICE_NAME is a unique descriptive name for this service instance
  • EXTENSION_1...N are the names of the optional extensions you want to disable

For example, your PostgreSQL service instance is named my-pg-service and has the following extensions enabled, uuid-ossp, postgis, citext and pg_stat_statements. Run the following to disable pg_stat_statements:

cf update-service my-pg-service -c '{"disable_extensions": ["pg_stat_statements"], "reboot": true}'

Rebooting a service instance will cause some service downtime. You should schedule this downtime to minimise service disruption. Refer to the documentation on rebooting a service instance for more information.

Enable an optional extension when restoring a service instance

When you restore a PostgreSQL service instance from a snapshot, that restored service instance will include all extensions enabled at time of backup.

You can also enable optional extensions when restoring a service instance:

cf create-service postgres PLAN NEW_SERVICE_NAME -c '{"restore_from_latest_snapshot_of": "GUID", "enable_extensions": ["EXTENSION_1","EXTENSION_2",...,"EXTENSION_N"]}'

where:

  • PLAN is the plan used in the original service instance
  • NEW_SERVICE_NAME is a unique, descriptive name for the restored service instance
  • GUID is the global unique identifier of the backed up service instance
  • EXTENSION_1...N are the names of the optional extensions you want to enable

Refer to the documentation on restoring a PostgreSQL service snapshot for more information.

Remove the service

Unbind a PostgreSQL service from your app

You must unbind the PostgreSQL service before you can delete it. To unbind the PostgreSQL service, run the following code in the command line:

cf unbind-service APPLICATION SERVICE_NAME

where APPLICATION is the name of a deployed instance of your application (exactly as specified in your app’s manifest.yml file or push command) and SERVICE_NAME is a unique descriptive name for this service instance, for example:

cf unbind-service my-app my-pg-service

If you unbind your services from your app but do not delete them, the services will persist even after your app is deleted, and you can re-bind or re-connect to them in future.

Delete a PostgreSQL service

Once the PostgreSQL service has been unbound from your app, you can delete it. Run the following code in the command line:

cf delete-service SERVICE_NAME

where SERVICE_NAME is a unique descriptive name for this service instance.

Type yes when asked for confirmation.

Maintaining the service

Data classification

You can store data classified up to ‘official’ on the GOV.UK PaaS. Refer to the data security classification documentation for more information.

PostgreSQL plans

Each service in the marketplace has multiple plans that vary by availability and storage capacity.

You can view available plans through the Cloud Foundry command line interface, or via the GOV.UK PaaS admin marketplace.

Some service plans are paid and we will bill you based on your service usage.

New organisations cannot access paid plans by default. Enabling this access is controlled by an organisation’s quota settings.

If paid plans are not enabled, when you try to use a paid service you will receive an error stating “service instance cannot be created because paid service plans are not allowed”. One of your org managers must contact us at gov-uk-paas-support@digital.cabinet-office.gov.uk to request that we enable paid services.

There is a free plan available with limited storage which should only be used for development or testing, but not production.

Encrypted plans - PostgreSQL

All plans have encryption at rest unless stated otherwise. This means that both the data on the disk and in snapshots is encrypted.

High availability plans - PostgreSQL

We recommend you use a high availability (HA) plan for your PostgreSQL services. These plans use Amazon RDS Multi-AZ instances, which are designed to be 99.95% available. See Amazon’s Service Level Agreement (SLA) for details.

When you use a high availability plan, Amazon RDS provides a hot standby service for failover in the event that the original service fails.

Refer to the Amazon RDS documentation on the failover process for more information.

You should test how your app deals with a failover to make sure you are benefiting from the high availability plan. Contact us at gov-uk-paas-support@digital.cabinet-office.gov.uk to arrange for us to trigger a failover for you.

Read replicas - PostgreSQL

Amazon RDS has the capability to provide a read-only copy of your database known as a read replica. This can be useful for performance, availability or security reasons.

Refer to the Amazon RDS documentation on read replicas for more information.

GOV.UK PaaS does not currently support read replicas, but if you think you would find them useful, please contact us at gov-uk-paas-support@digital.cabinet-office.gov.uk, providing details of your use case.

PostgreSQL maintenance & backups

PostgreSQL maintenance times

Each PostgreSQL service you create will have a randomly-assigned weekly 30 minute maintenance window, during which there may be brief downtime. Select a high availability (HA) plan to minimise this downtime. Minor version upgrades (for example from 12.13 to 12.15) are applied during this maintenance window.

Contact us at gov-uk-paas-support@digital.cabinet-office.gov.uk to find out the default time of your maintenance window. Window start times will vary from 22:00 to 06:00 UTC.

You can set your own maintenance window by running cf update-service in the command line and setting the preferred_maintenance_window custom parameter:

cf update-service SERVICE_NAME -c '{"preferred_maintenance_window": "START_DAY:START_TIME-END_DAY:END_TIME"}'

where SERVICE_NAME is a unique, descriptive name for this service instance, for example:

cf update-service my-pg-service -c '{"preferred_maintenance_window": "Tue:04:00-Tue:04:30"}'

For more information on maintenance times, refer to the Amazon RDS Maintenance documentation.

Queue a plan migration - PostgreSQL

Migrating to a new plan may cause interruption to your service instance. To minimise interruption, you can queue the change to begin during a maintenance window by running the following code in the command line:

cf update-service SERVICE_NAME -p PLAN -c '{"apply_at_maintenance_window": true, "preferred_maintenance_window": "START_DAY:START_TIME-END_DAY:END_TIME"}'

where SERVICE_NAME is a unique, descriptive name for this service instance and PLAN is the plan that you are upgrading to, for example:

cf update-service my-pg-service -p small-ha-12 -c '{"apply_at_maintenance_window": true, "preferred_maintenance_window": "wed:03:32-wed:04:02"}'

Passing the preferred_maintenance_window parameter will alter the default maintenance window for any future maintenance events required for the database instance.

You can only migrate your service if the new plan has the same encryption type as your current plan.

PostgreSQL service backup

The data stored within any PostgreSQL service you create is backed up using the standard Amazon RDS backup system if you are using a paid plan. Your data is not backed up if you are using the free plan.

Backups are taken nightly at some time between 22:00 and 06:00 UTC. Data is retained for 7 days.

There are two ways you can restore data to an earlier state:

  1. You can restore to the latest snapshot. Refer to Restoring a PostgreSQL service snapshot for details.

  2. You can restore to any point from 5 minutes to 7 days ago, with a resolution of one second. Refer to Restoring a PostgreSQL service from a point in time for details.

Note that data restore will not be available in the event of an RDS outage that affects the entire Amazon availability zone.

For more details about how the RDS backup system works, see Amazon’s DB instance backups documentation.

Restoring a PostgreSQL service snapshot

You can create a copy of any existing PostgreSQL service instance using the latest snapshot of the RDS instance. These snapshots are taken during the PostgreSQL nightly backups.

This can be useful if you want to clone a production database to be used for testing or batch processing.

To restore from a snapshot:

  1. Get the global unique identifier (GUID) of the existing instance by running the following code in the command line:

    cf service SERVICE_NAME --guid
    

    where SERVICE_NAME is the name of the PostgreSQL service instance you want to copy. For example:

    cf service my-pg-service --guid
    

    This returns a GUID in the format XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX, for example 32938730-e603-44d6-810e-b4f12d7d109e.

  2. Trigger the creation of a new service based on the snapshot by running:

    cf create-service postgres PLAN NEW_SERVICE_NAME -c '{"restore_from_latest_snapshot_of": "GUID"}'
    

    where PLAN is the plan used in the original instance (you can find this out by running cf service SERVICE_NAME), and NEW_SERVICE_NAME is a unique, descriptive name for this new instance. For example:

    cf create-service postgres small-12 my-pg-service-copy  -c '{"restore_from_latest_snapshot_of": "32938730-e603-44d6-810e-b4f12d7d109e"}'
    
  3. It takes between 5 to 10 minutes for the new service instance to be set up. To find out its status, run:

    cf service NEW_SERVICE_NAME
    

    for example:

    cf service my-pg-service-copy
    
  4. The new instance is set up when the cf service NEW_SERVICE_NAME command returns a create succeeded status. See Set up a PostgreSQL service for more details.

This feature has the following limitations:

  • You can only restore the most recent snapshot from the latest nightly backup
  • You cannot restore from a service instance that has been deleted
  • You must use the same service plan for the copy as for the original service instance
  • You must create the new service instance in the same organisation and space as the original. This is to prevent unauthorised access to data between spaces. If you need to copy data to a different organisation and/or space, you can connect to your PostgreSQL instance from a local machine using Conduit.

By default, the database is restored to the most recent snapshot. If you need to restore to a snapshot before a certain date and time, you can use the restore_from_latest_snapshot_before parameter.

For example:

cf create-service postgres small-12 my-pg-service-copy  -c '{"restore_from_latest_snapshot_of": "32938730-e603-44d6-810e-b4f12d7d109e", "restore_from_latest_snapshot_before": "2020-04-01 13:00:00"}'

will create a new database from the most recent snapshot created before April 1st 2020 13:00 UTC.

Restoring a PostgreSQL service from a point in time

You can create a copy of any existing PostgreSQL service instance using the write-ahead log of the RDS instance. The database generates write-ahead log checkpoints during normal use and stores them every 5 minutes. You can restore to a point in time with a resolution of 1 second.

To restore from a point in time:

  1. Get the global unique identifier (GUID) of the existing instance by running the following code in the command line:

    cf service SERVICE_NAME --guid
    

    where SERVICE_NAME is the name of the PostgreSQL service instance you want to copy. For example:

    cf service my-pg-service --guid
    

    This returns a GUID in the format XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX, for example 32938730-e603-44d6-810e-b4f12d7d109e.

  2. Trigger the creation of a new service based on the snapshot by running:

    cf create-service postgres PLAN NEW_SERVICE_NAME -c '{"restore_from_point_in_time_of": "GUID"}'
    

    where PLAN is the plan used in the original instance (you can find this out by running cf service SERVICE_NAME), and NEW_SERVICE_NAME is a unique, descriptive name for this new instance. For example:

    cf create-service postgres large-11 my-pg-service-copy  -c '{"restore_from_point_in_time_of": "32938730-e603-44d6-810e-b4f12d7d109e"}'
    
  3. It takes between 5 to 10 minutes for the new service instance to be set up. To find out its status, run:

    cf service NEW_SERVICE_NAME
    

    for example:

    cf service my-pg-service-copy
    
  4. The new instance is set up when the cf service NEW_SERVICE_NAME command returns a create succeeded status. See Set up a PostgreSQL service for more details.

This feature has the following limitations:

  • You cannot restore from a service instance that has been deleted
  • You must use the same service plan for the copy as for the original service instance
  • You cannot restore to a point in time prior to the oldest snapshot (7 days ago)
  • You must create the new service instance in the same organisation and space as the original. This is to prevent unauthorised access to data between spaces. If you need to copy data to a different organisation and/or space, you can connect to your PostgreSQL instance from a local machine using Conduit.

By default, the database is restored to the most recent point in time checkpoint available. If you need to restore to a particular point in time, you can use the restore_from_point_in_time_of parameter.

For example:

cf create-service postgres large-11 my-pg-service-copy  -c '{"restore_from_point_in_time_of": "32938730-e603-44d6-810e-b4f12d7d109e", "restore_from_point_in_time_before": "2020-10-27 13:00:00"}'

will create a new database from the write-ahead logs before October 27th 2020 13:00 UTC.

Upgrading major versions of Postgres

You can upgrade from one major version to the next by performing a plan upgrade.

There are two constraints on major version changes:

  1. You cannot downgrade
  2. You can only upgrade 1 major version at a time. E.g. 10 to 11, not 10 to 12.

To perform the plan upgrade, run the following code at the command line:

$cf update-service SERVICE_NAME -p PLAN_NAME
Updating service instance accounts-db as <REDACTED>@digital.cabinet-office.gov.uk...
OK

Update in progress. Use 'cf services' or 'cf service SERVICE_NAME' to check operation status.

Where SERVICE_NAME is the name of the Postgres service instance being upgraded, and PLAN_NAME is the name of the plan it should be upgraded to.

When the update process finishes, your database will be upgraded to the version named in the plan. To confirm that the database upgraded successfully to the right version, use the cf service SERVICE_NAME command to show the details:

$cf service SERVICE_NAME
Showing info of service SERVICE_NAME in org <redacted> / space <redacted> as <redacted>@digital.cabinet-office.gov.uk...

name: SERVICE_NAME service: postgres tags: plan: PLAN_NAME description: AWS RDS PostgreSQL service documentation: https://docs.cloud.service.gov.uk/deploying_services/postgresql/ dashboard: service broker: rds-broker

This service is not currently shared.

Showing status of last operation from service SERVICE_NAME...

status: update succeeded message: DB Instance 'rdsbroker-<redacted>' status is 'available' started: 2020-12-04T12:58:56Z updated: 2020-12-04T13:06:23Z

bound apps: name binding name status message APP_NAME create succeeded

Upgrades are not supported by this broker. $

Application and database availability during upgrade

Migrating to a new plan may cause interruption to your service instance. To minimise interruption, you can queue the change to begin during a maintenance window.