I have two virtual machines:
Both have PostgreSQL 9.2 installed; once PG was installed (never initialized) on
dbrepb was made as a clone.
I need to have the databases on
dbrepb be read-only copies of those on
And so, I set the appropriate settings of
listen_address = '*' wal_level = hot_standby max_wal_senders = 3 # Aside, I assume '3' is arbitrary
dbrepa/pg_hba.conf (paying attention to spacing):
host replication all 192.168.108.191/32 trust
hot_standby = on
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.
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
wal_level is clearly set to
What else could be going wrong?
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 !!!
I know this is an old question but I think I just found an answer. If you modify the sample config (
initdb -D /tmp/foo -s | grep POSTGRESQL_CONF_SAMPLE) and set
wal_level=hot_standby in the sample config, then the base and WAL files will be created appropriately and Postgres will start and try to connect to the master DB.
I haven’t figured out how to pass this to
initdb directly yet. I’ve tried
PGOPTIONS="-c wal_level=hot_standby" and many similar invocations without success. YMMV.