Skip to content

Use cases

Jump to bottom
O2eg edited this page Apr 17, 2022 · 50 revisions

Various tasks can be solved using the db_converter. This page covers the most simple and useful examples. More examples see in packets directory.

DBA tasks examples

How to create a user from the command line

Task: create a read-only user in all databases

Prepare the packet directory:

cd db_converter
mkdir packets/create_user

Make the packet with one object generator:

cat > packets/create_user/01_gen_obj.sql  << EOL
    SELECT '' as maint, 'md5' || md5('DBC_PL_PASSW' || 'DBC_PL_USER_NAME');
EOL

cat > packets/create_user/01_step.sql << EOL
    CREATE USER DBC_PL_USER_NAME WITH ENCRYPTED PASSWORD 'GEN_OBJ_FLD_1';
EOL

cat > packets/create_user/02_step.sql << EOL
    GRANT USAGE on SCHEMA public TO DBC_PL_USER_NAME;
    ALTER DEFAULT PRIVILEGES FOR ROLE DBC_PL_USER_NAME IN SCHEMA public
        GRANT SELECT ON TABLES TO DBC_PL_USER_NAME;
EOL

Run packet and check output:

python3 db_converter.py --packet-name=create_user --db-name=ALL \
   --placeholders="{'USER_NAME':'new_test_user','PASSW':'12345'}"

>>
	...
	Info: ('CREATE ROLE', None)
	Info: ('GRANT', None)
	Info: ('ALTER DEFAULT PRIVILEGES', None)
	...

To remove the user, run:

ALTER DEFAULT PRIVILEGES FOR ROLE new_test_user IN SCHEMA public
   REVOKE ALL ON TABLES FROM new_test_user;
REVOKE USAGE ON SCHEMA public FROM new_test_user;
DROP USER new_test_user;

Change index fillfactor in specified schemas

Task: there are several databases with a large number of schemes. It is necessary to change the fillfactor for certain indexes

Prepare the packet directory:

cd db_converter
mkdir packets/set_fillfactor_idx

Make the packet with one schema generator:

cat > packets/set_fillfactor_idx/01_gen_nsp.sql  << EOL
    SELECT '' as maint, nspname
    FROM pg_namespace n
    WHERE nspname like 'client_%_n'
EOL

cat > packets/set_fillfactor_idx/01_step.sql << EOL
    ALTER INDEX GEN_NSP_FLD_1.tbl_fld_idx SET (fillfactor = 70);
EOL

Run packet and check the output:

python3 db_converter.py --packet-name=set_fillfactor_idx --db-name=some_db_*

Configure session parameters for certain users

Prepare the packet directory:

cd db_converter
mkdir packets/alter_user

Make the packet with one object generator:

cat > packets/alter_user/01_gen_obj.sql  << EOL
    select '' as maint, 'support_read'
    union
    select '' as maint, 'support_write'
EOL

cat > packets/alter_user/01_step.sql << EOL
    ALTER USER GEN_OBJ_FLD_1 RESET ALL;
    ALTER USER GEN_OBJ_FLD_1 set work_mem = '50MB';
    ALTER USER GEN_OBJ_FLD_1 set maintenance_work_mem = '50MB';
    ALTER USER GEN_OBJ_FLD_1 set max_parallel_workers_per_gather = 0;
    ALTER USER GEN_OBJ_FLD_1 set statement_timeout = '10min';
    ALTER USER GEN_OBJ_FLD_1 set idle_in_transaction_session_timeout = '10min';
    ALTER USER GEN_OBJ_FLD_1 set lock_timeout = '5min';
EOL

Run packet and check the output:

python3 db_converter.py --packet-name=alter_user --db-name=some_db_*

Dev tasks examples

Delete old data from a huge table

Task: delete data older than 6 months. The data being deleted cannot be processed within a single transaction

Prepare the packet directory:

cd db_converter
mkdir packets/delete_old_data

Make the packet with one object generator:

-- a packet of this type can be redeployed
cat > packets/delete_old_data/meta_data.json  << EOL
{
	"type": "maintenance",
	"postgresql": {
		"statement_timeout": "10min"
	}
}
EOL

