Shulou(Shulou.com)06/01 Report--

1. HA Description:

Master:192.168.1.10

Slave:192.168.1.20

VIP: eth0:pgvip (master and slave) 192.168.1.30

PGDATA: / pgdata95

Pg_version: 9.5.2

2. Upgrade steps:

(1) upgrade the version of Slave database software

1.postgres (192.168.1.20)

Pg_ctl-D / pgdata95 stop-m fast

2.postgres (192.168.1.20)

Update postgresql soft version, do not use pg_upgrade command to upgrade, only minor version upgrade uses 9.5.2-"9.5.3"

Install the same version as the main branch, for example, 9.5.3

3.postgres (192.168.1.20)

Modify the database PATH to the latest version of the bin path, for example

# Installation prefix

Prefix=/opt/pgsql/9.5.7 (new version software catalog)

# Data directory

PGDATA= "/ pgdata95" (formerly data directory)

Start salve

Pg_ctl-D / path/to/database start

(2) stop VIP and prohibit the provision of services

4.root (192.168.1.10)

Ifconfig eth0:pgvip down

(3) Master performs checkpoints

5.db super (192.168.1.10)

Checkpoint

6.postgres (192.168.1.10)

SELECT sent_location, replay_location

FROM pg_stat_replication WHERE usename = 'rep_user'

7.postgres (192.168.1.10)

Repeat the above query until sent_location and replay_location are equal

8.postgres (192.168.1.10)

Pg_ctl-D / pgdata95 stop-m fast

(4) upgrade Slave to Master

9.postgres (192.168.1.20)

Pg_ctl-D / pgdata95 promote

(5) launch VIP to provide services to the outside world

10.root (192.168.1.20)

Ifconfig eth0:pgvip 192.168.1.30 up

(6) notify R & D or operation and maintenance to restart the database connection pool of the application

11. If necessary, notify R & D and database connection pooling that supports restarting the application.

(7) upgrade the version of Master database software

12.postgres (192.168.1.10)

Update postgresql soft version

Modify the database PATH to the latest version of the bin path, for example

# Installation prefix

Prefix=/opt/pgsql/9.5.7 (new version software catalog)

# Data directory

PGDATA= "/ pgdata95" (formerly data directory)

(8) redo Slave

13.postgres (192.168.1.10)

Clear the database data directory, rm-Rf / pgdata95

14.postgres (192.168.1.10)

Pg_basebackup-U rep_user-h 192.168.1.20-D / pgdata95-checkpoint=fast-xlog-method=stream-R

(IX) launch a new Slave

15.postgres (192.168.1.10)

Pg_ctl-D / path/to/database start

Mode of use

The above upgrades are best carried out if the major version of the database is the same.

Such as slave (9.5.2) upgrade to (10.0.beta1), pg_upgrade upgrade method

Slave log error:

2017-05-23 00 database system identifier differs between the primary and standby 09 CST [10078] database system identifier differs between the primary and standby

2017-05-23 00 The primary's identifier is 0919 CST [10078] DETAIL: The primary's identifier is 6417769161397888214, the standby's identifier is 6422989582

707312045.

2017-05-23 00 database system identifier differs between the primary and standby 09 CST [10098] database system identifier differs between the primary and standby

2017-05-23 00 The primary's identifier is 0940 CST [10098] DETAIL: The primary's identifier is 6417769161397888214, the standby's identifier is 6422989582

707312045.

If you encounter this situation, you need to stop the external service of Master database, then upgrade Master to a large version, and then do Slave solution again.

If slave (9.5.2) upgrades to (9.5.3)

Can be upgraded smoothly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.