08 PostgreSQL数据库部署-WindowsServer
<h1>一、 概述</h1>
<h2>1.1.适用范围</h2>
<p>本文档适用对象为系统运维工程师或具备一定计算机基础知识的人员，对 Windows Server2012 R2 操作系统环境下的 PostgreSQL 10.5 (以下简称 PG 或 PostgreSQL10)单机数据库环境进行部署及初始化配置调整。</p>
<h4>注意：本文以PostgreSQL 10.5为例，N2C5.0安装建议PostgreSQL 12版本</h4>
<h2>1.2.安装规划</h2>
<p>在进行操作系统及数据库安装前，请务必先做好安装规划，可参照本节推荐配置进行</p>
<h3>1.2.1.版本规划</h3>
<p>以下是本文档采用软件版本信息，生产环境请严格按照下表所列软件进行安装。</p>
<table>
<thead>
<tr>
<th style="text-align: left;">编号</th>
<th>安装项目</th>
<th>版本</th>
<th>备注</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">1</td>
<td>操作系统</td>
<td>Windows Server 2012 R2 64bit</td>
<td>64 位 Standard 版</td>
</tr>
<tr>
<td style="text-align: left;">2</td>
<td>数据库</td>
<td>PostgreSQL 10.5</td>
<td>软件下载参照 1.3.3 节</td>
</tr>
</tbody>
</table>
<h3>1.2.2.存储空间规划</h3>
<p>以下是安装开始前的存储规划信息，请仔细阅读。不同环境请按实际需求划分空间，建议
C:\盘 80GB~100GB，其余给数据盘使用，单盘大小建议不大于 2TB。比如剩余 5.2TB，那么可分
成 C、D、E 三个盘，分别为 2TB、2TB、1.2TB。</p>
<table>
<thead>
<tr>
<th style="text-align: left;">编号</th>
<th>规划项目</th>
<th>推荐规划</th>
<th>备注</th>
</tr>
</thead>
<tbody>
<tr>
<td style="text-align: left;">1</td>
<td>WindowsServer2012R2 系统磁盘空间</td>
<td>生产环境 PG 专用服务器建议分配 80GB~100GB，系统安装在服务器自带本地磁盘上。</td>
<td>为确保安全，生产环境系统盘必须放置在具备冗余存储（如常见的 RAID1/5/10 等）的本地磁盘组上。</td>
</tr>
<tr>
<td style="text-align: left;">2</td>
<td>PG</td>
<td>软件安装位置 C:\PostgreSQL\10</td>
<td>存放软件，大概 500MB</td>
</tr>
<tr>
<td style="text-align: left;">3</td>
<td>PG</td>
<td>数据文件位置 D:\pgdata 和 E:\pgdata</td>
<td>存放数据文件，要求空间大</td>
</tr>
</tbody>
</table>
<h4>注意：生产环境下，要求客户将本地磁盘或者外接存储做成 RAID 组磁盘冗余，首选 RAID10（至少 4 块盘），其次可选 RAID1（2 块盘镜像）或 RAID5（3 块盘以上冗余校验）。</h4>
<h3>1.2.3.PG 软件下载</h3>
<p><a href="https://cloud.morewis.com/web/downloadRes?category=243">https://cloud.morewis.com/web/downloadRes?category=243</a>
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=5c1cca9a501d17359057f6de96829cf2&amp;file=file.png" alt="" /></p>
<h2>二、 安装步骤</h2>
<h2>2.1. PostgreSQL 安装配置</h2>
<p>本章为 PostgreSQL 安装步骤，根据客户的实际应用场景进行了简化，请严格按照本章节要求进行安装，避免上线后的可能出现的异常情况。</p>
<h3>2.1.1. 安装 PG 软件</h3>
<p>双击下载的软件 exe 开始安装。
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=40bf0b1472c219d4e6b1f426530e7852&amp;file=file.png" alt="" />
根据 1.2.2 节，将软件安装到规划的目录下，并“Next”
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=a7df0545d1ed37522973af5a74da59a2&amp;file=file.png" alt="" />
勾选安装所有套件，并根据 1.3.2 节的规划指定 PG 数据库的数据目录。
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=e52f23c678dfa85f5a31cca7ee97270d&amp;file=file.png" alt="" />
输入密码，并选择监听端口为默认的 5432。特别注意，这里的 Locale 建议选择“C”以更
好地支持 Windows 中文等字符集并避免安装报错。
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=1e3068b228a691c0989e31118f213840&amp;file=file.png" alt="" />
再次确认配置信息，没问题最后 “Next”等安装结束即可完成软件安装，安装结束后，取
消勾选“Launch Stack Builder at exit?”选项，并“Finish”结束。</p>
<h3>2.1.2. 监听功能配置</h3>
<p>默认的，PG 是不允许其他机器访问数据库，我们需要对监听进行配置，使其他机器能够通过网络访问 PG 数据库。
打开 PG 数据文件路径，修改 pg_hba.conf 文件，在 IPv4 的位置，注销原有配置，并新增可访问的机器网段：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=0f21f1c3c98e8a4d5de2907481c182f1&amp;file=file.png" alt="" />
这里的 0.0.0.0/0 指所有机器都可以访问。
修改完毕后，需重新启动 PG 使配置生效：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=a7b8efa84430046917f528bc2ac760fa&amp;file=file.png" alt="" /></p>
<h3>2.1.3. UUID 扩展功能部署</h3>
<p>软件开发过程中，经常会用到 UUID 函数，生产一个唯一的 ID 值。默认情况下，PG 是未安装此功能的，需要手工安装，步骤如下。
点选“Windows”按钮（开始按钮），找到“SQL Shell(psql）”选项，打开 psql 窗口，如下图所示：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=101392192ef63bcd2a0b41f2989463cb&amp;file=file.png" alt="" />
Psql 即 PG 数据库的命令行窗口，类似 Oracle 的 sqlplus 界面。可以创建表、修改配置等常规操作。
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=8e5a805538cbf2c8c811c7bbee3cb08d&amp;file=file.png" alt="" />
查询 uuid_generate_v4()函数，能正常输出即表示扩展功能安装成功。</p>
<h3>2.1.4. PL/pgSQL debug 功能和监控功能开启</h3>
<p>软件开发过程和运维过程中，经常需要用到程序调试和性能监控。默认安装时，PG 并未开启这两项功能，我们需要通过以下配置开启这两项扩展功能。
找到$PGDATA 程序数据文件路径，同目录下的 postgres.conf 文件，在文件最后加入以下内容：</p>
<pre><code># Add settings for extensions here
shared_preload_libraries=&#039;plugin_debugger,pg_stat_statements&#039;
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on</code></pre>
<p>参照 2.1.3 节，打开 PG 命令行，在 DOS 窗口下连接到你要创建扩展的数据库，然后运行以下命令：</p>
<pre><code>create extension pldbgapi;
create extension pg_stat_statements;
create extension postgres_fdw;</code></pre>
<p>运行成功后，通过以下查询看到新出现 pldbgapi 和 pg_stat_statements 和 postgres_fdw 三项，表示插件安装成功</p>
<pre><code>SELECT * from pg_extension;</code></pre>
<p>最后新开一个 DOS 窗口，运行 services.msc 到 Windows 的“服务”下重启一次 PG 数据库服务即可。
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=e2a794589c954ce4999dcae5e212a920&amp;file=file.png" alt="" />
安装完这两个插件以后，在开发过程中，就可以进行函数的 debug 了（使用 Navicat、pgAdmin等工具，可以看到 Debug 按钮）；通过性能监控插件，还可以查看缓慢的 TOP SQL，如：
执行如下命令，查询单次调用最耗时 SQL TOP 10。</p>
<pre><code>select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 10;</code></pre>
<p>执行如下命令，查询总最耗时 SQL TOP 10。</p>
<pre><code>select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 10</code></pre>
<p>【IO 缓慢】
执行如下命令，查询单次调用最耗 IO SQL TOP 10。</p>
<pre><code>select userid::regrole, dbid, query from pg_stat_statements order by
(blk_read_time+blk_write_time)/calls desc limit 10;</code></pre>
<p>执行如下命令，查询总最耗 IO SQL TOP 10。</p>
<pre><code>select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)desc limit 10;</code></pre>
<h2>2.2. 项目数据迁入</h2>
<p>项目实施过程，经常需要进行数据迁移，将一台服务器上的数据导出并迁移到另一台服务器上。以下将通过案例演示从 Linux 迁移数据到某 Windows 服务器的过程。</p>
<h3>2.2.1. 导出项目数据</h3>
<p>通过 CRT 等工具登录 Linux 服务器后，运行导出命令：</p>
<pre><code>[root@a5c3a42da91e ~]# su – postgres
--切换到 PG 用户 postgres 用户下
[postgres@a5c3a42da91e ~]$
--导出数据命令如下：
nohup pg_dump -U n2admin -d n2db -p 5432 &gt;&quot;/home/postgres/n2admin.sql&quot; &amp;
注释：
1、灰色底色部分为导出命令，其中“nohup”和“&amp;”指后台运行，避免网络中断需要重新操作。
2、n2admin 指数据库用户名、n2db 指数据库名、5432 指数据库端口、/home/postgres/n2admin.sql 指导出文件的路径，请根据需要自行修改这几个值。</code></pre>
<p>导出完成后，查看该文件的后几行，能够看到“PostgreSQL database dump complete”字样，表示导出正常结束。压缩后适用 sftp 等工具传输到 Windows 中准备导入即可</p>
<pre><code>[postgres@a5c3a42da91e ~]$ tail /home/postgres/n2admin.sql
-- Name: TABLE dual; Type: ACL; Schema: n2admin; Owner: postgres
--
GRANT SELECT ON TABLE n2admin.dual TO PUBLIC;
--</code></pre>
<pre><code>-- PostgreSQL database dump complete
--
[postgres@a5c3a42da91e ~]$ zip –r /home/postgres/n2admin.sql.zip /home/postgres/n2admin.sql
为加快文件下载速度，先压缩成 zip 文件再下载到你的 Windows 上，请自己替换文件名</code></pre>
<h3>2.2.2. 创建表空间、数据库和用户</h3>
<p>本章节演示在目标 Windows 版 PG 数据库中创建一个表空间，并创建数据库和用户的过程。
点选“Windows”按钮（开始按钮），找到“SQL Shell(psql）”选项，打开 psql 窗口，如下图所示：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=4c44e8ca302cf53f2c2efb29122713ba&amp;file=file.png" alt="" />
Psql 即 PG 数据库的命令行窗口，依次执行命令即可，参考如下：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=b48bb82f1d37ad5359e96e4d6bfb16e5&amp;file=file.png" alt="" />
上图的命令脚本提取如下：</p>
<pre><code>postgres=# create tablespace tbs_n2db location &#039;D:\pgdata\pg_tblspc\&#039;;
#以上 D:\盘不够了可以用其他盘或路径
postgres=# create database n2db encoding=&#039;UTF8&#039; tablespace=tbs_n2db connection limit=-1;
#以上 tbs_n2db 来自上一步创建的表空间，limit=-1 表示不限制连接数
postgres=# CREATE USER n2admin WITH superuser ENCRYPTED PASSWORD &#039;N2ADMIN&#039;;
#创建用户名和密码，注意这里区分大小写
postgres=# GRANT ALL PRIVILEGES ON DATABASE n2db TO n2admin;
postgres=# \c n2db
#切换到新建的数据库
n2db=# create schema n2admin;
n2db=# grant all on schema n2admin to n2admin;
n2db=# alter database n2db set search_path to n2admin;
n2db=# create extension &quot;uuid-ossp&quot;;
n2db=# create extension pldbgapi;
n2db=# create extension pg_stat_statements;
#新建的数据库要单独创建 UUID 扩展、PL/pgSQL debug 扩展和性能监控扩展</code></pre>
<p>以上步骤要求都正确执行，如有错误提示，先进行调整后再继续。</p>
<h3>2.2.3. 导入项目数据</h3>
<p>在完成数据导出（2.3.1 节）并配置好目标导入数据库的环境（2.3.2 节）后，我们就可以在 DOS 窗口中进行项目数据的导入，具体步骤如下：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=22cbd51bd55543a6d5974e0bb008bf11&amp;file=file.png" alt="" />
如果导入失败，请按以下步骤处理，并解决问题后重新导入数据：
1、打开 psql
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=fefacd2d5cd30e060395c46d9174c00e&amp;file=file.png" alt="" />
2、删除原来的用户模式下的所有对象，并重建用户和授权
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=78467aafdc65f6bfd9936132d5f57966&amp;file=file.png" alt="" />
上图方框中为执行的命令，如命名不一样，请自行替换里面的数据库名、用户名等。</p>
<p>3、重新导入数据
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=1fef915315ee4e2fb6a62c2b8e53fcd0&amp;file=file.png" alt="" />
导入完成后，请检查程序和数据库的功能是否完整，有无乱码。</p>
<h3>2.2.4. 数据导出备份</h3>
<p>类似于 Linux 系统数据迁移方式（2.3.1 节的方式），在未安装任何插件的情况下，Windows下的 PG 数据库也可以用自带的 pg_dump 进行日常数据导出备份。我们可以将命令写成 bat 脚本，并通过计划任务调用，让系统自动备份数据。举例如下：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=e7a0d808e1bbc1601f4a7ef9b806474a&amp;file=file.png" alt="" />
pg_auto_backup.bat 内容参考：</p>
<pre><code>set week=%DATE:~-1%
C:\PostgreSQL\10\bin\pg_dump &quot;host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=n2admin
password=N2ADMIN dbname=n2db&quot; &gt; &quot;E:\pgbak\PG_n2db_n2admin_backup(%week%).sql&quot;</code></pre>
<p>该脚本运行时，会备份数据库，文件名括号中的中文数值表示星期几的备份，重复备份将被覆盖。请自行修改脚本内的安装程序路径、端口、用户名、密码、数据库、备份路径等信息。非特殊情况，请勿删除备份文件夹。</p>
<p>下面通过计划任务自动调用备份脚本，每天备份一次。可根据实际需求修改备份周期：
1、打开“任务计划程序”，选择“创建任务”
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=3864ef5b4b7ae1ae3c2d5c273a2ac809&amp;file=file.png" alt="" />
2、新建一个 PG_auto_backup_daily 的备份任务，在“触发器”选项卡中指定备份周期，比如每天凌晨 1：00 运行
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=f11dd4d00483a3cdd335cb7944fe617f&amp;file=file.png" alt="" />
3、在“操作”选项卡中指明 bat 脚本文件全路径，以及 bat 脚本所在文件夹。然后确定即可，如下图：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitFile?sign=0d56e626f798861927174b1bf977905e&amp;file=file.png" alt="" />
4、备份被触发后，检查是否有正常生成备份文件。</p>
<p>至此，WindowsServer2012R2 单机环境下部署 PG10 数据库，并迁移数据的完全部署过程结束！请做好操作记录总结。</p>
<h2>2.3.增量备份配置</h2>
<h3>2.3.1. 修改$PGDATA/postgres.conf</h3>
<pre><code>$ vi postgres.conf
…
#增加以下参数
wal_level=replica
archive_mode = on
archive_command = &#039;copy &quot;%p&quot; &quot;C:\\backup\\arch\\%f&quot;&#039; #路径为实际存放归档 wal 日志的路径，建议
归档 wal 日志放在备份目录</code></pre>
<p>配置完成后需要重启数据库</p>
<h3>2.3.2. 创建备份脚本</h3>
<pre><code>set today=%date:~0,4%%date:~5,2%%date:~8,2%
attrib -s -h -r -a /s /d &quot;c:\backup\bak\%today%&quot;
rd /s /q &quot;c:\backup\bak\%today%&quot;
set PGPASSWORD=abc@123
#postgres 的密码
&quot;C:\Program Files\PostgreSQL\10\bin\pg_basebackup.exe&quot; --host=localhost --port=5432 --userna
me=postgres -D c:\backup\bak\%today% -Ft -R -z -v --checkpoint=fast
forfiles /p &quot;C:\backup\arch&quot; /m *.backup /d -14 /c &quot;cmd /c &quot;C:\Progra~1\PostgreSQL\10\bin\pg_
archivecleanup.exe&quot; -d C:\backup\arch @file&quot; #删除 14 天前的归档 wal 日志
forfiles /p &quot;c:\backup\bak&quot; /d -14 /c &quot;cmd /c rd @path&quot;
#删除 14 天前的备份</code></pre>
<h3>2.3.3. 配置自动调度</h3>
<p>详情参考 2.2.4 的创建计划任务。</p>