Postgres主备切换

时间:2024-01-15 22:55:32

  

主备查询

主备不会自动切换(即需要实现线上环境主数据库宕掉之后,从数据库能够自动切换为主数据库,需要借用第三方软件,例如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 表示升级为主机成功。