pg主从配置实践2023

注意（创建postgres用户后，要从root进入postges,进行后边的操作，注意用户，主要使用的是和数据库有关的用户）

环境:

OS:CentOs 7

Postgres-11.6 源码编译

1.2  主库安装

1.2.1 安装介质准备

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

我这里下载的是11.6版本,介质如下:

postgresql-11.6.tar.gz

这里下载的源码,所以下面的步骤是源码安装

1.2.2 安装依赖包

yum install readline

yum install gcc

yum -y install -y readline-devel

yum install zlib-devel

1.2.3 编译安装

[root@localhost soft]# tar -xvf postgresql-11.6.tar.gz

[root@localhost soft]#mkdir -p /opt/postgresql-11.6

[root@localhost soft]# cd postgresql-11.6

[root@localhost soft]#./configure --prefix=/opt/postgresql-11.6

[root@localhost soft]#make

[root@localhost soft]#make install

1.2.4 创建相应的用户

[root@localhost opt]# groupadd postgres

[root@localhost opt]# useradd -g postgres postgres

1.2.5 创建数据及日志目录，并做相应授权

[root@localhost soft]#mkdir -p /opt/postgresql-11.6/{data,log}

[root@localhost soft]#chown -R postgres:postgres /opt/postgresql-11.6

1.2.6 初始化数据库

#su - postgres

[postgres@localhost bin]$ cd /opt/postgresql-11.6/bin

[postgres@localhost bin]$ ./initdb -D /opt/postgresql-11.6/data/

1.2.7 启动数据库

[postgres@localhost bin]$ cd /opt/postgresql-11.6/bin

[postgres@localhost bin]$./pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start

1.2.8 修改环境变量

[postgres@localhost ~]$ more .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/opt/postgresql-11.6/bin

export PATH

1.2.9 登陆使用

[postgres@localhost bin]$cd /opt/postgresql-11.6/bin

[postgres@localhost bin]$ ./psql

psql (11.6)

Type "help" for help.

postgres=# \du

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

1.2.10 修改postgres用户的访问密码并测试建库建表

PostgreSQL 数据库默认会创建一个postgres的数据库用户作为数据库的管理员，默认密码为空，我们需要修改为指定的密码，这里设定为postgres.

su - postgres

psql

# ALTER USER postgres WITH PASSWORD 'postgres';

# select * from pg_shadow ;

# create database hxl;

# \c hxl

project=# create table person(id integer, name text);

project=# insert into person values (1, 'hxl');

project=# select * from person;

1.2.11 配置postgresql允许远程访问

只需要修改data目录下的pg_hba.conf和postgresql.conf这两个文件:

pg_hba.conf：配置对数据库的访问权限;

postgresql.conf：配置PostgreSQL数据库服务器的相应的参数

vim /opt/postgresql-11.6/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

重新加载配置文件

su - postgres

pg_ctl -D /opt/postgresql-11.6/data reload

修改postgresql.conf

vim /opt/postgresql-11.6/data/postgresql.conf

listen_addresses = '*'   # what IP address(es) to listen on;

修改该改参数需要重启动

pg_ctl -D /opt/postgresql-11.6/data -l /opt/postgresql-11.6/log/postgres.log stop

pg_ctl -D /opt/postgresql-11.6/data -l /opt/postgresql-11.6/log/postgres.log start

到这里主库已经按照好了,下面进行主库的配置

1.2.12    主从配置

1.2.12.1 创建同步账号

[postgres@localhost data]$ psql

psql (11.6)

Type "help" for help.

postgres=# CREATE ROLE repl login replication encrypted password 'repl';

CREATE ROLE

1.2.12.2 修改配置文件(pg_hba.conf)

在该文件最后添加如下两行:

host    replication     repl            192.168.1.0/24          md5

host    all             repl            192.168.1.0/24          trust

1.2.12.3 修改配置文件(postgresql.conf)

找到相应的参数进行如下配置修改

wal_level = replica

archive_mode = on

archive_command = 'cp %p /opt/postgresql-11.6/data/pg_archive/%f'

##%p = path of file to archive

##%f = file name only

max_wal_senders = 6

wal_keep_segments = 10240

wal_sender_timeout = 60s

1.2.12.4 创建归档日志目录

mkdir -p /opt/postgresql-11.6/data/pg_archive

1.2.12.5 重启主库

pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log restart

1.3  从库安装

1.3.1 安装

从库的安装跟主库安装步骤一致,需要启动数据库

1.3.2 停掉从库

若从库的数据库已经在运行的话,事先将其停掉

[postgres@localhost data]$ pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log stop

waiting for server to shut down.... done

server stopped

1.3.3 准备data目录

从库安装完成后，不初始化，若已经初始化，删除其data目录

若之前安装的pg有data目录的话需要将其删除掉,并创建一个空的相同的目录

su - postgres

[postgres@localhost postgresql-11.6]$ cd /opt/postgresql-11.6

[postgres@localhost postgresql-11.6]$ mv data bakdata

[postgres@localhost postgresql-11.6]$ mkdir data

root用户下修改权限

chown -R postgres:postgres /opt/postgresql-11.6

chmod 0700 /opt/postgresql-11.6/data

1.3.4 同步主库的数据文件

[postgres@localhost postgresql-11.6]$ ./pg_basebackup -X s  -c fast -v --progress -U  repuser  -h 10.221.20.73 -p 5432    -D /opt/hikvision/pgsql/data

Password:

113625/113625 kB (100%), 1/1 tablespace

可以看到data目录下的所有文件都同步过来了

[postgres@localhost data]$ pwd

/opt/postgresql-11.6/data

[postgres@localhost data]$ ls -al

total 56

