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、查询表的主键
方法1:使用 information_schema
模式
PostgreSQL 提供了一个名为 information_schema 的模式,该模式包含了许多关于数据库元数据的信息。你可以通过查询 information_schema.table_constraints 和 information_schema.constraint_column_usage 表来获取主键信息。
SELECT tc.constraint_name, tc.table_name, kcu.column_name FROM information_schema.table_constraints AS tc JOIN information_schema.constraint_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_name = 'your_table_name';
将 'your_table_name'
替换为你要查询的表名。
方法2:使用 pg_class
和 pg_index
系统表
PostgreSQL 还提供了一些系统表,如 pg_class
和 pg_index
,这些表可以用来查询更底层的元数据信息。
SELECT a.attname AS column_name FROM pg_index i JOIN pg_attribute a ON a.attnum = ANY(i.indkey) JOIN pg_class t ON t.oid = i.indrelid JOIN pg_namespace n ON n.oid = t.relnamespace WHERE i.indisprimary AND t.relname = 'your_table_name' AND n.nspname = 'your_schema_name';
方法3、使用 \d
命令
如果你使用的是 psql 命令行工具,可以直接使用 \d
命令来查看表的结构,包括主键信息。
\d your_table_name
这将显示表的列信息、索引、约束等详细信息,其中包括主键信息。
2、当表没有主键时,将表的复制级别修改为full
alter table table_name replica identity full;
参考资料:
PG数据库获取表主键信息_怎么查看数据表的主键-CSDN博客
四、数据库账号权限信息查看
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、备份还原特定数据库中的特定表中的数据(不包含表结构)
1.1 备份
./pg_dump -h 127.0.0.1 -U postgres -p 7092 -a -b -f "/opt/tb_device.sql" -d databasename -t tablename
1.2 还原
./psql -h 127.0.0.1 -U postgres -p 7092 -d databasename < "/opt/tb_device.sql"
六、复制槽
1、查看当前数据库所有定义的复制槽
pg的版本低于 12.6
select * from pg_catalog.pg_replication_slots;
pg的版本等于以及高于 12.6
select * from pg_catalog.pg_replication_slots except select;
- slot_name 复制槽的唯一的名称
- plugin 正在使用的包含逻辑槽输出插件的共享对象的基本名称,对于物理插槽则为null。
- slot_type text 插槽类型 - 物理或逻辑
- datoid oid 该插槽所关联的数据库的OID,或为空。 只有逻辑插槽才具有关联的数据库。
- database text 该插槽所关联的数据库的名称,或为空。 只有逻辑插槽才具有关联的数据库。
- active boolean 如果此插槽当前正在使用,则为真
- active_pid integer 如果当前正在使用插槽,则使用此插槽的会话的进程ID。 NULL如果不活动。
- xmin xid 此插槽需要数据库保留的最早事务。 VACUUM无法删除任何后来的事务删除的元组。
- catalog_xmin xid 影响该插槽需要数据库保留的系统目录的最早的事务。 VACUUM不能删除任何后来的事务删除的目录元组。
- restart_lsn pg_lsn 最老的WAL的地址(LSN)仍然可能是该插槽的使用者所需要的,因此在检查点期间不会被自动移除。
2、删除复制槽
select pg_drop_replication_slot('etl_flink_22'); # etl_flink_22 复制槽名称
3、创建复制槽
a) 创建持久化逻辑复制槽
select pg_create_logical_replication_slot(slot_name, 'flying_decoding', false); ## flying_decoding 复制槽名称
持久化复制槽意味着即使创建该槽的会话结束或数据库重启,复制槽仍然存在。这对于需要长期维护复制状态的场景非常有用,因为复制槽会保留直到显式地被删除,确保复制进程可以从上次断开的地方继续。
b)创建临时逻辑复制槽
select pg_create_logical_replication_slot(slot_name, 'flying_decoding', true); ## flying_decoding 复制槽名称
临时复制槽会在创建它的会话结束时自动删除,或者在断开连接时消失。这适用于那些不需要长期保持复制状态或用于测试、一次性复制任务的场景,因为它减少了需要手动管理复制槽的需求。
七、日志wal
向数据库进行数据的增删改之后,都会先写入到pg的内存中,达到一定条件之后,会被写入到wal日志文件中。 每一个操作在wal日志中的位置均是通过lsn(Log Sequence Number)来进行定位。即:WAL是PostgreSQL事务处理系统的关键组件,用于崩溃恢复和复制。对数据库所做的每次更改在写入实际数据文件之前都会记录在WAL中
1、LSN 作用
- pg_lsn (PostgreSQL Log Sequence Number) 是 PostgreSQL 中一种用于标识数据库日志文件位置的数据类型。它表示一个持久日志文件中的特定位置,用于记录数据库中的事务和操作。pg_lsn 值可以用于确定数据库的一致性,用于数据库备份和恢复操作。
- pg_lsn 数据类型是一个 64 位的整数类型,它以 8 个字节的形式存储。在 PostgreSQL 中,pg_lsn 值可以与其他类型进行比较、排序和运算。通过比较两个 pg_lsn 值,可以判断哪个日志文件的位置较新或较旧。
- pg_lsn 数据类型在数据库复制和恢复过程中经常被使用。它可以帮助确定一个备份点的位置,以及在数据库恢复过程中需要恢复到哪个日志文件的位置。通过使用 pg_lsn 值,可以确保数据库的备份和恢复操作之间的一致性。
- pg_lsn数据类型可用于存储LSN(日志序列号)数据,LSN是指向WAL中某个位置的指针。这种类型是XLogRecPtr的一种表示形式,是PostgreSQL的内部系统类型。
- PostgreSQL中的pg_lsn数据类型表示日志序列号,这是一个64位的值,用于唯一标识PostgreSQL的预写日志(WAL)中的位置。
- pg_lsn数据类型用于跟踪数据库系统在WAL中的位置,从而实现高效的崩溃恢复和时间点恢复。它可以用于比较和排序WAL中的不同位置,并确定复制的进度。
2、LSN 格式
-
LSN 通常表示为 X/Y 的形式,其中 X 和 Y 为十六进制数。前 32 位 (X) 表示日志文件的编号,后 32 位 (Y) 表示日志文件中的偏移量。例如,16/B374D848。每次数据库发生写操作时,PostgreSQL 会在 WAL 日志中记录这些操作,并分配一个新的 LSN,以便在系统故障后进行恢复。
以 16/B374D848 为示例
- 前 32 位 (X):16 是前 32 位,表示日志文件的编号。
16
(十六进制)转换为十进制为 22,表示这是第 22个 WAL 文件。 - 后 32 位 (Y):B374D848 是后 32 位,表示日志文件中的偏移量。B374D848 (十六进制)转换为十进制为 3010779208。
3、WAL
WAL(Write-Ahead Logging,预写日志)是 PostgreSQL 数据库用于数据保护和恢复的一项关键机制。WAL 的核心思想是在对数据库中的数据页进行实际修改之前,先将修改记录到日志文件中。这种方法确保了在系统崩溃或故障时,所有已提交的事务都可以通过重放日志恢复,从而保证数据的一致性和完整性。
WAL 日志由一系列顺序写入的日志记录组成,这些记录包含了数据库所有的修改操作。在事务提交时,相关的 WAL 记录会被同步写入磁盘,以确保即使在系统故障后也能通过重放这些日志恢复数据。WAL 日志不仅用于崩溃恢复,还在流复制、备份和恢复操作(如时间点恢复,PITR)中起到至关重要的作用。通过记录和重放 WAL 日志,PostgreSQL 能够提供高效且可靠的数据恢复和一致性维护机制。
WAL 可以通过 pg_waldump 进行解析
如下:
- 通过
select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn()); 命令可以直接获得当前 lsn 号 以及其对应的wal文件
执行两次该命令可以获得 两个 lsn ,两个lsn 执行了 DML sql 语句。
可以看到第一个lsn 为DB/17438AD0 ,第二个 lsn 为 DB/174D48E0,两个 lsn 对应的文件为 00000001000000DB00000017
- 通过 pg_waldump 解决 DB/17438AD0 和 DB/174D48E0 之间的sql操作
3、相关命令
1) 查询数据库中当前lsn
select pg_current_wal_lsn();
2) 根据 lsn 查看对应的wal文件
select pg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn());
select pg_current_wal_lsn(),pg_walfile_name('16/B374D848');
3) 获取指定 LSN 号存储的 WAL 文件名称和偏移量
select pg_walfile_name_offset('DB/16A07B70');
输出显示,LSN 号 DB/16A07B70 对应的 WAL 文件名称为 00000001000000DB00000016,并且偏移量为 10517360 字节:
参考:
https://blog.csdn.net/sinat_36528886/article/details/135231138
八、工具命令使用方法
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 >:数据库目录所属用户
-D:指明数据目录的路径
-U:具有replication权力的角色用户
4、pg_receivewal
5、pg_resetwal
6、pg_waldump
6.1 使用场景
需要通过解析 pg_wal 中的 wal 日志 了解当前对数据库进行了哪些操作,或者明确哪些表的 dml 操作是对哪张表进行了操作,便于其它问题的排查
6.2 命令
6.3 操作示例
九、常见问题处理
1、xlog体积增大导致磁盘爆慢
问题描述:由于复制槽使用的不合规或者系统故障等原因,导致pg_wal日志体积过大,从而使得数据库所在磁盘可用空间爆满,最终导致平台系统运行异常
解决方案:
- 连接数据库删除复制槽
select pg_drop_replication_slot('etl_flink_22'); #etl_flink_22 复制槽名称
- 删除pg安装目录 data/pg_wal 下所有的文件及目录
rm -rf data/pg_wal/*
- 停止数据库实例
systemctl stop xxxx #xxxx 数据库系统服务名
- 重置 pg_wal
su - postgres # postgres 是数据据实例运行的用户
cd postgres安装根目录
bin/pg_resetwal -f data
- 启动数据库实例
systemctl start xxxx # xxxx 为数据库系统服务名
参考资料: