Postgres 数据库（一）基本命令

一、主备数据一致性

1、通过 wal 检测两个数据库实例数据是否一致（该sql语句需要在主库上执行）

1.1 pg_stat_replication 可以进行流复制监控（pg_stat_replication视图显示WAL发送进程的详细信息）

postgres=#SELECT *  FROM pg_stat_replication ;
-[RECORD 1 ]--—-----+-—----------------------
pid                 |  7683
usesysid            |  16384
usename             |  repuser
application_name    |  node2
client_addr         |  192.168.28.75
client_hostname     |  
client_port         |  57870
backend_start       |  2017-09-05 11:50:31.629468+08
backend_xmin        |  
state               |  streaming          
sent_lsn            |  3/643CB568 
write_lsn           |  3/643CB568
flush_lsn           |  3/643CB488
replay_lsn          |  3/643CB030
write_lag           |  00:00:00.000224
flush_lag           |  00:00:00.001562
replay_lag          |  00:00:00.006596
sync_priority       |  1
sync_state          |  sync

视图中的主要字段解释如下:

pid: WAL发送进程的进程号。
usename: WAL发送进程的数据库用户名。
application_name :连接WAL发送进程的应用别名，此参数显示值为备库recovery.conf配置文件中primary_conninfo参数application_name选项的值。
client_addr:连接到WAL发送进程的客户端IP地址，也就是备库的IP。backend_start: WAL发送进程的启动时间。
state:显示WAL发送进程的状态，startup表示WAL进程在启动过程中; catchup表示备库正在追赶主库;streaming表示备库已经追赶上了主库，并且主库向备库发送WAL日志流，这个状态是流复制的常规状态;backup表示通过pg_basebackup正在进行备份; stopping表示 WAL发送进程正在关闭。
sent_lsn: WAL发送进程最近发送的WAL日志位置。
write_Isn :备库最近写入的WAL日志位置，这时WAL日志流还在操作系统缓存中，还没写入备库 WAL日志文件。
flush_Isn:备库最近写入的WAL日志位置，这时WAL日志流已写入备库WAL日志文件。
replay_lsn:备库最近应用的WAL日志位置。
write_lag :主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流还没写入备库 WAL日志文件，还在操作系统缓存中）并返回确认信息的时间。
flush_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，但还没有应用WAL日志）并返回确认信息的时间。
replay_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，并且已应用WAL日志）并返回确认信息的时间。
sync_priority:基于优先级的模式中备库被选中成为同步备库的优先级，对于基于quorum的选举模式此字段则无影响。
sync_state:同步状态，有以下状态值，async表示备库为异步同步模式; potential表示备库当前为异步同步模式，如果当前的同步备库宕机，异步备库可升级成为同步备库;sync表示当前备库为同步模式;quorum表示备库为quorum standbys 的候选。

1.2 pg_stat_wal_receiver 监控备库的信息（显示WAL接收进程详细信息）

postgres=# SELECT * FROM pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-----------------
pid                   |  22573
status                |  streaming
receive_start_lsn     |  3/2D000000
receive_start_tli     |  1
received_lsn          |  3/852DC428
received_tli          |  1
last_msg_send_time    |  2017-09-06 15:35:28.178167+08
last_msg_receipt_time |  2017-09-06 15:35:28.177706+08
latest_end_lsn        |  3/852DC508
latest_end_time       |  2017-09-0615:35:28.178167+08
slot_name             |  
conninfo              |  user=repuser passfile=/home/postgres/.pgpass dbname=replication
    host=192.168.28.74 port=1921 application_name=node2 fallback_application_name=walreceiver sslmode=disable sslcompression=1 target_session_attrs=any

说明：

pid:WAL接收进程的进程号。status: WAL接收进程的状态。
receive_start_lsn: WAL接收进程启动后使用的第一个 WAL日志位置。received_lsn:最近接收并写入WAL日志文件的WAL位置。
last_msg_send_time :备库接收到发送进程最后一个消息后，向主库发回确认消息的发送时间。
last_msg_receipt_time:备库接收到发送进程最后一个消息的接收时间。
conninfo: WAL接收进程使用的连接串，连接信息由备库SPGDATA目录的recovery.
conf配置文件的 primary_conninfo参数配置

1.3 数据一致性检测

1.3.1 使用 WAL日志应用延迟量衡量监控主备同步数据延迟

select pid ,usename,client_addr,state,pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_depaly,pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_delay,write_lag,flush_lag,replay_lag  from pg_stat_replication;

说明：

