Managing backups in Managed Service for PostgreSQL
You can create backups and restore clusters from existing backups.
Managed Service for PostgreSQL also creates automatic daily backups. You can set the backup start time and retention period.
Restoring clusters from backups
Point-in-Time Recovery (PITR) technology allows you to restore the cluster state to any point in time in the interval from the creation of the oldest full backup to the archiving of the most recent write ahead log (WAL). For more information, see Backups.
When you restore a cluster from a backup, you create a new cluster with the backup data. If the folder has insufficient resources to create such a cluster, you will not be able to restore from the backup. The average backup recovery speed is 10 MBps per database core.
When creating a new cluster, set all required parameters.
When restored to the current state, the new cluster will match the state of:
- Existing cluster at the time of recovery.
- Deleted cluster at the time of archiving the most recent WAL.
Warning
For backups created manually:
- You cannot set any recovery time.
- You can only restore a cluster to the state when the backup is completed.
To restore an existing cluster from a backup:
-
Go to the folder page and select Managed Service for PostgreSQL.
-
Click the cluster name and open the Backups tab.
-
Click
for the backup you need and click Restore cluster. -
Set up the new cluster. You can select a folder for the new cluster from the Folder list.
-
To restore the cluster state to the required point in time after creating this backup, configure Date and time of recovery (UTC) as appropriate. You can enter the value manually or select it from the drop-down calendar.
If you do not change the setting, the cluster will be restored to the state when the backup was completed.
-
Click Restore cluster.
To restore a previously deleted cluster from a backup:
-
Go to the folder page and select Managed Service for PostgreSQL.
-
Select the Backups tab.
-
Find the backup you need using the backup creation time and cluster ID. The ID column contains IDs in
<cluster_ID>:<backup_ID>
format. -
Click
for the backup you need and click Restore cluster. -
Set up the new cluster. You can select a folder for the new cluster from the Folder list.
-
To restore the cluster state to the required point in time after creating this backup, configure Date and time of recovery (UTC) as appropriate. You can enter the value manually or select it from the drop-down calendar.
If you do not change the setting, the cluster will be restored to the state when the backup was completed.
-
Click Restore cluster.
Managed Service for PostgreSQL will launch the operation to create a cluster from the backup.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
By default, the cluster is restored to the same folder where the backup is stored. To restore the cluster to a different folder, specify its ID in the --folder-id
parameter.
To restore a cluster from a backup:
-
View a description of the CLI restore PostgreSQL cluster command:
yc managed-postgresql cluster restore --help
-
Getting a list of available PostgreSQL cluster backups:
yc managed-postgresql backup list
+--------------------------+---------------------+----------------------+---------------------+ | ID | CREATED AT | SOURCE CLUSTER ID | STARTED AT | +--------------------------+---------------------+----------------------+---------------------+ | c9qlk4v13uq7********:... | 2020-08-10 12:00:00 | c9qlk4v13uq7******** | 2020-08-10 11:55:17 | | ... | +--------------------------+---------------------+----------------------+---------------------+
The time when the backup was completed is shown in the
CREATED AT
column with a list of available backups, inyyyy-mm-dd hh:mm:ss
format (2020-08-10 12:00:00
in the example above). You can restore a cluster to any point in time starting with the point when the backup is created. -
Request the creation of a cluster from a backup:
yc managed-postgresql cluster restore \ --backup-id=<backup_ID> \ --time=<time> \ --name=<cluster_name> \ --environment=<environment> \ --network-name=<network_name> \ --host zone-id=<availability_zone>,` `subnet-name=<subnet_name>,` `assign-public-ip=<public_access_to_host> \ --resource-preset=<host_class> \ --disk-size=<storage_size_in_GB> \ --disk-type=<disk_type>
Where:
-
--backup-id
: Backup ID. -
--time
: Time point to restore the PostgreSQL cluster to, inyyyy-mm-ddThh:mm:ssZ
format. -
--name
: Cluster name. -
--environment
: Environment:PRESTABLE
: For testing purposes. The prestable environment is similar to the production environment and likewise covered by the SLA, but it is the first to get new functionalities, improvements, and bug fixes. In the prestable environment, you can test compatibility of new versions with your application.PRODUCTION
: For stable versions of your apps.
-
--network-name
: Network name. -
--host
: Host parameters:-
zone-id
: Availability zone. -
subnet-name
: Subnet name. Specify if two or more subnets are created in the selected availability zone. -
assign-public-ip
: Flag to be set if public access to the host is required,true
orfalse
.
-
-
--resource-preset
: Host class. -
--disk-size
: Storage size in GB. -
--disk-type
: Disk type:network-hdd
network-ssd
local-ssd
network-ssd-nonreplicated
-
Use Terraform to restore:
- Existing cluster from a backup.
- Cluster created and deleted via the management console, CLI, or API.
Note
The cluster is restored to the folder, the ID of which is specified in the folder_id
parameter of provider settings
To restore a cluster, you will need the backup ID. Get a list of available PostgreSQL cluster backups using the CLI:
yc managed-postgresql backup list
+--------------------------+---------------------+----------------------+---------------------+
| ID | CREATED AT | SOURCE CLUSTER ID | STARTED AT |
+--------------------------+---------------------+----------------------+---------------------+
| c9qlk4v13uq7********:... | 2020-08-10 12:00:00 | c9qlk4v13uq7******** | 2020-08-10 11:55:17 |
| ... |
+--------------------------+---------------------+----------------------+---------------------+
Time limits
A Terraform provider sets the timeout for Managed Service for PostgreSQL cluster operations:
- Creating a cluster, including restoring from a backup: 30 minutes.
- Editing a cluster: 60 minutes.
- Deleting a cluster: 15 minutes.
Operations exceeding the set timeout are interrupted.
Add the timeouts
block to the cluster description, for example:
resource "yandex_mdb_postgresql_cluster" "<cluster_name>" {
...
timeouts {
create = "1h30m" # 1 hour 30 minutes
update = "2h" # 2 hours
delete = "30m" # 30 minutes
}
}
To restore an existing cluster from a backup:
-
Create a Terraform configuration file for the new cluster.
Do not use resources of the databases (
yandex_mdb_postgresql_database
) and users (yandex_mdb_postgresql_user
). They will be restored from the backup. -
Add a block named
restore
to the configuration file:resource "yandex_mdb_postgresql_cluster" "<cluster_name>" { ... restore { backup_id = "<backup_ID>" time = "<time>" } }
Where:
backup_id
: Backup ID.time
: Time point to restore the PostgreSQL cluster to, starting from the selected backup's creation time, inyyyy-mm-ddThh:mm:ss
format.
Note
If you omit the
time
parameter, the cluster will be restored to the state when the backup was completed. -
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Terraform will create a copy of the existing cluster. The databases and users are deployed from the selected backup.
To restore a previously deleted cluster from a backup:
-
Create a Terraform configuration file for the new cluster.
Do not use resources of the databases (
yandex_mdb_postgresql_database
) and users (yandex_mdb_postgresql_user
). They will be restored from the backup. -
In this configuration file, add a
restore
block with the name of the backup to restore the cluster from:resource "yandex_mdb_postgresql_cluster" "<cluster_name>" { ... restore { backup_id = "<backup_ID>" } }
Where
backup-id
is the ID of the remote cluster's backup. -
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
-
Terraform will create the new cluster. The databases and users are deployed from the backup.
To restore a cluster from a backup, use the restore REST API method for the Cluster resource or the ClusterService/Restore gRPC API call and provide the following in the request:
- Backup ID in the
backupId
parameter. To find out the ID, retrieve a list of cluster backups. - Time point to which you want to restore the cluster, in the
time
parameter. - Name of the new cluster that will contain the data recovered from the backup, in the
name
parameter. It must be unique within the folder.
By default, the cluster is restored to the same folder where the backup is stored. To restore the cluster to a different folder, specify its ID in the folderId
parameter.
Creating a backup
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Backups tab.
- Click
Create backup.
The service will start creating a backup without an additional confirmation.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To create a cluster backup:
-
View a description of the CLI create PostgreSQL backup command:
yc managed-postgresql cluster backup --help
-
Request the creation of a backup specifying the cluster name or ID:
yc managed-postgresql cluster backup my-pg-cluster
The cluster name and ID can be retrieved with the list of clusters.
To create a cluster backup, use the backup REST API method for the Cluster resource or the ClusterService/Backup gRPC API call and provide the cluster ID in the clusterId
request parameter.
You can get the cluster ID with a list of clusters in the folder.
Warning
While you are creating your backup, the cluster performance might degrade.
Getting a list of backups
To get a list of cluster backups:
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Backups tab.
To get a list of all backups in a folder:
- Go to the folder page and select Managed Service for PostgreSQL.
- Select the Backups tab.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To get a list of PostgreSQL cluster backups available in the default folder, run the command:
yc managed-postgresql backup list
+--------------------------+---------------------+----------------------+---------------------+
| ID | CREATED AT | SOURCE CLUSTER ID | STARTED AT |
+--------------------------+---------------------+----------------------+---------------------+
| c9qlk4v13uq7********:... | 2020-08-10 12:00:00 | c9qlk4v13uq7******** | 2020-08-10 11:55:17 |
| c9qpm90p3pcg********:... | 2020-08-09 22:01:04 | c9qpm90p3pcg******** | 2020-08-09 21:30:00 |
+--------------------------+---------------------+----------------------+---------------------+
To get a list of cluster backups, use the listBackups REST API method for the Cluster resource or the ClusterService/ListBackups gRPC API call and provide the cluster ID in the clusterId
request parameter.
To get a list of backups for all the Managed Service for PostgreSQL clusters in the folder, use the list REST API method for the Backup resource or the BackupService/List gRPC API call and provide the folder ID in the folderId
request parameter.
You can get the cluster ID with a list of clusters in the folder.
Getting information about backups
To get information about the backup of an existing cluster:
- Go to the folder page and select Managed Service for PostgreSQL.
- Click the cluster name and open the Backups tab.
To get information about the backup of a previously deleted cluster:
- Go to the folder page and select Managed Service for PostgreSQL.
- Select the Backups tab.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To get information about a PostgreSQL cluster backup, run the command:
yc managed-postgresql backup get <backup_ID>
You can retrieve the backup ID with a list of backups.
To get information about a cluster backup, use the get REST API method for the Backup resource or the BackupService/Get gRPC API call and provide the backup ID in the backupId
request parameter.
You can retrieve the backup ID with the backup list.
Setting the backup start time
In the management console, you can set the backup start time when creating or updating a cluster.
To set the backup start time, use the -- backup-window-
start flag. Time is given in HH:MM:SS
format.
yc managed-postgresql cluster create \
--cluster-name <cluster_name> \
--environment <environment> \
--network-name <network_name> \
--host zone-id=<availability_zone>,subnet-id=<subnet_ID> \
--resource-preset <host_class> \
--user name=<username>,password=<user_password> \
--database name=<DB_name>,owner=<DB_owner_name> \
--disk-size <storage_size_in_GB>
--backup-window-start 10:00:00
Where environment
is either prestable
or production
.
To change the backup start time in an existing cluster, use the update
command:
yc managed-postgresql cluster update \
--cluster-name <cluster_name> \
--backup-window-start 11:25:00
-
Open the current Terraform configuration file with an infrastructure plan.
For more information about how to create this file, see Creating clusters.
For a complete list of available Managed Service for PostgreSQL cluster configuration fields, see the Terraform provider documentation
. -
Add a block named
backup_window_start
to the Managed Service for PostgreSQL cluster description underconfig
:resource "yandex_mdb_postgresql_cluster" "<cluster_name>" { ... config { ... backup_window_start { hours = <hour> minutes = <minute> } ... } ...
Where:
hours
: Backup start hour (UTC).minutes
: Backup start minute (UTC).
-
Make sure the settings are correct.
-
Using the command line, navigate to the folder that contains the up-to-date Terraform configuration files with an infrastructure plan.
-
Run the command:
terraform validate
If there are errors in the configuration files, Terraform will point to them.
-
-
Confirm updating the resources.
-
Run the command to view planned changes:
terraform plan
If the resource configuration descriptions are correct, the terminal will display a list of the resources to modify and their parameters. This is a test step. No resources are updated.
-
If you are happy with the planned changes, apply them:
-
Run the command:
terraform apply
-
Confirm the update of resources.
-
Wait for the operation to complete.
-
Time limits
A Terraform provider sets the timeout for Managed Service for PostgreSQL cluster operations:
- Creating a cluster, including restoring from a backup: 30 minutes.
- Editing a cluster: 60 minutes.
- Deleting a cluster: 15 minutes.
Operations exceeding the set timeout are interrupted.
How do I change these limits?Add the
timeouts
block to the cluster description, for example:resource "yandex_mdb_postgresql_cluster" "<cluster_name>" { ... timeouts { create = "1h30m" # 1 hour 30 minutes update = "2h" # 2 hours delete = "30m" # 30 minutes } }
-
To set the backup start time, use the update REST API method for the Cluster resource or the ClusterService/Update gRPC API call and provide the following in the request:
- Cluster ID in the
clusterId
parameter. You can retrieve it with a list of clusters in the folder. - New backup start time in the
configSpec.backupWindowStart
parameter. - List of cluster configuration fields to be edited (in this case,
configSpec.backupWindowStart
) in theupdateMask
parameter.
Warning
This API method overrides all parameters of the object being modified that were not explicitly passed in the request to the default values. To avoid this, list the settings you want to change in the updateMask
parameter (one line separated by commas).
Deleting a backup
You can only delete backups that were created manually.
- Go to the folder page and select Managed Service for PostgreSQL.
- Select the Managed Service for PostgreSQL cluster whose backup you want to delete.
- In the left-hand panel, select Backups.
- Click
to the right of the backup you want to delete. - Select Delete backup.
- Confirm deletion and click Delete.
If you do not have the Yandex Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To delete a backup:
-
View a description of the CLI command to delete a PostgreSQL cluster backup:
yc managed-postgresql backup delete --help
-
Request the deletion of a backup by specifying its ID:
yc managed-postgresql backup delete <backup_ID>
You can retrieve the backup ID with a list of backups.
To delete a backup, use the delete REST API method for the Backup resource or the BackupService/Delete gRPC API call and provide the backup ID in the backupId
request parameter.
You can retrieve the backup ID with a list of backups.