Managing Replicated Databases

A replicated database is a standard postgreSQL database with some replica system table, replica functions and triggers created by pgReplicator.

Creating a new replicated database

To create a new replicated database named test from the command line, type

$ createdbr test

The createdbr command also works on previously created databases

Destroying a replicated database

To destroy a replicated database named test, from the command line type

$ destroydbr test

You can use dropdbr instead of destroydbr, like to PostgresSQL 7.0. syntax

Destroydbr (and dropdbr) make use of postgres' destroydb (and dropdb) commands, so the whole database will be destroyed!

Setting up replication features

Here is some replica system tables you can find in a replicated database and you have to interact with:

Table name	Description
pgr_lsite	here you set the site ID of the local host
pgr_hosts	insert here all hosts information
pgr_tables	insert here tables' replication information
pgr_distrib	insert here the tables' replication map among sites

In the following example we suppose to work on 2 hosts called Ut and Penguin. We will adopt the notation below:

(orange) marked operations should be executed on host Ut

(yellow) marked operations should be executed on host Penguin

(light-blue) marked operations should be executed on BOTH Ut and Penguin

[Suppose ]we want the new database test to be replicated on Ut and Penguin. As user replicator:

$ createdbr test

Each site hosting a replicated database must have a unique site ID in the database domain. So we must set the site ID for test database in each host: Ut (siteID=1) and Penguin (siteID=2) .

$ psql test test=> insert into pgr_lsite values (1);	$ psql test test=> insert into pgr_lsite values (2);

For each site we fill up the pgr_hosts table with information about hosts: hostname, domain name, siteID and postmaster port.

test=> insert into pgr_hosts values ('ut', 'bp.lnf.it', 1, 5432); test=> insert into pgr_hosts values ('penguin', 'bp.lnf.it', 2, 5432);

Now we are ready to create the customers table in test database

$ psql test

test=> create table customers ( cust_code int4, customer varchar(20));

test=> grant all on customers to public;

Let's inform pgReplicator that customers table in test database will be replicated; we should insert some information in pgr_tables, but the pgr_replica_table function is provided in order to assign default values that can be later customized

test=> select pgr_replica_table ('customers');

A record in the pgr_tables has been created. You can customize it for accomplish your needs.

Attribute	inserted values	Description
tablename	'customers'	Table name
permissions	RWLP	Permissions: ROT = Read Only Table RWT = Read-Write Table RWLP = Read-Write on Local Partition
flow_eq_filter	false	enable transaction simplifier (not yet implemented)
encrypt	NO	encrypt captured transactions (not yet implemented)
resolv_site	1	conflict-resolver site for <tablename>
resolver	NO	conflict resolution alghoritm. Choose one of them or define a new one. NO No Conflict Resolution algorithm 1SO One Site Only FSO First Site Only FTO First Transactions Only FTO-MP First Transactions Only - Master Priority FTS First Transactions Site FTS-MP First Transactions Site - Master Priority TS Time Stamp TS-MP Time Stamp - Master Priority
history	true	keep history of transactions after a replica session. History files are daily defined. If there are more than one replica synchronization event per day, transactions are queued in the same file.
silent	true	don't send warning emails to users having rolled back transactions
monitor	false	keep statistics on table operations in order to schedule database synchronization based on threshold values
locked	false	Used by replica process to disable write access on replicated tables during synchronization

The map of replicated tables is stored in pgr_distrib and is automatically updated every time pgr_tables is updated too, so now we have:

test=> select * from pgr_distrib; site\|tablename ----+--------- 1 \|customers	test=> select * from pgr_distrib; site\|tablename ----+--------- 2 \|customers

Let's complete the map stored in pgr_distrib with remote hosts replicated tables' information:

test=> insert into pgr_distrib values (2, 'customers');
test=> select * from pgr_distrib;
site|tablename
----+---------
1   |customers
2   |customers

test=> insert into pgr_distrib values (1, 'customers');
test=> select * from pgr_distrib;
site|tablename
----+---------
2   |customers
1   |customers

As you can see, both pgr_hosts and pgr_distrib tables are equal on both Ut and Penguin, pgr_lsite tables are always different and pgr_tables tables may be different or equal.

Users' operations capture

All users operations on a replicated tables but replicator ones are captured by replica triggers and inserted into a related auxiliary table. So, let's connect to test database as postgres user and insert, update, delete some tuples.

test=> \connect - postgres
You are now connected as new user postgres.
test=> insert into customers values (101, 'theos');
test=> insert into customers values (102, 'jack');
test=> update customers set customer=upper(customer);
test=> delete from customers where cust_code = 102;

test=> \connect - postgres
You are now connected as new user postgres.
test=> insert into customers values (201, 'ROCK');
test=> insert into customers values (202, 'RICHARD');
test=> update customers set customer = lower(customer);

All these operations are automatically stored by replica triggers into customers_flow auxiliary table and will be propagated among sites by pgReplicator during the replica process.

NEXT SECTION: Data Synchronization