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:

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.

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.

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.

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
```
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.

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
```
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.

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}'
```
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:

install and configure the PostgreSQL command line (CLI) tools on your local machine (configuration options vary depending on operating system and version)
log into Cloud Foundry
create the new PaaS-hosted PostgreSQL database
target the space where your new database is located

PaaS to Non-PaaS

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

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.
Run the following command in the CLI to import data into the non-PaaS database from an SQL data file:
```
psql -h HOSTNAME DATABASE_NAME < DATA_FILE_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 target database

Non-PaaS to PaaS

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

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 --no-acl --no-owner
```
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
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 will see some error messages when importing the data file into the target database. These errors are expected and the import process will succeed.

You can only use certain PostgreSQL extensions.

PaaS to PaaS

To move data between two PaaS-hosted PostgreSQL databases:

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.
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.

You will see some error messages when importing the data file into the target database. These errors are expected and the import process will succeed.

Expected Errors

When importing data into a target database, you may encounter errors that do not stop the import process from finishing:

    ERROR:  function "forbid_ddl_reader" already exists with same argument types
    ERROR:  function "make_readable" already exists with same argument types
    ERROR:  function "make_readable_generic" already exists with same argument types
    ERROR:  function "reassign_owned" already exists with same argument types
    WARNING:  no privileges were granted for "sql_implementation_info"
    WARNING:  no privileges were granted for "public"
    ERROR:  permission denied to create event trigger "forbid_ddl_reader"
    HINT:  Must be superuser to create an event trigger.
    ERROR:  permission denied to create event trigger "make_readable"
    HINT:  Must be superuser to create an event trigger.
    ERROR:  permission denied to create event trigger "reassign_owned"
    HINT:  Must be superuser to create an event trigger.

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.

Set up a new PostgreSQL service with a new plan that has a different encryption type to your current plan.
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 can downgrade your service plan provided that:

the amount of storage for the new plan is not less than the existing plan
both the existing plan and the new plan have the same major version

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 when a failover happens.

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, 13
citext	10, 11, 12, 13

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, 13
address_standardizer_data_us	10, 11, 12, 13
bloom	10, 11, 12, 13
bool_plperl	13
btree_gin	10, 11, 12, 13
btree_gist	10, 11, 12, 13
chkpass	10
cube	10, 11, 12, 13
dblink	10, 11, 12
dict_int	10, 11, 12, 13
dict_xsyn	10, 11, 12, 13
earthdistance	10, 11, 12, 13
fuzzystrmatch	10, 11, 12, 13
hll	10, 11, 12, 13
hstore	10, 11, 12, 13
hstore_plperl	10, 11, 12, 13
intagg	10, 11, 12, 13
intarray	10, 11, 12, 13
ip4r	10, 11, 12, 13
isn	10, 11, 12, 13
jsonb_plperl	13
libprotobuf	10, 11, 12
log_fdw	10, 11, 12
ltree	10, 11, 12, 13
orafce	10, 11, 12, 13
pageinspect	10, 11, 12
pg_bigm	13
pg_buffercache	10, 11, 12
pg_freespacemap	10, 11, 12
pg_hint_plan	10, 11, 12, 13
pg_prewarm	10, 11, 12, 13
pg_proctab	13
pg_repack	10, 11, 12
pg_similarity	10, 11, 12, 13
pg_stat_statements	10, 11, 12, 13
pg_transport	11, 12
pg_trgm	10, 11, 12, 13
pg_visibility	10, 11, 12
pgaudit	10, 11, 12, 13
pgcrypto	10, 11, 12, 13
pglogical	10, 11, 12
pgrouting	10, 11, 12, 13
pgrowlocks	10, 11, 12, 13
pgstattuple	10, 11, 12
pgtap	11, 12, 13
plcoffee	10, 11, 12, 13
plls	10, 11, 12, 13
plperl	10, 11, 12, 13
plpgsql	10, 11, 12, 13
pltcl	10, 11, 12, 13
plv8	10, 11, 12, 13
postgis	10, 11, 12, 13
postgis_raster	13
postgis_tiger_geocoder	10, 11, 12, 13
postgis_topology	10, 11, 12, 13
postgres_fdw	10, 11, 12
prefix	10, 11, 12, 13
rdkit	13
sslinfo	10, 11, 12, 13
tablefunc	10, 11, 12, 13
test_parser	10, 11, 12, 13
tsearch2	10
tsm_system_rows	10, 11, 12, 13
tsm_system_time	10, 11, 12, 13
unaccent	10, 11, 12, 13

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 postgres PLAN SERVICE_NAME -c '{"enable_extensions": ["EXTENSION_1","EXTENSION_2",...,"EXTENSION_N"]}'

where:

SERVICE_NAME is a unique descriptive name for this service instance
PLAN is the plan you want
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.

Paid plans - PostgreSQL

Some service plans are paid, and we will bill you based on the size of your plan.

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 benefitting from the high availability plan.

High IOPS plans - PostgreSQL

For services which have a large volume of database reads and writes, GOV.UK PaaS has high-iops variants of its PostgreSQL service plans available.

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.

You can retrieve the day and time of this maintenance window with the Cloud Foundry CLI (version 8 and above) by running:

cf service --params SERVICE_NAME

{
  ...
  "preferred_maintenance_window": "sun:23:00-mon:01:30"
  ...
}

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 unencrypted plans.

Backups are taken nightly in your service instance’s backup window. Data is retained for 7 days. When a database is deleted the final snapshot is held for 35 days.

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

You can restore to the latest snapshot. Refer to Restoring a PostgreSQL service snapshot for details.
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:

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.
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"}'
```
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
```
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:

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.
Trigger the creation of a new service based on the most recent available point in time 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"}'
```
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_before 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.
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
```
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), unless it’s a final snapshot from a deleted database (up to 35 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.

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:

You cannot downgrade
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.