《PostgreSQL修炼之道：从小工到专家》 本文基本是根据该书安装与配置的
链接：https://pan.baidu.com/s/1JBqgOtCfD4HIxVjNMSs0Hw?pwd=vs3m
提取码：vs3m

一、PostgreSQL安装基本介绍

1.1 几种PostgreSQL的安装方式

1. Windows环境下的安装是比较简单的，只需要运行图形界面，在安装的过程中基本上只需要点选“Next”项就可以完成安装。
2. Linux环境下PostgreSQL的安装方式则是使用相应发行版本的包管理器来进行。
3. Docker 安装更是简便，一行命令即可：mkdir -pv /data/postgres && docker run -it --name postgres --restart always -e POSTGRES_PASSWORD='postgresPW' -e ALLOW_IP_RANGE=0.0.0.0/0 -v /data/postgres:/var/lib/postgresql -p 5432:5432 -d postgres 。
4. 从发行版本安装的PostgreSQL一般不是最新版本，如果想安装最新版本的PostgreSQL，需要使用下面介绍的源码安装方法。

1.2 删除原有的PostgreSQL

# （1）查看原有PostgreSQL的状态
systemctl list-units | grep postgresql
service postgresql-<version> status
# （2）将原有的PostgreSQL服务停止
service postgresql-<version> stop
# （3）卸载所有的PostgreSQL（注意输入“y”即可）
yum remove postgresql*
# （4）验证PosgreSQL是否被删除
rpm -qa | grep postgresql

1.3 编译安装过程简介

从源码安装的编译安装过程如下。

1. 第一步，下载源代码。
2. 第二步，编译安装，过程与Linux下其他软件的编译安装过程相同，都是“三板斧”：
   • ./configure。
   • make。
   • make install。
3. 第三步，编译安装完成后执行如下步骤：
   • 使用initdb命令初始化数据库簇。
   • 启动数据库实例。
   • 创建一个应用使用的数据库。

数据库簇是数据库实例管理的系统文件及各个数据库文件的集合

二、源码编译安装方式详情

2.1 下载源代码

打开PostgreSQL官方网站的源代码下载页面 https://www.postgresql.org/download/，在下载页面中单击左侧的“Source”，进入源代码下载页面。

在源代码版本选择页面中选择合适的版本，比如“v16.2”。

在页面中选择合适的压缩包进行下载，一般选择bz2压缩包，因为这种格式的压缩包体积较小。

2.2 编译安装

运行 configure

将前面下载的压缩包解压

tar xvf postgresql-16.2.tar.bz2
cd ./postgresql-16.2

编译安装的“第一板斧”是运行“configure”，对于PostgreSQL9.X及之后的版本，一般编译安装的命令如下：

./configure --prefix=/usr/local/pgsql16.2 --with-perl --with-python

但是对于PostgreSQL8.X的老版本，需要在configure的命令上加“–enable-thread-safety”，如下：

./configure --prefix=/usr/local/pgsql8.4.17 --enable-thread-safety --with-perl --with-python

加这个选项的原因在于，在日常使用中，一般要求客户端是线程安全的，PostgreSQL9.X版本之后考虑到这个问题，默认改成线程安全的了。而PostgreSQL8.X没有做成这样，所以要加上这个选项。

