Chapter 10. Replication and External Data
PostgreSQL has a number of options for sharing data with external
servers or data sources. The first option is the built-in replication options of PostgreSQL, which allow you to create a
copy of your server ready to run on another PostgreSQL server. The second
option is to use third-party add-ons, many of which are freely available and
time-tested. The third option is to use a foreign data wrapper (FDW). FDWs
give you the flexibility to query from a wide array of external data
sources. Since version 9.3, some FDWs also permit updating: these include
(see “Querying Other Tabular Formats with ogr_fdw”).
The reasons for replicating your databases distill down to two: availability and scalability. Availability is assured by providing a redundant server so that, if your main server goes down, you have another that can immediately assume its role. For small databases, you could just make sure you have another physical server ready and restore the database onto it. But for large databases (in the terabytes), the restore itself could take hours, if not days. To avoid downtime, you’ll need to replicate.
The other motivation for replications is scalability. Suppose you set up a database to breed fancy elephant shrews for profit. After a few years of breeding, you now have thousands of elephant shrews. People all over the world come to your site to gawk and purchase. You’re overwhelmed by the ...