Postgresql Master/Slaver

1.Install PostgreSql9.3

a:yum install

copy repository to: /etc/yum/yum.repos.d

yum -y install postgresql93-server postgresql93-contrib --skip-broken

yum install http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-redhat95-9.5-2.noarch.rpm

sudo yum install postgresql95-server postgresql95-contrib

2.Install postgres

yum  -y install postgresql93-server postgresql93-contrib

3.Master 配置

1.创建PG Data Dir

mkdir –p  /opt/PostgreSQL/data

mkdir –p  /opt/PostgreSQL/data/archive

chmod 700 /opt/PostgreSQL/data

sudo chown postgres:postgres /opt/PostgreSQL/data

2.设置profile

sudo vi /etc/profile

export PATH=/usr/pgsql-9.3/bin:$PATH

export PGDATA=/opt/PostgreSQL/data/

su – postgres

vi ~/.bash_profile

export PATH=/usr/pgsql-9.3/bin:$PATH

export PGDATA=/opt/PostgreSQL/data/

 

3.InitDB For master

su  -postgres
initdb --encoding=UTF-8  --username=postgres --pwprompt --pgdata=/opt/PostgreSQL/data/

4.配置文件

vi  $PGDATA_HOME/pg_hba.conf

(replace "ident" with "md5")
(#IPv4 local connections)
host    all         all         192.168.1.1/16        md5
(replace ident or peer with trust)
(#"local" is for Unix domain socket connections only)
local   all             all                                     trust
# Allow replication connections from localhost, by a user with the# replication privilege
.#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            iden
thost    replication     postgres         192.168.1.1/16          trust

vi $PGDATA_HOME/postgresql.conf

listen_addresses = '*'

port = 5432

wal_level = hot_standby

max_wal_senders = 5

wal_keep_segments = 32

archive_mode = on

archive_command = 'cp %p /opt/PostgreSQL/data/%f < /dev/null'

checkpoint_segments = 8

5.启动Master DB

su  -postgres
pg_ctl -D /opt/PostgreSQL/data/ -l logfile start

pg_ctl –D /var/lib/pgsql/9.3/data -l logfile start 
psql -c "CREATE USER postgres REPLICATION LOGIN ENCRYPTED PASSWORD 'conversant';"

$psql
postgres=# alter user postgres with password 'conversant';

4.Slave

4.1 Install Postgres

4.2 Configure Profile

4.3 Copy master data

su –postgres
pg_basebackup -h 192.168.1.12 -D /opt/PostgreSQL/data -U replicator -P -v -x

4.4 配置

vi $PGDATA_HOME/postgresql.conf

listen_addresses = '*'
hot_standby = on
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

vi $PGDATA_HOME/recovery.conf

sudo su -

cp /usr/pgsql-9.3/share/recovery.conf.sample  /opt/PostgreSQL/data/

chown postgres:postgres  recovery.conf.sample  

mv recovery.conf.sample   recovery.conf

vi recovery.conf
primary_conninfo = 'host=192.168.1.12 port=5432 user=replicator password=conversant'
trigger_file = '/opt/PostgresSql/data/trigger'
standby_mode = 'on'

 

5.打开防火墙 5432端口

 

$sudo /sbin/iptables -I INPUT -p tcp --dport 5432 –j ACCEPT
$sudo /etc/rc.d/init.d/iptables save
$sudo /etc/init.d/iptables status

 

6.启动Slave

su  -postgres
pg_ctl -D /opt/PostgreSQL/data/ -l logfile start

1. start
pg_ctl -D /opt/PostgreSQL/data/ -l /opt/PostgreSQL/data/logfile start

2. stop
pg_ctl -D /opt/PostgreSQL/data/ -l /opt/PostgreSQL/data/logfile --mode=fast stop

3. restart
pg_ctl -D /opt/PostgreSQL/data/ -l /opt/PostgreSQL/data/logfile restart

 

7.问题列表:

1.error check sum:

Fix error: yum primary.sqlite.bz2: [Errno -3] Error performing checksum Trying other mirror.

enabled=0

2.GPG key retrieval failed: [Errno 5] OSError

[Errno 2] No such file or directory: '/etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-93'

gpgcheck=0