SET search_path = ''; SET timezone TO 'Europe/Paris'; CREATE SCHEMA "PGTS"; -- Extension should be installable in a custom schema CREATE EXTENSION pg_track_settings WITH SCHEMA "PGTS"; -- But not relocatable ALTER EXTENSION pg_track_settings SET SCHEMA public; ERROR: extension "pg_track_settings" does not support SET SCHEMA -- Check the relations that aren't dumped WITH ext AS ( SELECT c.oid, c.relname FROM pg_depend d JOIN pg_extension e ON d.refclassid = 'pg_extension'::regclass AND e.oid = d.refobjid AND e.extname = 'pg_track_settings' JOIN pg_class c ON d.classid = 'pg_class'::regclass AND c.oid = d.objid ), dmp AS ( SELECT unnest(extconfig) AS oid FROM pg_extension WHERE extname = 'pg_track_settings' ) SELECT ext.relname FROM ext LEFT JOIN dmp USING (oid) WHERE dmp.oid IS NULL ORDER BY ext.relname COLLATE "C"; relname ------------------------------------ pg_track_settings_rds_src_tmp pg_track_settings_reboot_src_tmp pg_track_settings_settings_src_tmp (3 rows) -- Check that all objects are stored in the expected schema WITH ext AS ( SELECT pg_describe_object(d.classid, d.objid, d.objsubid) AS descr FROM pg_depend d JOIN pg_extension e ON d.refclassid = 'pg_extension'::regclass AND e.oid = d.refobjid AND e.extname = 'pg_track_settings' ) SELECT descr FROM ext WHERE descr NOT like '%"PGTS".%' ORDER BY descr COLLATE "C"; descr ------- (0 rows) -- test main config history SELECT COUNT(*) FROM "PGTS".pg_track_settings_history; count ------- 0 (1 row) SET work_mem = '10MB'; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) SELECT pg_catalog.pg_sleep(1); pg_sleep ---------- (1 row) SET work_mem = '5MB'; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) SELECT name, setting_exists, setting, setting_pretty FROM "PGTS".pg_track_settings_log('work_mem') ORDER BY ts ASC; name | setting_exists | setting | setting_pretty ----------+----------------+---------+---------------- work_mem | t | 10240 | 10MB work_mem | t | 5120 | 5MB (2 rows) SELECT name, from_setting, from_exists, to_setting, to_exists, from_setting_pretty, to_setting_pretty FROM "PGTS".pg_track_settings_diff(now() - interval '500 ms', now()); name | from_setting | from_exists | to_setting | to_exists | from_setting_pretty | to_setting_pretty ----------+--------------+-------------+------------+-----------+---------------------+------------------- work_mem | 10240 | t | 5120 | t | 10MB | 5MB (1 row) -- test pg_db_role_settings ALTER DATABASE postgres SET work_mem = '1MB'; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) ALTER ROLE postgres SET work_mem = '2MB'; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) ALTER ROLE postgres IN DATABASE postgres SET work_mem = '3MB'; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) SELECT COALESCE(datname, '-') AS datname, setrole::regrole, name, setting_exists, setting FROM "PGTS".pg_track_db_role_settings_log('work_mem') s LEFT JOIN pg_database d ON d.oid = s.setdatabase ORDER BY ts ASC; datname | setrole | name | setting_exists | setting ----------+----------+----------+----------------+--------- postgres | - | work_mem | t | 1MB - | postgres | work_mem | t | 2MB postgres | postgres | work_mem | t | 3MB (3 rows) SELECT COALESCE(datname, '-') AS datname, setrole::regrole, name, from_setting, from_exists, to_setting, to_exists FROM "PGTS".pg_track_db_role_settings_diff(now() - interval '10 min', now()) s LEFT JOIN pg_database d ON d.oid = s.setdatabase WHERE name = 'work_mem' ORDER BY 1, 2, 3; datname | setrole | name | from_setting | from_exists | to_setting | to_exists ----------+----------+----------+--------------+-------------+------------+----------- - | postgres | work_mem | | f | 2MB | t postgres | - | work_mem | | f | 1MB | t postgres | postgres | work_mem | | f | 3MB | t (3 rows) ALTER DATABASE postgres RESET work_mem; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) ALTER ROLE postgres RESET work_mem; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) ALTER ROLE postgres IN DATABASE postgres RESET work_mem; SELECT * FROM "PGTS".pg_track_settings_snapshot(); pg_track_settings_snapshot ---------------------------- t (1 row) -- test pg_reboot SELECT COUNT(*) FROM "PGTS".pg_reboot; count ------- 1 (1 row) SELECT now() - ts > interval '2 second' FROM "PGTS".pg_reboot; ?column? ---------- t (1 row) SELECT now() - ts > interval '2 second' FROM "PGTS".pg_track_reboot_log(); ?column? ---------- t (1 row) -- test the reset SELECT * FROM "PGTS".pg_track_settings_reset(); pg_track_settings_reset ------------------------- (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_history; count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_log('work_mem'); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_diff(now() - interval '1 hour', now()); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_db_role_settings_log('work_mem'); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_db_role_settings_diff(now() - interval '1 hour', now()); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_reboot; count ------- 0 (1 row) -------------------------- -- test remote snapshot -- -------------------------- -- fake general settings INSERT INTO "PGTS".pg_track_settings_settings_src_tmp (srvid, ts, name, setting, current_setting) VALUES (1, '2019-01-01 00:00:00 CET', 'work_mem', '0', '0MB'); -- fake rds settings INSERT INTO "PGTS".pg_track_settings_rds_src_tmp (srvid, ts, name, setting, setdatabase, setrole) VALUES (1, '2019-01-01 00:00:00 CET', 'work_mem', '0MB', 123, 0); -- fake reboot settings INSERT INTO "PGTS".pg_track_settings_reboot_src_tmp (srvid, ts, postmaster_ts) VALUES (1, '2019-01-01 00:01:00 CET', '2019-01-01 00:00:00 CET'); SELECT "PGTS".pg_track_settings_snapshot_settings(1); pg_track_settings_snapshot_settings ------------------------------------- t (1 row) SELECT "PGTS".pg_track_settings_snapshot_rds(1); pg_track_settings_snapshot_rds -------------------------------- t (1 row) SELECT "PGTS".pg_track_settings_snapshot_reboot(1); pg_track_settings_snapshot_reboot ----------------------------------- t (1 row) -- fake general settings INSERT INTO "PGTS".pg_track_settings_settings_src_tmp (srvid, ts, name, setting, current_setting) VALUES -- previously untreated data that should be discarded (1, '2019-01-02 00:00:00 CET', 'work_mem', '5120', '5MB'), -- data that should be processed (1, '2019-01-02 01:00:00 CET', 'work_mem', '10240', '10MB'), (1, '2019-01-02 01:00:00 CET', 'something', 'someval', 'someval'); -- fake rds settings INSERT INTO "PGTS".pg_track_settings_rds_src_tmp (srvid, ts, name, setting, setdatabase, setrole) VALUES -- previously untreated data that should be discarded (1, '2019-01-02 00:00:00 CET', 'work_mem', '5MB', 123, 0), -- data that should be processed (1, '2019-01-02 01:00:00 CET', 'work_mem', '10MB', 123, 0), (1, '2019-01-02 01:00:00 CET', 'something', 'someval', 0, 456); -- fake reboot settings INSERT INTO "PGTS".pg_track_settings_reboot_src_tmp (srvid, ts, postmaster_ts) VALUES -- previously untreated data that should not be discarded (1, '2019-01-02 00:01:00 CET', '2019-01-02 00:00:00 CET'), -- data that should also be processed (1, '2019-01-02 02:01:00 CET', '2019-01-02 01:00:00 CET'); SELECT "PGTS".pg_track_settings_snapshot_settings(1); pg_track_settings_snapshot_settings ------------------------------------- t (1 row) SELECT "PGTS".pg_track_settings_snapshot_rds(1); pg_track_settings_snapshot_rds -------------------------------- t (1 row) SELECT "PGTS".pg_track_settings_snapshot_reboot(1); pg_track_settings_snapshot_reboot ----------------------------------- t (1 row) -- test raw data SELECT * FROM "PGTS".pg_track_settings_list ORDER BY 1, 2; srvid | name -------+----------- 1 | something 1 | work_mem (2 rows) SELECT * FROM "PGTS".pg_track_settings_history ORDER BY 1, 2, 3; srvid | ts | name | setting | is_dropped | setting_pretty -------+------------------------------+-----------+---------+------------+---------------- 1 | Tue Jan 01 00:00:00 2019 CET | work_mem | 0 | f | 0MB 1 | Wed Jan 02 01:00:00 2019 CET | something | someval | f | someval 1 | Wed Jan 02 01:00:00 2019 CET | work_mem | 10240 | f | 10MB (3 rows) SELECT * FROM "PGTS".pg_track_db_role_settings_list ORDER BY 1, 2; srvid | name | setdatabase | setrole -------+-----------+-------------+--------- 1 | something | 0 | 456 1 | work_mem | 123 | 0 (2 rows) SELECT * FROM "PGTS".pg_track_db_role_settings_history ORDER BY 1, 2, 3; srvid | ts | name | setdatabase | setrole | setting | is_dropped -------+------------------------------+-----------+-------------+---------+---------+------------ 1 | Tue Jan 01 00:00:00 2019 CET | work_mem | 123 | 0 | 0MB | f 1 | Wed Jan 02 01:00:00 2019 CET | something | 0 | 456 | someval | f 1 | Wed Jan 02 01:00:00 2019 CET | work_mem | 123 | 0 | 10MB | f (3 rows) SELECT * FROM "PGTS".pg_reboot ORDER BY 1, 2; srvid | ts -------+------------------------------ 1 | Tue Jan 01 00:00:00 2019 CET 1 | Wed Jan 02 00:00:00 2019 CET 1 | Wed Jan 02 01:00:00 2019 CET (3 rows) -- test functions SELECT name, setting_exists, setting, setting_pretty FROM "PGTS".pg_track_settings_log('work_mem', 1) ORDER BY ts ASC; name | setting_exists | setting | setting_pretty ----------+----------------+---------+---------------- work_mem | t | 0 | 0MB work_mem | t | 10240 | 10MB (2 rows) SELECT name, from_setting, from_exists, to_setting, to_exists, from_setting_pretty, to_setting_pretty FROM "PGTS".pg_track_settings_diff('2019-01-01 01:00:00 CET', '2019-01-02 02:00:00 CET', 1); name | from_setting | from_exists | to_setting | to_exists | from_setting_pretty | to_setting_pretty -----------+--------------+-------------+------------+-----------+---------------------+------------------- something | | f | someval | t | | someval work_mem | 0 | t | 10240 | t | 0MB | 10MB (2 rows) SELECT * FROM "PGTS".pg_track_db_role_settings_log('work_mem', 1) s ORDER BY ts ASC; ts | setdatabase | setrole | name | setting_exists | setting ------------------------------+-------------+---------+----------+----------------+--------- Tue Jan 01 00:00:00 2019 CET | 123 | 0 | work_mem | t | 0MB Wed Jan 02 01:00:00 2019 CET | 123 | 0 | work_mem | t | 10MB (2 rows) SELECT * FROM "PGTS".pg_track_db_role_settings_diff('2018-12-31 02:00:00 CET', '2019-01-02 03:00:00 CET', 1) s WHERE name = 'work_mem' ORDER BY 1, 2, 3; setdatabase | setrole | name | from_setting | from_exists | to_setting | to_exists -------------+---------+----------+--------------+-------------+------------+----------- 123 | 0 | work_mem | | f | 10MB | t (1 row) SELECT * FROM "PGTS".pg_track_reboot_log(1); ts ------------------------------ Tue Jan 01 00:00:00 2019 CET Wed Jan 02 00:00:00 2019 CET Wed Jan 02 01:00:00 2019 CET (3 rows) -- check that all data have been deleted after processing SELECT COUNT(*) FROM "PGTS".pg_track_settings_settings_src_tmp; count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_rds_src_tmp; count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_reboot_src_tmp; count ------- 0 (1 row) -- test the reset SELECT * FROM "PGTS".pg_track_settings_reset(1); pg_track_settings_reset ------------------------- (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_history; count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_log('work_mem', 1); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_settings_diff('-infinity', 'infinity', 1); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_db_role_settings_log('work_mem', 1); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_track_db_role_settings_diff('-infinity', 'infinity', 1); count ------- 0 (1 row) SELECT COUNT(*) FROM "PGTS".pg_reboot; count ------- 0 (1 row)