选项	描述
–prefix=prefix	安装到prefix指向的目录；默认为 /usr/local/pgsql
–bindir=dir	安装应用程序到dir；默认为prefix/bin
–with-docdir=dir	安装文档到dir；默认为prefix/doc
-with-pgport=port	设置默认的服务器端网络链接勿复TCP端口号
–with-tcl	为服务端提供Tcl存储过程支持
–with-perl	为服务端提供Perl存储过程支持
–with-python	为服务端提供Python存储过程支持
–enable-debug	启用编译时的调试标志，生成包含调试信息的二进制文件，方便调试
–enable-cassert	启用编译时的断言检查，有助于检测运行时错误
–with-openssl	使用OpenSSL库为PostgreSQL提供加密支持，包括SSL连接等功能
–with-libxml	为PostgreSQL提供XML支持，包括XML解析和函数
–with-uuid=e2fs	使用e2fsprogs的UUID库（或其他库，如ossp-uuid）
–with-zlib	使用zlib库进行数据压缩支持
–with-ldap	添加LDAP身份验证支持
–with-ossp-uuid	使用OSSP UUID库
–with-systemd	配置PostgreSQL支持systemd服务管理器
–with-krb5	添加Kerberos身份验证支持
–with-geos	为PostGIS地理信息系统扩展提供GEOS库支持
–with-bonjour	添加Bonjour服务发现支持
–without-readline	不使用Readline库支持交互式终端输入编辑功能
–with-includes=dir	指定额外的头文件搜索路径
–with-libraries=dir	指定额外的库文件搜索路径
–datadir=dir	数据文件目录，默认为prefix/share/postgresql
–sysconfdir=dir	配置文件目录，默认为prefix/etc/postgresql
–sharedir=dir	共享文件目录，默认为prefix/share/postgresql
–docdir=dir	文档目录，不同于–with-docdir，用于HTML、man手册等文档
–enable-nls	启用本地化支持，提供多语言翻译
–disable-rpath	禁止在可执行文件中嵌入库路径
–enable-thread-safety	启用线程安全构建（默认开启）
–with-extra-version=version	在PostgreSQL版本信息中添加附加版本字符串

这些选项可以根据实际需求配置PostgreSQL的功能和安装路径，但并非所有选项都在所有平台上都适用，具体的选项和支持情况需参照PostgreSQL官方文档或实际运行./configure --help命令获取最新的和完整的配置选项列表。

configure: error: ICU library not found

# 查看是否安装此库。
icu-config --version
# 安装（输入“y”即可）。
sudo yum install libicu-devel

configure: error: readline library not found

# 查看是否安装此库。
rpm -qa | grep readline
# 安装
yum -y install -y readline-devel

如何使用较大的数据块提高I/O性能
在数据仓库中使用PostgreSQL时，如果希望使用较大的数据块以提高I/O性能怎么办？对于这类问题，只能使用从源码安装的方法，在执行“./configure”命令时指定较大的数据块，一般也需要指定较大的WAL日志块和WAL日志文件大小，如指定32KB的数据块、32KB的WAL日志块、64MB的WAL日志文件的configure命令如下：

./configure --prefix=/usr/local/pgsql16.2 --with-perl --with-python --with-blocksize=32 --with-wal-blocksize=32 --with-wal-segsize=64

对于此时编译出来的PostgreSQL程序创建的PostgreSQL数据库，不能使用其他块大小的PostgreSQL程序启动。

执行 make

编译安装的“第二板斧”是make命令，该命令比较简单，直接运行即可

make

按官方文档要求，使用make命令时，其版本要在gmake v3.8以上，目前绝大多数Linux发行版本都满足要求，所以一般在Linux环境下不需要检测make版本，但如果是在其他非Linux的UNIX平台上，建议先检测make的版本，检测命令如下：

osdba@osdba-laptop:~$ make --version
GNU Make 3.81
Copyright (C) 2006 Free Software Foundation, Inc.

在其他UNIX平台上，可能存在非GNU的make，此时GNU的make的名称会是“gmake”。

执行 make install

编译安装的“第三板斧”是运行make install命令，如果是在一般用户下进行编译，可能对“/usr/local”目录没有写的权限，所以运行make install命令时需要使用root权限，在Debian或Ubuntu下可以使用sudo命令：

sudo make install

前面我们看到–prefix设置的路径为“/usr/local/pgsql16.2”，如果不进行设置，默认的路径将是“/usr/local”，为什么要在此路径上加上PostgreSQL的版本号呢？这是为了方便升级。make install命令运行完成后，还要进入“/usr/local”目录，为“/usr/local/pgsql16.2”建立一个/usr/local/pgsql链接：

cd /usr/local
sudo ln -sf /usr/local/pgsql16.2 /usr/local/pgsql

