postgresql加固

合集下载

postgresql教程

postgresql教程PostgreSQL是一个开源的关系数据库管理系统（RDBMS），它的目标是成为最先进的开源数据库，并支持许多标准SQL 功能以及一些不同的高级功能。

本教程将向您介绍PostgreSQL的基本概念和用法。

我们将从安装和设置开始，然后逐步深入了解表、视图、索引、事务和查询等主题。

第一部分：安装和设置在本部分中，我们将向您介绍如何下载、安装和设置PostgreSQL数据库。

我们还将介绍一些基本概念和术语，如数据库、表和列。

1. 安装PostgreSQL：在本节中，我们将向您展示如何从官方网站或其他来源下载并安装PostgreSQL。

2. 设置数据库连接：在本节中，我们将介绍如何设置和配置数据库连接，包括创建用户、设置密码和分配权限等内容。

3. 创建数据库和表：在本节中，我们将介绍如何创建数据库和表，并向您展示一些常用的数据类型和约束。

第二部分：表和视图在本部分中，我们将更详细地介绍表格和视图的概念，并向您展示如何使用它们来存储和查询数据。

1. 创建和修改表格：在本节中，我们将介绍如何创建新的表格，并向您展示如何修改和删除现有的表格。

2. 数据类型和约束：在本节中，我们将深入了解不同的数据类型和约束，并向您展示如何使用它们来保证数据的完整性和一致性。

3. 视图和触发器：在本节中，我们将向您展示如何创建和使用视图和触发器，以及如何利用它们来简化复杂的查询和操作。

第三部分：索引和查询优化在本部分中，我们将介绍索引和查询优化的概念，并向您展示如何使用索引来提高查询性能。

1. 索引的概念和类型：在本节中，我们将介绍不同类型的索引，如B-tree、哈希和GiST索引，并向您展示如何创建和使用它们。

2. 查询优化和性能调优：在本节中，我们将介绍一些常见的查询优化技术，如查询计划、索引优化和统计信息收集等。

3. 复杂查询和连接：在本节中，我们将向您展示如何编写复杂的查询，包括多表连接、子查询和聚合等。

PostgreSQL数据库调优经验

PostgreSQL数据库调优经验一、概述数据库的性能优化对于提升系统的整体性能至关重要。

本文将介绍一些PostgreSQL数据库调优的经验和技巧，旨在帮助开发人员和管理员提升数据库的性能和效率。

二、硬件调优1. 存储设备选择：选择高速且稳定的存储设备，如SSD硬盘，以提高数据库的读写性能。

2. 内存设置：合理设置shared_buffers参数，将其调整到适当的大小，以便缓存更多的数据块，提高查询的响应速度。

3. CPU设置：根据服务器的负载情况，调整max_connections参数以控制并发连接数，在高负载情况下可以考虑增加系统的CPU核心数。

三、索引优化1. 使用合适的索引：根据查询的需求和表的大小，选择合适的索引类型（B树、哈希、GiST等），并确保创建索引的列具有高选择性。

2. 删除不必要的索引：定期审查并删除不再使用或无效的索引，以减少索引维护的开销。

3. 索引覆盖：通过创建索引包含所需的查询列，减少磁盘I/O，提高查询的性能。

四、查询优化1. 避免全表扫描：使用WHERE子句和索引来过滤数据，避免全表扫描的开销。

2. 使用合适的JOIN类型：根据数据之间的关联关系，选择合适的JOIN类型（INNER JOIN、LEFT JOIN、OUTER JOIN等），以减少查询的复杂度。

3. 分解复杂查询：对于复杂的查询，可以将其分解为多个简单的查询，并使用临时表或WITH语句组合结果，以提高查询的可维护性和性能。

五、配置优化1. 文件系统设置：使用合适的文件系统（如XFS、EXT4等）以及正确的文件系统参数，提高I/O性能。

2. 日志设置：根据实际需求，合理设置日志级别和日志记录方式，避免过多的日志输出对性能造成影响。

3. 超时设置：根据业务需求和系统负载情况，调整合适的超时设置，避免长时间的等待或超时导致的性能问题。

六、并发控制1. 事务管理：合理管理事务的提交和回滚，尽量减少长事务的使用，以避免锁定资源时间过长，影响并发性能。

PostgreSQL数据库介绍

PostgreSQL数据库介绍PostgreSQL是一种功能强大的关系型数据库管理系统(RDBMS)，它采用了开源的方式进行开发和维护。

由于它的稳定性、安全性和可扩展性，PostgreSQL已成为最受欢迎的开源数据库之一、本文将从数据库概述、特点、架构、应用领域和优势等方面对PostgreSQL进行详细介绍。

一、概述PostgreSQL是一个高度模块化的数据库系统，它支持SQL语言，具有ACID（原子性、一致性、隔离性和持久性）事务，并具有复杂的查询、并发控制和储存优化功能。

它的目标是成为一个功能完善、兼容性良好且具有高可靠性的数据库管理系统。

二、特点1. 多版本并发控制：PostgreSQL支持多个事务并发进行操作，具有高并发性能。

它采用多版本并发控制(MVCC)技术来处理并发访问问题，确保事务的正确性和一致性。

2. 对象关系性：PostgreSQL支持复杂的数据类型，如数组、JSON、XML等。

它还提供了表继承、视图、触发器和存储过程等高级特性，能够灵活地处理复杂的数据模型。

3. 扩展性：PostgreSQL具有可扩展的体系结构，可以实现水平和垂直扩展。

它支持分区表、并行查询和复制等特性，能够应对大规模数据和高负载的情况。

4. 安全性：PostgreSQL具有强大的安全性功能，支持通过用户名和密码、客户端证书、多种加密算法等方式进行身份验证和数据加密。

它还提供了访问控制列表（ACL）、行级安全性和安全沙箱等特性，确保数据的安全性和隐私性。

5. 可扩展的生态系统：PostgreSQL拥有庞大的开发社区，提供了丰富的第三方插件和扩展，可以满足各种特定需求。

它还与许多编程语言和开发工具进行了集成，提供了便捷的开发和管理接口。

三、架构PostgreSQL的架构主要分为后台进程和前台连接两部分。