lsn：在pg中的每写入一条记录都会在wal日志中增加一条wal记录，写入这个记录的位置就是lsn，全称为Log Sequence Number，lsn的值是wal文件中字节偏移量
pg_current_wal_lsn()：函数显示流复制主库当前WAL日志文件写入的位置
pg_wal_Isn_diff()：函数计算两个WAL日志位置之间的偏移量，返回单位为字节数
write_Isn :备库最近写入的WAL日志位置，这时WAL日志流还在操作系统缓存中，还没写入备库 WAL日志文件。
flush_Isn:备库最近写入的WAL日志位置，这时WAL日志流已写入备库WAL日志文件。
replay_lsn:备库最近应用的WAL日志位置。

1.3.2 使用 WAL延迟时间衡量监控主备同步数据延迟

SELECT pid, usename,client_addr,state,write_lag,flush_lag, replay_lag FROM pg_stat_replication;

说明：

write_lag :主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流还没写入备库 WAL日志文件，还在操作系统缓存中）并返回确认信息的时间。
flush_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，但还没有应用WAL日志）并返回确认信息的时间。
replay_lag:主库上WAL日志落盘后等待备库接收WAL日志(这时WAL日志流已写入备库WAL日志文件，并且已应用WAL日志）并返回确认信息的时间。

write_lag、flush_lag、replay_lag 为 PostgreSQL10版本新增字段，10版本前pg_statreplication视图不提供这三个字段，但是也有办法监控主备延时，在流复制备库执行以下SQL：

SELECT EXTRACT(SECOND FROM now ()- pg_last_xact_replay_timestamp());

说明：

pg_last_xact_replay_timestamp函数显示备库最近WAL日志应用时间，通过与当前时间比较可粗略计算主备库延时，这种方式的优点是即使主库宕掉，也可以大概判断主备延时。缺点是如果主库上只有读操作，主库不会发送WAL日志流到备库，pg_last_xact_replay_timestamp 函数返回的结果就是一个静态的时间，这个公式的判断结果就不严谨了。

1.3.3 通过创建主备延时测算表方式

这种方法在主库上创建一张主备延时测算表，并定时往表插入数据或更新数据，之后在备库上计算这条记录的插入时间或更新时间与当前时间的差异来判断主备延时，这种方法不是很严谨，但很实用，当主库宕机时，这种方式依然可以大概判断出主备延时。

2、通过 pg_controldata 命令获取两个pg实例的时间线来判定两边数据是否一致

$ pg_controldata | grep TimeLineID   时间线检查

说明：

该命令需要在主备机的 pg 实例上均运行，获取各自的时间线进行比对。

二、统计数据库大小

1、查询所有库数据大小

postgres=# SELECT pg_database.datname as "Database",
postgres-# pg_size_pretty(pg_database_size(pg_database.datname)) as "Size"
postgres-# FROM pg_database
postgres-# ORDER BY pg_database_size(pg_database.datname) DESC;
     Database     |  Size
------------------+---------
 postgres         | 65 GB
 lm_lmlicensedb   | 14 MB
 svm_svmdb        | 11 MB
 lsm_casdb        | 7933 kB
 mps_mpsdb        | 7773 kB
 isecure_portaldb | 7741 kB
 template0        | 7601 kB
 template1        | 7601 kB
(8 rows)

说明：

pg_size_pretty：以人性化角度展示数据库大小

2、查询所有数据库总大小

SELECT pg_size_pretty(sum(pg_database_size(pg_database.datname))) as "Size"
FROM pg_database

3、查看某个数据库大小

select pg_database_size('xx');

4、查询所有表总大小（也就相当于查询所有数据库总大小）

postgres=# select
postgres-#     pg_size_pretty(sum(t.size))
postgres-# from (
postgres(#     SELECT
postgres(#           table_schema || '.' || table_name AS table_full_name
postgres(#         , pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')AS size
postgres(#     FROM information_schema.tables
postgres(#     ORDER by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
postgres(#     ) t;
 pg_size_pretty
----------------
 65 GB
(1 row)

5、查看每个表所占用磁盘空间大小

SELECT 
 
      table_schema || '.' || table_name AS table_full_name
 
    , pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') AS size_bytes
 
    , pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')/1024/1024 as size_mb
 
    , pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')/1024/1024/1024 as size_gb
 
FROM information_schema.tables
 
ORDER by pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC
;

6、查看某个表大小

select pg_relation_size('table_name');   --bytes
select pg_size_pretty(pg_relation_size('table_name'));       --- 以KB\MB\GB方式查看表大小

7、查看某个表的总大小，包括索引

select pg_size_pretty(pg_total_relation_size('table_name'));

8、查看某个索引所占磁盘空间大小

select pg_size_pretty(pg_relation_size('table_pkey'));

9、查看所有表空间

select spcname from pg_tablespace;

10、查看某个表空间大小

select pg_size_pretty(pg_tablespace_size('pg_default'));

11、创建表并插入10000000条数据

CREATE TABLE test_per4(id int4, name text,flag char(1) ) ;
INSERT INTO test_per4 (id, name)
SELECT n,n || 'per4'
FROM generate_series( 1,10000000)n;

12、查看某个数据库database中的所有表

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';



SELECT table_schema || '.' || table_name AS table_full_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_full_name;

13、刷盘使得pg内存数据存入磁盘中

/opt/hikvision/web/components/postgresql11linux64.1/bin/psql -h 127.0.0.1 -p 7092 -U postgres -c checkpoint;


或者 
/opt/hikvision/web/components/postgresql11linux64.1/bin/psql -h 127.0.0.1 -p 7092 -U postgres
连接上pg

再执行 checkpoint；命令

14、查询数据库版本

/opt/hikvision/web/components/postgresql11linux64.1/bin/psql -h 127.0.0.1 -p 7092 -U postgres -c 'select version();'

15、中断某个数据库database的所有连接

在连接上运行的数据库实例之后，希望直接通过 "drop databse testdb;" 命令，将该database删除，但是往往执行之后会提示删除失败，且报以下的错误信息

ERROR: database "testdb" is being accessed by other users
DETAIL: There are 40 other sessions using the database.

该错误信息表示当前的testdb数据库正在被使用，有40个连接。为了解决该问题，需要执行以下命令之后再删除数据库 testdb.

select pg_terminate_backend(pid) from pg_stat_activity where datname='issc_isscdb' and pid<>pg_backend_pid();

说明：

pg_terminate_backend：用来终止与数据库的连接的进程id的函数。

pg_stat_activity：是一个系统表，用于存储服务进程的属性和状态。

pg_backend_pid()：是一个系统函数，获取附加到当前会话的服务器进程的ID。

重点：执行以上命令的时候，需要连接数据库的用户具有超级管理员权限。

三、数据库账号权限信息查看

1、查看所有账号权限信息

 /opt/hikvision/web/components/postgresql11linux64.1/bin/psql -h 127.0.0.1 -p 7093 -U postgres -c "SELECT * from pg_roles;"

通过 psql 工具连接上数据库之后，可以直接通过 sql 语句 ”select * from pg_roles;“ 查询所有账号权限信息。

2、查询某个账号对某个数据库中某个表的权限

首先使用 postgres （超管账号）连接目标数据库，比如：svm_svmdb 数据库（这个是业务数据库）

./psql -h 127.0.0.1 -p 7093 -U postgres -d svm_svmdb;

查询 information_schema.table_privileges 表中的数据

 select * from  information_schema.table_privileges as a where a.table_catalog='svm_svmdb' and a.table_schema='public';

从图片上可以看出通过 table_privileges 表能够查询都账号对某个表的具体权限，包括：insert、select、update、delete、truncate 等等操作权限。

3、查看某个账号对某个数据库的操作权限

三、备份数据并恢复到其它

四、工具命令使用方法

1、pg_rewind

1.1 使用场景

主备数据库，主库因故掉线但仍然在运行，备库升级为主。当主库变成从库加入到集群中时，可能会出现主备两个数据库实例时间线不一致的问题，此时需要在当前从库上执行 pg_rewind ，保持和当前主库数据一致的目的。

1.2 命令

su - hikpostgres -c "/opt/temp/web/components/postgresql11linux64.1/bin/pg_rewind --target-pgdata=/opt/temp/web/components/postgresql11linux64.1/data --source-server='host=10.19.214.18 port=7092 user=repuser dbname=postgres' --progress "

说明：

host：值为对端pg的IP

port：值为对端pg的端口

user：为pg用户，具有复制权限

2、pg_controldata

2.1 使用场景

如果想查看当前数据库的一些信息，如：当前数据库的时间线，checkpoint信息，角色，每片wal文件大小等等。

2.2 命令

/opt/temp/web/components/postgresql11linux64.1/bin/pg_controldata -D /opt/temp/web/components/postgresql11linux64.1/data

说明：

-D：指明数据目录的路径

3、pg_basebackup

3.1 使用场景

一般用于全量备份数据库的情况

3.2 命令

su - hikpostgres -c "/opt/temp/web/components/postgresql11linux64.1/bin/pg_basebackup -D /opt/temp/web/components/postgresql11linux64.1/data -Fp -Xs -v -P -h 10.19.214.18 -p 7092 -U repuser"

说明：

< hikpostgres >：数据库目录所属用户