环境：Ubuntu 12.04.4 LTS \n \l

master：192.153.167.136

slave：192.153.167.137

pg: postgresql-9.2.4-1-linux-x64.run

安装省略

1、在master 备份postgresql.conf文件并做修改，其修改主要字段如下：
port = 5432

wal_level = hot_standby

checkpoint_segments = 16

archive_mode = on

max_wal_senders = 3
wal_keep_segments = 16

2、创建复制用户

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 4 ENCRYPTED PASSWORD ‘123’;

3、设置 pg_hba.conf访问权限，添加以下

IPv4 local connections:

host all all 192.153.167.137/24 md5
#host replication postgres ::1/128 md5
host replication repuser 192.153.167.137/24 md5

4、启动库并查看表空间

/etc/init.d/postgresql-9.2 restart

cyyun=# \db
List of tablespaces
Name | Owner | Location
------------±---------±------------------
fastspace | postgres | /data1/tablespace
pg_default | postgres |
pg_global | postgres |
(3 rows)

5、在master上查看数据目录，先查看表空间目录和数据目录，因为这些目录需要在备库主机上手工创建

echo $PGDATA
/opt/PostgreSQL/9.2/data

6、在slave库上面创建目录并且授权

mkdir -p /data/psql

mkdir -p /data1/tablespace

chown postgres. /data/psql

chown postgres. /data1/tablespace

chmod 700 /data/psql

7、在slave上面创建.pgpass

touch .pgpass

chmod 600 .pgpass

[email protected]:~$ cat .pgpass
192.153.167.136:3500:replication:repuser:123

8、
先删除从库的data目录
使用 pg_basebackup 生成备库

[email protected]:~$ pg_basebackup -D /usr/local/pgsql/data -Fp -Xs -v -P -h 192.168.163.182 -p 5432 -U repuser
transaction log start point: 0/3000020
pg_basebackup: starting background WAL receiver
25769/25769 kB (100%), 2/2 tablespaces
transaction log end point: 0/30000E0
pg_basebackup: waiting for background process to finish streaming…
pg_basebackup: base backup completed
9、设置从库 postgresql.conf
hot_standby = on
10、创建 recovery.conf
[email protected]:~$ cat /data/psql/recovery.conf|tail -5
#---------------------------------------------------------------------------

standby_mode = on
primary_conninfo = ‘host=192.153.167.136 port=3500 user=repuser’
trigger_file = ‘/data/psql/postgresql.trigger.3500’

11、修改启动脚本 /etc/init.d/postgresql-9.2里对应的data和log目录改成新的目录/data/psql下的

12、启动

/etc/init.d/postgresql-9.2 start

13、查看slave进程及log

[email protected]:~$ ps aux |grep postgres
postgres 19904 0.0 0.0 37280 1476 pts/0 S 17:09 0:00 su - postgres
postgres 19905 1.0 0.0 21252 3864 pts/0 S 17:09 0:00 -su
postgres 20002 0.2 0.0 90124 8080 pts/0 S 17:10 0:00 /opt/PostgreSQL/9.2/bin/postgres -D /data/psql
postgres 20003 0.0 0.0 56380 1220 ? Ss 17:10 0:00 postgres: logger process
postgres 20004 0.1 0.0 90156 1864 ? Ss 17:10 0:00 postgres: startup process recovering 000000010000000000000004
postgres 20005 0.0 0.0 90124 1436 ? Ss 17:10 0:00 postgres: checkpointer process
postgres 20006 0.1 0.0 90124 1440 ? Ss 17:10 0:00 postgres: writer process
postgres 20007 0.0 0.0 58524 1304 ? Ss 17:10 0:00 postgres: stats collector process
postgres 20008 0.5 0.0 105324 2788 ? Ss 17:10 0:00 postgres: wal receiver process streaming 0/4000280
postgres 20073 0.0 0.0 15268 1220 pts/0 R+ 17:10 0:00 ps aux
postgres 20074 0.0 0.0 7844 936 pts/0 S+ 17:10 0:00 grep --color=auto postgres

[email protected]:/data/psql$ tailf pg_log/postgresql-2015-02-03_171003.log

2015-02-03 17:10:03 CST LOG: database system was interrupted; last known up at 2015-02-03 16:49:25 CST
2015-02-03 17:10:03 CST LOG: creating missing WAL directory “pg_xlog/archive_status”
2015-02-03 17:10:03 CST LOG: entering standby mode
2015-02-03 17:10:03 CST LOG: redo starts at 0/3000020
2015-02-03 17:10:03 CST LOG: consistent recovery state reached at 0/30000E0
2015-02-03 17:10:03 CST LOG: database system is ready to accept read only connections
2015-02-03 17:10:04 CST LOG: streaming replication successfully connected to primary

14、查看master进程

[email protected]:/data1/tablespace/PG_9.2_201204301# ps aux |grep postgres
postgres 14446 0.0 0.0 90736 8096 pts/1 S 16:14 0:00 /opt/PostgreSQL/9.2/bin/postgres -D /opt/PostgreSQL/9.2/data
postgres 14447 0.0 0.0 57072 1188 ? Ss 16:14 0:00 postgres: logger process
postgres 14449 0.0 0.0 90860 2296 ? Ss 16:14 0:00 postgres: checkpointer process
postgres 14450 0.0 0.0 90736 1384 ? Ss 16:14 0:00 postgres: writer process
postgres 14451 0.0 0.0 90736 1380 ? Ss 16:14 0:00 postgres: wal writer process
postgres 14452 0.0 0.0 91552 2636 ? Ss 16:14 0:00 postgres: autovacuum launcher process
postgres 14453 0.0 0.0 59168 1212 ? Ss 16:14 0:00 postgres: archiver process last was 000000010000000000000003
postgres 14454 0.0 0.0 59328 1460 ? Ss 16:14 0:00 postgres: stats collector process
postgres 18964 0.0 0.0 64152 1976 pts/1 S 16:36 0:00 su - postgres
postgres 18965 0.0 0.0 29852 7940 pts/1 S 16:36 0:00 -su
postgres 20912 0.0 0.0 15512 1464 pts/1 S+ 16:43 0:00 /bin/bash /opt/PostgreSQL/9.2/bin/psql cyyun
postgres 20914 0.0 0.0 55216 2936 pts/1 S+ 16:43 0:00 /opt/PostgreSQL/9.2/bin/psql.bin cyyun
postgres 20947 0.0 0.0 92764 6068 ? Ss 16:43 0:00 postgres: postgres cyyun [local] idle
postgres 27512 0.0 0.0 91568 2836 ? Ss 17:09 0:00 postgres: wal sender process repuser 59.53.67.37(44583) streaming 0/4000828
root 28200 0.0 0.0 10424 932 pts/0 S+ 17:14 0:00 grep --color=auto postgres

到此主从搭建完成。

常见报错 ：

1、[email protected]:~$ pg_basebackup -D ?/usr/local/pgsql/data -Fp -Xs -v -P -h 192.168.163.182 -p 5432 -U repuser?
pg_basebackup: could not connect to server: could not connect to server: Connection refused
Is the server running on host “192.168.163.182” and accepting
TCP/IP connections on port 5432?

解决办法:
在主从数据库上面查看日志cat logfile


前面添加#，注释掉，127.0.0.1表示本机

查看本机的端口号，然后kill-9 端口号。重新启动数据库服务。