백업과 마운트

BACKUP DATABASE INTO DISK = 'path/to/backup_directory_name';

BACKUP TABLE table_name INTO DISK = 'path/to/backup_directory_name';

BACKUP DATABASE AFTER 'path/to/previous_backup' INTO DISK = 'path/to/incremental_backup_dir';

BACKUP TABLE table_name AFTER 'path/to/previous_backup' INTO DISK = 'path/to/incremental_backup_dir';

BACKUP DATABASE
    FROM time_expression_start
    TO time_expression_end
    INTO DISK = 'path/to/backup_directory_name';

BACKUP TABLE table_name
    FROM time_expression_start
    TO time_expression_end
    INTO DISK = 'path/to/backup_directory_name';

MOUNT DATABASE '/path/to/backup_directory' TO mount_name;

SELECT column_list
FROM mount_name.user_name.table_name
WHERE [conditions];

MOUNT DATABASE '/backup/jan_data' TO backup_jan;

-- Querying table 'sensor_data' (owned by 'sys') from a backup mounted as 'backup_jan'
SELECT COUNT(*) FROM backup_jan.sys.sensor_data;
SELECT * FROM backup_jan.sys.sensor_data
 WHERE time BETWEEN TO_DATE('2024-01-05') AND TO_DATE('2024-01-06');

UNMOUNT DATABASE mount_name;

machbase-neo restore --data <machbase_home_dir> <path/to/backup_directory>

CREATE TAG TABLE IF NOT EXISTS EQPT_A (
    name VARCHAR(20) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE SUMMARIZED
) tag_partition_count=1;

CREATE TAG TABLE IF NOT EXISTS EQPT_B (
    name VARCHAR(20) PRIMARY KEY,
    time DATETIME BASETIME,
    value DOUBLE SUMMARIZED
) tag_partition_count=1;

SELECT TO_CHAR(MIN(time)), TO_CHAR(MAX(time)) FROM EQPT_A;
SELECT COUNT(*) FROM EQPT_A;

-- 1. Perform a full database backup into a specified directory
-- Use a new target path and ensure Machbase has permission to create it.
BACKUP DATABASE INTO DISK = '/backup/full_db_20240630'; -- Use an appropriate path

-- 2. Mount the backup with an alias 'mount_fulldb'
MOUNT DATABASE '/backup/full_db_20240630' TO mount_fulldb;

-- 3. Query data from the mounted backup
-- Check time range in mounted EQPT_A
SELECT TO_CHAR(MIN(time)), TO_CHAR(MAX(time)) FROM mount_fulldb.sys.EQPT_A;
-- Check row count in mounted EQPT_A
SELECT COUNT(*) FROM mount_fulldb.sys.EQPT_A;
-- Query specific data from mounted EQPT_B
SELECT name, TO_CHAR(time), value FROM mount_fulldb.sys.EQPT_B LIMIT 5;

-- 4. Unmount the backup when access is no longer needed
UNMOUNT DATABASE mount_fulldb;

-- 1. Backup data only from Jan 1st, 2024 to Mar 31st, 2024
BACKUP DATABASE
    FROM TO_DATE('2024-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    TO TO_DATE('2024-03-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    INTO DISK = '/backup/db_2024Q1'; -- Use an appropriate path

-- 2. (Optional) Simulate data aging by deleting older data from the live tables
DELETE FROM EQPT_A BEFORE TO_DATE('2024-03-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
DELETE FROM EQPT_B BEFORE TO_DATE('2024-03-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS');
-- Verify live data count has decreased
SELECT COUNT(*) FROM EQPT_A;

-- 3. Mount the time-range backup
MOUNT DATABASE '/backup/db_2024Q1' TO mount_q1;

-- 4. Query the mounted backup - it should contain the original data for Q1
SELECT COUNT(*) FROM mount_q1.sys.EQPT_A; -- Should match original Q1 count
SELECT TO_CHAR(MIN(time)), TO_CHAR(MAX(time)) FROM mount_q1.sys.EQPT_A; -- Should show Q1 range

-- Compare counts between live (post-delete) and mounted (pre-delete)
SELECT COUNT(*) AS live_count FROM EQPT_A;
SELECT COUNT(*) AS mounted_q1_count FROM mount_q1.sys.EQPT_A;

-- 5. Unmount the backup
UNMOUNT DATABASE mount_q1;

-- 1. Backup only table EQPT_A for the period April 1st to May 15th, 2024
BACKUP TABLE EQPT_A
    FROM TO_DATE('2024-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
    TO TO_DATE('2024-05-15 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
    INTO DISK = '/backup/eqpta_20240401_20240515'; -- Use an appropriate path

-- 2. Mount the table-specific backup
MOUNT DATABASE '/backup/eqpta_20240401_20240515' TO mount_eqpta_partial;

-- 3. Query the mounted backup
-- Verify time range and count match the specified backup window
SELECT TO_CHAR(MIN(time)), TO_CHAR(MAX(time)) FROM mount_eqpta_partial.sys.EQPT_A;
SELECT COUNT(*) FROM mount_eqpta_partial.sys.EQPT_A;
-- Query raw data sample
SELECT name, TO_CHAR(time), value FROM mount_eqpta_partial.sys.EQPT_A LIMIT 5;

-- Attempting to query EQPT_B from this mount will fail (it wasn't included)
-- SELECT COUNT(*) FROM mount_eqpta_partial.sys.EQPT_B; -- Expected error: table not found

-- 4. Unmount the backup
UNMOUNT DATABASE mount_eqpta_partial;

-- Assuming backups from previous examples exist:
-- '/backup/db_2024Q1' (Full DB, Q1)
-- '/backup/full_db_20240630' (Full DB, up to Jun 30)
-- '/backup/eqpta_20240401_20240515' (EQPT_A only, Apr 1 - May 15)

-- 1. Mount all three backups with unique aliases
MOUNT DATABASE '/backup/db_2024Q1' TO mount_q1;
MOUNT DATABASE '/backup/full_db_20240630' TO mount_jun30;
MOUNT DATABASE '/backup/eqpta_20240401_20240515' TO mount_eqpta_partial;

-- 2. Query data across different mounts
-- Count from EQPT_A in Q1 backup
SELECT COUNT(*) FROM mount_q1.sys.EQPT_A;
-- Count from EQPT_B in the full backup
SELECT COUNT(*) FROM mount_jun30.sys.EQPT_B;
-- Count from EQPT_A in the partial table backup
SELECT COUNT(*) FROM mount_eqpta_partial.sys.EQPT_A;
-- Attempt to count EQPT_B from the partial backup (will fail)
-- SELECT COUNT(*) FROM mount_eqpta_partial.sys.EQPT_B;

-- 3. Unmount all backups when finished
UNMOUNT DATABASE mount_q1;
UNMOUNT DATABASE mount_jun30;
UNMOUNT DATABASE mount_eqpta_partial;