PostgreSQL安裝及Streaming Replication配置詳解
作者：靜水深流
本文我們主要介紹了PostgreSQL安裝及Streaming Replication配置方法，希望本次的介紹能夠?qū)δ兴鶐椭?/div> 
分享到微信 
分享到微博
PostgreSQL安裝及Streaming Replication配置是本文我們主要要介紹的內(nèi)容，因?yàn)轫?xiàng)目需要搭建postgres環(huán)境，并要求具有一定的可靠性。所以筆者在搭建這個(gè)環(huán)境的同時(shí)把步驟及命令記錄下來(lái)的。筆者是DB2 DBA.但現(xiàn)在項(xiàng)目準(zhǔn)備從DB2遷移到postgresql. postgresql筆者也是剛剛接觸.筆者以后會(huì)把學(xué)到的關(guān)于postgresql的知識(shí)，以及DB2遷移postgresql過(guò)程中遇到的問(wèn)題及經(jīng)驗(yàn)總結(jié)出來(lái)，陸續(xù)整理成文檔.，然后和有同樣需求的朋友進(jìn)行交流，希望能夠?qū)δ兴鶐椭?/p>
- -------------------------------------------------------
- >>>>>>>>>INSTALL<<<<<<<<<<<<<
- --primary 10.4.5.94
- --standby 10.4.5.93
- --standby 10.4.5.91
- psql (PostgreSQL) 9.0.4
- -------------------------------------------------------
- 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:
- 修改訪問(wèn)控制
- 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
- 修改監(jiān)聽(tīng)范圍
- vi /usr/local/pgsql/etc/postgresql.conf
- 修改listen_addresses = ‘localhost’為listen_addresses = ‘*’，如果前面有#號(hào)則需要?jiǎng)h除#號(hào)
- 重啟
- /etc/init.d/postgresd restart
- --IN PRIMARY SERVER:
- 設(shè)置同步賬號(hào)
- psql
- create user repl superuser login password 'meiyoumima';
- 修改訪問(wèn)控制
- vi /usr/local/pgsql/etc/pg_hba.conf
- ***添加以下內(nèi)容
- host replication repl 10.4.5.93/32 password
- host replication repl 10.4.5.91/32 password
修改postgresql服務(wù)配置文件
- 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 </dev/null'
- #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服務(wù)配置文件
- 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
刪除原數(shù)據(jù)目錄下數(shù)據(jù)文件
- exit (su - root)
- cd /usr/local/pgsql/
- rm -rf data/
- mkdir data
- chown postgres data
- chmod -R 700 data/
- >>>>>>>>>>>>>>傳送數(shù)據(jù)文件到StandBy并啟動(dòng)集群<<<<<<<<<<<<<<<<<
- --IN PRIMARY
- su - postgres
- psql -c "SELECT pg_start_backup('label',true);"
- cd /usr/local/pgsql/
- scp -r data/ postgres@10.4.5.93:/usr/local/pgsql/
- scp -r data/ postgres@10.4.5.91:/usr/local/pgsql/
- --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
關(guān)于PostgreSQL安裝及Streaming Replication配置就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！
【編輯推薦】
責(zé)任編輯：趙鵬
來(lái)源：
ChinaUnix博客
分享到微信 分享到微博相關(guān)推薦
2009-06-19 18:19:01
2009-07-09 15:58:40
