一、yum安装&配置PostgreSQL 12

目录

• 一、yum安装&配置PostgreSQL 12
  • 一、前言
    • 1、本文主要内容
    • 2、本文环境信息与适用范围
  • 二、PostgreSQL安装
    • 1、导入yum源
    • 1、安装PostgreSQL服务
    • 2、初始化数据库
    • 3、启动PostgreSQL服务
  • 二、修改postgres账号密码
    • 1、进入PostgreSQL命令行
    • 2、启动SQL Shell
    • 3、修改密码
  • 三、配置远程访问
    • 1、开放端口
    • 2、修改IP绑定
    • 3、允许所有IP访问
    • 4、重启PostgreSQL服务
  • 四、PostgreSQL shell常用语法示例
    • 1、数据库相关语法示例
    • 2、用户与访问授权语法示例
  • 五、备注
    • 1、相关阅读
  • 六、数据的备份与还原
    • 1.备份脚本
    • 2.数据还原
• 二、二进制安装psql
  • 一、pg数据库安装包下载
  • 二、安装依赖包
  • 三、安装postgres
    • 1.在根目录下新建pgsql文件夹，并将pgsql的压缩包放入
    • 2.解压压缩包
    • 3.进入解压后的文件夹
  • 四、创建用户组postgres并创建用户postgres
  • 五、创建postgresql数据库的数据主目录并修改文件所有者
  • 六、配置环境变量
  • 七、切换用户到postgres并使用initdb初始化数据库
  • 八、配置服务
  • 九、设置PostgreSQL开机自启动
    • 5.开放5432端口。
    • 6.执行service postgresql start,启动Postgresql服务
  • 十一、开始测试
• postgres流复制部署

1、本文主要内容

• PostgreSQL 12 安装(yum)
• PostgreSQL 12 基础配置
• PostgreSQL 12 远程访问配置
• PostgreSQL 基础管理

2、本文环境信息与适用范围

• 环境信息

软件

版本

CentOS

7.6 Release

PostgreSQL

12.x

• 适用范围

软件

版本

CentOS

CentOS 7.x

PostgreSQL

9.x-12.x

1、导入yum源

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

1、安装PostgreSQL服务

sudo yum install -y postgresql12 postgresql12-server

安装PostgreSQL 11就是 yum install postgresql12 postgresql12-server

安装PostgreSQL 9.5就是 yum install postgresql95 postgresql95-server

依此类推

2、初始化数据库

sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

#Initializing database ... OK

3、启动PostgreSQL服务

#启动PostgreSQL服务
sudo systemctl start postgresql-12

#设置PostgreSQL服务为开机启动
sudo systemctl enable postgresql-12

9.x版本的服务名是postgresql-9.x

PostgreSQL安装成功之后，会默认创建一个名为postgres的Linux用户，初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，相当于MySQL中默认的名为mysql数据库。

postgres数据库中会初始化一名超级用户postgres

为了方便我们使用postgres账号进行管理，我们可以修改该账号的密码

1、进入PostgreSQL命令行

通过su命令切换linux用户为postgres会自动进入命令行

su postgres

2、启动SQL Shell

psql

3、修改密码

ALTER USER postgres WITH PASSWORD 'NewPassword';

1、开放端口

sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload

2、修改IP绑定

#修改配置文件
vi /var/lib/pgsql/12/data/postgresql.conf

#将监听地址修改为*
#默认listen_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'

3、允许所有IP访问

#修改配置文件
vi /var/lib/pgsql/12/data/pg_hba.conf

#在问价尾部加入
host  all  all 0.0.0.0/0 md5

4、重启PostgreSQL服务

#重启PostgreSQL服务
sudo systemctl restart postgresql-12

配置完成后即可使用客户端进行连接

启动SQL shell：

su postgres
psql

1、数据库相关语法示例

#创建数据库
CREATE DATABASE mydb;

#查看所有数据库
\l

#切换当前数据库
\c mydb

#创建表
CREATE TABLE test(id int,body varchar(100));

#查看当前数据库下所有表
\d

2、用户与访问授权语法示例

postgres=# ALTER DATABASE model OWNER TO model ;
ALTER DATABASE
postgres=# \c model
You are now connected to database "model" as user "postgres".

#新建用户
CREATE USER test WITH PASSWORD 'test';

#赋予指定账户指定数据库所有权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO test;

#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb TO test

权限代码：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE

1、相关阅读

• https://www.postgresql.org/download/
• https://www.runoob.com/postgresql/postgresql-privileges.html

---

