PostgreSQL 使用小结

用了多年 MySQL，想尝试一下新鲜事物 PostgreSQL，过程中踩了不少坑，于是便有了这篇文章

安装

环境 Ubuntu-18.04

~~官方 apt 仓库安装，但由于服务器在国外速度过慢，弃置~~
从源码编译安装

官方文档 Chapter 17. Installation from Source Code，本文与其的一切差异以官方文档为准

在官方 ftp 找到需要的版本

1	wget https://ftp.postgresql.org/pub/source/version/postgresql-version.tar.gz

进行解压

1	tar xf postgresql-version.tar.gz

编译并安装

cd postgresql-version
./configure
make
su
make install
adduser postgres
mkdir -p /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

首先 postgres 将默认安装在 /usr/local/pgsql，并新建 postgres 用户

initdb -D location：在 location 创建一个数据库集群
pg_ctl -D location -l logfile start：启动 location 数据库集群服务，将日志写入到 logfile 中
createdb test：创建 test 数据库
psql test：使用命令行客户端工具访问 test 数据库

启动服务

安装完成后服务自动启动，关闭服务使用命令

1	pg_ctl stop -D location

pg_ctl 是 PostgreSQL 提供的一个管理脚本，在前面已经使用其成功启动了服务。可以使用命令 pg_ctl --help 查看其功能

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

Common options:
...

自启动

可以将其封装为 systemd，以 /etc/systemd/system/postgresql.service 为例

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
Wants=network-online.target
After=network.target network-online.target

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

在拥有 sudo 权限的用户下使用 sudo systemctl enable postgresql.service 实现自启

外部访问

PostgreSQL 默认配置下不可由外部访问，需要通过修改数据库集群的配置文件从而允许外部访问。配置文件均位于数据库集群路径下，如 /usr/local/pgsql/data/postgresql.conf, /usr/local/pgsql/data/pg_hba.conf

修改 postgresql.conf，配置 PostgreSQL 数据库服务器的相应的参数。

1	listen_addresses='*'

listen_addresses：制定 server 监听的地址，使用 ',' 分隔不同的地址

特殊值如下：

0.0.0.0：本地所有的 IPV4 地址
::：本地所有的 IPV6 地址
*：本地所有的地址

修改 `pg_hba.conf`，配置对数据库的访问权限

在最后增加一行

1	host all all 0.0.0.0/0 md5

分别对应

1	TYPE DATABASE USER CIDR-ADDRESS METHOD

表示新增一条外部链接类型规则（host），对于所有的数据库（all），所有合法的数据库用户名（all），所有 IPV4 地址（0.0.0.0/0），提供 md5 加密连接（md5）

由于之前 METHOD 写成了 trust，远程访问数据库不需要密码，也没改默认端口，导致被扫数据库还注入了挖矿病毒…

本文采用署名-非商业性使用-相同方式共享 4.0 国际许可协议，转载请注明出处。