vlambda博客
学习文章列表

十二、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
--------------------
on

postgres=# 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)
PostgreSQL数据库准备完毕, 后续将介绍用鼎甲迪备对PostgreSQL备份恢复过程。