核心专业区
数据库深度探秘
PostgreSQL
postgresql基础配置

部署管理配置

使用pg_ctl方式进行管理

1.数据库的启动，查看状态，关闭

[root@localhost ~]# su - postgres
Last login: Tue Oct 12 23:34:33 EDT 2021 on pts/1
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ start
waiting for server to start....2021-10-13 00:40:19.174 EDT [25652] LOG:  listening on IPv6 address "::1", port 1921
2021-10-13 00:40:19.174 EDT [25652] LOG:  listening on IPv4 address "127.0.0.1", port 1921
2021-10-13 00:40:19.176 EDT [25652] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2021-10-13 00:40:19.189 EDT [25653] LOG:  database system was shut down at 2021-10-12 23:36:10 EDT
2021-10-13 00:40:19.191 EDT [25652] LOG:  database system is ready to accept connections
 done
server started
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ status
pg_ctl: server is running (PID: 25652)
/opt/pg10/bin/postgres "-D" "/pgdata/10/data"
[postgres@localhost ~]$ /opt/pgsql/bin/pg_isready -p 1921
/tmp:1921 - accepting connections
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ -ms stop　　#smart（-ms）,fast(-mf),immediate(-mi)三种关闭模式
waiting for server to shut down....2021-10-13 00:43:51.783 EDT [25652] LOG:  received smart shutdown request
2021-10-13 00:43:51.784 EDT [25652] LOG:  worker process: logical replication launcher (PID 25659) exited with exit code 1
2021-10-13 00:43:51.785 EDT [25654] LOG:  shutting down
2021-10-13 00:43:51.791 EDT [25652] LOG:  database system is shut down
 done
server stopped

2.配置开机启动

1.配置服务脚本，将linux脚本拷贝到/etc/init.d/中，将脚本重命名为postgresql-10并赋予执行权限

[root@localhost ~]# ls postgresql-10.0/contrib/start-scripts/
freebsd  linux  osx
[root@localhost ~]# cp postgresql-10.0/contrib/start-scripts/linux /etc/init.d/postgresql-10
[root@localhost ~]# chmod +x /etc/init.d/postgresql-10
[root@localhost ~]# ls -lh /etc/init.d/postgresql-10
-rwxr-xr-x. 1 root root 3.5K Oct 13 00:48 /etc/init.d/postgresql-10

使用

chkconfig –list查看postgresql是否开机启动

设置开机启动，或关闭chkconfig postgresql-10 on/off

3.数据库配置基础

postgresql中两个重要的全局配置文件postgresql.conf(文件配置，资源限制，集权设置)和pg_hba.conf（客户端连接和认证）都存于初始化目录中

全局配置的修改方法

  • 修改postgresql.cong文件
  • 在库中通过alter system set 命令修改全局配置（会产生一个postgresql.auto.conf文件，数据库启动时会加载此文件，并覆盖postgresql.conf中配置，不要修改它）

非全局修改方法

  • 设置database级别
  • alter database name set configparameter
  • 设置session级别配置
  • 通过set命令设置当前session配置

查询配置　　show all

使配置生效：

select pg_reload_conf();
或者
[postgres@localhost ~]$ /opt/pgsql/bin/pg_ctl -D /pgdata/10/data reload
server signaled
2021-10-13 01:31:28.393 EDT [25748] LOG:  received SIGHUP, reloading configuration files

允许数据库远程访问，需修改监听地址，pg_hba.conf文件

 vi /pgdata/10/data/postgresql.conf
找到#listen_addresses = 'localhost'改为
listen_addresses = '*'
重启数据库：　 /opt/pgsql/bin/pg_ctl -D /pgdata/10/data stop/startecho "host mydb pguser 0.0.0.0/0 md5" >> /pgdata/10/data/pg_hba.confreload生效/opt/pgsql/bin/pg_ctl -D /pgdata/10/data/ reload

　　

配置默认环境，设置完后执行source ~/.bashrc

vi ~/.bashrc
export PG_HOME=/opt/pgsql
export PATH=$PG_HOME/bin:$PATH
PGUSER=postgres
PGHOST=127.0.0.1
PGDATABASE=postgres
PGPORT=1921

