.psqlrc

-- Author  : Tanveer Munavar
-- Description : scripts to run from a psql session
-- script deployment location `touch ~/.psqlrc`
-- usage : `postgres=> :vacuum_queries`
-- hint : ": + double tab will list all the custom scripts alias"

-- ref https://github.com/heroku/heroku-pg-extras/tree/master/commands
-- ref https://tapoueh.org/blog/2017/12/setting-up-psql-the-postgresql-cli/

\set QUIET 1

-- If you want to display messages when starting the psql prompt you can use the echo command.
-- Always be in a transaction. It's safer this way if you are new postgresql and running SQL in production
-- \set AUTOCOMMIT on

-- return the value NULL in desired format
\pset null '[null]'

-- comma separated numeric
-- \pset numericlocale

-- format output default
\pset format aligned

-- format output custom
\pset format wrapped

-- \set PROMPT1 '%[%033[1m%]%M/%/%R%[%033[0m%]%# '
-- \set PROMPT2 '%M %n@%/%R %# '

-- Show how long each query takes to execute
\timing

-- Use best available output format
-- \x auto

-- set time to local time
\set timezone TO 'asia/kolkata'

-- You can also set verbosity of error reports with options "default", "verbose", or "terse".
\set VERBOSITY verbose

-- If this variable is set to ignorespace, lines which begin with a space are not entered into the history list. If set to a value of ignoredups, lines matching the previous history line are not entered. A value of ignoreboth combines the two options. If unset, or if set to any other value than those above, all lines read in interactive mode are saved on the history list.
\set HISTCONTROL ignoredups

-- The number of commands to store in the command history. The default value is 500.
\set HISTSIZE 1000

-- tab completes SQL keywords in upper
\set COMP_KEYWORD_CASE upper

-- if a statement in a transaction block generates an error,  errors are only ignored in interactive sessions, and not when reading script files
\set ON_ERROR_ROLLBACK interactive

\set uptime 'select now() - pg_postmaster_start_time() AS uptime;'

-- queries > 15ms
\set long_queries 'SELECT now()::timestamp(0) as time,datname,pid,(now() - query_start) AS runtime,EXTRACT(EPOCH FROM (now() - query_start))*1000::INT AS runtime_millisecs,query_start::timestamp(0),usename,client_addr,state, query FROM pg_stat_activity WHERE state <> \'idle\' AND pid <> pg_backend_pid() AND  now() - query_start > \'15 milliseconds\'::INTERVAL ORDER BY EXTRACT(EPOCH FROM (now() - query_start))*1000::INT DESC;'

\set long_queriesn 'SELECT now()::timestamp(0) as time,datname,pid,(now() - query_start) AS runtime,EXTRACT(EPOCH FROM (now() - query_start))*1000::INT AS runtime_millisecs,query_start::timestamp(0),usename,client_addr,state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state <> \'idle\' AND pid <> pg_backend_pid() AND  now() - query_start > \'15 milliseconds\'::INTERVAL ORDER BY EXTRACT(EPOCH FROM (now() - query_start))*1000::INT DESC;'

\set long_queries_bubcardo 'SELECT now()::timestamp(0) as time,datname,pid,(now() - query_start) AS runtime,EXTRACT(EPOCH FROM (now() - query_start))*1000::INT AS runtime_millisecs,query_start::timestamp(0),usename,client_addr,state, query FROM pg_stat_activity WHERE state <> \'idle\' AND pid <> pg_backend_pid() AND  now() - query_start > \'15 milliseconds\'::INTERVAL AND usename=\'bucardoaxs\' ORDER BY EXTRACT(EPOCH FROM (now() - query_start))*1000::INT DESC;'

--
-- vacuum metrics
--

\set vacuum_queries 'SELECT now()::timestamp(0) as date, pid, datname,(now() - query_start) AS runtime,query_start::timestamp(0),usename,client_addr,state,query FROM pg_stat_activity WHERE query <> current_query() and query ilike \'%vacuum%\' AND pid <> pg_backend_pid() AND  now() - query_start > \'1 seconds\'::INTERVAL ORDER BY EXTRACT(EPOCH FROM (now() - query_start))::INT DESC;'

