Postgresql – Creating a hot standby with Postgres 9.6

postgresql

I'm trying to create a hot standby with Postgres 9.6 on Ubuntu 16.04.

I can successfully ssh from my standby machine to the primary as postgres.

I have removed /var/lib/postgresql/9.6/main and running:

pg_basebackup -h <primary IP> -D /var/lib/postgresql/9.6/main -U rep -v -P --xlog-method=stream

that successfully creates a new main directory.

In /etc/postgresql/9.6/main/postgresql.conf, I have:

  • added this servers IP address to the listen_addresses

  • set wal_level = replica

  • set hot_standby = on

I've created a recovery.conf with:

standby = on

primary_conninfo = 'host=<primary ip> port=5432 user=rep password=<password>

On sudo service postgres start, postgres starts and I see:


2016-12-22 07:24:29 UTC [10728-1] LOG: database system was interrupted; last known up at 2016-12-22 07:13:44 UTC
2016-12-22 07:24:30 UTC [10728-2] LOG: redo starts at 3/4D000028
2016-12-22 07:24:30 UTC [10728-3] LOG: consistent recovery state reached at 3/4D000130
2016-12-22 07:24:30 UTC [10728-4] LOG: redo done at 3/4D000130
2016-12-22 07:24:30 UTC [10728-5] LOG: MultiXact member wraparound protections are now enabled
2016-12-22 07:24:30 UTC [10732-1] LOG: autovacuum launcher started
2016-12-22 07:24:30 UTC [10727-4] LOG: database system is ready to accept connections
2016-12-22 07:24:30 UTC [10734-1] [unknown]@[unknown] LOG: incomplete startup packet

Should I not see a message where it tries to contact the master?

Best Answer

2016-12-22 07:24:30 UTC [10728-3] LOG:  consistent recovery state reached at 3/4D000130
2016-12-22 07:24:30 UTC [10728-4] LOG:  redo done at 3/4D000130
2016-12-22 07:24:30 UTC [10728-5] LOG:  MultiXact member wraparound ?protections are now enabled
2016-12-22 07:24:30 UTC [10732-1] LOG:  autovacuum launcher started
2016-12-22 07:24:30 UTC [10727-4] LOG:  database system is ready to accept connections

Looks like your recovery.conf file has ignored. Are you shure that the recovery.conf has the right permissions (basically owned by the user that run the service, usually postgres)?

Related Question