Postgresql – Setting up binary replication between two PostgreSQL instances

postgresqlpostgresql-9.2replication

I have two virtual machines: dbrepa (-.175) and dbrepb (-.191).
Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on dbrepa, dbrepb was made as a clone.
I need to have the databases on dbrepb be read-only copies of those on dbrepa.

And so, I set the appropriate settings of dbrepa/postgresql.conf:

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3 # Aside, I assume '3' is arbitrary

and dbrepa/pg_hba.conf (paying attention to spacing):

host   replication   all   192.168.108.191/32   trust

and dbrepb/postgresql.conf:

hot_standby = on

and dbrepb/recovery.conf (a new file):

standby_mode = 'on'
primary_conninfo = 'host=192.168.108.175'

Each database is empty, but the following command was run on both:

[root@dbrep-]# service postgresql-9.2 initdb

Now, since there are no databases, I don't need to worry if they are out of sync (since there is no data to be out of sync).
I start the master:

[root@dbrepa]# service postgresql-9.2 start
Starting postregsql-9.2 service:   [  OK  ]

and I start the standby:

[root@dbrepb]# service postgresql-9.2 start
Starting postregsql-9.2 service:   [FAILED]

Examining the log files of each:

dbrepa/data/pg_log/-.log is empty.

dbrepb/data/pg_log/-.log:

LOG:  entering standby mode
WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server
HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.
LOG:  startup process (PID 4320) exited with exit code 1
LOG:  aborting startup due to startup process failure

but when I double-check dbrepa/data/postgres.conf, wal_level is clearly set to hot_standby.

What else could be going wrong?

Best Answer

Your last two steps are in the wrong order.

The appropriate steps says

  • _ 5. Start the standby first, so that they can't get out of sync. (Messages will be logged about not being able to connect to the primary server, that's OK.)
  • _6. Start the master.

You are supposed to start the master after the standby.

Try reversing everything as follows:

  • [root@dbrepa]# service postgresql-9.2 stop
  • [root@dbrepb]# service postgresql-9.2 stop
  • rsync everything from Master (except the pg_xlog and config files (Your Step 4)) to Standby
  • [root@dbrepb]# service postgresql-9.2 start
  • [root@dbrepa]# service postgresql-9.2 start

Give it a Try !!!

Related Question