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