欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

PostgreSQL Stream 配置

程序员文章站 2022-04-30 19:00:42
...

一、安装配置数据库(master\slave)系统信息:192.168.1.20 gserver20(master)192.168.1.21 gserver21(slave)1.建立目录mkdir /

一、安装配置数据库(master\slave)
系统信息:
192.168.1.20 gserver20(master)
192.168.1.21 gserver21(slave)
1.建立目录
mkdir /opt/pgsql-9.3.1
mkdir /opt/pgdata/main
mkdir /opt/pgdata/archive

2.建立postgres用户
adduser postgres
chown -R postgres /opt/pgdata/
3.设置密码
# passwd postgres
Changing password for user postgres.
New password:
BAD PASSWORD: it is too simplistic/systematic
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.
#
4.设置用户环境变量
su - postgres
vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export PGHOME=/opt/pgsql-9.3.1
export PGDATA=/opt/pgdata/main
export PATH=$PGHOME/bin:$PATH:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
[postgres@gserver21 ~]$
5.安装postgresql
./configure --prefix=/opt/pgsql-9.3.1
gmake
su
gmake install

二、配置stream replication
1.master
1)切换到postgres用户
$su - postgres
2)初始化数据库
$initdb
3)配置pg_hba.conf
在# IPv4 local connections下面添加一行,设置PostgreSQL的访问及其权限
host all all 192.168.111.1/24 trust
在# replication privilege.下面添加一行,设置replication用户及权限
host replication postgres 192.168.111.1/24 trust
4)配置postgresql.conf
配置监听,修改listen_addresses = 'localhost'
listen_addresses = '*'# what IP address(es) to listen on;
配置Primary Replication参数
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
archive_mode = on
archive_command = 'cp %p /opt/pgdata/archive/%f "/opt/pgdata/archive"是Replication的archive的存储路径。PostgreSQL会将Replication的WAL保存在 "/opt/pgdata/archive"路径下。
5) 启动Primary上的PostgreSQL数据库
$pg_ctl start
6) 在primary上执行以下命令
$psql -c "SELECT pg_start_backup('label', true)"
将Primary的PGDATA目录下的文件,除了postmaster.pid复制到Standby节点的“/opt/pgdata/main”目录下,该目录是 Standby节点上的
PostgreSQL数据库的PGDATA目录。
$rsync -a ${PGDATA}/ postgres@192.168.1.21:/opt/pgdata/main --exclude postmaster.pid
$psql -c "SELECT pg_stop_backup()"
2.standby
su - postgres
PGDATA=/opt/pgdata/main
Standby节点的PGDATA路径就是Primary节点的PGDATA的副本
1)配置postgresql.conf
设置hot_standby为
hot_standby= on
2)编辑recovery.conf,文件路径为$(PGDATA)/recovery.conf,,内容为
---------------------------------------------------------------------------------------------------------------------------
# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode='on'
# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo= 'host=192.168.1.20 port=5432 user=postgres'
# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/opt/pgdata/trigger'
# Specifies a command to load archive segments from the WAL archive. If
# wal_keep_segments is a high enough number to retain the WAL segments
# required for the standby server, this may not be necessary. But
# a large workload can cause segments to be recycled before the standby
# is fully synchronized, requiring you to start again from a new base backup.
restore_command = 'cp /opt/pgdata/archive/%f %p'
--------------------------------------------------------------------------------------------------------------------------
3)复制pg_xlog下的所有文件到/opt/pgdata/archive目录下
cd /opt/pgdata/main/pg_xlog
mv * ../../archive
4)启动standby节点,完成Replication
pg_ctl start
3.完成Streaming Replication配置。

PostgreSQL 的详细介绍:请点这里
PostgreSQL 的下载地址:请点这里

PostgreSQL缓存详述

Windows平台编译 PostgreSQL

Ubuntu下LAPP(Linux+Apache+PostgreSQL+PHP)环境的配置与安装

Ubuntu上的phppgAdmin安装及配置

PostgreSQL Stream 配置