后台进程包括主进程（会监听数据库连接）和多个工作进程（每个进程处理一个数据库连接），用于处理数据库的各种操作。

设置CA证书来强化PostgreSQL的安全性的教程-电脑资料

设置CA证书来强化PostgreSQL的安全性的教程-电脑资料这篇文章主要介绍了设置CA证书来强化PostgreSQL的安全性的教程,主要用到了CA私钥,需要的朋友可以参考下在经历了多次的摸索实验后我终于成功地实现了SSL证书认证的功能，因此我想这次我要把这些步骤记录下来供日后查阅，。

出于安全和方便的原因，我要在一台单独的专用机器上签署客户的证书，这台机器也称为证书授证中心(CA)。

这让我们在授权新的客户端时不必先登录到PostgreSQL服务器然后再签署证书或者修改pg_hba.conf。

我们要创建一个特殊的数据库组，叫sslcertusers。

这个组里的所有用户都可以通过由CA签署的证书进行连接。

在下面的例子中，请将"trustly"替换成你的公司名或组织名。

所有的命令都是基于Ubuntu Linux 12.04 LTS。

设置CACA应该是一台离线的处于高度安全环境中的计算机。

生成CA私钥sudo openssl genrsa -des3 -out /etc/ssl/private/trustly-ca.key 2048sudo chown root:ssl-cert /etc/ssl/private/trustly-ca.keysudo chmod 640 /etc/ssl/private/trustly-ca.key 生成CA证书sudo openssl req -new -x509 -days 3650 \\-subj ‘/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=trustly‘ \ \-key /etc/ssl/private/trustly-ca.key \\-out /usr/local/share/ca-certificates/trustly-ca.crtsudo update-ca-certificates配置PostgreSQL服务器生成PostgreSQL服务器私钥# Remove default snakeoil certssudo rm /var/lib/postgresql/9.1/main/server.keysudo rm /var/lib/postgresql/9.1/main/server.crt# Enter a passphrasesudo -u postgres openssl genrsa -des3 -out /var/lib/postgresql/9.1/main/server.key 2048# Remove the passphrasesudo -u postgres openssl rsa -in /var/lib/postgresql/9.1/main/server.key -out /var/lib/postgresql/9.1/main/server.keysudo -u postgres chmod 400 /var/lib/postgresql/9.1/main/server.key生成PostgreSQL服务器证书签署请求(CSR)sudo -u postgres openssl req -new -nodes -key /var/lib/postgresql/9.1/main/server.key -days 3650 -out /tmp/server.csr -subj ‘/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=postgres ‘用CA私钥签署PostgreSQL服务器证书请求sudo openssl req -x509 \\-key /etc/ssl/private/trustly-ca.key \\-in /tmp/server.csr \\-out /var/lib/postgresql/9.1/main/server.crtsudo chown postgres:postgres /var/lib/postgresql/9.1/main/server.crt 创建根(root)证书=PostgreSQL服务器证书+CA证书sudo -u postgres sh -c ‘cat /var/lib/postgresql/9.1/main/server.crt /etc/ssl/certs/trustly-ca.pem > /var/lib/postgresql/9.1/main/root.crt‘sudo cp /var/lib/postgresql/9.1/main/root.crt /usr/local/share/ca-certificates/trustly-postgresql.crtsudo update-ca-certificates 授权访问CREATE GROUP sslcertusers;ALTER GROUP sslcertusers ADD USER joel; # /etc/postgresql/9.1/main/pg_hba.conf:hostssl nameofdatabase +sslcertusers 192.168.1.0/24 cert clientcert=1 重启PostgreSQLsudo service postgresql restartPostgreSQL客户端设置从PostgreSQL服务器上复制根证书mkdir ~/.postgresqlcp /etc/ssl/certs/trustly-postgresql.pem ~/.postgresql/root.crt生成PostgreSQL客户端私钥openssl genrsa -des3 -out ~/.postgresql/postgresql.key 1024 # If this is a server, remove the passphrase:openssl rsa -in ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.key 生成PostgreSQL客户端证书签署请求并签署# Replace "joel" with username:openssl req -new -key ~/.postgresql/postgresql.key -out ~/.postgresql/postgresql.csr -subj‘/C=SE/ST=Stockholm/L=Stockholm/O=Trustly/CN=joel‘sud o openssl x509 -req -in ~/.postgresql/postgresql.csr -CA /etc/ssl/certs/trustly-ca.pem -CAkey /etc/ssl/private/trustly-ca.key -out ~/.postgresql/postgresql.crt -CAcreateserialsudo chown joel:joel -R ~/.postgresqlsudo chmod 400 -R ~/.postgresql/postgresql.key。

基于PostgreSQL的数据库安全性加固与备份策略

基于PostgreSQL的数据库安全性加固与备份策略在当今信息化时代，数据安全已经成为企业发展中不可或缺的重要环节。

作为一种开源的关系型数据库管理系统，PostgreSQL在数据安全性方面备受关注。

本文将探讨基于PostgreSQL的数据库安全性加固与备份策略，帮助企业建立更加健壮的数据保护机制。

1. 数据库安全性加固1.1 加强访问控制在数据库安全性加固过程中，加强访问控制是至关重要的一环。

通过合理设置用户权限、角色和访问规则，可以有效地限制用户对数据库的操作范围，降低潜在的风险。

在PostgreSQL中，可以通过GRANT和REVOKE语句来管理用户权限，同时结合行级别安全策略，实现更精细化的权限控制。

1.2 加密数据传输为了防止数据在传输过程中被窃取或篡改，建议使用SSL/TLS等加密协议对数据库连接进行加密。

通过配置PostgreSQL服务器端和客户端的SSL支持，可以确保数据在传输过程中得到有效保护，提高数据传输的安全性。

1.3 定期审计与监控定期审计数据库操作日志，并监控数据库性能和异常行为是保障数据库安全性的有效手段。

通过使用PostgreSQL提供的日志记录功能和第三方监控工具，可以及时发现潜在威胁并采取相应措施，确保数据库系统处于一个安全可控的状态。

2. 数据库备份策略2.1 制定备份计划建立合理的数据库备份计划是保障数据可靠性和完整性的基础。

