PostgreSQL Stream 配置

PostgreSQL Stream 配置

一、安装配置数据库（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 < /dev/null'
"/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安装及配置