Replicating a PostGIS database with streaming replication

The reality of the world is that, given enough time, everything will break. This includes the hardware and software of computers running PostgreSQL. To protect data in PostgreSQL from corruption or loss, backups are taken using tools such as pg_dump. However, restoring a database backup can take a very long time during which users cannot use the database.

When downtime must be kept to a minimum or is not acceptable, one or more standby servers are used to compensate for the failed primary PostgreSQL server. The data on the standby server is kept in sync with the primary PostgreSQL server by streaming data as frequently as possible.

In addition, you are strongly discouraged from trying to mix different PostgreSQL versions. Primary and standby servers must run the same PostgreSQL version.

Getting ready

In this recipe, we will use the streaming replication capability introduced in PostgreSQL 9.0. This recipe will use one server with two...