基本操作

–创建用户，创建空间目录，创建数据库，赋权

postgres=# create role pguser with password 'pguser';
CREATE ROLE
[root@localhost ~]# mkdir -p /database/pg10/pg_tbs/tbs_mydb
root@localhost ~]# chown -R postgres.postgres /database
[postgres@localhost ~]$ psql
psql (10.0)
Type "help" for help.

postgres=# create tablespace tbs_mydb owner pguser location '/database/pg10/pg_tbs/tbs_mydb';
CREATE TABLESPACE
postgres=# create database mydb with owner = pguser TEMPLATE=template0 ENCODING = 'UTF8' TABLESPACE=tbs_mydb;
CREATE DATABASE

postgres=# grant ALL on DATABASE mydb to pguser with Grant option;(WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人)
GRANT
postgres=# grant ALL on TABLESPACE tbs_mydb to pguser ;
GRANT

postgres=# alter role pguser login ;
ALTER ROLE

–psql元命令介绍

\l查看库

\db查看表空间

img

\d查看表定义

mydb=> create table test_1(id int,name text ,create_time timestamp without time zone default clock_timestamp()); CREATE TABLE

mydb=> alter table test_1 add primary key (id); ALTER TABLE mydb=> \d test_1 Table “public.test_1” Column | Type | Collation | Nullable | Default ————-+—————————–+———–+———-+——————- id | integer | | not null | name | text | | | create_time | timestamp without time zone | | | clock_timestamp() Indexes: “test_1_pkey” PRIMARY KEY, btree (id)

查看表，索引大小

mydb=> insert into test_1 (id,name) select n,n || '_francs' from generate_series(1,500000) n;
INSERT 0 500000
mydb=> select * from test_1  limit 1;
 id |   name   |        create_time
----+----------+----------------------------
  1 | 1_francs | 2021-10-13 03:36:25.607786
(1 row)
mydb=> \di+ test_1_pkey
                          List of relations
 Schema |    Name     | Type  | Owner  | Table  | Size  | Description
--------+-------------+-------+--------+--------+-------+-------------
 public | test_1_pkey | index | pguser | test_1 | 11 MB |
(1 row)

\sf查看函数代码

img

\x设置查询结果输出模式

img

psql导入、导出数据表

COPY是sql命令，需要superuser，主机上的文件

\copy是元命令，不需要superuser，psql端文件

COPY导入导出命令

[postgres@localhost ~]$ cat test_copy.txt
1    a
2    b
3    c
4    d
[postgres@localhost ~]$ psql mydb postgres
mydb=# COPY public.test_copy from '/home/postgres/test_copy.txt';（实例名）
COPY 4
mydb=# select * from test_copy ;
 id | name
----+------
  1 | a
  2 | b
  3 | c
  4 | d
(4 rows)

导出

mydb=# COPY public.test_copy to '/home/postgres/test.txt';
COPY 4
mydb=# \q
[postgres@localhost ~]$ ls
test_copy.txt  test.txt
[postgres@localhost ~]$ cat test.txt
1    a
2    b
3    c
4    d

导出到csv格式：加 with csv header导出格式为csv，并显示字段名称

mydb=# copy public.test_copy to '/home/postgres/test_copy.csv' with csv header;
COPY 4

img

如何导出部分数据，导出表test_copy，id等于2的数据

mydb=# copy (select * from public.test_copy where id=2) to '/home/postgres/test_copy.csv' with csv header;
COPY 1
mydb=# \q
[postgres@localhost ~]$ ls
test_copy.csv  test_copy.txt  test.txt
[postgres@localhost ~]$ cat test_copy.csv
id,name
2,b

\copy原命令数据导入导出，一样的，把COPY命令换位\copy即可

小表用\copy,大表建议用COPY效率更高

PG基础问题及处理方法

1、PG中如何判断 idle in transaction 为僵尸事务

backend_xid：用来表示会话是否申请了事务号

backend_xmin：事务会话的快照ID

如果backend_xid 和 backend_xmin 为空，可清理掉该进程， 如果不为空，说明事务还没结束，如未提交

2、查看当前事务

select txid_current();

3、PG等待事件根因分析表

https://pgfans.cn/a/1654

