pgsql常见操作

实用SQL语句

一、数据库连接

1、获取数据库实例连接数

select count(*) from pg_stat_activity;

2、获取数据库最大连接数

show max_connections

3、查询当前连接数详细信息

select * from pg_stat_activity;

4、查询数据库中各个用户名对应的数据库连接数

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

二、赋权操作

1、为指定用户赋予指定表的select权限

GRANT SELECT ON table_name TO username;

2、修改数据库表所属的ownner

alter table table_name owner to username;

3、授予指定用户指定表的所有权限

grant all privileges on table product to username;

4、授予指定用户所有表的所有权限

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

三、数据库表或者索引

1、获取数据库表中的索引

select * from pg_indexes where tablename = 'product'; 

2、获取当前db中所有表信息

select * from pg_tables;

3、查询数据库安装了哪些扩展

select * from pg_extension; 

4、查询数据库中的所有表及其描述

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'));

2、查询数据库实例当中各个数据库大小

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

3、查询单表数据大小

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

4、查询数据库表包括索引的大小

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

5、查看表中索引大小

select pg_size_pretty(pg_indexes_size('product'));

6、获取各个表中的数据记录数

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

7、查看数据库表对应的数据文件

select pg_relation_filepath('product');

五、数据库分析

1、查看数据库实例的版本

select version();

2、查看最新加载配置的时间

select pg_conf_load_time();

3、查看当前wal的buffer中有多少字节未写入磁盘

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

4、查询最耗时的5个sql

select * from pg_stat_statements order by total_time desc limit 5;

备注：需要开启pg_stat_statements

5、获取执行时间最慢的3条SQL，并给出CPU占用比例

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、分析评估SQL执行情况
EXPLAIN ANALYZE SELECT * FROM product
7、查看当前长时间执行却不结束的SQL
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;
8、查出使用表扫描最多的表
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;
9、查询读取buffer最多的5个SQL
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;

10、获取数据库当前的回滚事务数以及死锁数

select datname,xact_rollback,deadlocks from pg_stat_database

11、查询访问指定表的慢查询

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

六、数据库备份(非SQL)

1、备份postgres库并tar打包

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

2、备份postgres库，转储数据为带列名的INSERT命令

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

七、查看集群

同步关系

select  * from pg_stat_replication ;
 pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn
 replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+----------+-----------
------------+-----------+-----------+------------+---------------+------------
(0 rows)

select pg_is_in_recovery();

t ： t标示备库
F ： f标识主库

在主库中查询：
select * FROM pg_stat_replication ;

八、查看路径

#su - postgres

[/home/postgres]env|grep PGDATA    配置文件所在目录
PGDATA=/pg_data/postgres

[/home/postgres]cd /pg_data/postgres
[/pg_data/postgres]ls
      change_role       pg_commit_ts   pg_logical    pg_serial     pg_subtrans  pg_wal                postmaster.opts
1.sh              current_logfiles  pg_dynshmem    pg_multixact  pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
backup_label.old  global            pg_hba.conf    pg_notify     pg_stat       pg_twophase  postgresql.auto.conf  recovery.done
base              log               pg_ident.conf  pg_replslot   pg_stat_tmp   PG_VERSION   postgresql.conf

[/pg_data/postgres]cd pg_wal/
[/pg_data/postgres/pg_wal]ll -rt
总用量 167838356
-rw------- 1 postgres postgres 16777216 4月   2 2023 000000020000009000000033
-rw------- 1 postgres postgres 16777216 4月   2 2023 000000020000009000000034

[/home/postgres]pg_controldata
pg_control version number:            1002     解释：控制文件版本号
Catalog version number:               201707211
Database system identifier:           6914157978164143340
Database cluster state:               in production    实例的状态
pg_control last modified:             2023年11月21日 星期二 10时48分28秒
Latest checkpoint location:           90/3256A878
Prior checkpoint location:            90/3226A0E0
Latest checkpoint's REDO location:    90/324E9B98
Latest checkpoint's REDO WAL file:    000000020000009000000032
Latest checkpoint's TimeLineID:       2
Latest checkpoint's PrevTimeLineID:   2
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:156973716
Latest checkpoint's NextOID:          412135
Latest checkpoint's NextMultiXactId:  121
Latest checkpoint's NextMultiOffset:  301
Latest checkpoint's oldestXID:        548
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  156973716
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            2023年11月21日 星期二 10时48分00秒
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              1000
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            77456932e92e3c50985a10a59903ba6dd0a962d0bd3b9f625c1400dcfec7540d

(Database cluster state: 记录实例的状态。源码文件中看到数据库的几种状态：

starting up：表示数据库正在启动状态。

shut down： 数据库实例（非Standby）正常关闭后控制文件中就是此状态。

shut down in recovery：Standby实例正常关闭后控制文件中就是此状态。

shutting down：正常停库时，先做checkpoint，开始做checkpoint时，会把状态设置为此状态，做完后把状态设置为shut down。

in crash recovery：数据库实例非异常停止后，重新启动后，会先进行实例的恢复，在实例恢复时的状态就是此状态。

in archive recovery：Standby实例正常启动后，就是此状态。

in production：数据库实例正常启动后就是此状态。Standby数据库正常启动后不是此状态

pg_control last modified: 记录控制文件最后更新的时间)