安装postgresql11

两台机器都装好

  1. yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/postgresql11-libs-11.2-1PGDG.rhel7.x86_64.rpm
  2. yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/postgresql11-11.2-1PGDG.rhel7.x86_64.rpm
  3. yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/postgresql11-server-11.2-1PGDG.rhel7.x86_64.rpm
  5. yum -y install postgresql11 postgresql11-server postgresql11-libs
  7. postgresql-11-setup initdb
  9. #启动
  10. systemctl start postgresql-11
  11. systemctl enable postgresql-11

修改配置文件

vim /var/lib/pgsql/11/data/postgresql.conf

修改的地方为

  1. listen_addresses = '*'
  2. port = 5432

vim /var/lib/pgsql/11/data/pg_hba.conf

增加一行

  1. host    all             all             0.0.0.0/0               trust

改完之后重启postgresql

  1. systemctl restart postgresql-11

更改初始用户密码

  1. # 使用postgres用户操作
  2. su - postgres  
  3. psql
  4. # 改一下密码
  5. postgres=# ALTER USER postgres WITH PASSWORD '12345678';
  6. ALTER ROLE #提示这个则表示成功
  7. #退出
  8. postgres=# \q

Navicat连接

image.png

主从复制配置

现有两台服务器10.27.3.176，10.27.3.175，以176为主库

主库配置

  1. su - postgres  
  2. psql
  3. postgres=# create role repl login replication encrypted password 'hatech1618';
  4. CREATE ROLE

vim /var/lib/pgsql/11/data/pg_hba.conf

添加一行

  1. host    replication     repl            0.0.0.0/0               trust

vim /var/lib/pgsql/11/data/postgresql.conf

修改的地方为

  2. listen_addresses = '*'            # what IP address(es) to listen on;
  3. port = 5432                # (change requires restart)
  4. max_connections = 512            # (change requires restart)
  5. shared_buffers = 128MB            # min 128kB
  6. dynamic_shared_memory_type = posix    # the default is the first option
  7. wal_level = hot_standby        # minimal, replica, or logical
  8. archive_mode = on        # enables archiving; off, on, or always
  9. archive_command = 'cp %p /var/lib/pgsql/11/data/pg_archive/%f'        # command to use to archive a logfile segment
  10. max_wal_senders = 6        # max number of walsender processes
  11. wal_keep_segments = 10240    # in logfile segments, 16MB each; 0 disables
  12. wal_sender_timeout = 60s    # in milliseconds; 0 disables
  13. log_directory = 'log'    # directory where log files are written

创建刚刚配置的目录

  1. mkdir /var/lib/pgsql/11/data/pg_archive/
  2. chown -R postgres.postgres /var/lib/pgsql/11/data

重启主库服务

  1. systemctl restart postgresql-11

从库配置

把主节点的配置文件拷贝过来 先删再拷贝

  1. rm -rf  /var/lib/pgsql/11/data/*
  2. # 根据主节点ip拷贝
  3. pg_basebackup -h 10.27.3.176 -U repl -D /var/lib/pgsql/11/data/ -X stream -P  
  4. # 把复制来的文件赋权
  5. chown -R postgres.postgres /var/lib/pgsql/11/data

修改配置文件

vim /var/lib/pgsql/11/data/postgresql.conf

根据配置修改

  1. listen_addresses = '*'            # what IP address(es) to listen on;
  2. port = 5432                # (change requires restart)
  3. max_connections = 1000            # (change requires restart)
  4. shared_buffers = 128MB            # min 128kB
  5. dynamic_shared_memory_type = posix    # the default is the first option
  6. wal_level = replica        # minimal, replica, or logical
  7. archive_mode = on        # enables archiving; off, on, or always
  8. archive_command = 'cp %p /var/lib/pgsql/11/data/pg_archive/%f'        # command to use to archive a logfile segment
  9. wal_sender_timeout = 60s    # in milliseconds; 0 disables
  10. hot_standby = on            # "on" allows queries during recovery
  11. max_standby_streaming_delay = 30s    # max delay before canceling queries
  12. wal_receiver_status_interval = 10s    # send replies at least this often
  13. hot_standby_feedback = on        # send info from standby to prevent
  14. log_directory = 'log'    # directory where log files are written,

创建恢复文件

  1. cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
  2. vim /var/lib/pgsql/11/data/recovery.conf
  1. # 调整参数：
  2. recovery_target_timeline = 'latest'   #同步到最新数据
  3. standby_mode = on          #指明从库身份
  4. trigger_file = 'failover.now'
  5. primary_conninfo = 'host=10.27.3.176 port=5432 user=repl password=12345678'   #连接到主库 配置主库的ip 账号 密码 端口
  1. #启动从库
  2. systemctl restart postgresql-11

查看从库信息 出现async表示成功

  1. [root@localhost data]# su - postgres
  2. Last login: Wed Dec  1 09:44:23 CST 2021 on pts/2
  3. -bash-4.2$ psql
  4. psql (11.14)
  5. Type "help" for help.
  7. postgres=# select client_addr,sync_state from pg_stat_replication;
  8.  client_addr | sync_state 
  9. -------------+------------
  10.  10.27.3.175 | async      
  11. (1 row)

查看详细从库信息

  1. select * from pg_stat_replication;