如果我们要升级到PostgreSQL16.3，在编译PostgreSQL16.3后，只需停掉现有的数据库，然后将链接“/usr/local/pgsql”指向新版本的目录“/usr/local/pgsql16.3”即可完成升级。这样是不是很方便呢？

2.3 PostgreSQL的配置

PostgreSQL安装完成后，需要设置可执行文件的路径：

export PATH=/usr/local/pgsql/bin:$PATH

然后设置共享库的路径：

export LD_LIBRARY_PATH=/usr/local/pgsql/lib

如果想让以上配置对所有的用户生效，可以把以上内容添加到/etc/profile文件中，/etc/profile中的内容类似如下内容：

...
...
...
if [ -d /etc/profile.d ]; then
	for i in /etc/profile.d/*.sh; do
		if [ -r $i ]; then
			. $i
		fi
	done
	unset i
fi

export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

如果想让以上配置对当前用户生效，在Linux下可以把以上内容添加到.bashrc文件中，在其他UNIX下可以加到.profile文件中。

在Linux下为何不添加到.profile文件或.bash_profile文件中？这是因为，在图形界面下打开一个终端，有时.profile或.bash_profile不会生效，而.bashrc会生效。

2.4 安装contrib目录下的工具

contrib下有一些工具比较实用，一般用户都会安装这些工具，其安装的方法也与Linux下的编译过程相同，安装命令如下：

cd postgresql-16.2/contrib
make
sudo make install

三、初始化数据库

3.1 创建数据库管理员

# 使用useradd命令创建用户（pg默认是postgres，但若还有其他类型数据库，个人为了方便管理统一用osdba）
sudo useradd osdba
#设置密码
sudo passwd osdba
# 系统将提示你输入密码，输入后按Enter键确认，再次输入以确认密码

# 或者使用adduser命令创建用户（有些系统可能需要root权限或sudo）
sudo adduser osdba
# 接下来adduser会提示你输入密码、用户信息等，根据提示操作即可

# 若要赋予该用户sudo权限（可选，取决于你希望osdba用户是否具有管理员权限）
sudo usermod -aG wheel osdba  # 对于使用wheel组的系统
sudo usermod -aG sudo osdba   # 对于使用sudo组的系统

# 确认用户和组信息已生效
id osdba

3.2 创建数据库实例

添加并修改目录权限

mkdir -p /home/osdba/pgdata
chown -R osdba:osdba /home/osdba/
chmod -R 700 /home/osdba/
# 将目录权限设置为700，这意味着仅属主（这里是postgres用户）有权读取、写入和执行目录内的内容，其他用户无权访问，这是出于数据库安全性的考虑。

首先设定数据库的数据目录的环境变量：

echo 'export PGDATA=/home/osdba/pgdata' >> /etc/profile
source /etc/profile

然后执行下面的命令创建数据库簇：

su osdba
initdb

至此，数据库实例的创建就完成了。

3.3 启动和停止数据库

启动数据库的命令如下：

pg_ctl start -D $PGDATA

其中，环境变量“PGDATA”指向具体的PostgreSQL数据库的数据目
录，示例如下：

osdba@osdba-laptop:~$ pg_ctl start -D /home/osdba/pgdata
server starting

停止数据库的命令如下：

pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]

其中 -m 用于指定数据库的停止方法，有以下3种模式：

• smart：等所有连接中止后，关闭数据库。如果客户端连接不终
  止，则无法关闭数据库。
• fast：快速关闭数据库，断开客户端的连接，让已有的事务回滚，
  然后正常关闭数据库。相当于Oracle数据库关闭时的immediate模式。
• immediate：立即关闭数据库，相当于数据库进程立即停止，直接
  退出，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的
  abort模式。

PostgreSQL数据库中的immediate关机模式相当于Oracle数据库中的abort关机模式，而Oracle中的immediate关机模式实际上对应的是PostgreSQL中的fast模式，对于从Oracle数据库中转过来的DBA尤其需要注意这一点。

较常用的关闭数据库的方法是fast模式，因为如果采用smart模式，有用户连接到数据库时，系统会一直等待，而无法关闭数据库。PostgreSQL9.5之前的版本默认是smart模式，通常要使用命令“pg_ctl stop -m fast”来关闭数据库，在PostgreSQL9.5以上的版本中可以直接用“pg_ctl stop”命令来关闭数据库。

打开数据块的checksum功能

对于一些数据可靠性要求很高的场景，如一些金融领域，建议打开数据块的checksum校验功能。而在PostgreSQL12版本之前，需要在用initdb命令创建数据库时就把这个功能加上：

initdb -k

initdb命令中增加了“-k”参数，所创建的数据库的数据块就有了checksum功能。在PostgreSQL12版本之后提供了工具pg_checksums，可以把一个没有checksum功能的数据库转换为具有该功能的数据库。运行这个工具需要先把数据库停掉，否则会报如下错误：

pg_checksums: error: cluster must be shut down

用“pg_checksums -c”检查当前数据库是否打开了checksum功能（注意数据库要在关闭状态）：

[postgres@pg01 ~]$ pg_checksums -c
pg_checksums: error: data checksums are not enabled in cluster

上面的提示是指没有打开checksum功能，则用下面的命令把数据库转换成具有checksum功能的数据库：

bash-4.4$ pg_checksums -e -P
22/22 MB (100%) computed
Checksum operation completed
Files scanned:   951
Blocks scanned:  2828
Files written:  782
Blocks written: 2828
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster

在上面的命令中，“-P”参数是为了显示进度。

如果数据库比较大，使用pg_checksums把数据库转换成具有checksum功能的数据库需要比较长的时间，所以做这个操作要求有比较长的数据库停机时间。

3.4 设置数据库密码

PostgreSQL的默认用户名通常是postgres（我这里用的是osdba），但并没有默认密码。在首次安装PostgreSQL时，系统不会自动生成一个默认密码，而是需要你在安装后手动设置。

在很多Linux发行版中，安装完成后，作为超级用户（如root）可以通过以下步骤为osdba用户设置密码：

sudo -u osdba psql -d postgres
# 进入psql命令行后，可以执行以下命令设置密码
ALTER USER osdba WITH PASSWORD 'your_password';
\q

在设置完密码后，强烈建议对默认用户进行如下操作：

1. 更改默认用户的密码，以增加安全性。
2. 根据实际需要创建新的数据库用户，并赋予合适的权限，而非直接使用postgres用户进行日常操作。
3. 对数据库实例采取必要的安全措施，比如禁用不需要的协议，启用SSL连接，以及合理配置防火墙规则，限制对PostgreSQL服务的访问仅限于授权的IP地址或网络。

总之，在安装PostgreSQL并完成初步配置后，确实应当更改默认用户的密码，并根据实际情况进行更细致的安全配置。

以下是一些 psql 常见的命令行参数及其描述：

命令（参数）	描述
-h HOSTNAME 或 --host=HOSTNAME	指定要连接到的数据库服务器主机名或IP地址。默认情况下，psql 会尝试连接到本地主机。
-p PORT 或 --port=PORT	指定数据库服务器监听的端口号，默认为 5432。
-U USERNAME 或 --username=USERNAME	指定要使用的数据库用户名。默认情况下，psql 会尝试使用当前操作系统的用户名。
-d DATABASE 或 --dbname=DATABASE	指定要连接的数据库名称。如果不指定，将连接到用户的默认数据库。
-w 或 --no-password	不提示输入密码。如果服务器需要密码认证且环境变量 PGPASSWORD 未设置，则连接尝试可能会失败。
-W 或 --password	强制 psql 提示输入密码，无论服务器是否需要密码认证。
-c COMMAND 或 --command=COMMAND	执行单条SQL命令后退出。例如：psql -c "SELECT * FROM mytable;"
-f FILE 或 --file=FILE	执行指定文件中的SQL命令集后退出。
-l 或 --list	列出服务器上所有可用的数据库。
-v VARNAME=value 或 --set=VARNAME=value	设置一个变量，可以在SQL脚本中引用。
\? 或 \\?	在 psql 交互模式中查看帮助，列出元命令列表。
-E 或 --echo-queries	显示发送到服务器的SQL命令。
-a 或 --single-transaction	在执行SQL脚本时，以单个事务的方式执行所有命令。

请注意，\? 和 -E 是在 psql 交互式会话中使用的元命令，而不是命令行参数。不过，这里为了完整性也一并列出。在实际使用中，可以根据需要组合使用这些参数。

四、PostgreSQL的简单配置

PostgreSQL数据库的配置主要是通过修改数据目录下的 postgresql.conf和pg_hba.conf 文件来实现的。

cd $PGDATA 

4.1 pg_hba.conf 的配置

默认创建的数据库无法接受远程连接，因为默认情况下pg_hba.conf中没有相应的配置项。我们可以在pg_hba.conf文件中加入以下命令行：

#该命令允许任何用户远程连接本数据库，连接时需要提供密码。
host    all     all             0/0                 md5

pg_hba.conf文件是一个黑白名单的访问控制文件，可以控制允许哪些IP地址的机器访问数据库。

4.2 postgresql.conf 的配置

修改监听的IP和端口

在数据目录下编辑 postgresql.conf 文件，找到如下内容：

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
#port = 5432                            # (change requires restart)

其中，参数“listen_addresses”表示监听的IP地址，默认是在“localhost”处监听，也就是在IP地址“127.0.0.1”上监听，这会造成远程主机无法登录该数据库，如果想从其他机器上登录该数据库，需要把监听地址改成实际网络的地址，一种简单的方法是把地址改成“*”，表示在本地的所有地址上监听，命令如下：

listen_addresses = '*'         # what IP address(es) to listen on;
#port = 5432                            # (change requires restart)

参数“port”表示监听的数据库端口，默认为“5432”，可以使用默认设置。如果一台机器上安装了多个数据库实例（如安装了多个不同版本的PostgreSQL），可以设置为不同的端口。
对于这两个参数的修改，需要重启数据库才能生效。

数据库日志相关参数

还是在 postgresql.conf ，下面来看看与日志相关的几个参数：
日志的收集一般是需要打开的，所以需要进行如下设置：

logging_collector = on

注意，在新版本的数据库中，以上参数默认已打开，如PostgreSQL10版本。
日志的目录一般使用默认值即可（旧版是 pg_log）：

log_directory = 'log'                   # directory where log files are written,
                                        # can be absolute or relative to PGDATA

日志切换和是否覆盖一般可以使用如下几种不同的方案。

方案一：每天生成一个新的日志文件

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0

方案二：每当日志写满一定的大小（如10MB），则切换一个日志

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 0
log_rotation_size = 10M

方案三：只保留最近7天的日志，进行循环覆盖

log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

PostgreSQL10以上版本默认的日志方案是方案三，不需要再进行修改

log_filename 中的 % 符号及其后面的字母组合是用来格式化日志文件名中的日期和时间部分的。下面是常用的时间日期格式符号以及它们对应的含义：

符号	含义
%Y	四位数的年份
%m	两位数的月份（01-12）
%d	两位数的日期（01-31）
%H	两位数的小时（00-23）
%M	两位数的分钟（00-59）
%S	两位数的秒（00-59）
%a	缩写的星期几名字（Sun-Sat）

内存参数的设置

PostgreSQL安装完毕后，可以修改以下主要内存参数。

• shared_buffers：共享内存的大小，主要用于共享数据块。
• work_mem：单个SQL执行时，以及排序、Hash Join时使用的内存，SQL运行完毕后，该内存就会被释放。
• shared_buffers的默认值为32MB，如果你的机器上有足够的内存，可以把这个参数设置得大一些，如可以设置为物理内存大小的四分之一，这样数据库就可以缓存更多数据块，当读取数据时，就可以从共享内存中进行读取，而不需要再从文件上去读。而work_mem设置大一些，会使排序操作效率更高。

五、客户端连接数据库

更改完以上配置后，重启数据库

su osdba
pg_ctl restart
pg_ctl status

DBeaber 客户端链接成功