主备查询
主备不会自动切换（即需要实现线上环境主数据库宕掉之后，从数据库能够自动切换为主数据库，需要借用第三方软件，例如heartbeat等）
（1）如何查看是primary还是standby
- 方法1：查看进程
主机 sender
[wln@localhost data]$ ps -ef | grep wal
wln 10421 10416 0 07:52 ? 00:00:00 postgres: wal writer process
wln 10691 10416 0 08:04 ? 00:00:00 postgres: wal sender process repluser 127.0.0.1(59226) streaming 0/6011BAC
wln 10702 8927 0 08:05 pts/2 00:00:00 grep wal
备机 receiver
[wln@localhost data]$ ps -ef | grep wal
wln 10421 10416 0 07:52 ? 00:00:00 postgres: wal writer process
wln 10690 10685 3 08:04 ? 00:00:03 postgres: wal receiver process streaming 0/6011BAC
wln 10702 8927 0 08:05 pts/2 00:00:00 grep wal
- 方法2：
主机：
[wln@localhost data]$ pg_controldata | grep Database
Database system identifier: 6032728846531166188
Database cluster state: in production
Database block size: 8192
备机：
[wln@localhost standby]$ pg_controldata | grep Database
Database system identifier: 6032728846531166188
Database cluster state: in archive recovery
Database block size: 8192
主备切换(方式1)
（1）备机切换为主机
- 将主机停掉
[wln@localhost data]$ pg_ctl stop
waiting for server to shut down…. done
server stopped
- 在备机PGDATA对应目录下生成recovery.conf文件中指定的文件trigger_file = ‘/pgdata/9.1/data2/trigger_activestb’ （此时备机正常运行状态）
touch /pgdata/9.1/data2/trigger_activestb
会看到 recovery.conf 文件变为 recovery.done
- 查看原来备机日志：
LOG: trigger file found: /home/wln/standby/trigger_activestb
LOG: redo done at 0/6011C40
LOG: last completed transaction was at log time 2014-07-06 08:05:40.152147+08
LOG: selected new timeline ID: 2
LOG: archive recovery complete
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
- 登录原来的备机
[postgres@postgres ~]$ psql
psql (9.1.0)
Type “help” for help.
You are now connected to database “postgres” as user “postgres”.
postgres=# SHOW transaction_read_only
postgres-# ;
transaction_read_only
———————–
off
(1 row)
可以看出现在原备机具有读写功能。即主备切换成功
postgres=# \d
List of relations
Schema | Name | Type | Owner
——–+——+——-+——-
public | t1 | table | wln
(1 row)
postgres=# create table t2(id int);
CREATE TABLE
4、主备切换（方式2：实践过程中采用的方式）
备机中recovery.conf中设置为：
standby_mode = on
recovery_target_timeline = ‘latest’
primary_conninfo = ‘host=localhost port=5433 user=repluser password=password’
#trigger_file = ‘/home/wln/standby/trigger_activestb’ #这个注释掉
备机升为主机方式为：pg_ctl promote -D standbyPath
会看到recovery.conf过几秒后变为recovery.done 表示升级为主机成功。
