postgres的一些总结

checkpoint_timeout：自动 WAL 检查点之间的最长时间，以秒计。默认5分钟检查一次
max_wal_size：在自动 WAL检查点之间允许WAL 增长到的最大尺寸
checkpoint_completion_target：指定检查点完成的目标，作为检查点之间总时间的一部分。默认是 0.5。
假如我的checkpoint_timeout设置是30分钟，而wal生成了10G，那么设置成0.5就允许我在15分钟内完成checkpoint，调大这个值就可以降低checkpoint对性能的影响，但是万一数据库出现故障，那么这个值设置越大数据就越危险。

共享库预载入(preload_libraries )
local_preload_libraries (string) 这个变量指定一个或者多个要在连接开始时预载入的共享库。 这个参数在连接启动时起作用，对后续更改没有影响。 如果指定的库没有找到，连接尝试将会失败
session_preload_libraries (string) 这个变量指定一个或者多个要在连接开始时预载入的共享库。只有超级用户更够更改这个设置。 这个参数只在连接开始时起效。后续的改变没有效果。如果指定的库没有找到，连接尝试将会失败
shared_preload_libraries (string) 这个变量指定一个或者多个要在服务器启动时预载入的共享库。这个参数只能在服务器启动时设置。 如果指定的库没有找到，服务器将无法启动。
参考：http://postgres.cn/docs/9.6/runtime-config-client.html#GUC-SESSION-PRELOAD-LIBRARIES

auto_explain模块提供了一种方式来自动记录慢速语句的执行计划，而不需 要手工运行EXPLAIN。这在大型应用中追踪未被优化的查询时有用。
可以查看 auto_explain 相关参数

postgres=# select name,setting from pg_settings where name like 'auto_explain%';
                name                | setting 
------------------------------------+---------
 auto_explain.log_analyze           | off
 auto_explain.log_buffers           | off
 auto_explain.log_format            | text
 auto_explain.log_min_duration      | -1
 auto_explain.log_nested_statements | off
 auto_explain.log_timing            | on
 auto_explain.log_triggers          | off
 auto_explain.log_verbose           | off
 auto_explain.sample_rate           | 1
(9 rows)

auto_explain.log_min_duration是最小语句执行时间（以毫秒计），这将导致语句的计划被记录。设置这个参数为零将记录所有计划。负一（默认值）禁用记录计划。例如，如果你将它设置为250ms，则所有运行时间等于或超过 250ms 的语句将被记录。只有超级用户能够改变这个设置。
auto_explain.log_nested_statements导致嵌套语句（在一个函数内执行的语句）会被考虑在记录范围之内。当它被关闭时，只有顶层查询计划被记录。这个参数默认情况下是关闭的。只有超级用户能够改变这个设置。
参考：http://postgres.cn/docs/9.6/auto-explain.html

pg_statsinfo 定期收集一台或多台 PostgreSQL 服务器的活动和统计信息，并将它们打包为快照。快照存储在另一个或同一 PostgreSQL 服务器上的存储库数据库中。此外，它从 PostgreSQL 的 CSV 格式日志文件中提取一些活动，并生成相应的纯日志文件，包括其独特的消息。

两个或多个 PostgreSQL实例可以共享单个存储库数据库。

您可以使用pg_stats_reporter以易于掌握的图形表示检查服务器运行状况和活动 。它以交互式表格和图形的形式显示各种信息。

pg_statsinfo 的组件通常如下图所示放置。每个 pg_statsinfo 收集它所在的数据库服务器的信息并将快照发送到存储库服务器。pg_stats_reporter 在 web 服务器后面运行，以便用户可以在客户端的web 浏览器上获得图形报告。

您可以使用pg_stat_statements获得涉及查询统计信息的快照。pg_statsinfo 自动检测 pg_stat_statements 并使用它。pg_stat_statements 的安装将在将其添加到 postgresql.conf 中的 shared_preload_libraries 之后的以下步骤中完成。

http://pgstatsinfo.sourceforge.net/documents/statsinfo13/pg_statsinfo.html

pg_stat_statements模块提供一种方法追踪一个服务器所执行的所有 SQL 语句的执行统计信息。
该模块必须通过在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。这意味着增加或移除该模块需要一次服务器重启。

https://www.postgresql.org/docs/13/pgstatstatements.html

pg_stat_statements.track (enum)
pg_stat_statements.track控制哪些语句会被该模块计数。指定top可以跟踪顶层语句（那些直接由客户端发出的语句），指定all还可以跟踪嵌套的语句（例如在函数中调用的语句），指定none可以禁用语句统计信息收集。默认值是top。 只有超级用户能够改变这个设置。