4、AUTOVACUUM优化的小技巧

三个基本参数： autovacuum_max_workers：

工作进程数数量默认为3，增加 autovacuum worker的数量意味着更多的进程可用于清理数据，通常应该将worker的数量设置为3-cpu_count之间最先设置为CPU_COUNT的1/2吧，如果在系统高峰期，CPU资源出现了瓶颈，那么适当缩小该值，如果CPU资源还很充足，但是autovacuum的性能不足，那么就增加这个值。

maintenance_work_mem：

值越大，vacuum的效率越高，设置的太大耗尽了内存，会产生严重的性能问题

物理内存充足，建议maintenance_work_mem至少设置为 1 GB

1 GB的维护工作内存足以一次处理大约 1.79 亿个死元组

autovacuum_freeze_max_age：

减少 TXID 回绕的机会，越大，autovacuum运行的频率就越低，延迟太久会消耗txid编号

对表级别进行设置：

这些参数属于表的storage参数。通过ALTER TABLE .. SET STORAGE_PARAMETER可以进行个性化的设置。

autovacuum_vacuum_threshol，autovacuum_analyze_threshol

这两个参数分别确定要为 autovacuum 和 autoanalyzer 表中从上一次vacuum后更新或删除的记录数的最小数量。两者的默认值都是 50

autovacuum_vacuum_scale_factor

autovacuum_analyze_scale_factor

分别确定要为 autovacuum 和 autoanalyzer 安排的表需要更改的表的百分比

autovacuum_vacuum_scale_factor值为 0.2 (20%) 和autovacuum_analyze_scale_factor 0.1 (10%)。

针对大量行超级大表

vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

其中n_live_tup是活动元组的数量，这个值可以从pg_stat_all_tables视图获得。一旦死元组的数量达到了这个限额，那么就可以启动vacuum作业。

5、逻辑备份pg_dump

特点：

即使数据库正在被并发使用，它也能创建一致的备份。

不阻塞其他用户访问数据库（读取或写入）

只能备份单个数据库，不会导出角色和表空间相关的信息

生产环境建议方式

\1. -F c 备份为二进制格式, 压缩存储. 并且可被pg_restore用于精细还原，输出输入 IO 比较稳定

二进制文件备份

\1. pg_dump -F c -f /tmp/testdb.dmp -C -EUTF8 -h 127.0.0.1 -U postgres testdb

另外可以

\1. 1）根据二进制备份文件生成toc文件

\2. pg_restore -l -f /tmp/toc1 /tmp/testdb.dmp

\3. 2）修改 toc文件，以首行加分号“；”的方式注释掉不用还原的内容

\4. vi /tmp/toc1

\5. 265; 1259 25280 TABLE public postgres_log postgres

\6. 266; 1259 25293 TABLE public t2 postgres

并行处理

\1. pg_dump -Fd -j4 -f /tmp/db.dir testdb #-F d 以目录的格式创建备份

\2. pg_restore -d testdb3 -j4 /tmp/db.dir

\3. -j 参数指定同时几个进程来同时执行，每个进程同时只处理一个表的数据。

6、优化方法论

了解需求，考虑将需求最小化，具备少做事的意识乃是顶级优化。

7、监控粒度信息

最多的是DataFileWrite，其次是transactionid,再后面是DatafileRead和buffer_mapping。

DatafileWrite是等待想relation文件写入

DatafileRead是从relation文件读入

Transactionid是等待一个事务结束

Buffer_mapping是等待将数据块与缓冲池中的缓冲区。这些都是和Benchmark测试关联比较紧密的

Extend是等待relation文件扩展结束。这个等待居然比tuple还高

tuple是等待获取元组锁。

8、绑定变量的使用问题

优点：让SQL可以共享，可以让一条类似的SQL在多次执行中共享查询执行计划

缺点：绑定变量的差异可能选择不同执行计划才好的问题在很多时候都是存在的

9、开启大页

作用：降低系统内存占用

优势：

1、CPU的TLB可以缓存的物理地址空间更大，从而提升TLB的命中率，降低CPU负载；

2、Huge Page使用的内存是不可交换（swap）的，没有内存空间换入/换出的开销；