根据业务需求和数据重要性，可以选择全量备份、增量备份或差异备份等不同方式进行数据备份。

同时，要考虑到备份频率、存储介质和备份策略的灵活性，确保备份数据能够及时恢复并满足业务需求。

2.2 多点备份存储为了防止单点故障导致数据丢失，建议将备份数据存储在多个地点或介质上。

可以选择云存储、本地磁盘或远程服务器等多种方式进行备份存储，提高数据的容灾能力和可靠性。

2.3 定期测试恢复定期测试数据库备份的恢复能力是确保备份策略有效性的重要手段。

通过模拟真实场景下的数据恢复过程，验证备份数据的完整性和可用性，及时发现问题并进行修复调整，以确保在关键时刻能够快速有效地恢复数据。

PostGreSQL.配置和日常管理.

4
Do you want to continue? [Y/n]: Y --------------------------------------------------------------------------Please wait while Setup installs PostgreSQL on your computer. Installing 0% ______________ 50% ______________ 100% ######################################### --------------------------------------------------------------------------Setup has finished installing PostgreSQL on your computer. Launch Stack Builder at exit? Stack Builder may be used to download and install additional tools, drivers and applications to complement your PostgreSQL installation. [Y/n]: Y
--------------------------------------------------------------------------Existing data directory
An existing data directory has been found at /usr/local/postgresql8/data set to use port 5432. This directory and it's configuration will be used for this installation. Press [Enter] to continue :

postgresql upsert 原理

postgresql upsert 原理
UPSERT（也称为MERGE 或INSERT ON CONFLICT）是一种流行的技术，用于在数据库中执行插入或更新操作。

在PostgreSQL 中，UPSERT 操作由INSERT ON CONFLICT 语法实现。

INSERT ON CONFLICT 语法允许您在INSERT 操作中定义一个冲突目标，以及冲突后要执行的动作。

如果记录不存在，则INSERT 新记录。

但是，如果记录已经存在，则可以更新该记录的值，也可以忽略该记录或抛出异常。

UPSERT 在内部执行以下步骤：
1. INSERT 新行。

2. 如果冲突，则执行UPDATE 操作。

在更新操作期间，您需要指定要更新的列以及要更新的值。

此外，您必须定义约束，以便系统可以确定何时发生冲突。

如果您不想更新任何数据并将冲突视为成功，则可以使用ON CONFLICT DO NOTHING 选项。

另外，如果要在冲突时使用不同的UPDATE 语句，则可以使用ON CONFLICT DO UPDATE 选项。

在执行UPSERT 操作时，PostgreSQL 使用索引来查找现有的行并决定是否存在冲突。

如果您的表没有适当的索引，则UPSERT 操作可能会变得非常缓慢。

因此，在编写UPSERT 操作时，确保为表定义了必要的索引。

总的来说，UPSERT 是一种复杂但强大的技术，可用于在PostgreSQL 中合并和更新数据。

它涉及许多内部步骤，包括查找现有行、执行插入操作、执行更新操作以及定义合适的索引和约束。

PostgreSQL-Linux数据库基线

检查结果
截图
加固方案
1.备份数据库pg_hba.conf配置文件,用数据库的系统账户执行“cp -a /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.bak”
2. 编辑pg_hba.conf配置文件，配置安全合理的认证方式.
postgres为数据库实例名，即可修改配置值为info
修改后对新会话生效，当前连接会话不生效
编码
PostgreSQL-11
名称
日志的开头记录内容配置
权重
3
检测方法
1.使用psql工具或pgAdmin工具连接到数据库
2.登录成功后执行“show log_line_prefix;”
合规判定条件
1.当结果不为空时合规，表示会记录产生该条日志的信息
编码
PostgreSQL-4
名称
账户验证超时配置
权重
1
检测方法
1.使用psql工具或pgAdmin工具连接到数据库
2.登录成功后执行“show authentication_timeout;”
合规判定条件
1.当结果小于等于60时合规，表示验证超过60秒则超时关闭
2.当结果大于60时不合规
检查结果
截图
2.编辑配置文件，找到max_connections所在的行，并修改配置值为500，即可配置最大连接数为500
3.重启数据库后生效
编码
PostgreSQL-9
名称
日志记录信息格式配置
权重
3
检测方法
1.使用psql工具或pgAdmin工具连接到数据库
2.登录成功后执行“show log_error_verbosity;”

postgresql pg_basebackup 原理

postgresql pg_basebackup 原理
pg_basebackup是PostgreSQL中用于备份数据库的工具，它的
工作原理如下：
1. 打开pg_basebackup：执行pg_basebackup命令时，会启动
一个新的PostgreSQL进程。

2. 获取数据库信息：pg_basebackup进程首先连接到正在运行
的PostgreSQL数据库，并获取数据库的基本信息，如数据库
版本号、表空间、WAL日志位置等。

3. 创建备份目录：pg_basebackup会在备份目录下创建一个新
的子目录，用于存储备份文件。

4. 备份数据文件：pg_basebackup会依次备份数据库的所有数
据文件（包括表、索引、存储过程等）到备份目录中。

备份时，它会逐个文件读取并写入到备份目录中。

5. 备份WAL日志：在备份数据文件之后，pg_basebackup会
备份数据库的WAL日志文件。

WAL日志文件包含了数据库
的所有修改操作，用于在恢复时重新应用这些修改。

6. 完成备份：备份完成后，pg_basebackup进程会向PostgreSQL数据库发送一个标识备份完成的信号。

总的来说，pg_basebackup的原理就是通过连接到PostgreSQL
数据库并读取数据文件和WAL日志文件，将它们复制到指定
的备份目录中，从而实现数据库的备份。

备份完成后，可以使用这些备份文件来进行数据库的还原和恢复操作。

PostgreSQL中的高可用性解决方案

PostgreSQL中的高可用性解决方案在现代的数据应用中，高可用性（High Availability，HA）是一个至关重要的因素。

在数据库领域，PostgreSQL提供了一些高可用性的解决方案，可以帮助用户实现数据的持续可用性和系统的可靠性。

本文将介绍一些常用的PostgreSQL高可用性解决方案。