\set vacuum_progress 'select now()::timestamp(0) as date, a.pid, a.datname, relid::regclass, (now() - query_start) AS runtime, phase, floor(heap_blks_scanned::numeric/heap_blks_total::numeric * 100) as heap_scan_percent, floor(heap_blks_vacuumed::numeric/heap_blks_total::numeric * 100) as heap_vacuum_percent, heap_blks_vacuumed, heap_blks_scanned, index_vacuum_count, max_dead_tuples,num_dead_tuples from pg_stat_progress_vacuum a join pg_stat_activity b on a.pid=b.pid  ;'


--
-- connetion metrics
--

\set con_ssl 'select datname, usename , client_addr,state, ssl, version, cipher, bits, count(*) from pg_stat_activity join pg_stat_ssl on pg_stat_activity.pid = pg_stat_ssl.pid group by 1,2,3,4,5,6,7,8 order by 9 desc ;'

\set con_stat 'select state,count(*) from pg_stat_activity group by 1 order by 2 desc ; select datname, client_addr, usename, state, count(1) from pg_stat_activity group by 1,2,3,4 order by 5 desc; SELECT now() as time, datname,usename,waiting, count(1) from pg_stat_activity group by 1,2,3,4 order by 5 desc; select datname, usename, client_addr, now()-xact_start as xact_time, now()-state_change as state_time, state, waiting, pid, query from pg_stat_activity where state <> \'idle\' and pid <> pg_backend_pid() order by 1 desc limit 20;'

\set con_statn 'SELECT now()::timestamp(0);select state,count(*) from pg_stat_activity group by 1 order by 2 desc ; select datname, client_addr, usename, state, count(1) from pg_stat_activity group by 1,2,3,4 order by 5 desc; select datname,usename,wait_event_type,wait_event, count(1) from pg_stat_activity group by 1,2,3,4 order by 5 desc; SELECT now() as time, datname, usename, client_addr, now()-xact_start as xact_time, now()-state_change as state_time, state, wait_event_type, wait_event, pid, query from pg_stat_activity where state <> \'idle\' and pid <> pg_backend_pid() AND  now() - query_start > \'15 milliseconds\'::INTERVAL ORDER BY EXTRACT(EPOCH FROM (now() - query_start))::INT DESC LIMIT 10;'

--
-- wrap around metrics
--

\set wa_db 'select max(age(datfrozenxid)) from pg_database;SELECT datname, age(datfrozenxid) FROM pg_database WHERE datname NOT IN (\'postgres\',\'template1\',\'template0\') ORDER BY age(datfrozenxid) DESC;'

\set wa_tables 'SELECT pg_class.oid::regclass AS full_table_name, greatest(age(pg_class.relfrozenxid), age(toast.relfrozenxid)) as freeze_age, pg_size_pretty(pg_relation_size(pg_class.oid)) as size FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid LEFT OUTER JOIN pg_class as toast ON pg_class.reltoastrelid = toast.oid WHERE nspname not in (\'pg_catalog\', \'information_schema\') AND nspname NOT LIKE \'pg_temp%\' AND pg_class.relkind = \'r\' ORDER BY freeze_age DESC, pg_relation_size(pg_class.oid) DESC LIMIT 30;'

\set wa_tx_before_wraparound_vacuum 'SELECT relnamespace::regnamespace, oid::regclass::text AS table,age(relfrozenxid) AS xid_age, mxid_age(relminmxid) AS mxid_age,least( (SELECT setting::int FROM pg_settings WHERE name = \'autovacuum_freeze_max_age\') - age(relfrozenxid), (SELECT setting::int FROM pg_settings WHERE name = \'autovacuum_multixact_freeze_max_age\') - mxid_age(relminmxid)) AS tx_before_wraparound_vacuum,pg_size_pretty(pg_total_relation_size(oid)) AS size, pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum FROM pg_class WHERE not (relfrozenxid = 0) AND oid > 16384 ORDER BY tx_before_wraparound_vacuum,pg_total_relation_size(oid)  LIMIT 30;'


