panzelva · March 16, 2020 10:00
diff --git a/change_user_password.sql b/change_user_password.sql
 ALTER USER <user_name> WITH PASSWORD '<user_password>';
diff --git a/create_readonly_user.sql b/create_readonly_user.sql
 -- Create a group
 CREATE ROLE readonly;

 -- Grant access to existing tables
 GRANT USAGE ON SCHEMA public TO readonly;
 GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

 -- Grant access to future tables
 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

 -- Create a final user with password
 CREATE USER <user_name> WITH PASSWORD '<user_password>';
 GRANT readonly TO <user_name>;
diff --git a/create_superuser.sql b/create_superuser.sql
 -- create superuser
 CREATE USER <user_name> WITH PASSWORD '<user_name>' WITH SUPERUSER;
 ALTER USER <user_name> WITH SUPERUSER;

 -- revoke superuser
 ALTER USER <user_name> WITH NOSUPERUSER;
 DROP USER <user_name>;
diff --git a/todo.sql b/todo.sql
 -- show running queries (pre 9.2)
 SELECT procpid, age(clock_timestamp(), query_start), usename, current_query 
 FROM pg_stat_activity 
 WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
 ORDER BY query_start desc;

 -- show running queries (9.2)
 SELECT age(clock_timestamp(), query_start) AS "age", * 
 FROM pg_stat_activity 
 WHERE query NOT ILIKE '%pg_stat_activity%' AND state = 'active'
 ORDER BY query_start DESC;

 -- kill running query
 SELECT pg_cancel_backend(procpid);

 -- kill idle query
 SELECT pg_terminate_backend(procpid);

 -- vacuum command
 VACUUM (VERBOSE, ANALYZE);

 -- all database users
 select * from pg_stat_activity where current_query not like '<%';

 -- all databases and their sizes
 select * from pg_user;

 -- all tables and their size, with/without indexes
 select datname, pg_size_pretty(pg_database_size(datname))
 from pg_database
 order by pg_database_size(datname) desc;

 -- cache hit rates (should not be less than 0.99)
 SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
 FROM pg_statio_user_tables;

 -- table index usage rates (should not be less than 0.99)
 SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
 FROM pg_stat_user_tables 
 ORDER BY n_live_tup DESC;

 -- how many indexes are in cache
 SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
 FROM pg_statio_user_indexes;

 -- Dump database on remote host to file
 $ pg_dump -U username -h hostname databasename > dump.sql

 -- Import dump into existing database
 $ psql -d newdb -f dump.sql
	-- Create a group
	CREATE ROLE readonly;

	-- Grant access to existing tables
	GRANT USAGE ON SCHEMA public TO readonly;
	GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

	-- Grant access to future tables
	ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

	-- Create a final user with password
	CREATE USER <user_name> WITH PASSWORD '<user_password>';
	GRANT readonly TO <user_name>;
	-- create superuser
	CREATE USER <user_name> WITH PASSWORD '<user_name>' WITH SUPERUSER;
	ALTER USER <user_name> WITH SUPERUSER;

	-- revoke superuser
	ALTER USER <user_name> WITH NOSUPERUSER;
	DROP USER <user_name>;
	-- show running queries (pre 9.2)
	SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
	FROM pg_stat_activity
	WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
	ORDER BY query_start desc;

	-- show running queries (9.2)
	SELECT age(clock_timestamp(), query_start) AS "age", *
	FROM pg_stat_activity
	WHERE query NOT ILIKE '%pg_stat_activity%' AND state = 'active'
	ORDER BY query_start DESC;

	-- kill running query
	SELECT pg_cancel_backend(procpid);

	-- kill idle query
	SELECT pg_terminate_backend(procpid);

	-- vacuum command
	VACUUM (VERBOSE, ANALYZE);

	-- all database users
	select * from pg_stat_activity where current_query not like '<%';

	-- all databases and their sizes
	select * from pg_user;

	-- all tables and their size, with/without indexes
	select datname, pg_size_pretty(pg_database_size(datname))
	from pg_database
	order by pg_database_size(datname) desc;

	-- cache hit rates (should not be less than 0.99)
	SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
	FROM pg_statio_user_tables;

	-- table index usage rates (should not be less than 0.99)
	SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
	FROM pg_stat_user_tables
	ORDER BY n_live_tup DESC;

	-- how many indexes are in cache
	SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
	FROM pg_statio_user_indexes;

	-- Dump database on remote host to file
	$ pg_dump -U username -h hostname databasename > dump.sql

	-- Import dump into existing database
	$ psql -d newdb -f dump.sql