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

2 Answers 2

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 Allred 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 Allred 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

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.

share|improve this answer
    
Hi, and welcome to DB.SE! Thanks for the info. Unfortunately, I very much don't have the test environment anymore, so I can't truly give a tick unless your question would be exactly the same as mine (same commands, same output, same everything) so I'm hesitant to do so. As for the last paragraph, your best bet is to ask a new question and provide a link back to this one. Best of luck! –  Sean Allred Nov 4 at 4:52
    
Thanks. I'll let others who stumble on this provide feedback. Cheers. –  imoverclocked Nov 4 at 5:28

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.