pg_statsinfo仓库构成
http://pgstatsinfo.sourceforge.net/documents/statsinfo13/files/pg_statsinfo_v13_report_infomation.xls

pg_report构成
http://pgstatsinfo.sourceforge.net/documents/statsinfo13/files/pg_statsinfo_v13_report_infomation.xls

checkpoint：
数据（表行的块）读取 共有内存 -》 OS内存 -》 磁盘
WAL日志 操作记录
检查点是特定时间点的同步事件，它会导致共享缓冲区中的部分或所有脏页写入磁盘。

checkpoint相关的参数：

1、checkpoint_timeout：
这是自动WAL检查点之间的最长时间（默认为5分钟）。增加此参数可能会增加崩溃恢复所需的时间。

2、max_wal_size:
使WAL增长到自动WAL检查点之间的最大大小。默认值为1 GB。增大此参数可能会增加崩溃恢复所需的时间。

如果我们同时设置了这两个参数，则检查点将以先到者为准。

3、min_wal_size：
只要WAL磁盘使用率保持低于此设置，旧的WAL文件将始终在检查点被回收以备将来使用，而不是被删除。这可以用来确保保留足够的WAL空间来处理WAL使用率的峰值，例如在运行大型批处理作业时。 （默认为80 MB）

4、checkpoint_completion_target :
由于每5分钟或达到每个max_wal_size阈值都会发生一次检查点，因此在检查点时间内，共享缓冲区中存在的所有脏页将被刷新到磁盘，从而导致巨大的IO。
checkpoint_completion_target来这里进行救援。
这会使刷新速度变慢，这意味着PostgreSQL应该花费checkpoint_completion_target * checkpoint_timeout的时间来写入数据。
例如，如果我的checkpoint_completion_target为0.5，并且数据库将限制写入，以便最后写入在2.5分钟后完成。

5、wal_buffers :
用于尚未写入磁盘的WAL数据的共享内存量。默认设置为-1，选择的大小等于shared_buffers的1/32（大约3％），但不小于64kB，也不大于一个WAL段的大小，通常为16MB。

6、checkpoint_flush_after:
在执行检查点时，只要写入的字节数超过checkpoint_flush_after，则尝试强制OS将这些写入操作刷到存储中。这样做将限制内核页面缓存中的脏数据量，从而减少在检查点末尾发出fsync时停顿的可能性。
此设置在某些平台上可能无效。

7 archive_timeout 强制归档时间

参考：https://postgreshelp.com/postgresql-checkpoint/

min_wal_size + wal_keep_segments ≈ max_wal_szie

参考：https://blog.csdn.net/dazuiba008/article/details/115123122

Postgresql之VACUUM和VACUUM FULL对比
参考：https://www.cnblogs.com/niubaba/p/9541246.html

postgres低级 API 基本备份
pg_start_backup

1. 创建一个检查点

2. 强制进入全页写模式
   判断当前配置是否为全页写模式，如果当前full_page_writes设置为off，则强制更改为on

3. 排他基础备份的情况下还会创建backup_label文件，backup_label文件包含以下几项 ： 
   

START WAL LOCATION： WAL起始位置
CHECKPOINT LOCATION：记录由命令创建的检查点的LSN位置
BACKUP METHOD： 值为pg_start_backup或pg_basebackup，若只是配置流复制则为streamed
BACKUP FROM： 是在主库还是从库做的基础备份
STARTTIME： 执行pg_start_backup的时间戳
LABEL： 在pg_start_backup中指定的标签

pg_stop_backup
执行pg_stop_backup 命令时，执行五个操作来结束备份：
如果在执行pg_start_backup命令时，full-page-writes的值曾被强制修改，则恢复到之前的值
写一个备份结束的XLOG记录
切换WAL文件
创建一个备份历史文件，该文件包含 backup_label文件的内容及执行pg_stop_backup的时间戳
删除backup_label文件，backup_label文件对于从基本备份进行恢复是必需的，一旦进行复制，就不需要该文件了

官网的解释说明：full_page_writes（布尔值）
当这个参数打开时，PostgreSQL服务器在检查点后第一次修改该页面期间将每个磁盘页面的全部内容写入 WAL。这是必需的，因为在操作系统崩溃期间正在进行的页面写入可能只是部分完成，导致磁盘页面包含新旧数据的混合。通常存储在 WAL 中的行级更改数据将不足以在崩溃后恢复期间完全恢复这样的页面。存储整页图像可保证页面可以正确恢复，但代价是必须写入 WAL 的数据量增加。（因为 WAL 重放总是从一个检查点开始，所以在检查点后每个页面的第一次更改期间这样做就足够了。因此，降低整页写入成本的一种方法是增加检查点间隔参数。）

