十二、PostgreSQL 数据库安装
前言
PostgreSQL 是一个开源的关系型数据库，在过去 10 年里，它的社区活跃度迅速升温，现位于Oracle、MySQL、SQL Server三大数据库之后，属于第四大关系数据库 。随着MySQL纳入Oracle麾下，PostgreSQL未来有可能超越MySQL，根据 DB-Engines 年度数据库报告，PostgreSQL 已经连续三年获得“全球增长最快数据库”称号。
原计划介绍一下 PostgreSQL 数据库备份，因首先得有一个 PostgreSQL环境，于是记录下PostgreSQL数据库安装准备过程，有需要的读者可以参考。
本次环境：
PostgreSQL 13.2 on x86_64 Red Hat Enterprise Linux Server release 7.9 (Maipo)
1、设置镜像源
设置光盘镜像源，以获取相关环境编译依赖包。
[root@localhost /]# mkdir -p /mnt/cdrom
[root@localhost /]# mount /dev/cdrom /mnt/cdrom
mount: /dev/sr0 is write-protected, mounting read-only
[root@localhost /]# vi /etc/yum.repos.d/iso.repo
编辑repo 文件
[base-local]
name=iso
baseurl=file:///mnt/cdrom
enable=1
gpgcheck=0
测试repolist
[root@localhost /]# yum repolist
Loaded plugins: product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
2、查询&& 安装 gmake ,gcc
查询gmake
[root@localhost /]# gmake -v
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright (C) 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
查询gcc
[root@localhost /]# gcc -v
-bash: gcc: command not found
安装gcc
[root@localhost /]# yum install gcc
查询 readline， 用于pg命令行语句上下翻页，建议安装
[root@localhost /]# rpm -qa |grep readline*
readline-6.2-11.el7.x86_64
查询 zlib ， 用于备份数据压缩，建议安装
[root@localhost /]# rpm -qa |grep zlib*
zlib-1.2.7-18.el7.x86_64
[root@localhost /]# yum install readline*
=================================================================================================================================================================
Package Arch Version Repository Size
==================================================================================================================================================================
Installing:
readline-devel x86_64 6.2-11.el7 base-local 139 k
Installing for dependencies:
ncurses-devel x86_64 5.9-14.20130511.el7_4 base-local 713 k
[root@localhost /]# yum install zlib*
Dependencies Resolved
==================================================================================================================================================================
Package Arch Version Repository Size
==================================================================================================================================================================
Installing:
zlib-devel x86_64 1.2.7-18.el7 base-local 50 k
3、PostgreSQL源码安装
添加安装用户&&用户组
[root@localhost /]# groupadd postgres 增加postgres组
[root@localhost /]# cat /etc/group 查看postgres组
[root@localhost /]# useradd -g postgres postgres 增加postgres用户
[root@localhost /]# mkdir -p /data/postgres/13.2/ 创建程序目录
[root@localhost 13.2]# chown -R postgres:postgres /data 将目录赋权给postgres用户及组
[root@localhost 13.2]#su - postgres 切换至postgres用户
[postgres@localhost ~]$ curl -o https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz 下载postgres官方安装包，可自己根据需要去下载对应版本源码
[postgres@localhost ~]$ tar -zxvf postgresql-13.2.tar.gz 解压缩
[postgres@localhost postgresql-13.2]$ ./configure --prefix=/data/postgres/13.2/ 指定安装目录
[postgres@localhost postgresql-13.2]$ gmake world 生成二进制码
[postgres@localhost postgresql-13.2]$ gmake install-world 编译安装
4、初始化数据库
[postgres@localhost postgresql-13.2]$ /data/postgres/13.2/bin/initdb -D /data/postgres/13.2/data 指定数据库目录
Success. You can now start the database server using:
/data/postgres/13.2/bin/pg_ctl -D /data/postgres/13.2/data -l logfile start 系统提示按下述命令启动数据库
查询postgre进程，无postgres服务进程
[postgres@localhost postgresql-13.2]$ ps -ef |grep postgres
root 12060 1035 0 Apr11 ? 00:00:00 sshd: postgres [priv]
postgres 12064 12060 0 Apr11 ? 00:00:00 sshd: postgres@pts/0
postgres 12065 12064 0 Apr11 pts/0 00:00:00 -bash
postgres 13111 12065 0 00:05 pts/0 00:00:00 ps -ef
postgres 13112 12065 0 00:05 pts/0 00:00:00 grep --color=auto postgres
启动数据库 参数-l 打印运行日志至logfile
[postgres@localhost postgresql-13.2]$ /data/postgres/13.2/bin/pg_ctl -D /data/postgres/13.2/data -l logfile start
查询postgre进程，可以看到服务运行状态
[postgres@localhost postgresql-13.2]$ ps -ef |grep postgre
root 12060 1035 0 Apr11 ? 00:00:00 sshd: postgres [priv]
postgres 12064 12060 0 Apr11 ? 00:00:00 sshd: postgres@pts/0
postgres 12065 12064 0 Apr11 pts/0 00:00:00 -bash
postgres 13138 1 0 00:07 ? 00:00:00 /data/postgres/13.2/bin/postgres -D /data/postgres/13.2/data
postgres 13140 13138 0 00:07 ? 00:00:00 postgres: checkpointer
postgres 13141 13138 0 00:07 ? 00:00:00 postgres: background writer
postgres 13142 13138 0 00:07 ? 00:00:00 postgres: walwriter
postgres 13143 13138 0 00:07 ? 00:00:00 postgres: autovacuum launcher
postgres 13144 13138 0 00:07 ? 00:00:00 postgres: stats collector
postgres 13145 13138 0 00:07 ? 00:00:00 postgres: logical replication launcher
postgres 13146 12065 0 00:08 pts/0 00:00:00 ps -ef
postgres 13147 12065 0 00:08 pts/0 00:00:00 grep --color=auto postgre
5、配置环境变量
建议添加环境变量bin 与PGDATA，简化日常管理
[postgres@localhost postgresql-13.2]$ vi ~/.bash_profile 编辑用户的bash_profile文件# User specific environment and startup programs
添加bin与PGDATA
PATH="$PATH:$HOME/.local/bin:$HOME/bin:/data/postgres/13.2/bin"
PGDATA=/data/postgres/13.2/data
export PATH PGDATA
让环境变量生效
[postgres@localhost postgresql-13.2]$souce ~/.bash_profile
[postgres@localhost postgresql-13.2]$ psql 测试命令
psql (13.2)
Type "help" for help.
postgres=# \q 退出
6、修改数据库参数
为了数据库管理与与操作，还需设置下述两个配置参数文件
修改pg_hba.conf 主机连接模式为md5，允许所有IP以密码加密方式连接
[postgres@localhost postgresql-13.2]$ vi /data/postgres/13.2/data/pg_hba.conf
# IPv4 local connections:
#host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
修改postgresql.conf ，listen_addresses 设置IP为* ， 所有IP被listen on
[postgres@localhost postgresql-13.2]$ vi /data/postgres/13.2/data/postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
listen_addresses = '*' # what IP address(es) to listen on;
重启数据库生效
[postgres@localhost postgresql-13.2]$ pg_ctl restart -m fast
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-04-12 00:42:46.539 CST [13248] LOG: starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-04-12 00:42:46.542 CST [13248] LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-04-12 00:42:46.542 CST [13248] LOG: listening on IPv6 address "::", port 5432
2022-04-12 00:42:46.545 CST [13248] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-04-12 00:42:46.549 CST [13249] LOG: database system was shut down at 2022-04-12 00:42:46 CST
2022-04-12 00:42:46.551 CST [13248] LOG: database system is ready to accept connections
done
server started
[postgres@localhost postgresql-13.2]$ psql
psql (13.2)
Type "help" for help.
postgres=# show listen_addresses; 查询listen_addresses
listen_addresses
------------------
*
(1 row)
修改 postgres密码
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# exit
这样就可以通过工具与命令行远程连接，如果有数据库主机有防火墙注意关闭或设置准入策略
7、卸载
安装失败或是卸载后重新安装，可以通过下述命令卸载
[postgres@localhost postgresql-13.2]$ gmake distclean
8、数据库启停
数据库停止
[postgres@localhost ~]$ pg_ctl stop -m fast
数据库启动
[postgres@localhost ~]$ pg_ctl start -D /data/postgres/13.2/data/ -l ~/start.log
waiting for server to start.... done
server started
在环境变量配置了PGDADT， 则无须参数 -D指定位置
[postgres@localhost ~]$ pg_ctl status 查看运行状态
pg_ctl: server is running (PID: 13436)
/data/postgres/13.2/bin/postgres "-D" "/data/postgres/13.2/data"
远程连接 -h 主机或IP -p 端口， -d 数据库名 -U user
[postgres@localhost ~]$ psql -h 192.168.62.157 -p 5432 -d postgres -U postgres
Password for user postgres:
psql (13.2)
Type "help" for help.
9、数据库常见管理
[postgres@localhost ~]$ which pg_ctl 查看pg_ctl 位置
/data/postgres/13.2/bin/pg_ctl
postgres=# select version(); PostgreSQL版本查询
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7885 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7737 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7737 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(3 rows)
postgres=# select pg_postmaster_start_time();
pg_postmaster_start_time
-------------------------------
2022-04-12 03:21:25.903374+08
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
10、开归档
数据库归档，是数据库安全不可缺少的重要条件，数据库物理备份时，PostgresSQL 亦需要开启归档服务，开档需要启用下述3个以参数；
[postgres@localhost ~]$mkidr -p /data/archivelog 创建归档目录
[postgres@localhost ~]$ vi /data/postgres/13.2/data/postgresql.conf 编辑postgresql.conf文件
wal_level = replica 启用
archive_mode = on 启用
arhive_command= 'test ! -f /data/archivelog/%f && cp %p /data/archivelog/%f' 归档命令位置根据需要设定 ##linux
arhive_command = 'copy "%p" "c:\\server\\archiverdier\\%f"' ##windows
手工切换日志，看是否成功postgres=# show archive_mode; 查询归档开启
archive_mode
--------------------
onpostgres=# select pg_current_wal_lsn(); 查询lsn
pg_current_wal_lsn
--------------------
0/1657A60
(1 row)
postgres=# SELECT pg_switch_wal(); 手工切换
pg_switch_wal
---------------
0/1657A78
(1 row)
postgres=# select pg_current_wal_lsn(); 查询lsn
pg_current_wal_lsn
--------------------
0/2000060
(1 row)