1. 数据复制（Replication）数据复制是一种常见的高可用性解决方案，它通过将数据从主服务器复制到一个或多个备用服务器，实现数据的冗余存储和故障恢复能力。

PostgreSQL提供了多种数据复制方法，包括基于日志的物理复制（Physical Replication）和基于逻辑复制（Logical Replication）。

1.1 基于日志的物理复制基于日志的物理复制是PostgreSQL内置的一种数据复制方法，它通过复制主服务器上的事务日志（WAL），将变更的数据块物理复制到备用服务器。

这种方法可以实现快速的数据复制和故障切换，但对备用服务器的版本和配置要求较高。

1.2 基于逻辑复制基于逻辑复制是PostgreSQL 9.4及以上版本中引入的一种数据复制方法。

它通过解析和应用主服务器上的逻辑变更（例如INSERT、UPDATE、DELETE语句），将变更的数据逻辑复制到备用服务器。

这种方法相对灵活，可以实现不同版本和配置的备用服务器。

2. 流复制（Streaming Replication）流复制是PostgreSQL中一种基于日志的物理复制方法，它通过流式传输事务日志（WAL）来实现数据的持续复制和故障切换。

流复制要求主服务器和备用服务器之间有稳定的网络连接，并且备用服务器必须实时接收并应用主服务器上的更改。

2.1 同步流复制同步流复制是一种高可用性的方法，它确保主服务器上的事务在提交后，备用服务器立即应用并确认。

这种方法可以提供零数据丢失和最小的故障恢复时间，但对网络延迟和性能要求较高。

posatgresql_改造_goldendb_数据库_评估要点_概述及解释说明

posatgresql 改造goldendb 数据库评估要点概述及解释说明1. 引言1.1 概述在当前快速发展的大数据时代，数据库系统起着至关重要的作用。

为了满足数据存储和处理方面的需求，很多公司和组织都采用了数据库管理系统（DBMS）。

在这样的背景下，GoldenDB作为一个优秀高效的数据库软件被广泛应用。

然而，随着业务规模和数据量不断增长，我们意识到GoldenDB在某些方面存在一些局限性和问题。

因此，我们考虑对GoldenDB进行改造并将其迁移到基于PostgreSQL的系统上。

1.2 文章结构本文主要分为三个部分：引言、正文和结论。

引言部分将介绍文章的背景和目的；正文部分将详细阐述数据库评估要点以及介绍PostgreSQL数据库；最后，在结论部分将总结和分析改造GoldenDB数据库的综合评估结果，并提出建议和展望。

1.3 目的本文旨在评估改造GoldenDB数据库并迁移到基于PostgreSQL的新系统所涉及到的关键要点。

通过对性能、可靠性和扩展性等方面进行评估，我们可以更好地理解改造过程中需要注意的关键问题以及选择基于PostgreSQL作为新系统平台所带来的优势。

我们希望通过这篇文章为相关从业者提供有关数据库改造的宝贵信息和参考，以便更好地应对大规模数据存储和处理的挑战。

2. 正文:2.1 数据库评估要点:数据库评估是一项重要任务，它需要对数据库的性能、可靠性和扩展性进行全面评估。

以下是在改造GoldenDB数据库时需要考虑的数据库评估要点：2.1.1 性能评估:性能是一个关键指标，影响着数据库的运行效率和响应速度。

在评估GoldenDB 数据库并进行改造时，我们需要考察以下性能相关因素：- 查询性能: 通过对性能测试样本数据进行查询操作，并记录查询耗时、索引使用情况及优化建议等方面，来评估GoldenDB在查询方面的表现。

- 并发处理能力: 在同时处理多个用户请求时，GoldenDB是否可以有效地管理资源以及维持系统的稳定性。

漏洞加固方案

1. MS15-034/CVE-2015-1635 HTTP远程代码执行漏洞1.1 漏洞清单1.2 加固方案Windows6.1-KB3042553-x64.msu安装此补丁即可，系统会重启1.3 回退方案1、控制面板-添加删除程序，如下图右键删除程序即可1/362/361.4 加固结果未加固，需要搭建并行替换系统后方可加固2. Redis 未授权访问漏洞2.1 漏洞清单2.2 加固方案找到cd /usr/local/etc/中的redis.conf，用vi 编辑redis.conf，在redis.conf 中找到“requirepass”字段，取消注释并在后面填上需要的密码即可；需要重启系统才能生效；注：因为是主从设备请联系redis搭建人员确认加固方案风险3/362.3 回退方案找到cd /usr/local/etc/中的redis.conf，用vi 编辑redis.conf，在redis.conf 中找到“requirepass”字段，注释即可；需要重启系统才能生效注：因为是主从设备请联系redis搭建人员确认加固方案风险2.4 加固结果已经加固3. PostgreSQL数据库空密码漏洞3.1 漏洞清单3.2 加固方案如psql的默认用户为postgres，1、使用su – postgres进入postgres用户模式，2、使用psql –l查看用户，3、使用psql进入数据库输入ALTER USER postgres WITH PASSWORD '复杂密码'，再输入psql进入数据库输入select * from pg_shadow就能看见加过密的密码了。

4/363.3 回退方案如psql的默认用户为postgres，1、使用su – postgres进入postgres用户模式，2、使用psql –l查看用户，3、使用psql进入数据库输入ALTER USER postgres WITH PASSWORD 紧靠的双引号啥都不输入，再输入psql进入数据库输入select * from pg_shadow就没有密码了。

postgresql加固

2、将“listen_addresses”的值更改为localhost;
3、保存后重启postgresql：“service postgresql restart”
结合iptables限制postgresql的访问IP
1、例如只允许192。168.0.107访问：“iptables -I INPUT -p tcp —s 192.168.0。107 --dport 5432 —j ACCEPT”；
2、保存新增的配置：“service iptables save”；
3、重启iptables：“service iptab端地址和认证方法
1、使用命令“vi /var/lib/pgsql/data/pg_hba。conf”打开配置文件；
权限控制
应为应用创建专门的用户，禁止使用管理员用户,并且要保证这个用户具有尽可能小的权限
1、使用超级管理员登录到数据库的控制台：首先切换用户“su - postgres”，再进入控制台:“psql”；
2、例如，赋予dbuser用户创建用户的权限:“ALTER ROLE dbuser WITH CREATEUSER；”
1、使用超级管理员登录到数据库的控制台:首先切换用户“su - postgres”，再进入控制台：“psql”；
2、更改用户名为dbuser的密码：“alter user dbuser with password ’qt＠2016’；”
访问控制
如无业务需求,建议修改postgresql的监听地址
1、使用命令“vi /var/lib/pgsql/data/postgresql。conf"打开配置文件；
应对ddl和dml的相关操作进行记录
1、使用命令“vi /var/lib/pgsql/data/postgresql.conf”打开配置文件；

