犀渠玉剑良家子，白马金羁侠少年。这篇文章主要讲述PostgreSQL 数据库常用操作相关的知识，希望能为你提供帮助。
PostgreSQL 删除数据库

删除数据库有时会遇到这样的错误信息。

ERROR: database xxx is being accessed by other users

DETAIL: There are x other sessions using the database.

当遇到这样的错误信息时，我们需要先将连接到这个数据库的连接删除，然后才可以删除这个数据库。

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE datname =AND pid < > pg_backend_pid();

DROP DATABASE xxx;

CREATE DATABASE xxx;

PostgreSQL 删除空闲的连接

当某个实例的连接很多的时候，我们常常需要删除一些空闲的连接

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE datname =

AND pid< > pg_backend_pid()

AND state = idle;

PostgreSQL 复制槽

//复制槽视图

SELECT * FROM pg_replication_slots;

//查看复制槽

SELECT * FROM pg_stat_replication;

SELECT * FROM pg_publication_tables;

//创建物理复制槽

SELECT * FROM pg_create_physical_replication_slot ( test_slot );

//创建逻辑复制槽

SELECT * FROM pg_create_logical_replication_slot ( test_logical_slot_81_72, wal2json );

//删除复制槽

SELECT * FROM pg_drop_replication_slot ( test_slot );

PostgreSQL 手动清理 WAL 归档

pg_controldata $PGDATA

pg_archivecleanup -d $PGDATA/pg_wal 000000010000000000000009

PostgreSQL 杀死进程的方法

SELECT pg_cancel_backend(); --一般用于 SELECT

SELECT pg_terminate_backend(); --DML 操作

PostgreSQL 判断服务器是主库还是从库的方法

方法一 通过查看 wal 进程来判断

$ ps -ef | grep "wal" | grep -v "grep"

输出 wal sender......streaming 进程说明当前数据库为主库

输出 wal receiverstreaing 进程说明当前数据库为备库

方法二 通过查看视图表 pg_stat_replication 如果返回记录说明是主库，无记录则为备库

select * from pg_stat_replication;

方法三 通过查看视图表 pg_stat_wal_receiver 如果返回记录说明是备库，无记录则为主库

select * from pg_stat_wal_receiver;

方法四 通过函数 pg_is_in_recovery() 返回 f 表示主库，t 表示从库

select pg_is_in_recovery();

PostgreSQL 重载配置文件的方法

pg_hba.conf 修改后重载方式

postgres=# select pg_reload_conf();

pg_ctl reload -D /home/postgres/pgsql-12/data

PostgreSQL 获取当前正在运行的 SQL

SELECT pid,

query,

now()-query_start as runtime,

usename,

application_name,

client_hostname,

datname,

state

FROMpg_stat_activity

WHERE now()-query_start > 10 seconds::interval

AND state!=idle

ORDER BY runtime DESC;

SELECT pid,

query,

EXTRACT(EPOCH FROM(NOW()::TIMESTAMP-query_start::TIMESTAMP))

FROM pg_stat_activity;

PostgreSQL 日志解析工具 pgBadger 的安装与使用

下载地址

# https://github.com/darold/pgbadger/releases/tag/v11.5

# tar -zxvf pgbadger-11.5.tar.gz

# yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

# cd pgbadger-11.5

# perl Makefile.PL

# make

# make install

# pgbadger -V

# pgbadger --prefix %t [%p] %r %d %u/home/postgres/pgsql-12/data/log/postgresql*.log -O 20210816.html

# pgbadger -f stderr --prefix %m [%p] %x %d %r %a %u

# pgbadger -f stderr --prefix %m [%p] %x %d %r %a %u-b "2021-08-18 00:00:00" -e "2021-08-20 10:30:00" ./*.log -o 202108120.html

Linux 拷贝本地文件到远程服务器和从远程服务器拷贝文件到本地

scp /home/myfile/test.txt root@192.168.1.100:/data/ 拷贝本地文件到远程服务器

scp -r root@192.168.1.100:/data/ /home/myfile/ 从服务器复制文件夹到本地

Linux 网卡

Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7& arch=x86_64& repo=os& infra=stock error was 14: curl#7 - "Failed to connect to 2604:1580:fe02:2::10: Network is unreachable"

vim /etc/sysconfig/network-scripts/ifcfg-eno1

systemctl network restart

PostgreSQL 创建角色并授予权限

CREATE USER cloudhealth3 WITH PASSWORD cloudhealth3@0827;

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO cloudhealth3;

GRANT CONNECT ON DATABASE cloudhealth3 to cloudhealth3;

PostgreSQL 数据库已安装的插件

create extension "uuid-ossp"

SELECT * FROM pg_available_extensions WHERE name LIKE %uuid%;

PostgreSQL 查看每个表所占用的存储空间

SELECT table_schema || . || table_name AS table_full_name,

pg_size_pretty(pg_total_relation_size(" || table_schema || "." || table_name || ")) AS table_size

FROM information_schema.tables

WHERE (pg_total_relation_size(" || table_schema || "." || table_name || ")/1024/1024) > = 20

ORDER BY pg_total_relation_size(" || table_schema || "." || table_name || ") DESC;

PostgreSQL 查看每个数据库的占用空间

SELECT d.datname AS database_name,

pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

FROM pg_catalog.pg_database d

ORDER BY pg_catalog.pg_database_size(d.datname) DESC;

PostgreSQL 查看每个表的膨胀率

SELECT schemaname||.||relname AS table_full_name,

pg_size_pretty(pg_relation_size(schemaname||.||relname)) AS table_size,