关闭此参数可加快正常操作的速度，但可能会在系统出现故障后导致不可恢复的数据损坏或静默数据损坏。风险类似于关闭fsync，尽管较小，并且应该仅根据为该参数推荐的相同情况将其关闭。

关闭此参数不会影响使用 WAL 归档进行时间点恢复 (PITR)（请参阅第 25.3 节）。

此参数只能在postgresql.conf文件或服务器命令行中设置。默认为开启。
参考：https://blog.csdn.net/Hehuyi_In/article/details/102641959

synchronous_standby_names
设置备用服务器列表
注意如果是recovery，此参数必须设置为空，设置成非同步方式，否则主服务器无法commmit，一直会等待standy的相应。
参考：https://www.postgresql.org/docs/13/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES

pg_repack
Details
Full Table Repacks
To perform a full-table repack, pg_repack will:

1create a log table to record changes made to the original table
2add a trigger onto the original table, logging INSERTs, UPDATEs and DELETEs into our log table
3create a new table containing all the rows in the old table
4build indexes on this new table
5apply all changes which have accrued in the log table to the new table
6swap the tables, including indexes and toast tables, using the system catalogs
7drop the original table
pg_repack will only hold an ACCESS EXCLUSIVE lock for a short period during initial setup (steps 1 and 2 above) and during the final swap-and-drop phase (steps 6 and 7). For the rest of its time, pg_repack only needs to hold an ACCESS SHARE lock on the original table, meaning INSERTs, UPDATEs, and DELETEs may proceed as usual.

1,2,6,7会有短暂锁表，其他不会，pg_repack比Vacuum full相较而言，不锁表（其实还是锁了一下），可以正常增删改，实现就是多了个触发器去记录pg_repack的时候的增删改操作。最后整合到新表里。

参考：https://reorg.github.io/pg_repack

由于上述官网的安装步骤太过简易，特此memo
对象Postgres版本 9.6.5

1 官网下周pg_repack压缩包
https://pgxn.org/dist/pg_repack/

2 解压

3 设置环境变量，注意是root用户
echo $PATHexportPATH=$PATH:/usr/pgsql-9.6/bin/
echo ${PATH}

4 安装make所需的依赖包，否则make会报错

yum install openssl-devel.x86_64
yum install readline-devel