postgre rewritebatchedinserts原理-概述说明以及解释

postgre rewritebatchedinserts原理-概述说明以及解释1.引言1.1 概述Postgre RewriteBatchedInserts是一项优化功能，旨在提升PostgreSQL在高并发写入场景下的性能。

在传统的插入操作中，每条记录都会触发一次磁盘写入操作，这样会造成较高的I/O开销以及降低写入性能。

而通过使用RewriteBatchedInserts，可以将多个插入操作合并成一个批处理操作，从而减少磁盘写入的次数，提高写入性能。

PostgreSQL作为一个关系型数据库管理系统，设计了一套强大而灵活的分析引擎，因而在处理大量数据时具有较高的灵活性和可扩展性。

然而，在高并发写入场景下，由于每个写入操作都会产生额外的开销，导致系统性能下降。

这时，使用RewriteBatchedInserts可以显著优化PostgreSQL的写入性能，尤其是当需要处理大量小规模的插入操作时。

RewriteBatchedInserts的原理主要是通过合并多个插入操作，减少对磁盘的写入次数。

当一组插入操作到达时，RewriteBatchedInserts会将这些操作合并成一个批处理操作，而不是将每条记录都单独写入磁盘。

这样，可以大幅度减少磁盘I/O的次数，从而提高写入性能。

由于合并多个插入操作需要一定的时间和资源，因此RewriteBatchedInserts在某些场景下可能会带来额外的延迟。

因此，在使用RewriteBatchedInserts时需要权衡系统性能和延迟之间的平衡。

总而言之，Postgre RewriteBatchedInserts是一项重要的优化功能，通过合并多个插入操作来减少磁盘写入次数，提高PostgreSQL在高并发写入场景下的性能。

然而，在具体应用中需要评估系统的需求和权衡性能和延迟之间的关系。

未来，随着技术的进一步发展，我们可以期待RewriteBatchedInserts在更加复杂和大规模的写入场景中发挥更大的作用。

PostgreSQL性能优化综合案例-2

