PostgreSQL: Up and Running

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 PostgreSQL’s own built-in replication, which allows you to have a readied copy of your server on another PostgreSQL server. The second option, unveiled in 9.1, is the Foreign Data Wrapper, which allows you to query and copy data from many kinds of external data resources utilizing the SQL/Management of External Datasource (MED) standard. The third option is to use third-party add-ons, many of which are freely available and time-tested.

Replication Overview

You can probably enumerate countless reasons for the need to replicate, but they all boil 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 (say, in the terabytes), the restore itself could take many hours. To avoid the downtime, you’ll need to replicate. The other main reason is for scalability. You set up a database to handle your collection of fancy elephant beetles. After a few years of unbridled breeding, you now have millions of fancy elephant beetles. People all over the world now come to your site to check out the beetles. 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. People ...