--- title: "Clone to PIT" url: "https://docs.yugabyte.com/stable/manage/backup-restore/point-in-time-recovery/clone/" --- # Clone to PIT Clone your database in YugabyteDB for data recovery, development, and testing. Branch your database at a point in time for recovery, development, and testing Which PITR method should I use? To decide which point-in-time feature is right for your use case, refer to [The PIT recovery family](/stable/manage/backup-restore/point-in-time-recovery/#the-pit-recovery-family "The PIT recovery family"). Clone to PIT creates a zero-copy, independent writable clone of your database as of the current time or any time in the recent past (within the configured retention window). The clone always resides on the same physical cluster as the source. This capability is also known as branching. Cloning is fast and efficient because when initially created, the clone shares the same data files with the original database. Subsequently, as data is written to the clone, the clone stores its own changes as separate and independent delta files. Although they physically share some files, the two databases are logically isolated, which means you can freely use the clone database, perform DDLs, read and write data, and delete it without affecting the original database. Clone to PIT is particularly useful for data recovery from user or application errors when **intervening writes must be preserved**: create a clone as of the good state, perform forensic analysis, export lost or corrupted data from the clone, and import it back to the original database. ![Database clone](/images/manage/backup-restore/db-clone.png "Database clone") Cloning has two main use cases: - **Data recovery**. To recover from data loss due to user error (for example, accidentally dropping a table) or application error (for example, updating rows with corrupted data), you can create a clone of your production database from a point in time when the database was in a good state. This allows you to perform forensic analysis, export the lost or corrupted data from the clone, and import it back to the original database. For instance, if you dropped a table by mistake at 9:01, then detected this error at 10:45, you want to recover the lost data as it was at 9:00 (just before the table drop). At the same time, you don't want to lose any new data added to other tables between 9:01 and 10:45. With Clone to PIT, you can create a clone of the database as of 9:00 (before the table drop) and copy the data in the table from the cloned database to the production database. - **Development and testing**. Because the two databases are completely isolated, you can experiment with the cloned database, perform DDL operations, read and write data, and delete the clone without impacting the original. Developers can test their changes on an identical copy of the production database without affecting its performance. Unlike [Rewind to PIT](/stable/manage/backup-restore/point-in-time-recovery/rewind/ "Rewind to PIT"), Clone does not discard intervening writes on the original database. Unlike [Restore to PIT](/stable/manage/backup-restore/point-in-time-recovery/restore/ "Restore to PIT"), Clone keeps recovery on the original cluster in a logically isolated database. ## Prerequisites [Create a snapshot schedule](/stable/manage/backup-restore/point-in-time-recovery/enable-pitr/#create-a-schedule "Create a snapshot schedule") for the database you want to clone. For example, creating a snapshot schedule with a retention period of 7 days allows you to create a clone of the original database to any time in the past 7 days. ## Clone databases ### Clone a YSQL database Because YugabyteDB is PostgreSQL compatible, you can create a database as a clone of another using the `TEMPLATE` SQL option of the `CREATE DATABASE` command as follows: ```sql CREATE DATABASE clone_db TEMPLATE original_db; ``` In this example, `clone_db` is created as a clone of `original_db`, and contains the latest schema and data of `original_db` as of current time. To create a clone of the original database at a specific point in time (within the history retention period specified when creating the snapshot schedule), you can specify a timestamp using the `AS OF` option. The timestamp may be either a [Unix timestamp](https://www.unixtimestamp.com/ "Unix timestamp") in microseconds (as below), or a [PostgreSQL TIMESTAMP](https://www.postgresql.org/docs/current/datatype-datetime.html "PostgreSQL TIMESTAMP") in single quotes. ```sql CREATE DATABASE clone_db TEMPLATE original_db AS OF 1723146703674480; # Alternatively: CREATE DATABASE clone_db TEMPLATE original_db AS OF '2024-08-08 19:51:43.674480'; ``` ### Clone a YCQL keyspace You can create a clone in YCQL using the yb-admin [clone\_namespace](/stable/admin/yb-admin/#clone-namespace "clone_namespace") command as follows: ```sh ./bin/yb-admin --master_addresses $MASTERS clone_namespace ycql.originaldb1 clonedb2 1715275616599020 ``` In this example, `clonedb2` is created as a clone of `originaldb1` as of 1715275616599020 Unix timestamp. ## Clone database ownership The owner of the clone is determined as follows: - OWNER is explicitly specified in the CREATE DATABASE command: The specified role is the owner. For example: ```sql CREATE DATABASE cloned_db TEMPLATE src_db OWNER some_role AS OF ... ``` The role `some_role` is the owner. - OWNER is not specified: The current user executing the command becomes the owner of the clone. When using the yb-admin [clone\_namespace](/stable/admin/yb-admin/#clone-namespace "clone_namespace") command directly, the cloned database retains the original template database's owner. Note that to clone a database that's not marked `datistemplate`, you must be a superuser or the owner of the source database. ## Check the clone status To check the status of clone operations performed on a YSQL database, you can use the YSQL function `yb_database_clones()`, which provides details about the clone operations performed on the cluster. For example: ```sql SELECT * FROM yb_database_clones(); ``` ```output db_oid | db_name | parent_db_oid | parent_db_name | state | as_of_time | failure_reason --------+------------+---------------+----------------+----------+-------------------------------+---------------- 16386 | staging_db | 16384 | src_db | COMPLETE | 2026-05-12 21:10:19.191239+00 | (1 row) ``` This shows that a new database named `staging_db` with `db_oid` 16386 is created as a clone of the database `src_db`. The clone is `COMPLETE` and created as of time `2026-05-12 21:10:19.191239+00`. ### Use yb-admin To check the status of clone operations performed on a database using [yb-admin](/stable/admin/yb-admin/#list-clones "yb-admin"), use the `list_clones` command and provide the `source_database_id` (YSQL) or `source_namespace_id` (YCQL), as follows: ```sh ./bin/yb-admin --master_addresses $MASTERS list_clones 00004000000030008000000000000000 ``` ```output.json [ { "aggregate_state": "COMPLETE", "source_namespace_id": "00004000000030008000000000000000", "seq_no": "1", "target_namespace_name": "testing_clone_db", "restore_time": "2024-08-09 21:42:16.451974" }, { "aggregate_state": "COMPLETE", "source_namespace_id": "00004000000030008000000000000000", "seq_no": "2", "target_namespace_name": "dev_clone_db", "restore_time": "2024-08-09 21:42:55.048663" } ] ``` You can find the `source_database_id` or `source_namespace_id` from the [YB-Master leader UI](/stable/reference/configuration/default-ports/#servers "YB-Master leader UI") under the `/namespaces` endpoint. In this example, two clones were made of the source database `00004000000030008000000000000000` that are COMPLETE. The two clones are `testing_clone_db` and `dev_clone_db` and they each have a unique `seq_no` used to identify each clone operation from the same source database. You can check the status of a specific clone operation if you have both the `source_database_id` (YSQL) or `source_namespace_id` (YCQL) and the `seq_no` as follows: ```sh ./bin/yb-admin --master_addresses $MASTERS list_clones 00004000000030008000000000000000 2 ``` ```output.json [ { "aggregate_state": "COMPLETE", "source_namespace_id": "00004000000030008000000000000000", "seq_no": "2", "target_namespace_name": "dev_clone_db", "restore_time": "2024-08-09 21:42:55.048663" } ] ``` Use the `list_clones` command to check whether a clone operation completed successfully or not. Note that the cluster doesn't allow you to perform two clone operations concurrently on the same source database. You have to wait for the first clone to finish until you can perform another clone. ## Example: Recover from table deletion The following example demonstrates how to use a database clone to recover from an accidental table deletion. 1. Create a local cluster using [yugabyted](/stable/reference/configuration/yugabyted/ "yugabyted"): ```sh ./bin/yugabyted start --advertise_address=127.0.0.1 ``` 2. Start [ysqlsh](/stable/api/ysqlsh/ "ysqlsh") and create the database: ```sh ./bin/ysqlsh CREATE DATABASE production_db; ``` 3. Create a snapshot schedule that produces a snapshot once a day (every 1,440 minutes), and retains it for three days (4,320 minutes): ```sh ./bin/yb-admin --master_addresses ip1:7100,ip2:7100,ip3:7100 create_snapshot_schedule 1440 4320 ysql.production_db ``` 4. Create two tables `t1` and `t2`, and add some data: ```sql ./bin/ysqlsh yugabyte=# \c production_db; production_db=# CREATE TABLE t1 (k INT, v INT); production_db=# INSERT INTO t1 (k,v) SELECT i,i%2 FROM generate_series(1,5) AS i; production_db=# SELECT * FROM t1 ORDER BY k; ``` ```output k | v ---+--- 1 | 1 2 | 0 3 | 1 4 | 0 5 | 1 (5 rows) ``` ```sql production_db=# CREATE TABLE t2 (key INT, c1 TEXT); production_db=# INSERT INTO t2 (key,c1) SELECT i,md5(random()::text) FROM generate_series(1,5) AS i; production_db=# SELECT * FROM t2 ORDER BY key; ``` ```output key | c1 -----+---------------------------------- 1 | 450e6c49f86c76d944375e29e48f2dee 2 | b934a3bdf7438458a85b0858c41f731c 3 | 08697ed89ec387e714c6587e522d7a7e 4 | a879ff99872b3c3433803d3c3229f0cf 5 | 4d46a53780a7a348179e1af9b692e95e (5 rows) ``` 5. Determine the exact time when your database is in the correct state. This timestamp will be used to create a clone of the production database from the point when it was in the desired state. Execute the following SQL query to retrieve the current time in UNIX timestamp format: ```sql production_db=# SELECT (EXTRACT (EPOCH FROM CURRENT_TIMESTAMP)*1000000)::decimal(38,0); ``` ```output numeric ------------------ 1723243720285350 (1 row) ``` 6. To simulate a user error, drop the table `t1`. ```sql production_db=# DROP TABLE t1; ``` ```output DROP TABLE ``` 7. Meanwhile, as table `t2` is still accepting reads/writes, insert 2 more rows as follows: ```sh INSERT INTO t2 (key,c1) SELECT i,md5(random()::text) FROM generate_series(6,7) AS i; ``` 8. Now, if you try to query table `t1`, notice that the table is dropped and there is no way you can query it. ```sql production_db=# SELECT * FROM t1 ORDER BY k; ``` ```output ERROR: relation "t1" does not exist LINE 1: SELECT * FROM t1 ORDER BY k; ``` 9. Create a database `clone_db` using `production_db` as the template and using the timestamp generated in step 4. ```sql production_db=# CREATE DATABASE clone_db TEMPLATE production_DB AS OF 1723243720285350; ``` ```sql \c clone_db ``` ```output You are now connected to database "clone_db" as user "yugabyte". ``` ```sql clone_db=# SELECT * FROM t1 ORDER BY k; ``` ```output k | v ---+--- 1 | 1 2 | 0 3 | 1 4 | 0 5 | 1 (5 rows) ``` You now have two isolated databases that can serve reads and writes independently. `clone_db` contains all the data from `production_db` at the specified timestamp, which means you can read table `t1` that was dropped. To recover the lost data, copy the data from table `t1` back to `production_db` by exporting the data from the clone and importing it into `production_db`. Alternatively, you can switch the workload to `clone_db`. When you are done, you can clean up by dropping the clone as you would any database, by using the DROP DATABASE or DROP KEYSPACE command. The clone is deleted, along with any post-compaction uncompacted files from the original database. ## Best practices Although creating a clone database is quick and initially doesn't take up much added disk space as no data is copied, a clone does create an independent set of logical tablets. Increasing the number of tablets can cause: - Higher CPU usage due to the additional tablets - Increased memory consumption from the extra tablets - Increased disk use after compaction of either the clone or the original database. This is because both original and post-compaction data files must be kept on disk for access by whichever database did not do the compaction. For example, if compaction is performed on the original database, new compacted files are generated which serve reads for the original database. The old data files are retained on disk to serve reads for the clone database. Whenever the clone or original database is deleted, the cluster only cleans the unused data files. If you have [tablet limits](/stable/architecture/docdb-sharding/tablet-splitting/#tablet-limits "tablet limits") set, and creating the clone would lead to exceeding the limit, the clone operation will fail to respect the tablet limits. ## Limitations - Cloning as of a time close to the limit of the history retention period may fail. For example, if you have a history retention period of 10 minutes and you create a clone as of 9 minutes ago, the clone operation may fail. Use a slightly larger history retention period than you think you need. - Cloning to a point in time during which a DDL was running may fail. See issue [#28814](https://github.com/yugabyte/yugabyte-db/issues/28814). - Cloning is not supported for databases with [vector indexes](/stable/additional-features/pg-extensions/extension-pgvector/#vector-indexing "vector indexes"). - Databases with many objects in multi-region deployments may take longer to clone. If the operation takes longer than 10 minutes, increase the `ysql_clone_pg_schema_rpc_timeout_ms` YB-TServer runtime flag.