-- delete data older than six months in 1-week increments; the one transaction deletes data in one week
-- NOTE: taking into account the uniform distribution of data over time
cat > packets/delete_old_data/01_gen_obj.sql  << EOL
with dates as (
	 select
		(select date_trunc('d', min(inserted)) from some_tbl)::TIMESTAMP WITHOUT TIME ZONE as date_a,
		(select date_trunc('d', now() - interval '6 month'))::TIMESTAMP WITHOUT TIME ZONE as date_b
)
select
	(
		case when T.id % 10 = 1    -- every 10 transactions run vacuum analyze
		then 'vacuum analyze some_tbl'
		end
	) as maint, T.a, T.b from (
	with grid_a as (
		select T.dt_a as dt_a, row_number() OVER () as id
		from (
			SELECT generate_series(
				(select date_a - ('1 week'::interval) from dates),
				(select date_b from dates),
				'1 week'::interval
			) as dt_a
			union SELECT date_b from dates
			ORDER BY 1
		) T
	),
	grid_b as (
		select T.dt_b as dt_b, row_number() OVER () as id
		from (
			SELECT generate_series(
				(select date_a from dates),
				(select date_b from dates),
				'1 week'::interval
			) as dt_b
			union SELECT date_b from dates
			ORDER BY 1
		) T
	)
	SELECT A.dt_a as a, B.dt_b as b, A.id from grid_a A
	join grid_b B on A.id = B.id
) T;
EOL

cat > packets/delete_old_data/01_step.sql << EOL
	DELETE
	FROM some_tbl
	WHERE inserted >= 'GEN_OBJ_FLD_1' and inserted < 'GEN_OBJ_FLD_2'
EOL

Run packet and check the output:

python3 db_converter.py --packet-name=delete_old_data --db-name=some_db_*

Update all records in a huge table

Task: Update all records in a huge table. The data being updated cannot be processed within a single transaction

Prepare the packet directory:

cd db_converter
mkdir packets/update_table

Make the packet with one object generator:

cat > packets/update_table/01_gen_obj.sql  << EOL
with pk_intervals as (
	 select
		(select min(id) from some_table) as point_a,
		(select max(id) from some_table) as point_b
)
	select
		(case when T.id % 1000 = 1   -- each 1K transactions run vacuum analyze
			then 'vacuum analyze some_table' end
		) as maint,  -- "maint" is a system field
		T.a,	-- GEN_OBJ_FLD_1
		T.b	-- GEN_OBJ_FLD_2
	from (
		with grid_a as (
			select T.point_a as point_a, row_number() OVER () as id
			from (
				SELECT generate_series(
					(select point_a from pk_intervals),
					(select point_b from pk_intervals) + 50000,
					50000
				) as point_a
				ORDER BY 1
			) T
		),
		grid_b as (
			select T.point_b as point_b, row_number() OVER () as id
			from (
				SELECT generate_series(
					(select point_a from pk_intervals) + 50000,
					(select point_b from pk_intervals) + 50000,
					50000
				) as point_b
				ORDER BY 1
			) T
		)
		SELECT A.point_a as a, B.point_b as b, A.id from grid_a A
		join grid_b B on A.id = B.id
	) T;
EOL

cat > packets/update_table/01_step.sql << EOL
    UPDATE some_table SET id_new = id
    WHERE id >= GEN_OBJ_FLD_1 and id <= GEN_OBJ_FLD_2
EOL

Run packet and check the output:

python3 db_converter.py --packet-name=update_table --db-name=some_db_*

Alerts examples

Hooks can be used to deliver alerts. The launch of an alert package can be scheduled in cron.

How to determine the low number of possible new connections

Prepare the packet directory:

cd db_converter
mkdir packets/alert_max_connections

Make the packet and alert configuration:

cat > packets/alert_max_connections/01_step.sql << EOL
SELECT format('Low number of free connections: %s (%s) [max_connections = %s]', T.c, T.v || '%', T.s) as alert
FROM (
	SELECT
		round(((select count(1) from pg_stat_activity) * 100)::numeric / setting::integer, 2) as v,
		setting::integer - (select count(1) from pg_stat_activity) as c,
		setting as s
	FROM pg_settings
	WHERE name = 'max_connections'
) T WHERE T.v > 70
EOL

cat > packets/alert_max_connections/meta_data.json << EOL
{
	"description": "Check available connections",
	"type": "read_only",
	"client_min_messages": "INFO",
	"hook": {
		"type": "mattermost",
		"username": "db_converter",
		"verbosity": ["resultset"],
		"channel": "dbc-alert",
		"message": ":warning: @here Found problem with max_connections:"
	}
}