3、极大的减少了系统维护PageTable的内存开销。

劣势：

1、Huge Page使用的内存需要预先分配；

2、Huge Page使用固定大小的内存区域，不会被释放；

3、对于写密集型的场景，Huge Page会加大Cache写冲突的发生概率。

**使用场景：**不推荐PG实例开启Huge Page的场景：写业务密集，热点数据集中且内存使用较小。

计算大页脚本

10、快速加载sql入库的方法

查看自动提交是否关闭

\echo :AUTOCOMMIT

关闭

\set AUTOCOMMIT OFF

执行插入数据，在文件后加入commit;参数

\i /var/lib/pgsql/testdb1.sql

以下是测试200w数据

只导出insert形式的数据（用copy导入会报错）

pg_dump -U postgres -d postgres -p 5432 –column-inserts -t “testdb1” > /var/lib/pgsql/testdb1.sql

只导出表结构

pg_dump -U postgres -d postgres -p 5432 -s -t “testdb1” > /var/lib/pgsql/testdb1_struct.sql

方案一：psql -f 导入，7分钟导入数据量

img

方案二：\set AUTOCOMMIT OFF，添加

导入，5分钟之内能弄完

方案三：开启事务

begin:

\i 执行脚本

commit;

大页计算脚本和注意事项

#!/bin/bash PGDATA=’/pg13/pgdata’ pid=head -1 $PGDATA/postmaster.pid echo ‘Pid: $pid’ peak=grep ^VmPeak /proc/$pid/status | awk '{ print $2 }' echo ‘VmPeak: $peak kB’ hps=grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }' echo ‘Hugepagesize: $hps kB’ hp=$((peak/hps)) echo Set Huge Pages: $hp sysctl -w vm.nr_hugepages=$hp 不要忘记将此设置添加到/etc/sysctl.conf，以便在重启后重新应用它。 echo 3170 > /proc/sys/vm/nr_hugepages cat » /etc/sysctl.conf «‘EOF’ vm.nr_hugepages=3170 EOF sysctl -p 通过此脚本计算出大页的大小，然后添加到/etc/sysctl.conf里，sysctl -p生效。 参考连接： https://baijiahao.baidu.com/s?id=1709212963361949625&wfr=spider&for=pc

有用知识点

PG恢复被打了删除标记的列

授权某个模式下所有的表和新增的表

/image-20220329162624551

11、pg10分区表插入更新无法解决方法

方法1：建立函数

create or replace function f_upsert(integer,character,timestamp) returns void as $$  
declare  
  res int;  
begin  
  update users set user_name=$2,logdate=$3 where user_id=$1;  
  if not found then  
    insert into users (user_id,user_name,logdate) values ($1,$2,$3);  
  end if;  
  exception when others then  
    return;  
end;  
$$ language plpgsql strict;

方法2：采用with的方式

with upsert as (update users set user_name=$user_name,logdate=$logdate where user_id=$user_id returning *) insert into users select $user_id,$user_name,$logdate where not exists (select 1 from upsert where user_id=$user_id);    
insert into test (id,info,crt_time) values ($1,$2,$3); 

with upsert as (update users set user_name='DD',logdate='2021-03-21' where user_id=3 returning *) insert into users select 3,$user_name,$logdate where not exists (select 1 from upsert where user_id=$user_id);    


postgres=# select * from users;
 user_id | user_name |        logdate         
---------+-----------+------------------------
       1 | AA        | 2021-01-17 00:00:00-05
       2 | BB        | 2021-02-18 00:00:00-05
       3 | CC        | 2021-03-19 00:00:00-04
(3 rows)

postgres=# select f_upsert(1,'2','2021-01-18');
postgres=# select * from users;
 user_id | user_name |        logdate         
---------+-----------+------------------------
       1 | 2         | 2021-01-18 00:00:00-05
       2 | BB        | 2021-02-18 00:00:00-05
       3 | CC        | 2021-03-19 00:00:00-04
(3 rows)

12、postgresql中mvcc_表膨胀问题

pg中的mvcc 旧版本和新版本在同一个数据库的问题

行发生了修改就有新版本和旧版本，存在同样的数据文件里面，如果垃圾回收不及时，就会发生表膨胀

