pg_cheat_funcs

pig build pkg pg_cheat_funcs;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install pg_cheat_funcs;		# install via package name, for the active PG version

pig install pg_cheat_funcs -v 18;   # install for PG 18
pig install pg_cheat_funcs -v 17;   # install for PG 17
pig install pg_cheat_funcs -v 16;   # install for PG 16
pig install pg_cheat_funcs -v 15;   # install for PG 15
pig install pg_cheat_funcs -v 14;   # install for PG 14

CREATE EXTENSION pg_cheat_funcs;

SELECT pg_signal_process(12345, 'TERM');       -- send signal to a PG process
SELECT pg_get_priority(pg_backend_pid());      -- get scheduling priority
SELECT pg_set_priority(pg_backend_pid(), 10);  -- set scheduling priority (-20..19)
SELECT pg_postmaster_pid();                    -- get postmaster PID
SELECT pg_backend_start_time();                -- server process start time

-- Show memory context statistics (PG 9.6-13)
SELECT * FROM pg_stat_get_memory_context();
-- Columns: name, parent, level, total_bytes, total_nblocks, free_bytes, free_chunks, used_bytes

-- Show cached plan info for a prepared statement
SELECT * FROM pg_cached_plan_source('my_stmt');
-- Columns: generic_cost, total_custom_cost, num_custom_plans, force_generic, force_custom

SELECT pg_xlogfile_name('0/1234568'::pg_lsn, false);  -- LSN to WAL filename
SELECT pg_wait_syncrep('0/1234568'::pg_lsn);           -- wait for sync rep
SELECT * FROM pg_stat_get_syncrep_waiters();            -- list sync rep waiters
SELECT pg_set_next_xid('100'::xid);                    -- set next transaction ID (dangerous)
SELECT * FROM pg_xid_assignment();                      -- XID state info

SELECT pg_checkpoint(true, true, true);  -- fast, wait, force
SELECT pg_promote(true);                 -- promote standby (PG <= 11)
SELECT * FROM pg_recovery_settings();    -- show recovery.conf parameters
SELECT pg_show_primary_conninfo();       -- show primary_conninfo

SELECT * FROM pg_list_relation_filepath('my_table'::regclass);  -- list segment files
SELECT pg_file_write_binary('/tmp/test', '\x48656c6c6f'::bytea); -- write binary file
SELECT pg_file_fsync('/tmp/test');                                -- fsync file

SELECT to_octal(255);                   -- '377'
SELECT pg_text_to_hex('PostgreSQL');     -- '506f737467726553514c'
SELECT pg_hex_to_text('506f737467726553514c'); -- 'PostgreSQL'
SELECT pg_chr(9731);                     -- snowman character

SELECT pglz_compress('some text data');        -- PGLZ compress text to bytea
SELECT pglz_decompress(compressed_data);       -- decompress back to text
SELECT pglz_compress_bytea(data);              -- compress bytea
SELECT pglz_decompress_bytea(compressed_data); -- decompress to bytea

SELECT pg_advisory_xact_unlock(12345);              -- release exclusive advisory lock
SELECT pg_advisory_xact_unlock_shared(12345);       -- release shared advisory lock