--
-- locking metrics
--

\set blocks 'SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename  AS blocked_user, blocking_locks.pid     AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement 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 order by blocking_activity.query_start LIMIT 20;      SELECT blocked_locks.pid AS blocked_pid, now() - blocked_activity.query_start as blocked_duration,  blocked_locks.locktype as blocked_locktype,blocked_locks.mode as blocked_mode,blocked_locks.page as blocked_page,blocked_locks.tuple as blocked_tuple,blocking_locks.pid     AS blocking_pid, now() - blocking_activity.query_start as blocking_duration,blocking_locks.locktype as blocking_locktype, blocking_locks.mode as blocking_mode, blocking_locks.page as blocking_page, blocking_locks.tuple as blocking_tuple 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 order by now() - blocking_activity.query_start DESC LIMIT 20 ;'

\set blocksn 'SELECT blocked_locks.pid AS blocked_pid, now() - blocked_activity.query_start as blocked_duration,  blocked_locks.locktype as blocked_locktype,blocked_locks.mode as blocked_mode,blocked_locks.page as blocked_page,blocked_locks.tuple as blocked_tuple, blocked_activity.wait_event_type as blocked_wet, blocked_activity.wait_event as blocked_we,  blocked_activity.state as blocked_state, blocked_activity.query as blocked_query, blocking_locks.pid AS blocking_pid, now() -  blocking_activity.query_start as blocking_duration,blocking_locks.locktype as blocking_locktype, blocking_locks.mode as blocking_mode, blocking_locks.page as blocking_page,blocking_locks.tuple as blocking_tuple, blocking_activity.wait_event_type as blocking_wet,  blocking_activity.wait_event as blocking_we, blocking_activity.state as blocking_state,blocking_activity.query as blocking_query 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 order by now() - blocking_activity.query_start DESC LIMIT 20 ;'

\set locks 'SELECT now()::timestamp(0), pg_locks.pid, locktype,relation as relid, relname, relkind, mode, granted, query FROM pg_locks JOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid JOIN pg_class ON pg_class.oid = pg_locks.relation WHERE relation IS NOT NULL AND pg_locks.pid <> pg_backend_pid() AND granted =false  ORDER BY granted  LIMIT 20;'

\set blocking_locks 'SELECT (extract(epoch from now()) * 1e9)::int8 AS epoch_ns, waiting.locktype           AS tag_waiting_locktype, waiting_stm.usename        AS tag_waiting_user, coalesce(waiting.mode, \'null\'::text) AS tag_waiting_mode, coalesce(waiting.relation::regclass::text, \'null\') AS tag_waiting_table, waiting_stm.query          AS waiting_query, waiting.pid                AS waiting_pid, other.locktype             AS other_locktype, other.relation::regclass   AS other_table, other_stm.query            AS other_query, other.mode                 AS other_mode, other.pid                  AS other_pid, other_stm.usename          AS other_user FROM pg_catalog.pg_locks AS waiting JOIN get_stat_activity() AS waiting_stm ON ( waiting_stm.pid = waiting.pid ) JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other.\"database\" AND waiting.relation  = other.relation ) OR waiting.transactionid = other.transactionid) JOIN get_stat_activity() AS other_stm ON ( other_stm.pid = other.pid ) WHERE NOT waiting.GRANTED AND waiting.pid <> other.pid AND other.GRANTED AND waiting_stm.datname = current_database();'
\set lock_modes 'WITH q_locks AS ( select * from pg_locks where pid != pg_backend_pid() and database = (select oid from pg_database where datname = current_database()) ) SELECT now()::timestamp(0), lockmodes AS tag_lockmode, coalesce((select count(*) FROM q_locks WHERE mode = lockmodes), 0) AS count FROM unnest(\'{AccessShareLock, ExclusiveLock, RowShareLock, RowExclusiveLock, ShareLock, ShareRowExclusiveLock,  AccessExclusiveLock, ShareUpdateExclusiveLock}\'::text[]) lockmodes;'
\set waiting 'SELECT now()::timestamp(0),count(distinct pid) as waiting_for_locks FROM pg_locks WHERE granted = false group by 1;'