技术原理

存储旧版本：解决并发事务，方便查询旧的版本数据

影响范围，行业，业务

对于高频率的更新，插入，删除场景就会有问题

传感器，出租车位置，等更新多的场景，容易出问题，垃圾回收不及时，就会膨胀

什么时候回收不及时

看看有没有2pc

看看垃圾回收设置的内存是不是过小

垃圾回收工作进程太少了

磁盘性能

膨胀之后会有什么问题

1.存储空间不足

2.访问的时候io的范围会增加，本来访问一个数据块的现在需要访问2，3个数据块了

3.内存的消耗增加了，因为内存buffer要去缓存block

4.性能下降

解决：

设置参数 https://blog.csdn.net/weixin_34360651/article/details/90504302

表膨胀解决方法

https://ctypyb2002.blog.csdn.net/article/details/82774684?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7ECTRLIST%7Edefault-1.control

新引入的问题

1、需要io延时更低的硬盘

2、牺牲了长事务

3、需要增加监控项

有没有希望解决这个坑 ：基于专门的回归段的存储引擎

13、postgresql避免oom方法

操作系统层：

vm.overcommit_memory设置为2：sysctl -w vm.overcommit_memory=2 修改postgres进程的oom_score：echo -1000 > /proc/self/oom_score_adj

参考文章

https://weibo.com/ttarticle/p/show?id=2309404665709073662196&sudaref=www.baidu.com

https://cdn.modb.pro/db/43944

14、wal记录

数据先到wal buffer–>在落盘保证数据 lsn不断放大的号，确保回放，把最新的lsn，从上一次检查点重放 保证数据库不丢数据 是如何恢复的 重放xlog从磁盘到wal buffer ，redo poin它中有lsn号，去和shared buffer中的lsn做对比，大的就回放 块折断 full_page_writes作用：是否开启全页写入，为了防止块折断（块损坏）的一种策略 判断：checksum值 采用机制：在checkponit后的一个块第一次变脏后就要整块写入到wal日志中， 后续修改这个块，只要把修改信息写入到wal日志 断电重启，有块折断， 则在全页写入的块为基础进行恢复 最后覆盖磁盘上的折断块 参数checkpoint_segments对checkpoint影响 作用：控制checkpoint的间隔 过大：恢复时间会变长 过小：造成频繁的checkpoint进而导致写入了过多的全页，可能wal日志暴增 检查点作用 1.将事务提交的修改写进disk（写脏数据）；保证数据库的完整性和一致性。 2.缩短恢复时间,将脏页写入相应的数据文件,确保修改后的文件通过fsync()写入到磁盘。 触发条件： 1、checkpoint_timeout设置时间 2、为checkpoint_segments设置的 WAL 段文件的数量自上一个检查点以来已经被消耗（默认3） 3、WAL 段文件的总大小已超过参数max_wal_size的值（默认值为 1GB（64 个文件）） 4、PostgreSQL 服务器在smart或fast模式下停止 5、手动执行CHECKPOINT 6、写入WAL的数据量已达到参数max_wal_size（默认值：1GB） 7、执行pg_start_backup函数时 8、在进行数据库配置时

pg_control文件 包含检查点的基本信息，如果损坏或无法读取，则无法启动恢复过程，从而无法获得起点。

WAL段切换 1、wal段文件被写满 2、函数pg_switch——wal()被调用 3、启用了archive_mode,且已经超过archive_timeout配置的超时时间。 archive_timeout时间短会导致空间膨胀

15、postgresql中lock_timeout设置

设置为10s

session1：测试插入更新数据

session2:同时更新事务一中的数据，锁超时事务被杀掉

继续在seasion2中操作会报错

session2选择rollback或者commit

rollback回滚当前事务，commit提交报错之前执行的操作

session1执行commit提交成功；

16、PostgreSQL 恢复误删数据的操作

https://www.jb51.net/article/204286.htm

17、PostgreSQL 数据库跨版本升级常用方案解析

https://www.jb51.net/article/206773.htm

18、PostgreSQL之FSM与VM

https://www.cnblogs.com/mingfan/p/14771267.html

常用脚本postgrsql参数配置