30个实用SQL语句，玩转PostgreSQL

select count(*) from pg_stat_activity;
1.

show max_connections
1.

select * from pg_stat_activity;
1.

select usename, count(*) from pg_stat_activity group by usename; 
1.

GRANT SELECT ON table_name TO username;
1.

alter table table_name owner to username;
1.

grant all privileges on table product to username;
1.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
1.

select * from pg_indexes where tablename = 'product'; 
1.

select * from pg_tables;
1.

select * from pg_extension; 
1.

select relname as TABLE_NAME ,col_description(c.oid, 0) as COMMENTS from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%'
1.

select pg_size_pretty (pg_database_size('db_product'));
1.

select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;
1.

select pg_size_pretty(pg_relation_size('product')) as size;
1.

select pg_size_pretty(pg_total_relation_size('table_name')) as size;
1.

select pg_size_pretty(pg_indexes_size('product'));
1.

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by rowCounts desc
1.

select pg_relation_filepath('product');
1.

select version();
1.

select pg_conf_load_time();
1.

select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
1.

select * from pg_stat_statements order by total_time desc limit 5;
1.

SELECT substring(query, 1, 1000) AS short_query,
round(total_time::numeric, 2) AS total_time,
calls,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 3;
1.
2.
3.
4.
5.
6.
7.

EXPLAIN ANALYZE SELECT * FROM product
1.

select datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start, query_stay, replace(query, chr(10), ' ') as query from (select pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;
1.

select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
1.

select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
1.

select datname,xact_rollback,deadlocks from pg_stat_database
1.

select * from pg_stat_activity where query ilike '%<table_name>%' and query_start - now() > interval '10 seconds';
1.

pg_dump -h 127.0.0.1 -p 5432 -U postgres -f postgres.sql.tar -Ft
1.

pg_dumpall -d postgres -U postgres -f postgres.sql --column-inserts
1.