Once the source YugabyteDB cluster is configured, the following three scenarios were validated.
The most efficient and robust way to backup the data stored in YugabyteDB is to create a distributed snapshot. A distributed snapshot is a consistent cut of the data taken across all the nodes in the cluster. YugabyteDB does not create a physical copy of the data when a snapshot is created, instead it creates hard links to all the relevant files. These links are stored on the same storage volumes where the data resides, which makes the backup and operations very fast.
For YSQL, snapshots are created on a per-database level, whereas for YCQL, snapshots are created on a single keyspace. This section provides instructions to create distributed snapshots for YSQL.
For more information about creating a snapshot for YCQL, see Distributed snapshots for YCQL.
1. From the client machine, log in to the database and list the tables in the database.
[root@ydb-client1 ~]# ysqlsh -h ydb-node1 -U yugabyte
Password for user yugabyte:
ysqlsh (11.2-YB-2.16.0.1-b0)
Type "help" for help.
yugabyte=# \c yugabyte
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(10 rows)
2. From one of the database nodes, create a YugabyteDB distributed snapshot using yb-admin tool.
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 create_database_snapshot ysql.yugabyte
The above command returns a unique snapshot ID as shown below. Make a note of this snapshot unique ID. This id can be used to check the status of a snapshot.
Started snapshot creation: 522bf3d6-cd94-413b-9f08-afe813e177d2
3. List all the snapshots created in the source cluster. Check the status of the snapshot created in the previous step.
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 list_snapshots
Snapshot UUID State Creation Time
522bf3d6-cd94-413b-9f08-afe813e177d2 COMPLETE 2024-03-21 06:38:23.434417
4. In the client machine from which we logged into the database, create a table in the database and insert data into it.
yugabyte=# create table test (key int primary key, value text);
CREATE TABLE
yugabyte=# insert into test values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'), (9, 'nine'), (10, 'ten');
INSERT 0 10
yugabyte=# select * from test;
key | value
-----+-------
5 | five
1 | one
6 | six
7 | seven
9 | nine
10 | ten
4 | four
2 | two
8 | eight
3 | three
(10 rows)
5. Check the updated list of tables in the database.
yugabyte=#
List the updated list of tables in the database.
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | test | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(11 rows)
yugabyte=#
6. From one of the database nodes, restore the distributed snapshot using the snapshot ID.
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 restore_snapshot 522bf3d6-cd94-413b-9f08-afe813e177d2
The above command returns a restoration id as shown below. Make a note of the restoration id. This id can be used to check the status of restoration.
Started restoring snapshot: 522bf3d6-cd94-413b-9f08-afe813e177d2
Restoration id: 48a55153-b890-417f-9914-02c6cf9b18c1
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 list_snapshots
Snapshot UUID State Creation Time
522bf3d6-cd94-413b-9f08-afe813e177d2 COMPLETE 2024-03-21 06:38:23.434417
Restoration UUID State
48a55153-b890-417f-9914-02c6cf9b18c1 RESTORED
[root@ydb-node1 bin]#
7. After the restore operation is completed, check the list of tables. The list still consists of all the tables including the table test.
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | test | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(11 rows)
yugabyte=#
The above results are expected because with distributed snapshots, database schema changes are not restored.
In this section, instructions are provided which demonstrates how PowerFlex snapshots can be combined with distributed snapshots to restore database schema changes as well.
1. From the client machine, log in to the database and list the tables in the database.
[root@ydb-client1 ~]# ysqlsh -h ydb-node1 -U yugabyte
Password for user yugabyte:
ysqlsh (11.2-YB-2.16.0.1-b0)
Type "help" for help.
yugabyte=# \c yugabyte
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(10 rows)
2. From one of the database nodes, create a YugabyteDB distributed snapshot using the yb-admin tool.
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 create_database_snapshot ysql.yugabyte
The above command returns a unique snapshot ID as shown below. Make a note of this snapshot unique ID. This ID can be used to check the status of a snapshot.
Started snapshot creation: 6144dee8-031d-4881-9e57-2e1623059c5d
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 list_snapshots
Snapshot UUID State Creation Time
6144dee8-031d-4881-9e57-2e1623059c5d COMPLETE 2024-03-21 07:42:18.276089
3. Create PowerFlex storage snapshots of all the database data and software volumes. See User-driven PowerFlex Snapshots for details on how to create the PowerFlex snapshots.
4. Map the PowerFlex snapshot volumes to the ESXi hosts, create VMFS datastores on these volumes.
5. In the client machine from which you logged into the source database, create a new table in the database and insert data into it.
yugabyte=# create table test (key int primary key, value text);
CREATE TABLE
yugabyte=# insert into test values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'), (9, 'nine'), (10, 'ten');
INSERT 0 10
yugabyte=# select * from test;
key | value
-----+-------
5 | five
1 | one
6 | six
7 | seven
9 | nine
10 | ten
4 | four
2 | two
8 | eight
3 | three
(10 rows)
6. Check the updated list of tables in the database.
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | test | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(11 rows)
yugabyte=#
7. To clone the source database nodes, map the PowerFlex snapshot volumes to the cloned database nodes (VMs). Power ON the cloned nodes, assign new IP address, make necessary changes in /etc/hosts.
8. From the client machine, re-login to the cloned database nodes and check the list of tables.
[root@ydb-client1 ~]# ysqlsh -h 172.16.105.104 -U yugabyte
Password for user yugabyte:
ysqlsh (11.2-YB-2.16.0.1-b0)
Type "help" for help.
yugabyte=# \c yugabyte
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=#
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(10 rows)
yugabyte=#
The above list shows only 10 tables in the database which is the state of the database at the time of snapshot. This scenario demonstrates that when YugabyteDB distributed snapshots are combined with PowerFlex snapshots, the database schema changes can also be restored.
For more information about YugabyteDB distributed snapshots, see Distributed snapshots for YSQL.
Point-in-time recovery (PITR) in YugabyteDB enables recovery from a user or software error, while minimizing recovery point objective (RPO), recovery time objective (RTO), and overall impact on the cluster.
PITR is applicable to the following:
PITR in YugabyteDB is based on a combination of the following:
In this section, instructions are provided to demonstrate how the PowerFlex snapshots can be combined with PITR snapshots to restore YugabyteDB database.
1. From the client machine, login to the database and create a new table and insert data into it.
yugabyte=# create table pitr_test (key int primary key, value text);
CREATE TABLE
yugabyte=# insert into pitr_test values (1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), (5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'), (9, 'nine'), (10, 'ten');
INSERT 0 10
yugabyte=# select * from pitr_test ;
key | value
-----+-------
5 | five
1 | one
6 | six
7 | seven
9 | nine
10 | ten
4 | four
2 | two
8 | eight
3 | three
(10 rows)
yugabyte=#
2. From one of the database nodes, enable PITR by creating a snapshot schedule.
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 create_snapshot_schedule 15 2880 ysql.yugabyte
The above command returns a unique snapshot schedule ID as shown below. Make a note of this snapshot schedule ID.
{
"schedule_id": "3b82d4d6-acc8-48a0-9ab5-c361b63549e4"
}
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 list_snapshot_schedules
{
"schedules": [
{
"id": "3b82d4d6-acc8-48a0-9ab5-c361b63549e4",
"options": {
"filter": "ysql.yugabyte",
"interval": "15 min",
"retention": "2880 min"
},
"snapshots": [
{
"id": "e24839df-358f-44dc-a521-34e894439521",
"snapshot_time": "2024-03-29 10:56:35.654948"
}
]
}
]
}
[root@ydb-node1 bin]#
3. From the client machine from which we logged into the database, drop the newly added table pitr_test.
yugabyte=# drop table pitr_test ;
DROP TABLE
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(10 rows)
yugabyte=#
4. Create PowerFlex storage snapshots of all the database data and software volumes. See User-driven PowerFlex Snapshots for details on how to create the PowerFlex snapshots.
5. Map the PowerFlex snapshot volumes to the ESXi hosts, create VMFS datastores on these volumes.
6. Clone the database nodes, map the PowerFlex snapshot volumes to the cloned database nodes (VMs). Power ON the cloned nodes, assign new IP address, make necessary changes in /etc/hosts.
7. From the client machine, login to the database and check the list of tables. Observe that the table pitr_test is not present in the list.
[root@ydb-client1 ~]# ysqlsh -h 172.16.105.104 -U yugabyte
Password for user yugabyte:
ysqlsh (11.2-YB-2.16.0.1-b0)
Type "help" for help.
yugabyte=# \c yugabyte
You are now connected to database "yugabyte" as user "yugabyte".
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | stock | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(10 rows)
yugabyte=#
8. Log in to one of the database nodes and list the snapshot schedules and check the ddl_log.
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 list_snapshot_schedules
{
"schedules": [
{
"id": "3b82d4d6-acc8-48a0-9ab5-c361b63549e4",
"options": {
"filter": "ysql.yugabyte",
"interval": "15 min",
"retention": "2880 min"
},
"snapshots": [
{
"id": "e24839df-358f-44dc-a521-34e894439521",
"snapshot_time": "2024-03-29 10:56:35.654948"
},
{
"id": "4c646cfc-995d-4b7b-8bd3-5d6129745fb4",
"snapshot_time": "2024-03-29 11:11:40.671670",
"previous_snapshot_time": "2024-03-29 10:56:35.654948"
},
{
"id": "7668a199-b44f-483c-9436-c8bb65c6eaf3",
"snapshot_time": "2024-03-29 11:35:03.714901",
"previous_snapshot_time": "2024-03-29 11:11:40.671670"
}
]
}
]
}
[root@ydb-node1 bin]#
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 ddl_log | less
{
"log": [
{
"table_type": "PGSQL_TABLE_TYPE",
"namespace": "yugabyte",
"table": "pitr_test",
"action": "Drop",
"time": "2024-03-29 11:23:07.671609"
},
DDL logs indicate that the table pitr_test was deleted at the timestamp "2024-03-29 11:23:07.671609".
9. To restore the database using the above recorded timestamp, run the following command:
[root@ydb-node1 bin]# ./yb-admin -master_addresses ydb-node1.ydb.com:7100,ydb-node2.ydb.com:7100,ydb-node3.ydb.com:7100 restore_snapshot_schedule 3b82d4d6-acc8-48a0-9ab5-c361b63549e4 "2024-03-29 11:23:07"
The above command returns a restoration id as shown below. Make a note of the restoration id. This id can be used to check the status of restoration.
{
"snapshot_id": "7668a199-b44f-483c-9436-c8bb65c6eaf3",
"restoration_id": "f3b9bcd0-4aa7-4a0a-ac15-8e02b7300cf1"
}
yugabyte=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | yugabyte
public | district | table | yugabyte
public | history | table | yugabyte
public | item | table | yugabyte
public | new_order | table | yugabyte
public | oorder | table | yugabyte
public | order_line | table | yugabyte
public | pitr_test | table | yugabyte
public | stock | table | yugabyte
public | usertable | table | yugabyte
public | warehouse | table | yugabyte
(11 rows)
yugabyte=# select * from pitr_test ;
key | value
-----+-------
5 | five
1 | one
6 | six
7 | seven
9 | nine
10 | ten
4 | four
2 | two
8 | eight
3 | three
(10 rows)
yugabyte=#
For more information about YugabyteDB PITR, see Point-in-time recovery.