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 allows 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, unveiled in version 9.1, is to use foreign data
wrappers (FDW). FDWs gives you the flexibility to query from a
wide array of external data sources. Since version 9.3, some FDWs such as
also permit updating.
The seemingly countless reasons for the need to replicate your databases all distill down to two: availability and scalability. If your main server goes down, you want another to 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 many hours. To avoid downtime, you’ll need to replicate. The other main reason is scalability. You set up a database to breed fancy elephant shrews for profit. After a few years of breeding, you now have thousands of shrews. People all over the world come to your site to gawk and purchase. You’re overwhelmed by the traffic. Replication comes to your aid; you set up a read-only slave server to replicate with your main server. You direct ...