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.

Alerts

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/packets
mkdir 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.

DBA tasks

Dev tasks