Linux下PostgreSQL数据库安装、配置与日常服务管理

共计 5796 个字符，预计需要花费 15 分钟才能阅读完成。

安装环境准备
- 操作系统：Oracle Linux Server 6.5
- IP 地址：192.168.6.177
- 版本信息：MySQL Community Server 5.7.18
- 安装方式：RPM 包
下载安装包
- 访问 PostgreSQL 网站
  
  PostgreSQL 网站
- 根据操作系统类型和版本下载相应的安装包
  需要下载以下的安装包：
  - postgresql96-9.6.3-4PGDG.rhel6.x86_64.rpm
  - postgresql96-contrib-9.6.3-4PGDG.rhel6.x86_64.rpm
  - postgresql96-libs-9.6.3-4PGDG.rhel6.x86_64.rpm
  - postgresql96-server-9.6.3-4PGDG.rhel6.x86_64.rpm

安装 PostgreSQL

将 Postgres 安装包放置在同一个文件夹中，执行安装命令

[root@DB-Server-Node1 ~]# rpm -ivh postgresql96-*.rpm
Preparing...                ########################################### [100%]
1:postgresql96-libs      ########################################### [25%]
2:postgresql96           ########################################### [50%]
3:postgresql96-contrib   ########################################### [75%]
4:postgresql96-server    ########################################### [100%]

安装文件夹说明

通过 RPM 包安装，Postgres 安装程序会在 /var 和 /usr 下创建文件夹。

/var/lib/pgsql: 用于存放 Postgres 数据库默认的数据文件夹
/usr/pgsql-${version}: 用于存放 Postgres 数据库的命令、依赖库及文档目录等信息

将 Postgres 命令加入系统路径

修改 /etc/profile 文件，将 Postgres 命令加入系统路径，并测试

[root@DB-Server-Node1 ~]# vi /etc/profile

... ...
# 在文件最后加入以下语句
export PATH=$PATH:/usr/pgsql-9.6/bin

# 保存退出后，执行 source 命令，让配置即时生效
[root@DB-Server-Node1 ~]#  source /etc/profile

# 测试 Postgres 命令
[root@DB-Server-Node1 share]# postgres --version
postgres (PostgreSQL) 9.6.3

初始化数据库

使用 Postgres 数据库默认的数据文件存储目录

CentOS 6/RedHat 6/Oracle Linux 6

[root@DB-Server-Node1 bin]#  service postgresql-9.6 initdb
或者
[root@DB-Server-Node1 bin]# /etc/init.d/postgresql-9.6 initdb

CentOS 7/Redhat 7/Oracle Linux 7

[root@DB-Server-Node1 bin]# /usr/pgsql-9.6/bin/postgresql96-setup initdb

使用自定义的数据存储目录

# 创建数据存储目录
mkdir -p /data/postgres

# 修改目录权限 
chown -R postgres:postgres /data/postgres

# 初始化数据库
su -l postgres -c "/usr/pgsql-9.6/bin/initdb --no-locale -U postgres -E utf8 -D /data/postgres -W"

添加 Postgres 环境变量（可选）

[root@DB-Server-Node1 ~]# vi /etc/profile
 ... ... 
# 添加 Postgres 环境变量  
PGDATA=/data/postgres
PGHOST=127.0.0.1
PGDATABASE=postgres
PGUSER=postgres
PGPORT=5432
export PGDATA PGHOST PGDATABASE PGUSER PGPORT

修改 /etc/init.d/postgresql-9.6 中的变量(使用自定义数据目录)

如果使用自定义数据存放目录，而不是使用 PostgreSQL 默认数据目录时，需要修改启动文件中的 PGDATA 变量，将 PGDATA 目录指向自定义的数据目录，这样在使用 service posgresql-9.6 start 命令时才能正确启动。
```
[root@DB-Server-Node1 ~]# vi /etc/init.d/postgres-9.6
# 修改默认文件目录到指定的数据目录
PGDATA=/data/postgres

# 保存后退出
:wq!
```

