postgresql主从搭建

原创

宋鹏超 2021-11-30 16:01:05 博主文章分类：数据库服务 ©著作权

©著作权归作者所有：来自51CTO博客作者宋鹏超的原创作品，请联系作者获取转载授权，否则将追究法律责任

环境介绍：centos6.6

主：192.168.142.130 postgresql-9.2.4.tar.bz2

从： 192.168.142.131 postgresql-9.2.4.tar.bz2

postgresql主从复制是一种高可用解决方案，可以实现读写分离。postgresql主从复制是基于xlog来实现的，主库开启日志功能，从库根据主库xlog来完成数据的同步。

sed -i '41 s/^/* soft nofile 65535\n* hard nofile 65535\n* soft nproc 65535\n* hard nproc 65535/g' /etc/security/limits.conf
ulimit -n 65535
sed -i '/^SELINUX=enforcing/c#SELINUX=enforcing' /etc/selinux/config 
sed -i '/^SELINUXTYPE=targeted/c#SELINUXTYPE=targeted' /etc/selinux/config 
sed -i '12 s/^/SELINUX=disabled/g' /etc/selinux/config 
setenforce 0
service iptables stop
chkconfig iptables off 
#以上是基础环境准备

一、postgresql安装

yum install gcc gcc-c++ make readline-devel flex zlib-devel libxml2*  libxslt-devel libxslt -y 
tar xf 10103016223396.gz
cd uuid-1.6.1/  
./configure
make && make install
    
cd /home/soft/
tar xjf postgresql-9.2.4.tar.bz2 -C /usr/local/    
cd /usr/local/postgresql-9.2.4
./configure LDFLAGS=-L/usr/local/lib --prefix=/usr/local/pgsql --with-ossp-uuid --with-libxml --with-libxslt
make && make install

cd contrib/uuid-ossp/
make && make install
echo "LD_LIBRARY_PATH=/usr/local/lib" >> /etc/profile
source /etc/profile

# vim /root/.bash_profile   设置环境变量

把 PATH=$PATH:$HOME/bin
改成 PATH=$PATH:$HOME/bin:/usr/local/pgsql/bin

# vim /etc/passwd
把 postgres:x:528:528::/home/postgres:/bin/bash
改成 postgres:x:528:528::/usr/local/pgsql:/bin/bash


source /root/.bash_profile 
groupadd postgres
useradd -g postgres postgres
cp /root/.bash_profile /usr/local/pgsql/
chown postgres.postgres /usr/local/pgsql/.bash_profile
source /usr/local/pgsql/.bash_profile
mkdir /usr/local/pgsql/data
chown -R postgres.postgres /usr/local/pgsql


vim /etc/profile
export PGSQL_HOME=/usr/local/pgsql
export PGLIB=$PGSQL_HOME/lib
export MANPATH=$PGSQL_HOME/man
export PGDATA=$PGSQL_HOME/data
export PATH=$PGSQL_HOME/bin:$PATH

source /etc/profile

su - postgres
/usr/local/pgsql/bin/initdb 
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start

vim /usr/local/pgsql/data/postgresql.conf    #修改配置文件
  listen_addresses = '*' 
  port = 5432
  wal_level = hot_standby
  max_wal_senders = 1                   #Slave库的节点数
  hot_standby = on
  wal_keep_segments=64                   
  max_connections=1000
  
vim /usr/local/pgsql/data/pg_hba.conf
  # IPv4 local connections:
  host    all             all             127.0.0.1/32            trust
  host    replication     repuser         0.0.0.0/0               md5

vim /usr/local/pgsql/data/pg_hba.conf
192.168.142.130:5432:postgres:repuser:123456

cd /usr/local/postgresql-9.2.4
cp contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql
/etc/init.d/postgresql start
chkconfig --add postgresql 
chkconfig postgresql on
echo "/usr/local/lib" >> /etc/ld.so.conf
ldconfig
su - postgres
-bash-4.1$ psql
postgres=# ALTER USER postgres WITH PASSWORD 'postgres'; 
postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD '123456';

chown -R postgres.postgres /usr/local/pgsql/data
/etc/init.d/postgresql restart

从库只需安装postgresql，不用配置，后续会从主库拷贝

二、从库配置

rm -rf /usr/local/pgsql/data
su - postgres
pg_basebackup  -F p -h 192.168.142.130 -p 5432 -U repuser -x -D /usr/local/pgsql/data -F p 

vim /usr/local/pgsql/data/postgresql.conf
 listen_addresses = '*' 
 port = 5432
#wal_level=hot_standby                      #从库不需要这个配置,注释掉
#max_wal_senders=2                         #从库不需要这个配置,注释掉
#wal_keep_segments=64                      #从库不需要这个配置,注释掉
hot_standby=on                             #开启hot_standby模式
max_standby_streaming_delay=30s         #可选，流复制最大延迟
wal_receiver_status_interval=10s         #可选，向主库报告状态的最大间隔时间
hot_standby_feedback=on                  #可选，查询冲突时向主库反馈
max_connections=2000                      #最大连接数一般大于主库就行 

cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf

vi /usr/local/pgsql/data/recovery.conf       #新增以下三行
standby_mode = 'on'
recovery_target_timeline = 'latest       #恢复最新的数据
primary_conninfo = 'host=192.168.142.130 port=5432 user=repuser password=123456 keepalives_idle=60'


chown -R postgres.postgres /usr/local/pgsql/data
/etc/init.d/postgresql start