PostgreSQL 日常运维
postgres 关系数据库
Postgre
一、常用命令
1、登录psql
[root@k8s-master home]# su - postgres
[postgres@k8s-master bin]$ psql # psql -U postgres -d dbname -h 127.0.0.1 -p 5432（-U 指定用户 -d 指定数据库 -h 指定服务器 -p 指定端口）
2、创建用户
postgres=# create user qyz with password 'qianyuzhen' CREATEDB; #创建用户
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
qyz | Create DB | {}
replica | Replication | {}
3、创建数据库
postgres=# CREATE DATABASE testdb WITH OWNER = qyz ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1 TEMPLATE template0;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |
template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | qyz | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
(4 rows)
4、快捷命令查询
postgres=# \? # 显示帮助信息
postgres=# \q # 退出 or: exit;
postgres=# \l # 列出所有库
postgres=# \du # 列出所有用户, 指令显示用户和用户的用户属性
postgres=# \d # 列出库下所有表
postgres=# \di # 查看索引
postgres=# \dn # 查看模式schema列表
postgres=# \dn+ # 查看模式schema列表详情
postgres=# \z 或 \dp # 显示用户访问权限
postgres=# \c dbname # 切换数据库,相当于mysql的use dbname
postgres=# \c - username #切换用户
postgres=# \d tblname # 查看表定义, 相当于desc tblname,show columns from tbname
postgres=# \db #列出现有的表空间。
5、常用数据字典
常用数据字典（视图） 说明
pg_tables 该视图提供了对有关数据库中每个表的有用信息地访问
pg_user 该视图提供了对数据库用户的相关信息的访问。 这个视图只是pg_shadow表的公众可读的部分的视图化，但是不包含口令字段
pg_views 该视图提供了对数据库里每个视图的有用信息的访问途径
pg_indexes 该视图提供对数据库中每个索引的有用信息的访问
pg_proc 该视图存储关于函数(或过程)的信息
pg_database 该视图存储关于数据库的信息
pg_namespace 该视图存储关于表空间的信息
pg_roles 该视图提供访问数据库角色有关信息的接口。这个视图只是pg_authid表的公开可读部分的视图化，同时把口令字段用空白填充
pg_settings 该视图提供了对服务器运行时参数的访问。它实际上是SHOW和SET命令的另外一种方式
select version(); 查看数据库版本
select pg_postmaster_start_time(); 查看数据库启动时间
select pg_conf_load_time(); 查看最近一次load配置文件的时间
show timezone; 查看数据库时区
select now(); 查看当前系统时间
select user; 或者 select current_user; 查看当前用户名
select current_database(); 查看当前连接数据库
select pg_backend_pid(); 查询当前session的pid
show parameter_name; 查询参数配置
-----------------------------------------------------------------------------------
select * from pg_stat_activity; 查询长时间运行的sql
select pg_cancel_backend(pid); 取消正在执行的sql
select pg_terminate_backend(pid); 终止后台服务进程
-----------------------------------------------------------------------------------
select u.usename,p.oid,p.proname from pg_user u,pg_proc p 查看当前用户所在库的存储过程
where u.usesysid=p.proowner and u.usename='用户名';
二、查看postgres SQL中当前正在运行的事务
SELECT
pid,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.pid AS pid,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
三、postgresSQL备份
1、 PG备份工具介绍
| 数据库类型 | 备份类型 | 还原方法 | 特点 |
|---|---|---|---|
| pg_dump | 逻辑备份 | pg_restore | 只能备份单库，支持并发备份 |
| pg_dumpall | 逻辑备份 | 输入重定向 | 只能备份全库，不支持并发备份 |
| pg_basebackup | 物理备份 | 文件替换 | 需要准备另一个数据库实例进行备份，常用于流复制搭建 |
| pg_start_backup() pg_stop_backup() | 物理备份 | 文件替换 | 在线热备份，可以不开启归档 |
| pgbackrest | 物理备份 | pgbackrest | 支持全备、增备、差备，结合归档可恢复任意时间点，需单独安装插件，必须开启归档 |
注意事项：逻辑备份推荐使用pg_dump。
2、pg_dump
2.1、 备份，只能单个数据库，不会导出角色和表空间相关的信息，便于迁移或异机恢复。
pg_dump --help 可以查看用法
-U, --username=NAME 以指定的数据库用户联接
-v, --verbose 详细输出模式
-F, --format=c|d|t|p 导出文件的格式,-Fc备份为二进制格式, 压缩存储. 并且可被 pg_restore 用于精细还原。
-Fp备份为文本, 大库不推荐
-d, --dbname=DBNAME 指定数据库名
-O, --no-owner 在明文格式中, 忽略恢复对象所属者
-f, --file=FILENAME 导出后保存的文件名
–insert 导出成sql语句类型（耗时长）
pg_dump -U postgres -v -Fc -d cosmic_fi --no-owner -f /data/backup/cosmic_fi.dump
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lb0C35el-1672217965625)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221211162426502.png)]
2.2、 恢复，pg_restore 可以从pg_dump创建的存档中恢复一个PostgreSQL数据库
pg_restore --help 可以查看用法
-U, --username=NAME 以指定的数据库用户联接
-O, --no-owner 跳过对象所有权的恢复
-d, --dbname=DBNAME 指定数据库名
-v, --verbose 详细信息模式
–role=ROLENAME 在恢复之前执行SET ROLE命令
导入库参考：pg_restore -U postgres --no-owner --role ptest_biz_baseline_bdai -d ptest_biz_baseline_bdai -v ptest_biz_baseline_bdai.dump
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oN19wLpf-1672217965626)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221211163006097.png)]
2.3 总结
表级备份
#表级备份，备份testdb数据库中的userinfo表。 -t表名，-U用户，-W密码 -f输出的备份文件名字
pg_dump -t UserInfo -U postgres -f /data/UserInfo.dmp testdb
表级恢复
psql -U postgres -d testdb -f /data/UserInfo.dmp
库级备份
# 备份database，testdb是库名
pg_dump -U postgres -W -f /data/testdb.dmp testdb
库级恢复
# 1、库存在问题时，可先删除后，再进行创建库
postgres=# drop database testdb; #删库
testDB=# create database testdb with owner=postgres template=template0; #建库
testDB=# psql -U postgres -d testdb -f /data/testdb.dmp --single-transaction testdb
# --single-transaction表示整个恢复过程是一个事务，要么成功要么回滚
视图级别备份
备份database中的schema。-n 表示schema。public是所有database中默认的schema。这里要备份testdb中的view_userinfo schema
pg_dump testdb -n test_schema -U postgres -W -f /data/view_userinfo.dmp
视图级别恢复
# 删除schema
szlsd_db=> drop schema test_schema cascade;
# 恢复schema（无需手动创建schema）
psql -U postgres -d testdb -W -f /data/view_userinfo.dmp --single-transaction testdb
# 检查确认
testdb=# \dn
List of schemas
Name | Owner
-------------+-------------------
public | pg_database_owner
test_schema | postgres
(2 rows)
3、pg_dumpall
3.1、备份（pg_dumpall可以备份整库）
pg_dumpall --help 可以查看用法
-a, --data-only 仅转储数据，而不转储schema
-c, --clean 重新创建数据库之前清除（删除）数据库
-g, --globals-only 仅转储全局对象，而不转储数据库
-r, --roles-only 仅转储角色，而不转储数据库和表空间
-s, --schema-only 仅转储schema，而不转储数据
-O, --no-owner 以纯文本格式跳过对象所有权的恢复
–inserts 以INSERT命令（而非COPY命令）的形式转储数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k0yqcvdW-1672217965627)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221211184546628.png)]
pg_dumpall -g >/gpbackup/role_tbs.sql # 导出所有role和tablespace
pg_dumpall >/gpbackup/all.dmp # 导出所有database
无论是pg_dump还是pg_dumpall导出的数据都不是sql格式，而是copy格式，-d, --inserts参数控制到底是SQL是COPY格式
3.2、恢复
psql -f /data/all.sql #执行这个命令的时候连接到哪个数据库无关紧要
四、双机切换
查看双机状态
[postgres@cqypg1f ~]$ pg_autoctl show state
Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State
-------+-------+-----------------+-------------------+--------------+---------------------+--------------------
node_1 | 1 | 10.88.7.77:5432 | 61: 135/9448A000 | read-write | primary | primary
node_2 | 2 | 10.88.7.76:5432 | 61: 135/9448A000 | read-only | secondary | secondary
手动强行进行主从切换
[postgres@localhost ~]$ pg_autoctl perform promotion –name node_2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TUZ4q1DY-1672217965628)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221212083108675.png)]
手动故障切换
[postgres@localhost ~]$ pg_autoctl perform failover
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p0OK7piM-1672217965629)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221212083341807.png)]
高可用模式下修改配置文件
1、修改从库参数文件postgresql.conf
su - postgres
vi $PGDATA/postgresql.conf
2、重启从库的postgres服务
systemctl restart postgresql
3、设置从节点维护状态
pg_autoctl enable maintenance
4、修改主库参数文件
5、重启主库的postgres服务
6、取消从库的维护状态
pg_autoctl disable maintenance
7、检查集群
pg_autoctl show state
五、 wal日志清除
Postgre
一、常用命令
1、登录psql
[root@k8s-master home]# su - postgres
[postgres@k8s-master bin]$ psql # psql -U postgres -d dbname -h 127.0.0.1 -p 5432（-U 指定用户 -d 指定数据库 -h 指定服务器 -p 指定端口）
2、创建用户
postgres=# create user qyz with password 'qianyuzhen' CREATEDB; #创建用户
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
qyz | Create DB | {}
replica | Replication | {}
3、创建数据库
postgres=# CREATE DATABASE testdb WITH OWNER = qyz ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1 TEMPLATE template0;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc |
template0 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
testdb | qyz | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
(4 rows)
4、快捷命令查询
postgres=# \? # 显示帮助信息
postgres=# \q # 退出 or: exit;
postgres=# \l # 列出所有库
postgres=# \du # 列出所有用户, 指令显示用户和用户的用户属性
postgres=# \d # 列出库下所有表
postgres=# \di # 查看索引
postgres=# \dn # 查看模式schema列表
postgres=# \dn+ # 查看模式schema列表详情
postgres=# \z 或 \dp # 显示用户访问权限
postgres=# \c dbname # 切换数据库,相当于mysql的use dbname
postgres=# \c - username #切换用户
postgres=# \d tblname # 查看表定义, 相当于desc tblname,show columns from tbname
postgres=# \db #列出现有的表空间。
5、常用数据字典
常用数据字典（视图） 说明
pg_tables 该视图提供了对有关数据库中每个表的有用信息地访问
pg_user 该视图提供了对数据库用户的相关信息的访问。 这个视图只是pg_shadow表的公众可读的部分的视图化，但是不包含口令字段
pg_views 该视图提供了对数据库里每个视图的有用信息的访问途径
pg_indexes 该视图提供对数据库中每个索引的有用信息的访问
pg_proc 该视图存储关于函数(或过程)的信息
pg_database 该视图存储关于数据库的信息
pg_namespace 该视图存储关于表空间的信息
pg_roles 该视图提供访问数据库角色有关信息的接口。这个视图只是pg_authid表的公开可读部分的视图化，同时把口令字段用空白填充
pg_settings 该视图提供了对服务器运行时参数的访问。它实际上是SHOW和SET命令的另外一种方式
select version(); 查看数据库版本
select pg_postmaster_start_time(); 查看数据库启动时间
select pg_conf_load_time(); 查看最近一次load配置文件的时间
show timezone; 查看数据库时区
select now(); 查看当前系统时间
select user; 或者 select current_user; 查看当前用户名
select current_database(); 查看当前连接数据库
select pg_backend_pid(); 查询当前session的pid
show parameter_name; 查询参数配置
-----------------------------------------------------------------------------------
select * from pg_stat_activity; 查询长时间运行的sql
select pg_cancel_backend(pid); 取消正在执行的sql
select pg_terminate_backend(pid); 终止后台服务进程
-----------------------------------------------------------------------------------
select u.usename,p.oid,p.proname from pg_user u,pg_proc p 查看当前用户所在库的存储过程
where u.usesysid=p.proowner and u.usename='用户名';
二、查看postgres SQL中当前正在运行的事务
SELECT
pid,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.pid AS pid,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
三、postgresSQL备份
1、 PG备份工具介绍
| 数据库类型 | 备份类型 | 还原方法 | 特点 |
|---|---|---|---|
| pg_dump | 逻辑备份 | pg_restore | 只能备份单库，支持并发备份 |
| pg_dumpall | 逻辑备份 | 输入重定向 | 只能备份全库，不支持并发备份 |
| pg_basebackup | 物理备份 | 文件替换 | 需要准备另一个数据库实例进行备份，常用于流复制搭建 |
| pg_start_backup() pg_stop_backup() | 物理备份 | 文件替换 | 在线热备份，可以不开启归档 |
| pgbackrest | 物理备份 | pgbackrest | 支持全备、增备、差备，结合归档可恢复任意时间点，需单独安装插件，必须开启归档 |
注意事项：逻辑备份推荐使用pg_dump。
2、pg_dump
2.1、 备份，只能单个数据库，不会导出角色和表空间相关的信息，便于迁移或异机恢复。
pg_dump --help 可以查看用法
-U, --username=NAME 以指定的数据库用户联接
-v, --verbose 详细输出模式
-F, --format=c|d|t|p 导出文件的格式,-Fc备份为二进制格式, 压缩存储. 并且可被 pg_restore 用于精细还原。
-Fp备份为文本, 大库不推荐
-d, --dbname=DBNAME 指定数据库名
-O, --no-owner 在明文格式中, 忽略恢复对象所属者
-f, --file=FILENAME 导出后保存的文件名
–insert 导出成sql语句类型（耗时长）
pg_dump -U postgres -v -Fc -d cosmic_fi --no-owner -f /data/backup/cosmic_fi.dump
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zhesuy7O-1672217966533)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221211162426502.png)]
2.2、 恢复，pg_restore 可以从pg_dump创建的存档中恢复一个PostgreSQL数据库
pg_restore --help 可以查看用法
-U, --username=NAME 以指定的数据库用户联接
-O, --no-owner 跳过对象所有权的恢复
-d, --dbname=DBNAME 指定数据库名
-v, --verbose 详细信息模式
–role=ROLENAME 在恢复之前执行SET ROLE命令
导入库参考：pg_restore -U postgres --no-owner --role ptest_biz_baseline_bdai -d ptest_biz_baseline_bdai -v ptest_biz_baseline_bdai.dump
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XcKLu41m-1672217966534)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221211163006097.png)]
2.3 总结
表级备份
#表级备份，备份testdb数据库中的userinfo表。 -t表名，-U用户，-W密码 -f输出的备份文件名字
pg_dump -t UserInfo -U postgres -f /data/UserInfo.dmp testdb
表级恢复
psql -U postgres -d testdb -f /data/UserInfo.dmp
库级备份
# 备份database，testdb是库名
pg_dump -U postgres -W -f /data/testdb.dmp testdb
库级恢复
# 1、库存在问题时，可先删除后，再进行创建库
postgres=# drop database testdb; #删库
testDB=# create database testdb with owner=postgres template=template0; #建库
testDB=# psql -U postgres -d testdb -f /data/testdb.dmp --single-transaction testdb
# --single-transaction表示整个恢复过程是一个事务，要么成功要么回滚
视图级别备份
备份database中的schema。-n 表示schema。public是所有database中默认的schema。这里要备份testdb中的view_userinfo schema
pg_dump testdb -n test_schema -U postgres -W -f /data/view_userinfo.dmp
视图级别恢复
# 删除schema
szlsd_db=> drop schema test_schema cascade;
# 恢复schema（无需手动创建schema）
psql -U postgres -d testdb -W -f /data/view_userinfo.dmp --single-transaction testdb
# 检查确认
testdb=# \dn
List of schemas
Name | Owner
-------------+-------------------
public | pg_database_owner
test_schema | postgres
(2 rows)
3、pg_dumpall
3.1、备份（pg_dumpall可以备份整库）
pg_dumpall --help 可以查看用法
-a, --data-only 仅转储数据，而不转储schema
-c, --clean 重新创建数据库之前清除（删除）数据库
-g, --globals-only 仅转储全局对象，而不转储数据库
-r, --roles-only 仅转储角色，而不转储数据库和表空间
-s, --schema-only 仅转储schema，而不转储数据
-O, --no-owner 以纯文本格式跳过对象所有权的恢复
–inserts 以INSERT命令（而非COPY命令）的形式转储数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5N8RkWEb-1672217966535)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221211184546628.png)]
pg_dumpall -g >/gpbackup/role_tbs.sql # 导出所有role和tablespace
pg_dumpall >/gpbackup/all.dmp # 导出所有database
无论是pg_dump还是pg_dumpall导出的数据都不是sql格式，而是copy格式，-d, --inserts参数控制到底是SQL是COPY格式
3.2、恢复
psql -f /data/all.sql #执行这个命令的时候连接到哪个数据库无关紧要
四、双机切换
查看双机状态
[postgres@cqypg1f ~]$ pg_autoctl show state
Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State
-------+-------+-----------------+-------------------+--------------+---------------------+--------------------
node_1 | 1 | 10.88.7.77:5432 | 61: 135/9448A000 | read-write | primary | primary
node_2 | 2 | 10.88.7.76:5432 | 61: 135/9448A000 | read-only | secondary | secondary
手动强行进行主从切换
[postgres@localhost ~]$ pg_autoctl perform promotion –name node_2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0b1CKDtH-1672217966535)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221212083108675.png)]
手动故障切换
[postgres@localhost ~]$ pg_autoctl perform failover
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7KM55c1v-1672217966536)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20221212083341807.png)]
高可用模式下修改配置文件
1、修改从库参数文件postgresql.conf
su - postgres
vi $PGDATA/postgresql.conf
2、重启从库的postgres服务
systemctl restart postgresql
3、设置从节点维护状态
pg_autoctl enable maintenance
4、修改主库参数文件
5、重启主库的postgres服务
6、取消从库的维护状态
pg_autoctl disable maintenance
7、检查集群
pg_autoctl show state
五、 wal日志清除
技术共进，成长同行——讯飞AI开发者社区
更多推荐
2
0- 0
已为社区贡献1条内容
所有评论(0)