\set wait_events 'select datname, wait_event, wait_event_type, count(*) from pg_stat_activity where wait_event is not null and state <> \'idle\' group by 1,2,3 order by 4 DESC ;'


--
-- general queries
--

\set ip 'SELECT now()::timestamp(0), inet_server_addr(), inet_client_addr(), pg_backend_pid(), txid_current(); '


--
-- replication metric
--

-- PG96 or lower version
\set replication 'select now()::timestamp(0), pg_is_in_recovery() as replica;select now()::timestamp(0), now()-pg_last_xact_replay_timestamp() as replication_lag;SELECT now()::timestamp(0), slot_name, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),restart_lsn)) as replicationSlotLag, active from pg_replication_slots;'

-- PG10 or higher version
\set replicationn 'select now()::timestamp(0), pg_is_in_recovery() as replica;select now()::timestamp(0), now()-pg_last_xact_replay_timestamp() as replication_lag;SELECT now()::timestamp(0), slot_name, pg_current_wal_lsn(), restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) as replicationSlotLag, active from pg_replication_slots;'

\set rep_is_in_recovery 'select now()::timestamp(0), pg_is_in_recovery() as replica;'

\set rep_lag 'select now()::timestamp(0), now()-pg_last_xact_replay_timestamp() as replication_lag;'

-- PG96 or lower version
\set rep_slot 'SELECT now()::timestamp(0), slot_name, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),restart_lsn)) as replicationSlotLag, active from pg_replication_slots;'

-- PG10 or higher version
\set rep_slotn 'SELECT now()::timestamp(0), slot_name, pg_current_wal_lsn(), restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),restart_lsn)) as replicationSlotLag, active from pg_replication_slots;'

-- subscription table status
\set sub_status 'select srsubid , srrelid::regclass, srsubstate from pg_subscription_rel where srsubstate <> \'r\' ;'


--
-- postgresql_fdw metrics
--

\set fdwsql 'SELECT \'CREATE FOREIGN TABLE \' || quote_ident(\'${prefix}_\' || c.relname) || \'(\' || array_to_string(array_agg(quote_ident(a.attname) || \' \' || t.typname), \', \') || \') \' || \' SERVER ${prefix}_db OPTIONS\' || \' (schema_name \'\'\' || quote_ident(n.nspname) || \'\'\', table_name \'\'\' || quote_ident(c.relname) || \'\'\');\' FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid AND n.oid = c.relnamespace AND c.relkind in (\'r\', \'v\') AND n.nspname <> \'pg_catalog\' AND n.nspname <> \'information_schema\' AND n.nspname !~ \'^pg_toast\' AND pg_catalog.pg_table_is_visible(c.oid) GROUP BY c.relname, n.nspname ORDER BY c.relname;'


--
-- table metrics
--

\set seq_scan 'SELECT now()::timestamp(0), relname AS name,seq_scan as count FROM pg_stat_user_tables ORDER BY seq_scan DESC;'

\set index_usage 'SELECT now()::timestamp(0), schemaname, relname,CASE idx_scan  WHEN 0 THEN \'Insufficient data\' ELSE (100 * idx_scan / (seq_scan + idx_scan))::text END percent_of_times_index_used, n_live_tup rows_in_table FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20;'

\set cache_hit 'SELECT now()::timestamp(0), \'index hit rate\' AS name, (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio FROM pg_statio_user_indexes UNION ALL SELECT now()::timestamp(0), \'table hit rate\' AS name, sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio FROM pg_statio_user_tables;'

\set record_rank_liv 'SELECT now()::timestamp(0), relname AS name, n_live_tup AS estimated_count FROM pg_stat_user_tables ORDER BY 3 DESC LIMIT 20;'

\set record_rank_ins 'SELECT now()::timestamp(0), relname AS name, n_tup_ins AS insert_count FROM pg_stat_user_tables ORDER BY 3 DESC LIMIT 20;'

\set record_rank_upd 'SELECT now()::timestamp(0), relname AS name, n_tup_upd AS update_count FROM pg_stat_user_tables ORDER BY 3 DESC LIMIT 20;'

\set record_rank_del 'SELECT now()::timestamp(0), relname AS name, n_tup_del AS delete_count FROM pg_stat_user_tables ORDER BY 3 DESC LIMIT 20;'

\set record_rank_total 'SELECT now()::timestamp(0), relname AS name ,  n_tup_ins + n_tup_upd + n_tup_del AS ins_upd_del_total FROM pg_stat_user_tables ORDER BY 3 DESC LIMIT 20;'

-- \set bloat `cat /home/mohamedt/scripts/bloat.sql`


--
-- size metrics
--

\set size_cluster 'SELECT pg_size_pretty(sum(pg_database_size(d.datname))) AS cluster_size FROM pg_database d;'

\set size_db 'SELECT d.datname AS db_name, pg_size_pretty(pg_database_size(d.datname)) AS db_size FROM pg_database d ORDER BY pg_database_size(d.datname) DESC;'

\set size_schema 'SELECT schemaname, pg_size_pretty(SUM(pg_total_relation_size(quote_ident(schemaname) || \'.\' || quote_ident(tablename)))::BIGINT) FROM pg_tables group by schemaname ORDER BY SUM(pg_total_relation_size(quote_ident(schemaname) || \'.\' || quote_ident(tablename))) DESC ;'

\set size_tables 'SELECT relnamespace::regnamespace, relname,pg_size_pretty(pg_relation_size(pg_class.oid, \'main\')) as main, pg_size_pretty(pg_relation_size(pg_class.oid, \'fsm\')) as fsm, pg_size_pretty(pg_relation_size(pg_class.oid, \'vm\')) as vm, pg_size_pretty(pg_relation_size(pg_class.oid, \'init\')) as init, pg_size_pretty(pg_table_size(pg_class.oid)) as table, pg_size_pretty(pg_indexes_size(pg_class.oid)) as indexes, pg_size_pretty(pg_total_relation_size(pg_class.oid)) as total FROM pg_class WHERE relkind=\'r\' ORDER BY pg_total_relation_size(pg_class.oid) DESC LIMIT 10;'

\set size_temp 'SELECT datname , temp_files , pg_size_pretty(temp_bytes) AS temp_size_pretty FROM pg_stat_database where temp_files <> 0 order by temp_bytes desc;'

\set size_wal 'select pg_size_pretty(sum(size)) from pg_ls_waldir();'

\set size_log 'select pg_size_pretty(sum(size)) from pg_ls_logdir();'

\set size_archive_status 'select pg_size_pretty(sum(size)) from pg_ls_archive_statusdir() ;'


--
-- bucardo metrics
--

\set bucardo_manual_purge 'SELECT bucardo.bucardo_purge_delta (\'45 seconds\');'


--
-- maintenance scripts
--

\set unused_index 'SELECT now(), schemaname || \'.\' || relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, idx_scan as index_scans,pg_get_indexdef(i.indexrelid) FROM pg_stat_user_indexes ui JOIN pg_index i ON ui.indexrelid = i.indexrelid WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192 ORDER BY pg_relation_size(i.indexrelid) DESC LIMIT 20 ;'

\set missing_index 'SELECT schemaname, relname, case when seq_scan - idx_scan > 0 THEN \'Missing Index?\' ELSE \'OK\' END, pg_size_pretty(pg_relation_size(format(\'%I.%I\', schemaname, relname)::regclass)) AS rel_size, seq_scan, idx_scan FROM pg_stat_user_tables where schemaname not in (\'bucardo\') order by seq_scan desc;'

\set settings 'SELECT now()::timestamp(0),name as modified_settings, unit, setting FROM pg_settings WHERE boot_val IS DISTINCT FROM setting; SELECT name as default_settings_require_restart, setting, boot_val FROM pg_settings WHERE context = \'postmaster\' AND boot_val = setting;'

\set dblist 'select string_agg(a.datname, \' :\') from (select datname from pg_database where datname not like \'%_old\' and datname not in (\'template0\', \'template1\', \'rdsadmin\', \'postgres\') order by 1 ) a;'


\unset QUIET

-- end of script