配置数据库访问
- 修改 postgres.conf 文件
  主要配置参数详解：
  - listen_addresses=’localhost’
    Postgresql 监听的网卡 ip，默认仅仅本地，可以配置多个，使用“,”分割。“*”代表所有的网卡 ip
  - port=5432
    Postgres 服务端口
  - max_connections=100
    最大服务器连接数
  - superuser_reserved_connections=3
    为管理员保留的专用连接数，普通用户无法使用这些连接，不能大于 max_connections
  - authentication_timeout=60s
    登录验证超时时间设置
  - ssl=false
    是否使用 SSL 进行连接
  - password_encryption=true
    当使用 create user、alter user 管理用户时，如果没有显示进行加密与否的限定，postgresql 服务器是否自动进行密码加密
  - shared_buffers=32m
    共享缓存，非常重要的性能参数，其最小值为（128k,16k*max_connections）
  - max_prepared_transactions=5
    最大并行 prepared 事务，如果为 0，则禁止使用 prepared 事务，最大值与 max_connections 相同
  - temp_buffers=8m
    每个会话可以使用的临时（表）缓存大小
  - work_mem=1m
    指定内部排序、连接、group 等等时，postgresql 可以使用的内存大小，超过该值，将使用磁盘临时文件; 实际使用的内存和该类操作同时执行的数目相乘
  - maintenance_work_men=16m
    维护语句 vacuum、create index 等等可以使用的内存大小; 实际使用的内存和该类操作同时执行的数目相乘
  - fsync=on
    （物理数据）日志必须同步写入磁盘
    可能导致严重的性能损失，却能确保最高的日志数据安全。
  - synchronous_commit=on
    （逻辑事务数据）日志必须同步写入磁盘，如果设为 on，会立即调用 fsync，相当于设置了 fsync=on
  - full_page_writes=on
    写整页
  - wal_buffers=64K
    WAL 日志缓存大小
  - wal_writer_delay=200ms
    将 wal 日志从 wal_buffer 中写入磁盘的时间周期
  - commit_delay=0ms
    事务日志 commit 后，写入磁盘的延时。这个设置只有在 commit_sibings（并行的多个排队事务）在延时内存在是才有效
  - commit_siblings=5
    并行事务数
- 修改 pg_hba.conf
```
 [root@DB-Server-Node1 ~]# vi pg_hba.conf
 ... ...
 # "local" is for Unix domain socket connections only
 local   all             all                                     trust
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            trust
 host    all             all             192.168.6.0/24          trust
 # IPv6 local connections:
 host    all             all             ::1/128                 trust
```
  配置说明：
  - 格式：TYPE DATABASE USER ADDRESS METHOD
  - 参数：
    1. TYPE：值为 local 和 host, Local 值表示为主机 Socket 连接，host 代表允许的主机地址连接
    2. DATABASE: 允许访问的数据库名，all 代表允许全部数据库
    3. USER: 表示允许哪个用户访问数据库，all 代表所有用户都可以访问
    4. ADDRESS: 表示允许连接的主机信息，可以使用主机 IP 地址，也可以使用网段来表示，如 192.168.1.0/24 表示 192.168.1.0 网段可以连接
    5. METHOD：连接方法，通常使用的值为 md5 和 trust
启动数据库并测试数据库
- 启动 Postgres 数据库
```
[root@DB-Server-Node1 init.d]# service postgresql-9.6 start
Starting postgresql-9.6 service:                               [OK]
```
- 测试数据库
  
  使用 psql 命令进行测试。
  psql 命令详解：
  - -h host, 指定连接的 Postgres 数据库 IP 地址
  - -U username: 指定连接数据库的用户名
  - -d database: 指定连接的数据库名
  - -p port: 指定数据库连接的服务端口
  - -w: 表示不提示用户输入密码
  - -W : 表示验证数据库用户密码
  - -l : 表示列出 Postgres 可用的数据库信息
  示例如下：
```
[root@DB-Server-Node1 9.6]# psql -h localhost -U postgres -d postgres -W
Password for user postgres: 
psql (9.6.3)
Type "help" for help.

postgres=#
```
- 修改用户密码
  
  使用默认脚本 service postgresql initdb 或/usr/pgsql-9.6/bin/postgresql96-setup initdb初始化数据库时，需要修改用户的密码。在修改 Postgres 用户密码时，需要确保以下条件：
  
  配置 ”pg_hba.conf” 时需要确定服务器本地址的验证方法为 trust, 即:
  host all all 127.0.0.1/32 trust
  
  如果为 peer|indent|md5 方法时，需要将其修改为 trust, 否则登录时会提醒输入用户密码。切换系统用户为 postgres, 通过 psql -h localhost -U postgres -d postgres 登录 Postgres 数据库，进入数据后，使用 alter user postgres with password '设置的新密码' 来为用户修改默认密码。
  
  示例如下：
```
[root@DB-Server-Node1 9.6]#  su - postgres
-bash-4.2$ psql -h localhost -U postgres -d postgres -w
Password for user postgres: 
psql (9.6.3)
Type "help" for help.

postgres=#  alter user postgres with password '123456';
ALTER ROLE
postgres=#
```
- 设置服务开机自启动
```
chkconfig postgresql-9.6 on
```