Common query statements in postgresql

1. Look for sql that executes slowly:

Select* from pg_stat_statements

two。 Look up the reply based on the pid of the operating system:

Select d.query from pg_stat_activity d where pid=18707

3. Query slow sql:

SELECT query,calls,total_time, (total_time / calls) AS average,ROWS

100.0 * shared_blks_hit / NULLIF (shared_blks_hit + shared_blks_read,0) AS hit_percent

FROM pg_stat_statements ORDER BY average DESC LIMIT 10

4. Reset the pg_stat_ states table:

Select pg_stat_statements_reset ()

5. Authorization:

Schema read-only:

Grant select on all tables in schema app_schema to app_user_readonly

For schema read and write permissions:

Grant select,update,delete,insert on all tables in schema app_schema to app_user

Create database chunqiu

Create user u_chunqiu password 'upright chunqiu'

Alter database chunqiu owner to u_chunqiu

Create schema crmdb

Alter schema crmdb owner to u_chunqiu

Copy view (executed in the main database, but no result in the standby database):

Select * from pg_stat_replication

Modify the parameters:

Postgres=# alter system set shared_buffers='1000MB'

ALTER SYSTEM

8. Parameter view:

Show shared_buffers

Show hba_file

Show config_file

9. Clean shut down the database:

Pg_ctl stop-m fast

10. View the master-slave replication delay time:

Select extract (epoch from now ()-pg_last_xact_replay_timestamp ())

11. Refresh the configuration file:

A.SELECT pg_reload_conf ()

B.pg_ctl reload

twelve。 Common queries:

-- View all objects (table name, index name, sequence, etc.):

SELECT from pg_class where relname = 'activity_history'

Select from pg_attribute where attname = 'activity_history'

-- View all information:

Select from pg_index

-- View the corresponding information of the table and index as well as the creation information of the index:

Select from pg_indexes where indexname = 'index_name'

-- View the information of the table:

Select from pg_tables where tablename = 'pg_class'

-- View view information:

Select from pg_views

Select from pg_type

SELECT FROM information_schema.schemata

-- get the fields and types of the table:

SELECT a.attname as name,pg_type.typname as typename,col_description (a. Attrelid. A. Attnum) as comment, a.attnotnull as notnull

FROM pg_class as cjournal pgfight attribute as an inner join pg_type on pg_type.oid = a.atttypid

Where c.relname = 'activity_history' and a.attrelid = c.oid and a.attnum > 0

13. Toggle schema:

Show search_path

Set search_path to app

Set search_path to app,public

SET search_path TO myschema,public

14. Statistics related:

PG provides a view of statistics at each object level:

Pg_stat_database

Pg_stat_all_tables

Pg_stat_sys_tables

Pg_stat_user_tables

Pg_stat_all_indexes

Pg_stat_sys_indexes

Pg_stat_user_indexes

Test whether turning on the track_io_timing parameter creates a bottleneck according to the pg_test_timing tool provided by pg:

PG also provides a view to count the number of function calls and other information in the database: pg_stat_user_functions

PG also provides a statistical view of what is happening on each object:

Pg_statio_all_tables

Pg_statio_sys_tables

Pg_statio_user_tables

Pg_statio_all_indexes

Pg_statio_sys_indexes

Pg_statio_user_indexes

Pg_statio_all_sequences

Pg_statio_sys_sequences

Pg_statio_user_sequences

15. Common maintenance:

Displays the background process corresponding to the current session:

Select pg_backend_pid ()

Send an INT signal to the process to cancel the executing sql:

Pg_ctl kill INT xxx

Cancel is usually used:

Select pg_cancel_backend (xxx)

How long is the sql sleep (in seconds):

Select pg_sleep (xxx)

View the database startup time:

Select pg_postmaster_start_time ()

View the last load time of the configuration file:

Select pg_conf_load_time ()

Displays the current time zone of the database:

Show timezone

Displays the client ip address and port on which the current session is located:

Select inet_client_addr (), inet_client_port ()

Displays the ip address and port of the current database server:

Select inet_server_addr (), inet_server_port ()

View the wal file you are currently writing:

Version 9.x:

Select pg_xlogfile_name (pg_current_xlog_location ())

Version 10.x:

Select pg_walfile_name (pg_current_wal_insert_lsn ())

It will be updated continuously.