postgresSQL主从配置

主服务器

服务器相关信息

服务器IP:112.45.153.238:19082
用户名:root
密码:Careyes@123

postgreSQL的安装及配置

  1. 下载及安装
    # 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
  2. 配置postgreSQL
    1. 创建数据目录、归档目录

      `# mkdir -p /opt/postgresql/data/

      `# mkdir /opt/postgresql/data/pg_archive/

      将data目录的拥有者修改为postgres

      `# chown postgres:postgres /opt/postgresql/data/

    2. 修改数据存储路径

      `# vim /usr/lib/systemd/system/postgresql-10.service

      (找到Environment=PGDATA, 修改)
      Environment=PGDATA=/opt/postgresql/data/
    3. 初始化数据库

      `# /usr/pgsql-10/bin/postgresql-10-setup initdb

    4. 修改相关配置文件

      `# 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
    5. 开机自启

      # systemctl enable postgresql-10# systemctl start postgresql-10

    6. 创建主从同步replica用户

      `# su - postgres

      `-bash-4.2$ psql

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

    7. 防火墙开放相关信息

      `# vim /etc/profile

      # 末尾新增以下内容
      export PGHOST=/opt/postgresql/data
      export PGPORT=9999

      `# source /etc/profile

从服务器

服务器相关信息

服务器IP:212.64.3.225:33033
用户名:root
密码:CareyesTech!$%)

postgreSQL的安装及配置

  1. 下载及安装
    # 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
  2. 配置postgreSQL
    1. 创建数据目录、归档目录

      `# mkdir -p /opt/postgresql/data/

      `# mkdir /opt/postgresql/data/pg_archive/

      修改data目录的拥有者为postgres并修改权限

      `# chown postgres:postgres /opt/postgresql/data/

      `# chmod 700 /opt/postgresql/data

    2. 修改数据存储路径

      `# vim /usr/lib/systemd/system/postgresql-10.service

      (找到Environment=PGDATA, 修改)
      Environment=PGDATA=/opt/postgresql/data/
    3. 初始化数据库

      `# 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

    4. 修改相关配置文件

      `# 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 #如果有错误的数据复制,是否向主进行范例
    5. 开机自启

      # systemctl enable postgresql-10# systemctl start postgresql-10

    6. 防火墙开放相关信息

      `# 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的纪录则说明主从搭建成功)
02-13 01:22