Tell me more ×
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.
up vote 5 down vote favorite
1

I'm new in PostgreSQL database. Recently our developer needed to do some upgrades in our systems.

Because of that we are planning to implement some method in order to implement database failover.

Based on my reading from postgresql wiki here, we are trying to implement either warm standby or hot standby. So my questions are:

  1. What are major differences between them?
  2. Which one is better?
  3. Is there any other method that we can consider for making high availability in our Postgres databases?
share|improve this question
A proper heartbeat + STONITH setup is key if you plan on using automatic failover. Automated failover with a manual trigger may be safer. See also wiki.postgresql.org/wiki/High_Availability – Craig Ringer Oct 9 '12 at 6:00
@CraigRinger thanks.i will look into that.But what actually is the warm and hot standby?can u give some details? – user119720 Oct 9 '12 at 6:23

2 Answers

up vote 6 down vote accepted

1a. Warm standby is a "live", incremental backup fed with complete blocks of changes (wal segments) 16 mb each, that are send to the standby node once they're filled. You cannot query a warm standby node. 16 mb of changes (by default) can mean a lot of transactions, should the master fail, they'll be lost.

1b. Hot Standby. (also a "live" incremental backup) .small changes are send to the slave (wal records, which are tiny parts of a wal segment). You can query (read only) the hot standby node. The window for lost transactions should the master fail is very tiny. There are synchronous and asynchronous hot standby nodes, a synchronous node will force the master to wait for it to confirms the application of changes and then the master will commit the transaction.In asynchronous replication the master sends the wal records and does not wait for confirmation. The former requires a very reliable and fast link between the master and the slave,also adds overhead to the master but guarantees no data loss.

Regarding incremental backups: 1. You take a base copy of your whole database installation. 2. Ship it to the slave. 3. Configure it to catch up for changes.

Streaming Replication (hot standby) is the winner here. I personally prefer asynchronous replication as it does not impose a considerable burden on the master and the replication lag is very small (a couple of seconds in many cases)

One complement to this setup is pg-pool. It acts as a proxy between the application and the servers participating in a replication configuration like the one described above, it has load balancing and parallel query capabilities. It's able also to provide automatic failover. http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting/index.html

share|improve this answer
I really appreciate your swift reply which is really helpful to my requirement.can you also please recommend me any right links to achieve this setup? – user119720 Oct 9 '12 at 7:09
thanks.you're sure helping me a lot. =) – user119720 Oct 9 '12 at 7:30
You're welcome.The learning curve in these matters is kind of steep, just be patient. Good night (day or whatever) greetings from Mexico City. – Rene Romero Benavides Oct 9 '12 at 7:38
I have do some research based on your links and there are questions that have come up on my mind. 1. Does warm standby can be configured for streaming replication? 2. Does pg-pool can be configured for warm standby? 3. If we have configured our apps server point to our primary database during failover, do we need to change the database configuration of apps server to slave database or pg-pool itself will act as proxy to the slave? Sorry for the trouble.Hope you don't mind. – user119720 Oct 10 '12 at 3:18
up vote 1 down vote

The answer you've already gotten is useful but confusing terms a bit here. All of the built-in replication solutions use the same basic mechanism: copying write-ahead log data over to a standby server.

You can move that WAL data for replication either a 16MB file at a time, using the archive_command facility, or using Streaming Replication (SR). If using SR, you really should setup archiving too, and the server will switch between them as appropriate.

You can have a warm standby server, which cannot answer queries. Or you can have a hot standby server, which can answer read-only ones. This is unrelated to how the data is getting onto the standby.

Each of these two choices combines with each of the others, and you can have all four combinations. You can have a Hot Standby answering queries while being fed with file at a time WAL segments. You can have a Streaming Replication server that does not have Hot Standby enabled, so it will not answer queries. It's just that the most common case, nowadays, is both Streaming Replication plus Hot Standby. That's the full feature set. Again, don't ignore the old archive_command mechanism just because it's possible to avoid now. It can still save you from streaming failures that are otherwise hard to recover from.

share|improve this answer

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.