drwxrwxr-x 20 postgres postgres  4096 Jan 14 11:11 .

drwxr-xr-x  9 postgres postgres    94 Jan 14 11:08 ..

-rw-------  1 postgres postgres   224 Jan 14 11:11 backup_label

drwx------  6 postgres postgres    54 Jan 14 11:11 base

drwx------  2 postgres postgres  4096 Jan 14 11:11 global

drwxrwxr-x  2 postgres postgres   166 Jan 14 11:11 pg_archive

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_commit_ts

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_dynshmem

-rw-------  1 postgres postgres  4719 Jan 14 11:11 pg_hba.conf

-rw-------  1 postgres postgres  1636 Jan 14 11:11 pg_ident.conf

drwx------  4 postgres postgres    68 Jan 14 11:11 pg_logical

drwx------  4 postgres postgres    36 Jan 14 11:11 pg_multixact

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_notify

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_replslot

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_serial

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_snapshots

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_stat

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_stat_tmp

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_subtrans

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_tblspc

drwx------  2 postgres postgres     6 Jan 14 11:11 pg_twophase

-rw-------  1 postgres postgres     3 Jan 14 11:11 PG_VERSION

drwx------  3 postgres postgres    60 Jan 14 11:11 pg_wal

drwx------  2 postgres postgres    18 Jan 14 11:11 pg_xact

-rw-------  1 postgres postgres    88 Jan 14 11:11 postgresql.auto.conf

-rw-------  1 postgres postgres 24073 Jan 14 11:11 postgresql.conf

1.3.4 创建recovery.conf文件

从模板文件拷贝到data目录

[postgres@localhost share]$ cp /opt/postgresql-11.6/share/recovery.conf.sample /opt/postgresql-11.6/data/recovery.conf

对其进行修改，参数如下:

在data目录下创建recovery.conf文件,内容如下

standby_mode = on  # 这个说明这台机器为从库

primary_conninfo = 'host=192.168.1.130 port=5432 user=repl password=repl'  # 这个说明这台机器对应主库的信息

recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据

1.3.5 修改从库postgresql.conf文件

修改如下内容项:

max_connections = 1000 #一般查多于写的应用从库的最大连接数要比较大

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

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

wal_receiver_status_interval = 1s  #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间

hot_standby_feedback = on          #如果有错误的数据复制，是否向主进行反馈

1.3.6 启动从库

pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start

1.3.7    验证

1.3.7.1 查看同步情况

[postgres@localhost pg_wal]$ psql

postgres=# \x on

Expanded display is on.

postgres=# select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------

pid              | 28450

usesysid         | 16411

usename          | repl

application_name | walreceiver

client_addr      | 192.168.1.131

client_hostname  |

client_port      | 42388

backend_start    | 2020-01-14 11:30:41.314367+08

backend_xmin     | 600

state            | streaming

sent_lsn         | 0/8000140

write_lsn        | 0/8000140

flush_lsn        | 0/8000140

replay_lsn       | 0/8000140

write_lag        |

flush_lag        |

replay_lag       |

sync_priority    | 0

sync_state       | async

1.3.7.2 主库尝试创建对象看是否同步到从库

psql -h localhost -U uhxl -d hxl

create table tb_test

(

 id bigserial primary key not null,

 name varchar(64)

);

insert into tb_test(name) values('name1');

insert into tb_test(name) values('name2');

insert into tb_test(name) values('name3');

insert into tb_test(name) values('name4');

insert into tb_test(name) values('name5');

查看从库是否同步

1.4  主从切换(promote方式主从切换)

1.4.1 主备库进程查看

主库

[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'

Database cluster state:               in production

[postgres@localhost data]$

备库

[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'

Database cluster state:               in archive recovery

[postgres@localhost data]$

1.4.2 停掉主库

pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log stop

查看状态已经处于stop状态

[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'

Database cluster state:               shut down

1.4.3 提升从库为主库

这个时候从库保持运行状态,不需要停掉

[postgres@localhost data]$ pg_ctl promote -D /opt/postgresql-11.6/data/

waiting for server to promote.... done

server promoted

查看状态

[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'

Database cluster state:               in production

1.4.4 验证

这个时候从库的recovery.conf文件会自动命名为recovery.done

尝试在原来的从库写入数据

insert into tb_hxl01 values(20,'name6');

insert into tb_hxl01 values(21,'name7');

insert into tb_hxl01 values(22,'name8');

insert into tb_hxl01 values(23,'name9');

insert into tb_hxl01 values(24,'name10');

insert into tb_hxl01 values(25,'name10');

写入新增数据,重库启动后,模拟差异数据是否同步到从库

1.4.5    将原来的主库部署成为重库

1.4.5.1 创建recovery.conf文件

在原来主库的data目录下创建recovery.conf文件

1.4.5.2 启动

[postgres@localhost data]$ pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start

查看状态

[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'

Database cluster state:               in archive recovery

这个时候发现数据库无法连接

[postgres@localhost log]$ psql

psql: could not connect to server: No such file or directory

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

查看错误日志postgres.log

2020-01-19 15:07:55.297 CST [31189] FATAL:  hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 1000)

解决办法,修改当前数据库的max_connections与主库保持一致

1.4.5.3 验证刚才从库写入的数据是否同步过来

查看新备库中数据是否与现主库内容相同，9.6以后的版本应该会自动同步差异

（注：postgresql学习配置主从同步和自动备份_litaoa的专栏-CSDN博客）这是另外一篇博客，博主根据文档，添加自动同步脚本，同步数据，保证数据安全性

一、停主库

1、查看当前连接

2、杀死当前账户连接

3、停止主库服务

 4、从库激活为主库

5、检查从库是否提升为主库成功