Subscribing to a Replication Set

Once you've defined a replication set, the origin node starts pushing a history of every modification to the _cluster-name.sl_log_1 table and sends SYNC events to all subscriber nodes. Subscribing to a replication set is a two-step process. First, you copy the table (and sequence) definitions into the subscriber node. Second, you execute a subscribe set command to tell the slon daemons to start pulling data out of the sl_log_1 history file into the subscriber database.

Copying Table and Sequence Definitions

The easiest way to copy the table and sequence definitions to the subscriber node is to talk pg_dump and psql into doing it for you. First, make sure that the target schema exists on the subscriber node. For example, to create the target schema (springfield) on nodes boomtown and snoozeville, you could execute the following commands:

$ psql -h boomtown -c "CREATE SCHEMA springfield"
CREATE SCHEMA
$ psql -h snoozeville -c "CREATE SCHEMA springfield"
CREATE SCHEMA

Next, use pg_dump to extract the definition of each table (and sequence) in the replication set and pipe the result back into a psql command that connects to the subscriber node. For example, to copy the definitions of the customers and tapes tables from springfield to boomtown and snoozeville:

$ pg_dump -s -n springfield -t customers -h springfield |
> psql -h boomtown movies
$ pg_dump -s -n springfield -t tapes -h springfield |
> psql -h snoozeville movies
$ pg_dump -s -n springfield -t customers -h springfield |
> psql -h boomtown movies
$ pg_dump -s -n springfield -t tapes -h springfield |
> psql -h snoozeville movies

If you prefer, you can copy the entire springfield schema to each subscriber node instead:

$ pg_dump -s -n springfield -h springfield | psql -h boomtown movies
$ pg_dump -s -n springfield -h springfield | psql -h snoozeville movies

Note that you don't copy the data itself, just the metadata. (The -s flag tells pg_dump to extract the definitions, not the data.) If you do copy the customers and tapes table to the subscriber nodes, the slon daemons will overwrite the data as soon as replication begins.

Creating a Subscriber

Finally, to subscribe a node to a replication set, you execute a subscribe set command (within a slonik script). The syntax for the subscribe set command is

subscribe set 

(
 id=set-id, 
 provider=node-id, 
 receiver=node-id, 
 forward={TRUE|FALSE} 
);

The id=set-id option tells slonik which replication set you want to subscribe to. (set-id corresponds to the create set and set add table commands that you executed earlier.) The provider=node-id option determines which node provides the replication data to the subscriber. If the provider node is the same node as the set's origin, the subscriber pulls replication data straight from the source. If the provider node is not the set's origin, the subscriber pulls replication data from another subscriber. The receiver=node-id option specifies which node is doing the subscribing. If forward is FALSE (or if you omit the forward option), the subscriber node discards all replication data as soon as it has applied the modifications to the local copy of the database. If forward is trUE, the subscriber retains a copy of the replication data so that it can provide the data to other subscribers.

To subscribe nodes boomtown and snoozeville, you would execute the script shown in Listing 24.8.

Listing 24.8. subscribeSet.sk

#!/usr/local/bin/slonik

# File: subscribeSet.sk
include ;

subscribe set ( id = 1, provider = @SPRINGFIELD, 
 receiver = @BOOMTOWN, forward = yes );
subscribe set ( id = 1, provider = @BOOMTOWN, 
 receiver = @SNOOZEVILLE, forward = no );

The first subscribe set command subscribes node boomtown. boomtown pulls replication data directly from the set origin (springfield). The second subscribe set command subscribes node snoozeville. snoozeville pulls replication data from boomtown. When you change the customers (or tapes) table on springfield, the modifications are copied from springfield to boomtown, and then from boomtown to snoozeville.

Once you've executed a subscribe set command, the slon daemons begin replicating data. The first SYNC event received by boomtown copies the entire customers table (and the entire tapes table) from springfield to boomtown. When boomtown holds an identical copy of the replication set, it sends a SYNC event to snoozeville and snoozeville pulls the entire replication set from boomtown.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use

Performance

Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL

PL/pgSQL

The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization

Security

Replicating PostgreSQL Data with Slony

Contributed Modules

Index



PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
EAN: N/A
Year: 2004
Pages: 261

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net