Analyze: Convert TimescaleDB to plain tables (Auditlog)

[ootneim]

Analyze how to convert data from TimescaleDB to normal tables.

• Create a clone of an existing database for testing.
• Check how long the process takes for tt02 and prod.
• Document procedure

Procedure:

1. Copy data from Timescaledb to plan table
2. Drop the Timescaledb tables
3. Rename the plan tables to the original Timescaledb name
4. Remove the timescaledb exitension for PostgreSQL

Time to copy data from timescaldb to plain:

• TT02: 1.5h
• PROD 8,5h
  • 8vcpu, 32GB RAM = 4h 12min

Scripts

Run script as auth_auditlog_admin

authentication-eventlog

View colums in the table

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'authentication'
  AND table_name = 'eventlog';

Create new table

CREATE TABLE authentication.eventlog_plain (
    sessionid TEXT,
    externalsessionid TEXT,
    subscriptionkey TEXT,
    externaltokenissuer TEXT,
    created TIMESTAMPTZ,
    userid TEXT,
    supplierid TEXT,
    orgnumber TEXT,
    eventtypeid INTEGER,
    authenticationmethodid INTEGER,
    authenticationlevelid INTEGER,
    ipaddress TEXT,
    isauthenticated BOOLEAN
);

Copy data

INSERT INTO authentication.eventlog_plain (
    sessionid, 
    externalsessionid, 
    subscriptionkey, 
    externaltokenissuer, 
    created, 
    userid, 
    supplierid, 
    orgnumber, 
    eventtypeid, 
    authenticationmethodid, 
    authenticationlevelid, 
    ipaddress, 
    isauthenticated
)
SELECT 
    sessionid, 
    externalsessionid, 
    subscriptionkey, 
    externaltokenissuer, 
    created, 
    userid, 
    supplierid, 
    orgnumber, 
    eventtypeid, 
    authenticationmethodid, 
    authenticationlevelid, 
    ipaddress, 
    isauthenticated
FROM authentication.eventlog;

Verify data

SELECT 'authentication.eventlog' AS table_name, COUNT(*) AS row_count
FROM authentication.eventlog
UNION ALL
SELECT 'authentication.eventlog_plain' AS table_name, COUNT(*) AS row_count
FROM authentication.eventlog_plain;

Drop and rename table

DROP TABLE authentication.eventlog CASCADE;
ALTER TABLE authentication.eventlog_plain RENAME TO eventlog;

authz.eventlog

View colums in the table

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'authz'
  AND table_name = 'eventlog';

Create new table

CREATE TABLE authz.eventlog_plain (
    sessionid TEXT,
    created TIMESTAMPTZ,
    subjectuserid TEXT,
    subjectorgcode TEXT,
    subjectorgnumber TEXT,
    subjectparty TEXT,
    resourcepartyid TEXT,
    resource TEXT,
    instanceid TEXT,
    operation TEXT,
    ipaddress TEXT,
    contextrequestjson TEXT,
    decision TEXT
);

Copy data

INSERT INTO authz.eventlog_plain (
    sessionid, 
    created, 
    subjectuserid, 
    subjectorgcode, 
    subjectorgnumber, 
    subjectparty, 
    resourcepartyid, 
    resource, 
    instanceid, 
    operation, 
    ipaddress, 
    contextrequestjson, 
    decision
)
SELECT 
    sessionid, 
    created, 
    subjectuserid, 
    subjectorgcode, 
    subjectorgnumber, 
    subjectparty, 
    resourcepartyid, 
    resource, 
    instanceid, 
    operation, 
    ipaddress, 
    contextrequestjson, 
    decision
FROM authz.eventlog;

Verify data

SELECT 'authz.eventlog' AS table_name, COUNT(*) AS row_count
FROM authz.eventlog
UNION ALL
SELECT 'authz.eventlog_plain' AS table_name, COUNT(*) AS row_count
FROM authz.eventlog_plain;

Drop and rename table

DROP TABLE authz.eventlog CASCADE;
ALTER TABLE authz.eventlog_plain RENAME TO eventlog;

Before upgrading to version 16

Check if extension is dropped

SELECT * FROM pg_extension WHERE extname = 'timescaledb';

Drop extension is it still present

DROP EXTENSION IF EXISTS timescaledb CASCADE;

[Herskis]

Utføre en test med kraftigere jern før vi avslutter analysen