犀渠玉剑良家子，白马金羁侠少年。这篇文章主要讲述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 数据库常用操作】

推荐阅读

• 

  博士一般要读几年 博士读多久
• 

  香蕉吃多了会怎样呢
• 

  两岁半孩子脾气暴躁还打大人 两岁半孩子脾气暴躁还打大人的方法
• 

  2021-02-27
• 

  【机器学习】pandas基础铺垫
• 

  屮艸芔茻这4个字念啥 屮艸芔茻怎么读什么意思
• 

  谢娜追张杰，程莉莎追郭晓东，女追男，结果都挺好的
• 

  穴位埋线减肥平时要注意什么
• 

  朗朗上口的男孩小名 朗朗上口男孩的名字属虎
• 

  冰箱能否放厨房里 厨房里可以放冰箱吗
• 

  解读兄弟2260自检页面 看懂兄弟2260自检页
• 

  ON和OFF代表什么
• 

  如何设置苹果锁屏，iphone手机锁屏如何设置
• 

  小孩子要用防晒霜吗
• 

  D3/21|D3/21 Good &New
• 

  坐飞机核酸检测过期了怎么办
• 

  拟眼斑水龟的寿命 请查看寿命
• 

  老师70大寿祝福语
• 

  用完脱毛膏后有黑茬怎么去除
• 

  好听的游戏名字男英文

• 项目实战（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表单错误说未引用函数（）