CentOS安装PostgreSQL主从
Linux系统准备
检测ssh/telnet功能
- iptables防火墙
vi /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 23 -j ACCEPT
- firewall防火墙
firewall-cmd --zone=public --add-port=22/tcp --permanent
firewall-cmd --zone=public --add-port=23/tcp --permanent
firewall-cmd --reload
创建用户,postgres,分配目录权限
- 创建用户
useradd postgres
- 设置密码
passwd postgres
- 赋权
vi /etc/sudoers
## Allow root to run any commands anywhere
root ALL=(ALL) ALL
postgres ALL=(ALL) ALL
mkdir -p /home/postgres/pgsql/pg_9_5_4
mkdir -p /home/postgres/pgsql/pg_src
mkdir -p /home/postgres/pgsql/data
mkdir -p /home/postgres/pgsql/pg_wal_log
mkdir -p /home/postgres/pgsql/sqlfile
ssh免密码登录
- Postgres从机使用postgres用户执行:
ssh-keygen -t rsa
scp ~/.ssh/id_rsa.pub postgres@192.168.xxx.xxx:/home/postgres/.ssh/authorized_keys
- Postgres主机执行
chmod 766 authorized_keys
Postgres安装
下载postgres9.5.4源码包
可以去官网下载,也可点击文末的连接地址下载。
https://download.csdn.net/download/weixin_38598071/10436569
解压缩源码包
tar -zxvf postgresql-9.5.4.tar.gz /home/postgres/pgsql/pg_src
预安装依赖软件(检测)
yum -y install telnet-server
yum -y install gcc
yum -y install gcc-c++
yum -y inatall perl
yum -y install perl-ExtUtils-Embed
yum -y install readline
yum -y install readline-devel *
yum -y install zlib
yum -y install zlib-devel
yum -y install python
yum -y install python-devel *
yum -y install libxml2
yum -y install libxml2-devel
开始安装postgresql
cd /home/postgres/pgsql/pg_src/postgresql-9.5.4
./configure --prefix=/home/postgres/pgsql/pg_9_5_4 --with-perl --with-python
make
sudo make install
配置环境变量
vi /etc/profile
export PGSQL_HOME=/home/postgres/pgsql/pg_9_5_4
export PGSQL_DATA=/home/postgres/pgsql/data
export PATH=${PGSQL_HOME}/bin:$PATH
export LD_LIBRARY_PATH=${PGSQL_HOME}/lib
export MANPATH=${PGSQL_HOME}/share/man:$MANPATH
postgresql初始化
initdb -D ${PGSQL_DATA}
启动和关闭postgresql数据库服务
pg_ctl -D ${PGSQL_DATA} -l ${PGSQL_LOGS}/logfiles start
pg_ctl -D ${PGSQL_DATA} stop
安装contrib工具
cd /home/postgres/pgsql/pg_src/postgresql-9.5.4/contrib
make
sudo make install
设置系统启动阶段启动postgresql数据库
sudo cp /home/postgres/pgsql/pg_src/postgresql-9.5.4/contrib/start-scripts/linux /etc/init.d/postgresql
sudo chmod 755 /etc/init.d/postgresql
sudo vi /etc/init.d/postgresql
prefix=/home/postgres/pgsql/pg_9_5_4
PGDATA="/home/postgres/pgsql/data"
[服务方式启动关闭PG]
service postgresql start
service postgresql stop
[注册开机启动]
chkconfig --add postgresql
配置远程访问权限
vi /home/postgres/pgsql/data/postgresql.conf
listen_addresses = '*' # 配置监听地址为所有地址,包括外网
port = 5432 # 配置默认访问端口
[重启服务]
service postgresql restart
[前提条件打开数据库服务器端口,修改/etc/sysconfig/iptables]
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5433 -j ACCEPT
Postgres主从安装
主数据配置备份连接
- 主数据创建备份机访问用户及权限赋予
CREATE USER repuser replication LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD'mypassword';
- 配置用户备份访问权限
vi /home/postgres/pgsql/data/pg_hba.conf
host replication repuser 从库ip/32 md5
创建归档日志目录
mkdir -p /home/postgres/pgsql/pg_wal_log
配置热备份模式
vi /home/postgres/pgsql/data/postgresql.conf
#standby流复制
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 1000
#archive在线日志归档
archive_mode = on
archive_command = 'test ! -f /home/postgres/pgsql/pg_wal_log/%f && cp %p /home/postgres/pgsql/pg_wal_log/%f'
在standby机器上安装相同版本的postgreSQL数据库
cd /home/postgres/pg_src/postgresql-9.5.4
./configure --prefix=/home/postgres/pgsql/pg_9_5_4 --with-perl --with-python
make
sudo make install
配置环境变量
export PGSQL_HOME=/home/postgres/pgsql/pg_9_5_4
export PGSQL_DATA=/home/postgres/pgsql/data
export PATH=${PGSQL_HOME}/bin:$PATH
export LD_LIBRARY_PATH=${PGSQL_HOME}/lib
export MANPATH=${PGSQL_HOME}/share/man:$MANPATH
使用pg_basebackup从主库备份出基础数据库到数据目录
pg_basebackup -h 192.168.xxx.xxx -U repuser -F p -P -x -R -D ${PGSQL_DATA} -l repuser_backup201705101440
查看${PGSQL_DATA}是否有数据文件生成
修改从数据库配置文件postgresql.conf开启standby模式
hot_standby = on
启动standby数据库,并验证主从是否同步更新
pg_ctl -D ${PGSQL_DATA} start
主库建表:
create table teststandby(id int);
\dt
从库查看表是否新建:
\dt
https://download.csdn.net/download/weixin_38598071/10436569
注意:感谢大家支持,转载请务必注明引用。