Use cases

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

Change index fillfactor in specified schemes

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 a packet with one schema generator:

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

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

Run packet and check output:

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

Dev tasks examples

Alerts examples

Hooks can be used to deliver alerts. Launch of the 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 > 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 > alert_max_connections/meta_data.json << EOL
{
	"description": "Check available connections",
	"type": "read_only",
	"client_min_messages": "INFO",
	"hook": {
		"type": "matterhook",
		"username": "db_converter",
		"verbosity": ["resultset"],
		"channel": "dbc-alert",
		"message": ":warning: @here Found problem with max_connections:"
	}
}

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

Run packet and check 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 > 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 > alert_locks/meta_data.json << EOL
{
	"description": "Check locks",
	"type": "read_only",
	"client_min_messages": "INFO",
	"hook": {
		"type": "matterhook",
		"username": "db_converter",
		"verbosity": ["resultset"],
		"channel": "dbc-alert",
		"message": ":warning: @here Found locks:"
	}
}
EOL

Run packet and check 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    |