If the statement returns a result, then a hook will be triggered to send a message to the mattermost.

Run packet and check the output:

python3 db_converter.py --packet-name=alert_max_connections --db-name=some_db

>>
	----------------------------------------------------------------------
	| alert                                                              |
	----------------------------------------------------------------------
	| Low number of free connections: 10 (10%) [max_connections = 100] |

With a properly configured integration with mattermost, the message will be delivered to the corresponding chat.

How to detect locks

Prepare the packet directory:

cd db_converter
mkdir packets/alert_locks

Make the packet and alert configuration:

cat > packets/alert_locks/01_step.sql << EOL
SELECT
       blocking_locks.pid AS blocker_pid,
       blocking_activity.usename AS blocker_user,
       substring(blocking_activity.query FROM 0 FOR 64) AS blocker_statement,
       blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       substring(blocked_activity.query FROM 0 FOR 64) AS blocked_statement,
       now() - blocked_activity.state_change as blocked_wait_time
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED and now() - blocked_activity.state_change > '1 minute'::interval
LIMIT 10
EOL

cat > packets/alert_locks/meta_data.json << EOL
{
	"description": "Check locks",
	"type": "read_only",
	"client_min_messages": "INFO",
	"hook": {
		"type": "mattermost",
		"username": "db_converter",
		"verbosity": ["resultset"],
		"channel": "dbc-alert",
		"message": ":warning: @here Found locks:"
	}
}
EOL

Run packet and check the output:

python3 db_converter.py --packet-name=alert_locks --db-name=some_db

>>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| blocker_pid | blocker_user | blocker_statement                                     | blocked_pid | blocked_user | blocked_statement            | blocked_wait_time |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 31651       | postgres     | select * from dbc.dbc_actions; select pg_sleep(5000); | 31670       | postgres     | vacuum full dbc.dbc_actions; | 0:41:33.131784    |

Unit test examples

There are two types of testing:

  • unit tests for source code
  • unit tests for packets (packages)

Unit tests for packets are implemented in DBCPacketUnitTest class of tests/test_packets.py file.

Let's consider some package for which needs to write a test:

cd db_converter
mkdir packets/dba_task

cat > packets/dba_task/01_step.sql << EOL
select 1, 2, 3
union
select 4, 5, 6
EOL

To create a test, it is enough to make an empty directory of the same name with test_ prefix and run the test once:

python38 tests/test_packets.py -v DBCPacketUnitTest

The above example looks for all packages for which there is a test package with test_ prefix. Then runs the test one in turn and then main and compares the results of the queries. Before starting each test, the test_dbc_packets database will be recreated.

The first run will create *.sql_out files for each step:

cat packets/test_dba_task/01_step.sql_out
>>
[
    [
        [
            1,
            2,
            3
        ],
        [
            4,
            5,
            6
        ]
    ]
]

When *.sql_out files exist, running of test compares the results of steps to contents of the corresponding *.sql_out files. The test packet can contain data (like CREATE TABLE, INSERT, .. necessary for testing queries.

In some cases, the recordset values may change depending on various factors. For such cases, the keyword <SKIP> is provided:

cd db_converter
mkdir packets/dba_task

cat > packets/dba_task/01_step.sql << EOL
select 1, 2, floor(random() * 1000 + 1)::int
union
select 4, 5, 6
EOL

cat > packets/test_dba_task/01_step.sql_out << EOL
[
    [
        [
            1,
            2,
            <SKIP>
        ],
        [
            4,
            5,
            6
        ]
    ]
]
EOL

python38 tests/test_packets.py -v DBCPacketUnitTest
>>
    ...
    Info: Thread 'manager_db_test_dbc_packets', DB 'test_dbc_packets', PID 6682, Packet 'dba_task', Step '01_step.sql': progress 0.0%
    Info: 
    ----------------------------------
    | ?column? | ?column? | ?column? |
    ----------------------------------
    | 1        | 2        | 871      |
    | 4        | 5        | 6        |

    Info: <-------- Packet 'dba_task' finished for 'test_dbc_packets' database!
    ...
    Validating: 01_step.sql OK

Clone this wiki locally