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
You seem to be trying to replicate from one server to another that wasn't set up using a copy of the original server. That's why:
database system identifier differs between the primary and standby. The primary's identifier is 6022019027749040119, the standby's identifier is 6033562405193904122.
Because each newly initdb'd PostgreSQL gets a new random system identifier. When you copy an existing PostgreSQL install, it keeps the same system identifier. That's how PostgreSQL can keep track of whether one server can replay WAL from another.
You can only use physical replication if the replica is a copy (file-system level backup e.g. pg_basebackup) of the master. See the manual's detailed coverage on replication for more information.
Update:
The instructions shown above should be fine, but they're not as clear as they could be.
The standby server's data directory is supposed to be replaced by the base backup you create at step 8, if it exists in the first place.
You can't make an existing PostgreSQL instance into a standby for another without replacing its data directory. You need a copy of the master's data directory to run a standby. A common way to set that up is to take an existing standby, delete its data directory, replace it with a copy of the master's data directory, and then configure it as a replication slave. That's what I think step 8 is supposed to be doing.
Instead of doing that I think you probably used an existing data directory for the slave and tried to start it up as a replica of the master. That will not work, and will result in the errors you showed.
The main PostgreSQL documentation on replication is the recommended and primary resource for information. I suggest going there first.
You might also want to check out repmgr, which helps automate replication and failover tasks.
Best Answer
Np, PostgreSQL doesn't have any built-in facilities for automatic failover.
You have to use third-party software like Patroni.
Client failover can be managed by using a connection string that looks like