n_dead_tup,

n_live_tup,

round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio

FROM pg_stat_all_tables

WHERE n_dead_tup > = 1000

ORDER BY dead_tup_ratio DESC;

PostgreSQL 角色的删除与回收

# REASSIGN OWNED BY old_role TO new_role;

# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM old_role;

# REVOKE ALL ON DATABASE cloudhealth25 FROM old_role;

# DROP ROLE old_role;

CREATE USER tmp_user WITH PASSWORD tmp@20210924;

GRANT UPDATE ON ALL TABLES IN SCHEMA PUBLIC TO tmp_user;

GRANT CONNECT ON DATABASE cloudhealth25 to tmp_user;

PostgreSQL 只导表结构的语句

pg_dump -h IP -p 5432 -U postgres -s -d cloudhealth > /home/postgres/cloudhealth_schema.sql

PostgreSQL 流复制

pg_basebackup -D /home/postgres/pgsql-12/data -Fp -P -h 192.168.100.252 -U postgres

pg_basebackup -h 192.168.21.193 -U postgres -F p -x -P -R -D /home/postgres/pgsql-12/data

primary_conninfo = host=192.168.21.193 port=5432 user=postgres password=postgres_lsc

vim postmaster.opts

/usr/pgsql-12/bin/postgres "-D" "/home/postgres/pgsql-12/data"

191 数据库每日更新操作

[root@master_server release]# cd /root/his_update/

[root@master_server his_update]# ls -alh

[root@master_server his_update]# git pull

[root@master_server his_update]# cnhis|cnhis

[root@master_server his_update]# cd 1.8.110.20211025/

[root@master_server his_update]# ls -alh

[root@master_server 1.8.110.20211025]# ls -alh

[root@master_server 1.8.110.20211025]# sh his_sql.sh

[root@master_server 1.8.110.20211025]# cd release/

[root@master_server release]# sz sql.txt

PostgreSQL 备份与恢复

pg_dump -h 10.10.140.136 -p 5432 -T sys_operated_log -T sys_operated_log_sql -U postgres -d cloudhealth_0708 -f

zip -qrj /home/backup/postgresdb/cloudhealth_0708.zip/home/backup/postgresdb/cloudhealth_0708.bak

pg_restore -h ip -U postgres -d cloudhealth_0923 -Fd /home/postgres/cloudhealth_2021-09-23/

PostgreSQL 统计每个数据库的连接数

SELECT datname,

COUNT(*)

FROM pg_stat_activity

GROUP BY datname;

SELECT pid,

query,

now()-query_start as runtime,

usename,

application_name,

client_hostname,

datname,

state

FROMpg_stat_activity

WHERE datname =;

修改字段长度类型

begin;

drop table if exists view_def;

create temp table view_def(view_name name, view_def text,id int);

select relname,get_view_def(relname,id) from recursive_get_deps(bbx1::regclass);

select drop_depend_view();

alter table bbx1 alter column bbx04 type varchar(128);

alter table bbx1 alter column bbx05 type varchar(256);

select create_depend_view();

commit;

PostgreSQL 查看表的创建时间

select oid,datname,dattablespace from pg_database where datname=cloudhealth;

select relname,relfilenode,reltablespace from pg_class where relname=table_name;

\\db

【PostgreSQL 数据库常用操作】

推荐阅读

• 

  哪些生产环节会造成白色污染
• 

  矿泉水打开后几天不能喝 开盖后的矿泉水多久后不能喝
• 

  R语言从入门到机器学习|R语言回归模型诊断方式有哪些（如何解读这些诊断方式？（残差和样本杠杆值的关系图、比例-位置图、正太QQ图、残差和拟合曲线关系图））
• 

  索科洛夫斯基叶廖缅科 索科洛夫斯基和罗科索夫斯基
• 

  mysql如何添加数据库 mysql添加数据入库
• 

  中药苏子叶的功效与作用
• 

  TeamViewer传输文件的方法 TeamViewer如何传输文件
• 

  复式楼装修一般多少钱一平米 复式200平装修预算
• 

  土加一个斤念什么 土加斤念什么
• 

  矩阵矢量网络分析仪,nanovna矢量网络分析仪论坛
• 

  屏幕|盘点三款热门折叠屏手机！不仅是万元以下，还越来越好用了
• 

  tcl|强势种草 篇十三：门锁不能只求智能，安全更重要！TCL智能锁K7Q安装体验
• 

  191/200反思日记
• 

  远程创建文件夹 Word如何创建远程文件的本地副本
• 

  葱是什么
• 

  如何解决戴尔服务器低费用的问题？ 戴尔服务器费用低怎么解决
• 

  2022年江苏养老金上调最新消息 2022江苏养老金补发什么时候到账？
• 

  门牙牙缝很宽怎么办
• 

  高考考生 这份考前注意事项请收好 高考前注意事项
• 

  谤怎么读 谤字应该怎么读

• 项目实战（Qt球机控制工具 v1.0.0（球机运动八个方向以及运动速度，设置运动到指定角度，查询当前）
• 即使调用get_header()，也无法通过页面模板访问wordpress header.php中包含的文件
• 在Chrome和Chrome上预先登录WordPress主题(avada)时出错
• 致命错误（找不到类&#8221;WP_Customize_Control&#8221;）
• 加载资源失败（服务器响应，状态为404 Not Found）
• WordPress自定义帖子中面临错误
• WP排除多个页面模板
• 在相关的Post WordPress中排除草稿发布
• 错误WordPress jQuery表单错误说未引用函数（）