Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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 !!!

share|improve this answer
    
I'll try, but I did this the first few times I've gone through this. I'll let you know. Edit: Same result. dbrepb fails while dbrepa succeeds. –  Sean Aug 9 '13 at 17:35
    
Your link says when starting the standby first: Messages will be logged about not being able to connect to the primary server, that's OK. –  RolandoMySQLDBA Aug 9 '13 at 17:43
    
Then why is the service start failing? –  Sean Aug 9 '13 at 17:55
    
It's probably the logs in pg_xlog. Try shutting down again, rsync evening INCLUDING pg_xlog, and try it again. DISCLAIMER: I am not a PostgreSQL admin. Hopefully, someone with the proper expertise will eventually chime in. –  RolandoMySQLDBA Aug 9 '13 at 18:10
    
According to wiki.postgresql.org/wiki/Hot_Standby, it is suggested to use pg_basebackup to copy stuff to the standby. Try that instead of an rsync. –  RolandoMySQLDBA Aug 9 '13 at 18:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.