PostgreSQL: Up and Running, 2nd Edition

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 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 postgres_fdw and hadoop_fdw also permit updating.

Replication Overview

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 ...