1.备份脚本

1.备份脚本
#!/bin/bash -

HOST=172.30.3.11
PORT=5432
USER=esg
DATABASE=esg

dir_name=$(dirname $0) #获取当前脚本的目录路径，并将其赋值给变量 dir_name。
pg_dump -d ${DATABASE} -h ${HOST} -p ${PORT} -U ${USER} --format=d --file=${dir_name}/database/esg-$(date +%Y%m%d%H%M%S)
#这行代码的含义是执行 pg_dump 命令来备份 PostgreSQL 数据库。其中 ${DATABASE}、${HOST}、${PORT}、${USER} 是变量，分别表示数据库名、主机名、端口号和用户名。${dir_name} 是保存备份文件的目录路径，由前面的命令获取得到。-d 表示需要备份的数据库名，-h 表示连接的主机名，-p 表示连接的端口号，-U 表示连接的用户名。--format=d 表示备份格式为 tar 归档文件，--file 参数指定备份文件名及路径，文件名为 esg-当前时间，其中当前时间通过 $(date +%Y%m%d%H%M%S) 来获取。最终，这行代码的作用是将指定的 PostgreSQL 数据库进行备份并保存到指定的目录中。

2.数据还原

2.数据还原
#创建一个新的数据库，可以使用以下命令创建：
createdb -h ${HOST} -p ${PORT} -U ${USER} ${NEW_DATABASE}
#其中 ${HOST}、${PORT}、${USER} 分别为连接数据库所需的主机名、端口号和用户名，${NEW_DATABASE} 为新建的数据库名称。
#使用以下命令将备份文件还原到新建的数据库
pg_restore -h ${HOST} -p ${PORT} -U ${USER} -d ${NEW_DATABASE} ${BACKUP_FILE}

#其中 ${HOST}、${PORT}、${USER} 分别为连接数据库所需的主机名、端口号和用户名，${NEW_DATABASE} 为新建的数据库名称，${BACKUP_FILE} 为备份文件的路径及文件名。该命令会将备份文件中的数据还原到新建的数据库中。
#注意：还原数据时需要确保备份文件和新建的数据库版本是兼容的。如果备份文件和新建的数据库版本不兼容，还原数据可能会失败。

二、二进制安装psql

postgres安装部署

下载地址：http://www.postgresql.org/ftp/source/

选择要安装的版本进行下载

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake

1.在根目录下新建pgsql文件夹，并将pgsql的压缩包放入

2.解压压缩包

tar -xf postgresql-11.1.tar.gz

3.进入解压后的文件夹

cd postgresql-11.1

4.编译postgresql源码

./configure --prefix=/pgsql/postgresql

make
make install
至此，已完成postgresql的安装。进入/pgsql/postgresql/目录可以看到安装后的postgresql的文件。

groupadd postgres
useradd -g postgres postgres
id postgres


数据库主目录是随着实际情况而不同，这里我们的主要目录是在/pgsql/postgresql/data目录下；
cd /pgsql/postgresql
mkdir data
chown postgres:postgres data
ls -la

进入/home/postgres可以看到.bash_profile文件。
cd /home/postgres
ls -la

vim .bash_profile

添加以下内容
export PGHOME=/pgsql/postgresql
export PGDATA=/pgsql/postgresql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin

保存，生效。
source .bash_profile


su - postgres
初始化数据库：
initdb

可以看到/pgsql/postgresql/data/已经有文件了

cd /pgsql/postgresql/data

ls

修改/pgsql/postgresql/data目录下的两个文件。
postgresql.conf 配置PostgreSQL数据库服务器的相应的参数。
pg_hba.conf 配置对数据库的访问权限。

其中，参数"listen-addresses"表示监听的IP地址，默认是在localhost处监听，也就是127.0.0.1的ip地址上监听，只接受来自本机localhost的连接请求，这会让远程的主机无法登陆这台数据库，如果想从其他的机器上登陆这台数据库，需要把监听地址改为实际网络的地址，一种简单的方法是，将行开头的#去掉，把这个地址改为*，表示在本地的所有地址上监听。

vi pg_hba.conf
找到最下面这一行，这样局域网的人才能访问，红色为新添加内容。

PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下。

linux文件即为linux系统上的启动脚本

cd /pgsql/postgresql-11.1/contrib/start-scripts

1.切换到root用户，修改linux文件属性，添加X属性

chmod a+x linux

2.复制linux文件到/etc/init.d目录下，更名为postgresqls

cp linux /etc/init.d/postgresql