PostgreSQL性能优化综合案例-2【调优阶段8】1. 压⼒测试pgbench -M prepared -r -c 1 -f /home/postgres/test/login0.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 & pgbench -M prepared -r -c 1 -f /home/postgres/test/login1.sql -j 1 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 & pgbench -M prepared -r -c 2 -f /home/postgres/test/login2.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 & pgbench -M prepared -r -c 2 -f /home/postgres/test/login3.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 & pgbench -M prepared -r -c 2 -f /home/postgres/test/login4.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 & 2. 测试结果cat log.log*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 1number of threads: 1duration: 180 snumber of transactions actually processed: 296485tps = 1647.130827 (including connections establishing)tps = 1647.153173 (excluding connections establishing)statement latencies in milliseconds:0.003394 \setrandom userid 1 40000000.599293 SELECT f_user_login_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 1number of threads: 1duration: 180 snumber of transactions actually processed: 270077tps = 1500.414232 (including connections establishing)tps = 1500.434330 (excluding connections establishing)statement latencies in milliseconds:0.004436 \setrandom userid 4000001 80000000.656274 SELECT f_user_login_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 543390tps = 3018.814281 (including connections establishing)tps = 3018.901510 (excluding connections establishing)statement latencies in milliseconds:0.004553 \setrandom userid 8000001 120000000.652033 SELECT f_user_login_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 592774tps = 3293.147194 (including connections establishing)tps = 3293.235012 (excluding connections establishing)statement latencies in milliseconds:0.003446 \setrandom userid 12000001 160000000.599297 SELECT f_user_login_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 593614tps = 3297.831371 (including connections establishing)tps = 3297.946707 (excluding connections establishing)statement latencies in milliseconds:0.003421 \setrandom userid 16000001 200000000.598465 SELECT f_user_login_4(:userid);总计 :tps = 12757.337905 (including connections establishing)3. 瓶颈分析与优化测试中我们使⽤的数据库服务器cpu是8核的服务器, 根据以往的经验, 当活跃的进程数等于核数的2倍时可以发挥CPU的最⼤能⼒.所以我们通过增加并发连接来看看到底有多少性能提升.【调优阶段9】1. 压⼒测试pgbench -M prepared -r -c 2 -f /home/postgres/test/login0.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 &pgbench -M prepared -r -c 2 -f /home/postgres/test/login1.sql -j 2 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 &pgbench -M prepared -r -c 4 -f /home/postgres/test/login2.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 &pgbench -M prepared -r -c 4 -f /home/postgres/test/login3.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 &pgbench -M prepared -r -c 4 -f /home/postgres/test/login4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 &2. 测试结果cat log.log*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 375743tps = 2087.443600 (including connections establishing)tps = 2087.489913 (excluding connections establishing)statement latencies in milliseconds:0.003492 \setrandom userid 1 40000000.949744 SELECT f_user_login_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 2number of threads: 2duration: 180 snumber of transactions actually processed: 367801tps = 2043.313370 (including connections establishing)tps = 2043.386454 (excluding connections establishing)statement latencies in milliseconds:0.003710 \setrandom userid 4000001 80000000.969828 SELECT f_user_login_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 730267tps = 4057.007177 (including connections establishing)tps = 4057.148280 (excluding connections establishing)statement latencies in milliseconds:0.003962 \setrandom userid 8000001 120000000.976372 SELECT f_user_login_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 738398tps = 4101.985844 (including connections establishing)tps = 4102.135039 (excluding connections establishing)statement latencies in milliseconds:0.003615 \setrandom userid 12000001 160000000.966314 SELECT f_user_login_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 732793tps = 4070.957105 (including connections establishing)tps = 4071.200533 (excluding connections establishing)statement latencies in milliseconds:0.003882 \setrandom userid 16000001 200000000.973208 SELECT f_user_login_4(:userid);tps = 16360.707096 (including connections establishing)tps = 16361.360219 (excluding connections establishing)3. 瓶颈分析与优化继续增加连接,tps还可以再提⾼吗? : 不可以.8核的机器16个活动的会话基本上就到达它的上限了.因此要提⾼tps还可以加CPU.下⾯增加连接到30个的测试结果证明了上⾯的结论.pgbench -M prepared -r -c 6 -f /home/postgres/test/login0.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login0 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login1.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login1 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login2.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login2 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login3.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login3 & pgbench -M prepared -r -c 6 -f /home/postgres/test/login4.sql -j 6 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login4 & 结果cat log.log*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 544811tps = 3026.494301 (including connections establishing)tps = 3026.608244 (excluding connections establishing)statement latencies in milliseconds:0.003768 \setrandom userid 1 40000001.973230 SELECT f_user_login_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 544485tps = 3024.298399 (including connections establishing)tps = 3024.468785 (excluding connections establishing)statement latencies in milliseconds:0.003735 \setrandom userid 4000001 80000001.974466 SELECT f_user_login_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 544778tps = 3025.262019 (including connections establishing)tps = 3025.469901 (excluding connections establishing)statement latencies in milliseconds:0.003707 \setrandom userid 8000001 120000001.973661 SELECT f_user_login_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 542008tps = 3010.921306 (including connections establishing)tps = 3011.146550 (excluding connections establishing)statement latencies in milliseconds:0.003662 \setrandom userid 12000001 160000001.983714 SELECT f_user_login_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 6number of threads: 6duration: 180 snumber of transactions actually processed: 539505tps = 2996.511493 (including connections establishing)tps = 2996.874239 (excluding connections establishing)statement latencies in milliseconds:0.003768 \setrandom userid 16000001 200000001.992923 SELECT f_user_login_4(:userid);总计 :tps = 15083.487518 (including connections establishing)tps = 15084.567719 (excluding connections establishing)连接数超过2倍核数后根本不会有性能提升了, 这台服务器的潜⼒基本上挖掘得差不多了.接下来就需要通过增加服务器来提升数据库的整体性能了.⾸先要⽤到的是PostgreSQL的流复制, 通过hot standby可以进⾏读写分离, 也就是将SELECT的请求分发到hot standby上. (需要注意跨库事务的问题, 如standby的延时, 这⾥不详细阐述)新建查询函数和插⼊更新函数 :create or replace function f_user_login_sel_0(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info_0 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_1(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info_1 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_2(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info_2 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_3OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq from user_info_3 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_sel_4(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq from user_info_4 where userid=i_userid;return;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_0(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr()); update user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_1(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr()); update user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_2(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr()); update user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;when others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_3(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid;return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_insupd_4(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());update user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid;return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;hot standby库也需要将数据加载到内存, 具体操作略.【调优阶段10】1. 测试脚本cat log*\setrandom userid 1 4000000SELECT f_user_login_insupd_0(:userid);\setrandom userid 4000001 8000000SELECT f_user_login_insupd_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_insupd_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_insupd_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_insupd_4(:userid);\setrandom userid 1 4000000SELECT f_user_login_sel_0(:userid);\setrandom userid 4000001 8000000SELECT f_user_login_sel_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_sel_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_sel_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_sel_4(:userid);2. 压⼒测试pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login_sel4 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd0.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd1.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd2.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_insupd3.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_insupd4.sql -j 4 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_insupd4 & 3. 测试结果hot standby的测试数据 :cat log.login_sel*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 552618tps = 3012.767914 (including connections establishing) tps = 3012.877330 (excluding connections establishing) statement latencies in milliseconds:0.003166 \setrandom userid 1 40000000.988247 SELECT f_user_login_sel_0(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 750314tps = 4089.671930 (including connections establishing) tps = 4089.771337 (excluding connections establishing) statement latencies in milliseconds:0.003030 \setrandom userid 4000001 8000000 0.726462 SELECT f_user_login_sel_1(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 727839tps = 3967.242817 (including connections establishing) tps = 3967.364415 (excluding connections establishing) statement latencies in milliseconds:0.003260 \setrandom userid 8000001 12000000 0.748466 SELECT f_user_login_sel_2(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 715952tps = 3903.028278 (including connections establishing) tps = 3903.130455 (excluding connections establishing) statement latencies in milliseconds:0.003077 \setrandom userid 12000001 16000000 0.761439 SELECT f_user_login_sel_3(:userid); transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 964366tps = 5257.974345 (including connections establishing) tps = 5258.120849 (excluding connections establishing) statement latencies in milliseconds:0.003153 \setrandom userid 16000001 20000000 0.753196 SELECT f_user_login_sel_4(:userid); 总计 :tps = 20230.685284 (including connections establishing) tps = 20231.264386 (excluding connections establishing) primary的测试数据 :cat log.login_insupd*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 745415tps = 4141.145602 (including connections establishing) tps = 4141.250129 (excluding connections establishing) statement latencies in milliseconds:0.716912 SELECT f_user_login_insupd_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 737761tps = 4098.582645 (including connections establishing)tps = 4098.704693 (excluding connections establishing)statement latencies in milliseconds:0.003360 \setrandom userid 4000001 80000000.723997 SELECT f_user_login_insupd_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 761171tps = 4228.709500 (including connections establishing)tps = 4228.817139 (excluding connections establishing)statement latencies in milliseconds:0.003333 \setrandom userid 8000001 120000000.701648 SELECT f_user_login_insupd_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 761960tps = 4233.031271 (including connections establishing)tps = 4233.166856 (excluding connections establishing)statement latencies in milliseconds:0.003306 \setrandom userid 12000001 160000000.700967 SELECT f_user_login_insupd_3(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 4number of threads: 4duration: 180 snumber of transactions actually processed: 999167tps = 5550.893825 (including connections establishing)tps = 5551.246720 (excluding connections establishing)statement latencies in milliseconds:0.003385 \setrandom userid 16000001 200000000.712689 SELECT f_user_login_insupd_4(:userid);总计 :tps = 22252.362843 (including connections establishing)tps = 22253.185537 (excluding connections establishing)QPS :qps = 20230.685284 + (22252.362843 * 2) (including connections establishing)qps = 20231.264386 + (22253.185537 * 2) (excluding connections establishing)4. 瓶颈分析与优化主节点 :avg-cpu: %user %nice %system %iowait %steal %idle56.30 0.00 21.72 4.24 0.00 17.73Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sda3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00sdc 0.00 2781.50 0.00 93.50 0.00 22876.00 244.66 0.09 0.93 0.90 8.40sdd 0.00 10656.50 0.00 2302.50 0.00 105300.00 45.73 108.00 27.85 0.43 100.05 dm-0 0.00 0.00 0.00 2875.50 0.00 23004.00 8.00 2.56 0.89 0.03 8.30dm-1 0.00 0.00 0.00 12943.00 0.00 103544.00 8.00 569.00 34.94 0.08 100.10 dm-2 0.00 0.00 0.00 2832.50 0.00 22660.00 8.00 2.55 0.90 0.03 8.05dm-3 0.00 0.00 0.00 41.50 0.00 332.00 8.00 0.02 0.54 0.06 0.25dm-4 0.00 0.00 0.00 1.50 0.00 12.00 8.00 0.00 0.00 0.00 0.00dm-5 0.00 0.00 0.00 1.00 0.00 8.00 8.00 0.01 0.00 4.00 0.40dm-6 0.00 0.00 0.00 11545.50 0.00 92364.00 8.00 505.23 33.04 0.08 91.75 dm-7 0.00 0.00 0.00 1396.50 0.00 11172.00 8.00 63.54 50.65 0.15 20.65 standby节点 :avg-cpu: %user %nice %system %iowait %steal %idle0.00 0.00 0.31 12.87 0.00 86.82Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %utilcciss/c0d0 0.00 1222.39 0.00 996.52 0.00 19136.32 19.20 113.22 116.63 1.00 99.55cciss/c0d0p1 0.00 2.99 0.00 1.00 0.00 31.84 32.00 0.10 101.50 101.50 10.10cciss/c0d0p2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d0p3 0.00 1219.40 0.00 995.52 0.00 19104.48 19.19 113.12 116.64 1.00 99.55cciss/c0d1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d2 0.00 1384.08 0.00 251.74 0.00 13297.51 52.82 142.31 522.75 3.95 99.55cciss/c0d3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00cciss/c0d5 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-1 0.00 0.00 0.00 1638.81 0.00 13110.45 8.00 946.36 538.61 0.61 99.55dm-2 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-3 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-4 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00dm-5 0.00 0.00 0.00 2193.03 0.00 17544.28 8.00 275.53 132.89 0.45 99.55显然IO到达瓶颈了. 为什么每次IO都顶不住呢? 是的, 机械硬盘的随机IOPS能⼒就是这么差, 不要有太⾼的奢望.要提升IOPS要么就⽤⾼端存储要么就选择SSD硬盘. 下次有机会找块ssd硬盘来测试⼀下它的iops能⼒到底有多强.那么这些IO是怎么产⽣的呢?1. 主库的IO来⾃insert和update请求.2. hot standby的IO来⾃stream data recovery.因为我的测试环境没有办法扩存储, 所以这⾥就不通过扩存储来解决这个瓶颈了, 还是加服务器.但是这次加2台服务器, 1台⽤来做hot standby. 另⼀台我要把insert请求剥离过去.也就是总共⽤4台服务器.具体的操作如下 :初始化新增的⽇志库 :create table user_login_rec(userid int,login_time timestamp without time zone,ip inet);create table user_logout_rec(userid int,logout_time timestamp without time zone,ip inet);create or replace function f_user_login_ins(i_userid int)returns int as $BODY$declarebegininsert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;主库新增函数 :create or replace function f_user_login_upd_0(i_userid int)returns int as $BODY$declarebeginupdate user_session_0 set logintime=now(),login_count=login_count+1 where userid=i_userid;return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_1(i_userid int)returns int as $BODY$declarebeginupdate user_session_1 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_2(i_userid int)returns int as $BODY$declarebeginupdate user_session_2 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_3(i_userid int)returns int as $BODY$declarebeginupdate user_session_3 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;create or replace function f_user_login_upd_4(i_userid int)returns int as $BODY$declarebeginupdate user_session_4 set logintime=now(),login_count=login_count+1 where userid=i_userid; return 0;exceptionwhen others thenreturn 1;end;$BODY$language plpgsql;再增加⼀台standby, 流复制过程略, 请参考我写过的流复制环境搭建BLOG.《PostgreSQL HOT STANDBY using Stream》优化当前环境如下,primary : 172.16.3.150standby1 : 172.16.3.33standby2 : 172.16.3.39logdb : 172.16.3.40【调优阶段11】1. 测试脚本postgres@db5-> cat login_ins.sql\setrandom userid 1 20000000SELECT f_user_login_ins(:userid);postgres@db5-> cat login_sel*\setrandom userid 1 4000000\setrandom userid 4000001 8000000SELECT f_user_login_sel_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_sel_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_sel_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_sel_4(:userid);postgres@db5-> cat login_upd*\setrandom userid 1 4000000SELECT f_user_login_upd_0(:userid);\setrandom userid 4000001 8000000SELECT f_user_login_upd_1(:userid);\setrandom userid 8000001 12000000SELECT f_user_login_upd_2(:userid);\setrandom userid 12000001 16000000SELECT f_user_login_upd_3(:userid);\setrandom userid 16000001 20000000SELECT f_user_login_upd_4(:userid);2. 压⼒测试pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.33 -p 1921 -U digoal digoal >./log.login33_sel4 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel0.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel1.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel2.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_sel3.sql -j 3 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_sel4.sql -j 4 -n -T 180 -h 172.16.3.39 -p 1921 -U digoal digoal >./log.login39_sel4 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd0.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd0 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd1.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd1 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd2.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd2 & pgbench -M prepared -r -c 3 -f /home/postgres/test_zsplit/login_upd3.sql -j 3 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd3 & pgbench -M prepared -r -c 4 -f /home/postgres/test_zsplit/login_upd4.sql -j 4 -n -T 180 -h 172.16.3.150 -p 1921 -U digoal digoal >./log.login_upd4 & pgbench -M prepared -r -c 16 -f /home/postgres/test_zsplit/login_ins.sql -j 16 -n -T 180 -h 172.16.3.40 -p 1921 -U digoal digoal >./log.login_ins & 3. 测试结果cat log.login33_sel*transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 1534211tps = 8523.315651 (including connections establishing)tps = 8523.524318 (excluding connections establishing)statement latencies in milliseconds:0.002438 \setrandom userid 1 40000000.346514 SELECT f_user_login_sel_0(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 1533785tps = 8520.894378 (including connections establishing)tps = 8521.168645 (excluding connections establishing)statement latencies in milliseconds:0.002423 \setrandom userid 4000001 80000000.346564 SELECT f_user_login_sel_1(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3duration: 180 snumber of transactions actually processed: 1544585tps = 8580.974433 (including connections establishing)tps = 8581.260902 (excluding connections establishing)statement latencies in milliseconds:0.002448 \setrandom userid 8000001 120000000.344071 SELECT f_user_login_sel_2(:userid);transaction type: Custom queryscaling factor: 1query mode: preparednumber of clients: 3number of threads: 3。

Postgresql备份和增量恢复方案

Postgresql备份和增量恢复⽅案前⾔最近⼯作上使⽤的数据库⼀直是Postgresql，这是⼀款开源的数据库，⽽且任何个⼈可以将该数据库⽤于商业⽤途。

在使⽤Postgresql的时候，让我最明显的感觉就是这数据库做的真⼼好，虽然说数据库的安装包真的很⼩，但是性能和操作的便捷是⼀点也不输给其他商业的⼤型数据库，另外在命令⾏界⾯下对该数据库直接进⾏操作的感觉真的是很爽。

在使⽤数据库的时候，我们作为⼩公司的数据库管理员有⼀项⼯作是不可能避免的，那就是数据的备份和恢复问题。

PostgreSQL虽然各个⽅⾯的有点很多，但是在数据库备份这⽅⾯，它是不⽀持增量备份的，这点确实让⼈觉得很是可惜啊。

不过，瑕不掩瑜，总的来说这是⼀款很好的数据库软件。

之前，我们在《》⼀节中，部署了Postgresql的主从异步流复制环境。

主从复制的⽬的是为了实现数据的备份，实现数据的⾼可⽤性和容错⾏。

下⾯主要简单地介绍下我们运维Postgresql数据库时的场景备份与恢复⽅案。

增量备份PostgreSQL在做写⼊操作时，对数据⽂件做的任何修改信息，⾸先会写⼊WAL⽇志（预写⽇志），然后才会对数据⽂件做物理修改。

当数据库服务器掉重启时，PostgreSQL在启动时会⾸先读取WAL⽇志，对数据⽂件进⾏恢复。

因此，从理论上讲，如果我们有⼀个数据库的基础备份（也称为全备），再配合WAL⽇志，是可以将数据库恢复到任意时间点的。

上⾯的知识点很重要，因为我们场景的增量备份说⽩了就是通过基础备份 + 增量WAL⽇志进⾏重做恢复的。

增量备份设置为了演⽰相关功能，我们基于《》⼀节中的环境pghost1服务器上，创建相关管理⽬录切换到 postgres ⽤户下mkdir -p /data/pg10/backupsmkdir -p /data/pg10/archive_walsbackups⽬录则可以⽤来存放基础备份archive_wals⽬录⾃然⽤来存放归档了接下来我们修改我们的postgresql.conf⽂件的相关设置wal_level = replicaarchive_mode = onarchive_command = '/usr/bin/lz4 -q -z %p /data/pg10/archive_wals/%f.lz4'archive_command 参数的默认值是个空字符串，它的值可以是⼀条shell命令或者⼀个复杂的shell脚本。

postgresql基本操作

postgresql基本操作PostgreSQL是一种开源的关系型数据库管理系统，它具有高度的可扩展性、稳定性和安全性。

在使用PostgreSQL时，我们需要掌握一些基本的操作，以便更好地管理和维护数据库。

一、安装和配置PostgreSQL1. 下载并安装PostgreSQL软件包。

2. 配置PostgreSQL的环境变量。

3. 创建一个新的数据库集群。

4. 启动PostgreSQL服务器。

二、创建和管理数据库1. 创建一个新的数据库。

2. 删除一个已有的数据库。

3. 连接到一个数据库。

4. 断开与数据库的连接。

5. 列出所有的数据库。

6. 查看当前连接的数据库。

7. 查看数据库的大小和使用情况。

8. 备份和恢复数据库。

三、创建和管理表1. 创建一个新的表。

2. 删除一个已有的表。

3. 修改一个表的结构。

4. 查看一个表的结构。

5. 插入数据到一个表中。

6. 更新一个表中的数据。

7. 删除一个表中的数据。

8. 查询一个表中的数据。

四、创建和管理用户1. 创建一个新的用户。

2. 删除一个已有的用户。

3. 修改一个用户的密码。

4. 授予一个用户对数据库的访问权限。

5. 撤销一个用户对数据库的访问权限。

五、创建和管理索引1. 创建一个新的索引。

2. 删除一个已有的索引。

3. 查看一个表的索引。

4. 优化查询性能。

六、创建和管理视图1. 创建一个新的视图。

2. 删除一个已有的视图。

3. 修改一个视图的结构。

4. 查看一个视图的结构。

5. 查询一个视图中的数据。

七、创建和管理触发器1. 创建一个新的触发器。

2. 删除一个已有的触发器。

3. 修改一个触发器的结构。

4. 查看一个触发器的结构。

5. 触发器的应用场景。

总结：PostgreSQL是一种功能强大的数据库管理系统，它具有高度的可扩展性、稳定性和安全性。

在使用PostgreSQL时，我们需要掌握一些基本的操作，以便更好地管理和维护数据库。

这些操作包括安装和配置PostgreSQL、创建和管理数据库、创建和管理表、创建和管理用户、创建和管理索引、创建和管理视图以及创建和管理触发器。