Before we get too carried away with replication, we better lay down some common terminology used in replication.

When you set up replication, the additional servers can be on the same physical hardware running on a different port or one on the cloud halfway around the globe. Prior to 9.0, PostgreSQL only offered asynchronous warm slaves. A warm slave will retrieve WAL and keep itself in sync but will not be available for query. It acted only as a standby. Version 9.0 introduced asynchronous hot slaves and also streaming replication where users can execute read-only queries against the slave and replication can happen without direct file access between the servers (using database connections for shipping logs instead). Finally, with 9.1, synchronous replication became a reality. In 9.2, Cascading Streaming Replication was introduced. The main benefit of Cascading Streaming Replication is to reduce latency. It’s much faster for a slave to receive updates from a nearby slave than from a master far far away. Built-in replication relies on WAL shipping to perform the replication. The disadvantage is that your slaves need to have the same version of PostgreSQL and OS installed to ensure faithful execution of the received logs.

In addition to the built-in replication, common third party options abound. Slony and Bucardo are two of the most popular open source ones. Although PostgreSQL is improving replication with each new release, Slony, Bucardo, and other third-party replication options still offer more flexibility. Slony and Bucardo will allow you to replicate individual databases or even tables instead of the entire server. As such, they don’t require that all masters and slaves be of the same PostgreSQL version and OS. Both also support multi-master scenarios. However, both rely on additional triggers to initiate the replication and often don’t support DDL commands such as creating new tables, installing extensions, and so on. This makes them more invasive than merely shipping logs. Postgres-XC, still in beta, is starting to gain an audience. Postgres-XC is not an add-on to PostgreSQL; rather. it’s a completely separate fork focused on providing a write-scalable, multi-master symmetric cluster very similar in purpose to Oracle RAC. To this end, the raison d’etre of Postgres-XC is not replication, but distributed query processing. It is designed with scability in mind rather than high availability.

We urge you to consult a comparison matrix of popular third-party options here: http://wiki.postgresql.org/wiki/Replication%2C_Clustering%2C_and_Connection_Pooling.

The basic steps for setting up the master server are as follows:

To minimize headaches, slaves should have the same configuration as the master, especially if you’ll be using them for failover. In addition to those configurations, in order for it to be a slave, it needs to be able to play back the WAL transactions of the master. So, you need at least the following settings in postgresql.conf of a slave:

You should now be able to connect to both servers. Any changes you make on the master, even structural changes like installing extensions or creating tables, should trickle down to the slaves. You should also be able to query the slaves, but not much else.

When and if the time comes to liberate a chosen slave, create a blank file called failover.now in the data folder of the slave. What happens next is that Postgres will complete the playing back of WAL, rename the recover.conf to recover.done. At that point, your slave will be unshackled from the master and continue life on its own with all the data from the last WAL. Once the slave has tasted freedom, there’s no going back. In order to make it a slave again, you’ll need to go through the whole process from the beginning.

We’ll gain an introduction to FDW using the file_fdw wrapper. To install, use the command:

CREATE EXTENSION file_fdw;

Although file_fdw can only read from files on your local server, you still need to define a server for it. You register a FDW server with the following command.

CREATE SERVER my_server FOREIGN DATA WRAPPER file_fdw;

Next, you have to register the tables. You can place foreign tables in any schema you want. We usually create a separate schema to house foreign data. For this example, we’ll use our staging schema.

CREATE FOREIGN TABLE staging.devs (developer VARCHAR(150), company VARCHAR(150))
SERVER my_server
OPTIONS (format 'csv', header 'true', filename '/postgresql_book/ch10/devs.psv', delimiter '|', null ''),

When all the set up is finished, we can finally query our pipe delimited file directly:

SELECT * FROM staging.devs WHERE developer LIKE 'T%';

Once we no longer need our foreign table, we can drop it with the basic SQL command:

DROP FOREIGN TABLE staging.devs;

The database world does not appear to be getting more homogeneous. We’re witnessing exotic databases sprouting up left and right. Some are fads that go away. Some aspire to dethrone the relational databases altogether. Some could hardly be considered databases. The introduction of foreign data wrappers is in part a response to the growing diversity. Resistance is futile. FDW assimilates.

In this next example, we’ll demonstrate how to use the www_fdw foreign data wrapper to query web services. We borrowed the example from www_fdw Examples.

The first step to perform after you have copied the binaries and extension files is to install the extension in your database:

CREATE EXTENSION www_fdw;

We then create our Twitter foreign data server:

CREATE SERVER www_fdw_server_twitter
FOREIGN DATA WRAPPER www_fdw 
OPTIONS (uri 'http://search.twitter.com/search.json'),

The default format supported by the www_fdw is JSON, so we didn’t need to include it in the OPTIONS modifier. The other supported format is XML. For details on additional parameters that you can set, refer to the www_fdw documentation. Each FDW is different and comes with its own API settings.

Next, we define at least one user for our FDW. All users that connect to our server should be able to access the Twitter server, so we create one for the entire public group.

CREATE USER MAPPING FOR public SERVER www_fdw_server_twitter;

Now we create our foreign table:

CREATE FOREIGN TABLE www_fdw_twitter (
/* parameters used in request */
q text,	page text, rpp text, result_type text,
/* fields in response */
created_at text, from_user text, from_user_id text,	from_user_id_str text
 , geo text, id text, id_str text
 , is_language_code text, profile_image_url text
 , source text, text text, to_user text, to_user_id text)
SERVER www_fdw_server_twitter;

The user mapping doesn’t imply rights. We still need to grants rights before being able to query the foreign table.

GRANT SELECT ON TABLE www_fdw_twitter TO public;

Now comes the fun part. Here, we ask for page two of any tweets that have something to do with postgresql, mysql, and nosql:

SELECT DISTINCT left(text,75) As part_txt 
 FROM www_fdw_twitter WHERE q='postgresql AND mysql AND nosql' and
page='2';

Voilà! We have our response:

                                  part_txt
-----------------------------------------------------------------------------
 MySQL Is Done. NoSQL Is Done. It's the Postgres Age http://t.co/4DfqG75d
 RT @mjasay: .@451Research: <0.002% of paid MySQL deployments being repla
 @alanzeino: I know MySQL... but anyone with a brain is using PostgreSQL
 Hstore FTW! RT @mjasay: .@451Research: <0.002% of MySQL deployments bein
 @al3xandru: MySQL Is Done. NoSQL Is Done. It's the Postgres Age http://t