postgresql安装及Streaming Replication配置1P2S

这篇记录文档是因为项目需要搭建postgres环境,并要求具有一定的可靠性.所以我在搭建这个环境的同时把步骤及命令记录下来的.感兴趣的朋友基本上复制粘贴这些命令就可以完成Streaming Replication的环境建设.因为我并没有为每个命令的目的性进行解释,所以为了明确每个命令的目的性,我并没有把一些很简单的命令放到一起执行,文档中一行就是一个命令.

我是DB2 DBA.但现在项目准备从DB2迁移到postgresql. postgresql我也是刚刚接触.我以后会把我学到的关于postgresql的知识,以及DB2迁移postgresql过程中遇到的问题及经验总结出来,陆续整理成文档. 然后和有同样需求的朋友进行交流.也希望已有这方面经验的朋友多多指教.

-------------------------------------------------------
>>>>>>>>>INSTALL<<<<<<<<<<<<<
--primary 10.4.5.94
--standby 10.4.5.93
--standby 10.4.5.91

psql (PostgreSQL) 9.0.4

                           by Paolo
-------------------------------------------------------
cd /root/postgresql-9.0.4
./configure --with-wal-segsize=32 --with-wal-blocksize=16
gmake
gmake install
adduser postgres
mkdir -p /usr/local/pgsql/data
mkdir -p /usr/local/pgsql/etc
chown postgres /usr/local/pgsql/data
chown postgres /usr/local/pgsql/etc
chown postgres /pg_data_logs
cd /pg_data_logs/
mkdir pg_xlog
chown postgres pg_xlog/
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --xlogdir=/pg_data_logs/pg_xlog
mv /usr/local/pgsql/data/*.conf /usr/local/pgsql/etc
exit (su - root)
cp /root/postgresql-9.0.4/contrib/start-scripts/linux /etc/init.d/postgresd
vi /etc/init.d/postgresd  修改如下部分,用-c config_file指定postgresql.conf的位置:
===============================================================
  start)
        echo -n "Starting PostgreSQL: "
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
        su - $PGUSER -c "$DAEMON -D '$PGDATA' -c config_file=/usr/local/pgsql/etc/postgresql.conf &" >>$PGLOG 2>&1
        echo "ok"
        ;;
  restart)
        echo -n "Restarting PostgreSQL: "
        su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"
        test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj
        su - $PGUSER -c "$DAEMON -D '$PGDATA' -c config_file=/usr/local/pgsql/etc/postgresql.conf &" >>$PGLOG 2>&1
        echo "ok"
        ;;
===============================================================

vi /usr/local/pgsql/etc/postgresql.conf  修改如下部分:
===============================================================
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'           # use data in another directory
                                        # (change requires restart)
hba_file = '/usr/local/pgsql/etc/pg_hba.conf'     # host-based authentication file
                                        # (change requires restart)
ident_file = '/usr/local/pgsql/etc/pg_ident.conf' # ident configuration file
                                        # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'           # write an extra PID file
                                        # (change requires restart)
===============================================================

/etc/init.d/postgresd start

-------------------------------------------------------
>>>>>>>>>Streaming Replication<<<<<<<<<<<<<
-------------------------------------------------------
--IN ALL SERVER:
修改访问控制
vi /usr/local/pgsql/etc/pg_hba.conf
最后加一行
host all all 10.4.5.0/24 password
host all all 10.4.2.0/24 password

修改监听范围
vi /usr/local/pgsql/etc/postgresql.conf
修改listen_addresses = ‘localhost’为listen_addresses = ‘*’，如果前面有#号则需要删除#号

重启
/etc/init.d/postgresd restart

--IN PRIMARY SERVER:
设置同步账号
psql
create user repl superuser login password 'meiyoumima';

修改访问控制
vi /usr/local/pgsql/etc/pg_hba.conf
最后添加以下内容
host replication repl 10.4.5.93/32 password
host replication repl 10.4.5.91/32 password

修改postgresql服务配置文件
vi /usr/local/pgsql/etc/postgresql.conf
####Add by paolo for replications
wal_level = hot_standby
archive_mode = on
archive_command = 'cp -i %p /pg_data_logs/archivedir/%f #archive_timeout = 600
archive_timeout = 86400
max_wal_senders = 5
wal_keep_segments = 32

建立归档目录
mkdir -p /pg_data_logs/archivedir

重启
/etc/init.d/postgresd restart

--IN STANDBY SERVER:
修改postgresql服务配置文件
vi /usr/local/pgsql/etc/postgresql.conf
#Add by paolo for replications
wal_level = hot_standby
hot_standby = on

vi /usr/local/pgsql/etc/recovery.conf
#Add by paolo for replications
restore_command = 'cp /pg_data_logs/archivedir/%f %p'
archive_cleanup_command = 'pg_archivecleanup /pg_data_logs/archivedir %r'
standby_mode = 'on'
primary_conninfo = 'host=10.4.5.94 port=5432 user=repl password=meiyoumima'
trigger_file = '/home/postgres/trigger_activestb'

建立归档目录
mkdir -p /pg_data_logs/archivedir

停止postgres
/etc/init.d/postgresd stop

删除原数据目录下数据文件
exit (su - root)
cd /usr/local/pgsql/
rm -rf data/
mkdir data
chown postgres data
chmod -R 700 data/

>>>>>>>>>>>>>>传送数据文件到StandBy并启动集群<<<<<<<<<<<<<<<<<
--IN PRIMARY
su - postgres
psql -c "SELECT pg_start_backup('label',true);"
cd /usr/local/pgsql/
scp -r data/
scp -r data/

--IN STANDBY
su - postgres
cd /usr/local/pgsql/data
rm postmaster.pid
ln -s /usr/local/pgsql/etc/recovery.conf recovery.conf
cd pg_xlog
mv * /pg_data_logs/archivedir/
/etc/init.d/postgresd start

--IN PRIMARY
su - postgres
psql -c "SELECT * from pg_stop_backup();"

重启
/etc/init.d/postgresd restart

-------------------------------------------------------
>>>>>>>>>pg_archivecleanup inatall<<<<<<<<<<<<<
-------------------------------------------------------
su - root
cd postgresql-9.0.4/contrib/pg_archivecleanup/
make
make install