3.修改/etc/init.d/postgresql文件的两个变量

prefix设置为postgresql的安装路径：/pgsql/postgresql

PGDATA设置为postgresql的数据目录路径：/pgsql/postgresql/data

vim /etc/init.d/postgresql

4.设置postgresql服务开机自启动

chkconfig --add postgresql

查看开机自启动服务设置成功。

chkconfig

5.开放5432端口。

firewall-cmd --zone=public --list-ports centos8查看防火墙开放的端口

firewall-cmd --zone=public --add-port=5432/tcp --permanent 开通5432端口

firewall-cmd --reload 重启生效

6.执行service postgresql start,启动Postgresql服务

service postgresql start

查看postgresql服务

切换为postgres用户，进入客户端：

su - postgres

psql

postgres流复制部署

一、简介

PostgreSql在9.0之后引入了主从的流复制机制，所谓流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。

与基于文件日志传送相比，流复制允许保持从服务器更新。从服务器连接主服务器，其产生的流WAL记录到从服务器，而不需要等待主服务器写完WAL文件。

PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。

同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定成都上会增加事务的相应时间。

配置同步复制仅需要一个额外的配置步骤; synchronous_standby_names必须设置为一个非空值。synchronous_commit也必须设置为on。

注：主从服务器所在节点的系统、环境等最好一致。PostgreSQL版本也最好一致，否则可能会有问题。

二、安装部署

角色 IP地址 系统版本 数据库版本

主服务器 172.30.3.38 centos8.2 PostgreSQL11.1

从服务器 172.30.3.36 centos7.8 PostgreSQL11.1

1.配置主服务器

切换成postgres用户

su - postgres

进入数据库，创建一个普通的数据库用户进行主从同步（只需赋予登录和复制的权限即可）

postgres=# create role wml login replication encrypted password 'wml';

然后退出


在exit退出到原用户 用root用户

然后，修改postgresql的pg_hba.conf,配置运行刚刚创建的wml用户可以进行连接

vim /pgsql/postgresql/data/pg_hba.conf

最后，再次配置postgresql.conf

vim /pgsql/postgresql/data/postgresql.conf

listen_addresses = ‘*’ #监听所有ip

archive_mode = on #开启归档模式

archive_command = 'cp %p /pgsql/postgresql/data/%f' #归档命令

wal_level = hot_standby #热备模式

max_wal_senders = 5 #最多有1个流复制连接

wal_sender_timeout = 60s #流复制超时时间

max_connections = 100 #最大连接时间，必须要小于从库的配置

保存退出后，重启数据库服务

service postgresql restart

2.配置从服务器

首先，先测试以下是否能连接主服务器

如果可以，说明主机配置正常，否则检查一下主机的pg_hba.conf配置

进入到postgres用户

su - postgres

先清空data目录下的数据（data文件夹就是postgresql数据的存储文件夹）

rm -rf data/*

/pgsql/postgresql/data

然后，开始从主服务器上复制数据到从服务器上，这一步叫做“基础备份”

pg_basebackup -h 172.30.3.38 -U wml –D data -X stream –P

pg_basebackup是postgresql自带的基础备份工具

这样，基础备份就完成了

然后，创建recovery.conf用于从库恢复从主库获取的数据

vim recovery.conf

然后编辑如下配置

standby_mode = on #表示该节点是从库

primary_conninfo = 'host=172.30.3.36 post=5432 user=wml password=wml' #从机信息和连接用户

recovery_target_timeline = 'latest' #说明恢复到最新状态


保持并退出后，复制到data目录下

cp -rf recovery.conf data

然后，exit退出到root用户

最后，配置从机的postgresql.conf文件

vim /pgsql/postgresql/data/postgresql.conf

在文件末尾添加如下配置


wal_level = hot_standby #热备模式

max_connections = 300 #最大连接时间，必须要小于从库的配置

hot_standby = on #说明这台机器不仅用于数据归档，还可以用于数据查询

max_standby_streaming_delay = 30s #流备份的最大延迟时间

wal_receiver_status_interval = 10s #向主机汇报本机状态的间隔时间

hot_standby_feedback = on #r出现错误复制，向主机反馈

保存并退出

从机配置完成
重启数据库服务
service postgresql restart

4.验证主从配置是否成功
在主机上，切换到postgres用户
su - postgres
进入数据库
执行如下查询
select client_addr,sync_state from pg_stat_replication;

说明172.30.3.36是从服务器，在接收流，而且是异步流复制。说明主从配置成功

注：必须切换到postgres用户下进行查询