introduction
PostgreSQL is an open-source relational database with very powerful functions. It supports a variety of indexing modes such as hash indexing, reverse indexing, partial indexing, Expression indexing, GiST, GIN, etc., and can also install feature-rich expansion packages. Compared with Mysql, PostgreSQ supports a series of enhanced functions such as geospatial data, nested loops, hash connections, sorting and merging three table connection methods through PostGIS extension. This article mainly summarizes 30 practical SQLs, so that everyone can use PostgreSQL efficiently.
Practical SQL statements
1. Database connection
1. Get the number of connections to the database instance
select count(*) from pg_stat_activity;
2. Get the maximum number of connections to the database
show max_connections
3. Query the detailed information of the current connection number
select * from pg_stat_activity;
4. Query the number of database connections corresponding to each username in the database
select usename, count(*) from pg_stat_activity group by usename;
2. Empowerment operations
1. Give the select permissions for the specified table to the specified user
GRANT SELECT ON table_name TO username;
2. Modify the owner to which the database table belongs
alter table table_name owner to username;
3. Grant all permissions to the specified table to the specified user
grant all privileges on table product to username
4. Grant all permissions to all tables to the specified user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO username;
3. Database table or index
1. Get the index in the database table
select * from pg_indexes where tablename = 'product';
2. Get all table information in the current db
select * from pg_tables;
3. Query what extensions are installed in the database
select * from pg_extension;
4. Query all tables and their descriptions in the database
select relname as TABLE_NAME ,col_description(, 0) as COMMENTS from pg_class c where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%'
4. Get the data size
1. Query the execution database size
select pg_size_pretty (pg_database_size('db_product'));2. Query the database sizes in the database instance
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;
3. Query single table data size
select pg_size_pretty(pg_relation_size('table_name')) as size;4. Query the database table including index
select pg_size_pretty(pg_total_relation_size('table_name')) as size;5. Check the index size in the table
select pg_size_pretty(pg_indexes_size('table_name'));6. Get the number of data records in each table
select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' order by rowCounts desc
7. View the data files corresponding to the database table
select pg_relation_filepath('product');V. Database analysis
1. Check the version of the database instance
select version();
2. Check the latest loading configuration time
select pg_conf_load_time();
3. Check how many bytes in the current wal buffer are not written to disk
select pg_xlog_location_diff(pg_current_xlog_insert_location(),pg_current_xlog_location());
4. Query the most time-consuming 5 SQL
select * from pg_stat_statements order by total_time desc limit 5;
Note: pg_stat_statements need to be enabled
5. Get the three SQLs with the slowest execution time and give the CPU usage ratio
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;
6. Analyze and evaluate SQL execution
EXPLAIN ANALYZE SELECT * FROM product
7. Check SQL that has been executed for a long time but does not end
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 as datname, as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, 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 , as query from pg_stat_activity as pgsa where != 'idle' and != 'idle in transaction' and != 'idle in transaction (aborted)') idleconnections order by query_stay desc limit 5;
8. Find out the table that uses the most scans
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
9. Query and read the 5 SQLs with the largest number of buffers
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
10. Get the current number of rollback transactions and deadlocks in the database
select datname,xact_rollback,deadlocks from pg_stat_database
11. Query the slow query of the specified table
select * from pg_stat_activity where query ilike '%<table_name>%' and query_start - now() > interval '10 seconds';
6. Database backup
1. Back up the postgres library and tar package
pg_dump -h 127.0.0.1 -p 5432 -U postgres -f -Ft
2. Back up the postgres library and dump the data into the INSERT command with column names
pg_dumpall -d postgres -U postgres -f --column-inserts
Summarize
This article mainly summarizes the classification of SQL commonly used in daily development in PostgreSQL database. In your daily development work, you can have corresponding SQL statements to support it in terms of analyzing database performance, database connection status, SQL execution status, etc.