postgresSQL主从配置
主服务器
服务器相关信息
服务器IP:112.45.153.238:19082
用户名:root
密码:Careyes@123
postgreSQL的安装及配置
下载及安装
# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # yum install postgresql10 -y # yum install postgresql10-server -y
配置postgreSQL
创建数据目录、归档目录
`# mkdir -p /opt/postgresql/data/
`# mkdir /opt/postgresql/data/pg_archive/
将data目录的拥有者修改为postgres
`# chown postgres:postgres /opt/postgresql/data/
修改数据存储路径
`# vim /usr/lib/systemd/system/postgresql-10.service
(找到Environment=PGDATA, 修改) Environment=PGDATA=/opt/postgresql/data/
初始化数据库
`# /usr/pgsql-10/bin/postgresql-10-setup initdb
修改相关配置文件
`# vim /opt/postgresql/data/postgresql.conf
data_directory = '/opt/postgresql/data' listen_addresses = '*' port = 9999 max_connections = 300 unix_socket_directories = '/var/run/postgresql, /tmp, /opt/postgresql/data' wal_level = replica archive_mode = on archive_command = 'cp %p /opt/postgresql/data/pg_archive/%f' max_wal_senders = 16 wal_keep_segments = 256 wal_sender_timeout = 60s
`# vim /opt/postgresql/data/pg_hba.conf
# 在末尾添加从服务器信息 host all replica 115.199.178.134/24 trust host replication replica 115.199.178.134/24 md5
开机自启
# systemctl enable postgresql-10
# systemctl start postgresql-10创建主从同步replica用户
`# su - postgres
`-bash-4.2$ psql
postgres=# CREATE ROLE replica login replication encrypted password 'replica';
防火墙开放相关信息
`# vim /etc/profile
# 末尾新增以下内容 export PGHOST=/opt/postgresql/data export PGPORT=9999
`# source /etc/profile
从服务器
服务器相关信息
服务器IP:212.64.3.225:33033
用户名:root
密码:CareyesTech!$%)
postgreSQL的安装及配置
下载及安装
# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm # yum install postgresql10 -y # yum install postgresql10-server -y
配置postgreSQL
创建数据目录、归档目录
`# mkdir -p /opt/postgresql/data/
`# mkdir /opt/postgresql/data/pg_archive/
修改data目录的拥有者为postgres并修改权限
`# chown postgres:postgres /opt/postgresql/data/
`# chmod 700 /opt/postgresql/data
修改数据存储路径
`# vim /usr/lib/systemd/system/postgresql-10.service
(找到Environment=PGDATA, 修改) Environment=PGDATA=/opt/postgresql/data/
初始化数据库
`# su - postgres
`-bash-4.2$ pg_basebackup -h 112.45.153.238 -U replica -D /opt/postgresql/data -X stream -P -p 9999
Password: replica
(数据库开始备份, 当出现24428/24428 kB (100%), 1/1 tablespace时数据库备份完成)
-bash-4.2$ cp /usr/pgsql-10/share/recovery.conf.sample /opt/postgresql/data/recovery.conf
-bash-4.2$ exit修改相关配置文件
`# vim /opt/postgresql/data/recovery.conf
standby_mode = on primary_conninfo = 'user=replica password=replica host=112.45.153.238 port=9999' #trigger_file = '/var/lib/pgsql/9.6/data/trigger.kenyon' #主从切换时后的触发文件 (没有配置,使用默认, 占时未启用) recovery_target_timeline = 'latest'
`# vim /opt/postgresql/data/postgresql.conf
port = 5432 max_connections = 999 (master的最大连接数要小于slaver) hot_standby = on #说明这台机器不仅仅用于数据归档,也用于查询 max_standby_streaming_delay = 30s wal_receiver_status_interval = 10s #多久向主报告一次从的状态 hot_standby_feedback = on #如果有错误的数据复制,是否向主进行范例
开机自启
# systemctl enable postgresql-10
# systemctl start postgresql-10防火墙开放相关信息
`# vim /etc/profile
# 末尾新增以下内容 export PGHOST=/opt/postgresql/data export PGPORT=5432
`# source /etc/profile
测试主从搭建是否成功
主服务器
# su - postgres
-bash-4.2$ psql
postgres=# select client_addr,sync_state from pg_stat_replication;
client_addr | sync_state
-----------------+------------
115.199.178.134 | async
(1 行记录) (存在该条纪录才能同步)
postgres=# create database test;
(主库创建test数据库,在从库中查看是否同步)
从服务器
# su - postgres
-bash-4.2$ psql
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(4 行记录)(出现第四条test的纪录则说明主从搭建成功)