5 make&&make install
make成功会报如下信息
make install
make[1]: Entering directory /tmp/pg_repack-1.4.6/bin' /bin/mkdir -p '/usr/pgsql-9.6/bin' /usr/bin/install -c pg_repack '/usr/pgsql-9.6/bin' make[1]: Leaving directory/tmp/pg_repack-1.4.6/bin’
make[1]: Entering directory /tmp/pg_repack-1.4.6/lib' /bin/mkdir -p '/usr/pgsql-9.6/lib' /bin/mkdir -p '/usr/pgsql-9.6/share/extension' /bin/mkdir -p '/usr/pgsql-9.6/share/extension' /usr/bin/install -c -m 755 pg_repack.so '/usr/pgsql-9.6/lib/pg_repack.so' /usr/bin/install -c -m 644 .//pg_repack.control '/usr/pgsql-9.6/share/extension/' /usr/bin/install -c -m 644 pg_repack--1.4.6.sql pg_repack.control '/usr/pgsql-9.6/share/extension/' make[1]: Leaving directory/tmp/pg_repack-1.4.6/lib’
make[1]: Entering directory /tmp/pg_repack-1.4.6/regress' make[1]: Nothing to be done forinstall’.
make[1]: Leaving directory `/tmp/pg_repack-1.4.6/regress’

6 将pg_repack加载到指定数据库
psql -c “CREATE EXTENSION pg_repack” -d your_database

7 确认pg_repack是否加载成功

postgres=# \dx pg_repack //List of installed extensions
List of installed extensions
Name | Version | Schema | Description
-----------±--------±-------±-------------------------------------------------------------
pg_repack | 1.4.6 | public | Reorganize tables in PostgreSQL databases with minimal locks
(1 row)

参考：https://www.postgresql.org/docs/10/app-psql.html
https://github.com/reorg/pg_repack/blob/master/doc/pg_repack.rst

衍生处理：pg_repack中途异常终止的对应
由于官网对于pg_repack没有明确的recover对策记载，所以通过pg_repack -d jp5200wms --table testtalbe --DEBUG;

会有如下日志：

DEBUG: create_pktype     : CREATE TYPE repack.pk_oid
DEBUG: create_log        : CREATE TABLE repack.log_oid
DEBUG: create_trigger    : CREATE TRIGGER repack_trigger xxx
DEBUG: enable_trigger    : ALTER TABLE xxxx
DEBUG: create_table      : CREATE TABLE repack.table_oid

也就是说pg_repack执行的之后会有CREATE TYPE ,CREATE TRIGGER ,CREATE TABLE的动作
如果pg_repack中途失败，需要手动删除上述动作
1 查看type，删除type
\dT repack.pk_oid
drop type repack.pk_对象oid CASCADE;
2 查看trigger, 删除trigger
select event_object_table, event_manipulation from information_schema.triggers where trigger_name=‘repack_trigger’;

 event_object_table, event_manipulation
--------------------
 trsodetail, INSERT
 trsodetail, DELETE
 trsodetail, UPDATE
(3 rows)

drop trigger repack_trigger on database.table;
3 查看log file，删除log file
\d repack.log_oid
drop table repack.log_oid;
注意，2的触发器会记录及insert,delete,update的操作到3的log file
4 查看pg_repack一时文件，删除一时文件
\d repack.table_oid
\ddrop table repack.table_oid;
5 如果pg_repack对象是index,也是一样先查看index，然后删除index
\d database.index_oid；
drop index database.index_oid；

参考：
https://www.postgresql.org/docs/current/sql-droptype.html
https://www.postgresql.org/docs/current/sql-droptrigger.html
https://www.postgresql.org/docs/current/infoschema-triggers.html

衍生处理2：pg_repack因为执行后无法终止，并且会产生中间结果，
可以使用pg_terminate_backend(pid)函数来中止pg_repack,因为函数本身有roll back机能，
所以pg_repack被终止之后不会产生中间结果。
注意终止之后再/var/log/messages会产生错误日志：
FATAL: terminating connection due to administrator command
STATEMENT: ANALYZE test

查看数据库的所有索引index
\o /tmp/get_schema_info.log
SELECT tablename AS table_name, indexname, indexdef FROM pg_indexes order by tablename;
\o
table_name 表名
indexname 索引名
indexdef 定义索引的 语句

-o filename
–output=filename
Put all query output into file filename. This is equivalent to the command \o.
，\o是打开输出，这样所有的查询结果可以输出到一个文件里，知道在执行\o关闭输出

从csv文件导入数据
导入数据可以从csv文件的输入来导入，psql的copy命令可以实现
例如：
psql -d tesetdb-U postgres -h localhost -c “\COPY 【表名】(cell1, cell2) FROM ‘test.csv’ WITH csv header encoding ‘utf-8’;”

“\copy { table [ ( column_list ) ] } from { ‘filename’ | program ‘command’ | stdin | pstdin } [ [ with ] ( option [, …] ) ] [ where condition ]
\copy { table [ ( column_list ) ] | ( query ) } to { ‘filename’ | program ‘command’ | stdout | pstdout } [ [ with ] ( option [, …] ) ]

加上header会自动过滤csv文件的头部，否则csv的头部（title）也会导入
postgres=# select * from test_table;
time | datid
-----------------±------
20211019_162400 | 13323
20211019_162400 | 13323
20211019_162400 | 13323
20211019_162400 | 13323
20211019_162400 | 13323
time | oid
20211019_162400 | 13323
20211019_162400 | 13323
20211019_162400 | 13323

create user与create role的区别

官网上的描述是：

CREATE USER 现在是CREATE ROLE的别名 。唯一的区别是，当命令拼写为 CREATE USER 时，默认情况下假定为 LOGIN，而当命令拼写为 CREATE ROLE 时，假定为 NOLOGIN。

也就是说create user 默认有login权限，而create role没有。

pg_cancel_backend和pg_terminate_backend的区别

注意PID是postgres级别的后台进程ID，不是OS级别的进程ID

在pg_cancel_backend(pid)下，session还在，事物回退;
在pg_terminate_backend(pid)操作后，session消失，事物回退。

参考：https://www.postgresql.org/docs/9.6/functions-admin.html
https://blog.csdn.net/lt89102476/article/details/84759743

查看所有数据库的schema

select * from information_schema.schemata;
catalog_name | schema_name | schema_owner | default_character_set_catalog | default_character_set_schema | default_character_set_name | sql_path
--------------±-------------------±-------------±------------------------------±-----------------------------±---------------------------±---------
repository | statsrepo | postgres | | | |
repository | dbms_stats | postgres | | | |
repository | information_schema | postgres | | | |

切换schema（默认是public）
set public to 想要切换的schema名字