pg安装步骤
1.1.PostgreSQL单机安装(root执行)
注意：yum、rpm安装方式二选一
1、yum命令方式安装
yum install postgresql12
yum install postgresql12-server
2、rpm命令方式安装
rpm -ivh postgresql12-libs-12.3-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql12-12.3-1PGDG.rhel7.x86_64.rpm --nodeps
rpm -ivh postgresql12-server-12.3-1PGDG.rhel7.x86_64.rpm
注：CenterOS 7.6之前版本，安装postgresql10-10.7-1PGDG.rhel7.x86_64.rpm包，需要先安装libicu依赖包，执行如下命令安装
rpm -ivh libicu-50.1.2-17.el7.x86_64.rpm
3、查看rpm包安装路径
rpm -ql postgresql12-server-12.3-1PGDG.rhel7.x86_64
4、创建pgdata目录，修改为postgres组
mkdir -p /appdata/pg15/pgdata
mkdir -p /appdata/pg15/pgarch
mkdir -p /appdata/pg15/pglog
chown -R postgres:postgres /appdata/pg15/pgdata
chown -R postgres:postgres /appdata/pg15/pgarch
chown -R postgres:postgres /appdata/pg15/pglog
注：/appdata目录为部署规划的分区，需要修改为生产实际的分区目录
5、切换到postgres用户，初始化数据库
su - postgres
/usr/pgsql-12/bin/initdb -D /appdata/pg15/pgdata/data5432
6、修改配置文件
vi /appdata/pg15/pgdata/data5432/postgresql.conf
listen_addresses = '*'
port = 5432
password_encryption = md5
max_connections = 500
archive_mode = on
archive_command = 'test ! -f /appdata/pgarch/%f && cp %p /appdata/pgarch/%f'
wal_level = replica
wal_keep_segments = 1000
max_wal_size = 10GB
max_wal_senders = 10
hot_standby = on
7、添加pg访问白名单
vi /appdata/pg15/pgdata/data5432/pg_hba.conf
host all all 192.168.10.0/8 md5
注：192.168.10.0/8根据生产实际网段设置，配置的网段为共享交换应用所在主机的网段。
8、启动pg
/usr/pgsql-15/bin/pg_ctl start -D /appdata/pg15/pgdata/data5432 -l /appdata/pg15/pgdata/data5432/pg.log
9、初始化postgres密码
/usr/pgsql-15/bin/psql -p 5432 postgres
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=#
postgres=#\q #退出psql
10、查看pg状态
/usr/pgsql-15/bin/pg_ctl status -D /appdata/pg15/pgdata/data5432
11、停止pg（需要的话才执行）
/usr/pgsql-15/bin/pg_ctl stop -D /appdata/pg15/pgdata/data5432
1.2.PostgreSQL主备集群配置
Postgres主备集群采用primary-slave，1主1从异步复制方式，在2台机器上面安装好PG单机后，再执行下面步骤
1、primary主机上面创建复制用户
su - postgres
createuser -U postgres repuser -P -c 5 --replication
Enter password for new role:
Enter it again:
2、修改主备复制配置
vi /appdata/pgdata/data5432/postgresql.conf
synchronous_standby_names = '*'
hot_standby_feedback = on
3、增加复制用户访问控制
vi pg_hba.conf
host replication repuser 30.23.11.61/32 md5
4、重启primary主机的PG服务
/usr/pgsql-12/bin/pg_ctl stop -D /appdata/pgdata/data5432
/usr/pgsql-12/bin/pg_ctl start -D /appdata/pgdata/data5432 -l /appdata/pgdata/data5432/pg.log
5、停止slave备机PG服务
/usr/pgsql-12/bin/pg_ctl stop -D /appdata/pgdata/data5432
6、登录到slave备机，同步数据到备机
cd /appdata/pgdata/data5432
rm -rf *
cd
pg_basebackup -h 19.167.3.17 -U repuser -p 5432 -D /appdata/pgdata/data5432 --wal-method=stream --checkpoint=fast --progress --verbose --write-recovery-conf > makeslave$(date +%Y%m%d).log 2>&1
注：上面IP为PG主库IP地址
注：postgresql12开始已经弃用recovery.conf配置 ，PG12.3版本执行7.1、7.2步骤，PG10.7版本执行7.3步骤
7.1 修改postgresql.conf文件
wal_level = hot_standby
hot_standby = on
hot_standby_feedback = on
7.2 在postgre.auto.conf添加application_name =slave1，配置如下
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'application_name=slave1 user=repuser passfile=''/var/lib/pgsql/.pgpass'' host=30.23.10.79 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
7.3add wal switch flag
vi /appdata/pgdata/data5432/recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=repuser password=repuser host=30.23.10.79 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any'
recovery_target_timeline='latest'
1、启动slave备机服务
/usr/pgsql-12/bin/pg_ctl start -D /appdata/pgdata/data5432 -l /appdata/pgdata/data5432/pg.log
2、检查replication状态
primary主机
su - postgres
psql -h 127.0.0.1
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+----------------------------
pid | 17196
usesysid | 16384
usename | repuser
application_name | walreceiver
client_addr | 30.23.11.61
client_hostname |
client_port | 41375
backend_start | 2019-09-06 15:24:26.9693+08
backend_xmin | 558
state | streaming
sent_lsn | 0/3000638
write_lsn | 0/3000638
flush_lsn | 0/3000638
replay_lsn | 0/3000638
write_lag | 00:00:00.000535
flush_lag | 00:00:00.005545
replay_lag | 00:00:00.005578
sync_priority | 1
sync_state | sync
postgres=# CREATE USER usercenter WITH PASSWORD '${password}';
CREATE ROLE
postgres=# CREATE DATABASE usercenterdb OWNER usercenter ENCODING 'UTF-8';
CREATE DATABASE
postgres=# GRANT ALL PRIVILEGES ON DATABASE usercenterdb TO usercenter;
GRANT
postgres=# \c usercenterdb
You are now connected to database "usercenterdb" as user "postgres".
usercenterdb=# create table t1(id serial,user_name varchar(20));
CREATE TABLE
usercenterdb=# insert into t1(user_name) values('mia');
INSERT 0 1
usercenterdb=# select * from t1;
-[ RECORD 1 ]--
id | 1
user_name | mia
usercenterdb=# \q
slave备机
su - postgres
psql -h 127.0.0.1
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
(0 rows)
postgres=# \x
Expanded display is off.
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
usercenterdb | usercenter | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/usercenter +
| | | | | usercenter=CTc/usercenter
(4 rows)
postgres=# \c usercenterdb
You are now connected to database "usercenterdb" as user "postgres".
usercenterdb=# select * from t1;
id | user_name
----+-----------
1 | mia
(1 row)
usercenterdb=# \q
pg重启
nohup /usr/pgsql-10/bin/postgres -D /data/pgdata/data5432/ &
nohup /usr/pgsql-9.6/bin/postgres -D /data/pgdata/ &
浙公网安备 33010602011771号