Author: Seojin Heo
In some cases, DB data path may need to be rerouted during operations, and CUBRID supports rerouting DB data path through DB backup/recovery.
However, LOB data is not stored in the DB, but is stored in a file format in an external file system, and only the location information of the file is stored in the DB. Therefore, separate management of LOB data files is required when DB backup/restore.
In this post, I will guide you to change the path of LOB data in addition to the DB data path change. The example is created based on CUBRID version 9.3.
1. Test Information:
- DB Name: testdb
- Existing Path: /home/cubrid/CUBRID/databases/testdb
- Change Path: /disk/testdb
2. Path Change Procedure:
1) Service Termination
$ cubrid service stop
@ cubrid server stop: testdb
Server testdb notified of shutdown. This may take several minutes.
Please wait.
++ cubrid server stop: success
@ cubrid broker stop
++ cubrid broker stop: success
@ cubrid manager server stop
++ cubrid manager server stop: success
@ cubrid master stop
++ cubrid master stop: success
2) DB backup (refer to manual for backup option)
$ cubrid backupdb -D /disk/BACKUP/ -o backup.log -z --no-check testdb -S
Backup Volume Label: Level: 0, Unit: 0, Database testdb, Backup Time: Thu Jan 16 15:18:31 2020
3) Modify the $CUBRID/databases/databases.txt file
- Modify the vol-path (data volume path), log-path (log volume path), and lob-base-path (lob data storage path) as the path to be changed.
$ vi $CUBRID/databases/databases.txt
#db-name vol-path db-host log-path lob-base-path
testdb /disk/testdb localhost /disk/testdb file:/disk/testdb/lob
4) DB Recovery (Refer to manual for recovery option)
- Create the path in advance before recovery
- Create LOB directory in advance
- The u option is required (u option: the option to read the databases.txt file and recover to the corresponding path)
$ mkdir /disk/testdb
$ mkdir /disk/testdb/lob
$ cubrid restoredb -B /disk/BACKUP/ -u -o restore.txt testdb
CUBRID 9.3
5) Check the Data Path
- Check the DB data file and LOB path change
$ cubrid spacedb -S testdb Space
description for database 'testdb' with pagesize 16.0K. (log pagesize: 16.0K)
Volid Purpose total_size free_size Vol Name
0 GENERIC 512.0 M 509.6 M /disk/testdb/testdb
Space description for temporary volumes for database 'testdb' with pagesize 16.0K.
Volid Purpose total_size free_size Vol Name
LOB space description file:/disk/testdb/lob
6) Start DB
$ cubrid server start testdb
@ cubrid master start
++ cubrid master start: success
@ cubrid server start: testdb
This may take a long time depending on the amount of recovery works to do.
CUBRID 9.3
++ cubrid server start: success
7) Check the exisitng LOB data path stored in the DB
$ csql -u dba testdb@localhost
CUBRID SQL Interpreter
Type `;help' for help messages.
csql> select * from test_tbl;
=== <Result of SELECT Command in Line 1> ===
i j
===================================
1 file:/home/cubrid/CUBRID/databases/testdb/lob/ces_658/test_tbl.00001579155238194895_7622
2 file:/home/cubrid/CUBRID/databases/testdb/lob/ces_043/test_tbl.00001579155241496030_9788
3 file:/home/cubrid/CUBRID/databases/testdb/lob/ces_051/test_tbl.00001579155244055456_9598
3 rows selected. (0.284038 sec) Committed.
1 command(s) successfully processed
8) Change LOB Data Path
- LOB data files stored in the existing LOB path must exist
- Use clob_to_char function for CLOB and blob_to_bit function for BLOB
csql> update test_tbl set j=clob_to_char(j);
3 rows affected. (0.195812 sec) Committed.
csql> update test_tbl2 set j=blob_to_bit(j);
3 rows affected. (0.008269 sec) Committed.
9) Confirm LOB Data Path Change
csql> select * from test_tbl;
=== <Result of SELECT Command in Line 1> ===
i j
===================================
1 file:/disk/testdb/lob/ces_401/test_tbl.00001579156454712630_8701
2 file:/disk/testdb/lob/ces_520/test_tbl.00001579156454713722_8117
3 file:/disk/testdb/lob/ces_541/test_tbl.00001579156454714721_4275
3 rows selected. (0.007592 sec) Committed.
1 command(s) successfully processed.
10) Confirm that LOB data was actually generated in the changed path
$ ls -l /disk/testdb/lob/*
/disk/testdb/lob/ces_002: total 4
-rw-------. 1 cubrid cubrid 3 Jan 16 15:34 abc.00001579156443619562_9498
/disk/testdb/lob/ces_092: total 4
-rw-------. 1 cubrid cubrid 6 Jan 16 15:35 test_tbl2.00001579156554249465_0008
/disk/testdb/lob/ces_310: total 4
-rw-------. 1 cubrid cubrid 6 Jan 16 15:35 test_tbl2.00001579156554247635_2273
/disk/testdb/lob/ces_401: total 4
-rw-------. 1 cubrid cubrid 4 Jan 16 15:34 test_tbl.00001579156454712630_8701
/disk/testdb/lob/ces_520: total 4
-rw-------. 1 cubrid cubrid 4 Jan 16 15:34 test_tbl.00001579156454713722_8117
/disk/testdb/lob/ces_541: total 4
-rw-------. 1 cubrid cubrid 4 Jan 16 15:34 test_tbl.00001579156454714721_4275
/disk/testdb/lob/ces_633: total 4
-rw-------. 1 cubrid cubrid 6 Jan 16 15:35 test_tbl2.00001579156554248844_8435
