Tuesday, 18 May 2010

Data replication with Postgresql and Slony

Hi all,
I have been working on data replication around Postgresql. For that purpose, I used the famous Slony replication engine. Very nice piece of work but a cruel lack of documentation and how to. So, here is mine. First I want to say that my work was inspired after reading a similar article called “PostgreSQL, Slony-I, pgadmin, win32, First time config / installation” found on ChiChing means blog.

The tools

For this tutorial, you need your favourite linux box, running a Postgresql database and Slony installed. I encourage you to use the Postgres Application Stack builder which is a ”download and installation wizard combined with a set of pre-configured packages to complement PostgreSQL's one-click installers on Windows, Mac and Linux.” Very nice installer that will take care of all the deployment process. On top of that, you will also use your trusted pgadmin because my tutorial is about setting up replication by mainly using the gui (we’ll see later for the script mode …).
Of course, you need to be root.
image Stack installer main screen

Database setup

For this tutorial, you want a source table – Fact_Table in Database SOURCE and Schema A -  to be replicated into a target table – Fact_Table in Database TARGET and Schema A (same schema name). For the moment, I’m still having troubles to replicate data into a target schema with different name.image
If you don’t have a table ready for that tutorial, here is a code for creating a small and very stupid fact table. Any table you want to replicate needs to have a unique constraint (create a primary key and go on). Create it on SOURCE and TARGET database.
CREATE TABLE “Schema_A'”.”Fact_Table”
(
“ID” bigint NOT NULL,
“DATE” date,
“PRODUCT” chararcter(100),
“PRICE” double precision,
“Quantity” double precision,
CONSTRAINT “PK_FACTS” PRIMARY KEY (“ID”)
)
In this tutorial, I assume the following :
  • You want to replicate data from database=SOURCE, schema=Schema_A and table=Fact_Table to database=TARGET, schema=Schema_A and table=Fact_Table.
  • You use the built in postgresql user
  • Both source and target schema / tables have been previously created. Slony can manage DDL change / propagation during replication but this will be explained in a further article.

Setting up a simple replication process with pgadmin III

First create 2 databases, one called SOURCE and the other called TARGET.  The SOURCE will be the Master, while the TARGET will be the Slave. Right click on database and choose “New Database” (see below). Once done, you will have 2 more dbs in your object browser (below).
image
Now it is time to create the replication objects : everything has to be done into the Replication items inside the two newly created databases. The thery is simple : the two replication objects need references to each other, have a look below :
image
1 - Create base replication objects (screen caps below)
For the Master :
  • Right click on SOURCE: Replication and choose "New Slony-I Cluster"
  • Join existing cluster : Do not check this !
  • Cluster name : choose a name, here it is : REP_CLUSTER
  • Local Node Left : 1
  • Local Node Right : Master Node
  • Admin Node Left : 99
  • Admin Node Right : Admin Node
  • Press OK.
For the Slave :
  • Right click on TARGET : Replication and choose "New Slony-I Cluster"
  • Join existing cluster : Has to be checked
  • Server : localhost
  • Database : SOURCE
  • Cluster name : REP_CLUSTER (should be automatically recognized)
  • Local Node Left : 10
  • Local Node Right : Slave Node
  • Admin Node : 99 - Admin Node
  • Press OK.
                     image  image
                                 Master Node                           Slave Node
2 – Create path setup (screen caps below)
For the Master :
  • Right click on SOURCE : Replication : sample : Nodes : Master Node : Path and choose New path
  • Server : 10 - Slave Node
  • Connect Info : host=localhost port=5432 user=postgres password=yourpassword dbname=TARGET
  • Conn retry : 10
  • Press OK.
For the Slave :
  • Right click on TARGET : Replication : sample : Nodes : Slave Node : Path and choose New path
  • Server : 1 - Master Node
  • Connect Info : host=localhost port=5432 user=postgres password=yourpassword dbname=SOURCE
  • Conn retry : 10
  • Press OK.
             imageimage  
                        Path for Master Node                       Path for Slave Node
3 – Define replication set (screen caps below)
  • Right click on db SOURCE : Replication : REP_CLUSTER: Replication Sets and choose new Replication Set
  • ID : 1
  • Comment : Replication Set 1
image
Now we choose the table we want to be replicated from SOURCE to TARGET.
  • Right click on Replication Sets : sample set : Tables and choose New tables
  • Table : Any table of your choice
  • ID : 1
  • Index : Auto filled when choosing table. That’s important to have a unique constraint on the table you want to replicate (primary key for instance).
  • Comment : a nice comment
  • Press OK.
image
3 – Create a new Subscription (screen caps below)
  • Right click on SOURCE : Replication : sample : Replication Sets : sample set : Subscription and choose New subscription.
  • Origin : write “1”
  • Provider : Choose “1 - Master Node”
  • Receiver : Choose “10 - Slave Node”
  • Can forward : Keep it unchecked
  • Press OK.
image
4 – Object browser overview
      image   image
          Replication object for the Master         Replication object for the Slave

Well, we are done with the database setup using pgAdmin III. Now it’s time to … start the replication !

Start the replication

Now it’s time to write some linux commands. Go to /opt/PostgreSQL/8.3/bin (or any other location depending on your linux flavour and / or Postgresql installation path). You will find there some interesting scripts. Have a look to slon. This is the slony daemon you will start twice : one for the MASTER, the other for the SLAVE.
Note : if you were setting up replication between two physical servers, you would have to start slon on each machine.
slon syntax is quite simple, here it is :
slon [Options] [ClusterName] [ConnexionInfos]
For the Master :
  • ./slon –d 1 REP_CLUSTER “user=postgres password=vincentt host=localhost port=5432 dbname=SOURCE”
For the Slave :
  • ./slon –d 1 REP_CLUSTER “user=postgres password=vincentt host=localhost port=5432 dbname=TARGET”
The command line output is like below, for the Master :
image And for the Slave, a bit different :
image We can see we have a successfull truncate table (on the target Fact_Table, which was empty). We also have a “copy set 1 done” which means our replication is running fine.
Let’s have a look on the target table to see if everything went fine ….
image Yeeeeah, it went fine : our Source data is here in the Target table. Now let’s add some data into the Source table …
image And let’s check on the Target db if the replication did the job we are expecting …
image … yes Sir, it worked !! You can also check by reading the slon command line output.
You are now ready to create your own custom replication scripts using Slony.
I hope this tutorial helped you. Keep me in touch or contact me if you need more informations.

18 comments:

Anonymous said...

Как говорилось на Seexi.net Вот решила позвать домработницу на помощь. Муж не может помочь абсолютно, кроме всего прочего уже не будет этого делать. А я просто устала. Есть такие у кто обзавелся платными помощницами? Сколько вы за это платите и где вы их нашли? и еще, как Ваш муж отреагировал на то, что в дом будет приплестись посторонняя тетя

Anonymous said...

Как говорилось на Seexi.net Судя по отзывам на этом форуме, мужчины-близнец поголовно бабники, ветренные, кинут в всякий момент, бросят, изменят и тп и тд. Предлагаю опровергнуть стереотипы и устаревшие клише.
Доводы за и против мужчин-близнецов. +)

anil said...

i can't setup replication... Plz help

anil said...

I can't setup replication.... plz help me

anil said...

I can't create Table under Replication set. The following error has occurred. can you plz help me.

An error has occurred:

ERROR: relation "_REP_CLUSTER.sl_trigger" does not exist
LINE 2: FROM "_REP_CLUSTER".sl_trigger
^

Anonymous said...

I can't create Table under Replication set. The following error has occurred. can you plz help me.

An error has occurred:

ERROR: relation "_REP_CLUSTER.sl_trigger" does not exist
LINE 2: FROM "_REP_CLUSTER".sl_trigger
Can you please help me how to reslove this issue???

Anonymous said...

nepv [url=http://www.buycheapbeatsbydredr.co.uk]Beats by Dr Dre[/url] ybbr http://www.buycheapbeatsbydredr.co.uk gffb fhae [url=http://www.gobeatsbydrecheap.co.uk]Beats By Dre UK[/url] rzol http://www.gobeatsbydrecheap.co.uk zmda chhs [url=http://www.okcheapbeatsbydre.co.uk]Beats by Dr Dre Headphones[/url] mrpr http://www.okcheapbeatsbydre.co.uk lanq pkpz [url=http://www.salebeatsbydrecheap.co.uk]Cheap Beats Dr Dre[/url] hwsh http://www.salebeatsbydrecheap.co.uk dygh ecvg [url=http://www.salecheapdrdrebeats.co.uk]Beats By Dre[/url] whcs http://www.salecheapdrdrebeats.co.uk bawn

anil said...

stop

Anonymous said...

zoleh [url=http://drdrebeatsonlinesale.co.uk]beats by dre outlet[/url] ognbz http://drdrebeatsonlinesale.co.uk gzabh [url=http://beatsdrdresales.co.uk]beats by dre[/url] tboae http://beatsdrdresales.co.uk ycush [url=http://drdrebeatsonlinestores.co.uk]dr dre beats[/url] ypcdm http://drdrebeatsonlinestores.co.uk tltqk [url=http://cheapbeatsdrdreonlinesale.com]cheap beats by dre[/url] iulcp http://cheapbeatsdrdreonlinesale.com umkzd [url=http://beatsbydrdreshopping.com]beats by dre[/url] lebgi http://beatsbydrdreshopping.com kevzk [url=http://beatsbydrdreoutletforsale.com]beats by dre[/url] bcegl http://beatsbydrdreoutletforsale.com epio

Anonymous said...

ntlmc [url=http://www.getclarisonicmia.com]clarisonic mia[/url] znicrx http://www.getclarisonicmia.com ijlwi [url=http://www.saveclarisonicmia.com]clarisonic mia 2[/url] urpoxq http://www.saveclarisonicmia.com mnbby [url=http://www.justclarisonicmia.com]clarisonic mia[/url] anbxqs http://www.justclarisonicmia.com ncjg [url=http://www.vipclarisonicmia2.com]cheap clarisonic mia[/url] aaslvp http://www.vipclarisonicmia2.com zngyn [url=http://www.clarisonicmiawe.com]clarisonic mia outlet[/url] yylfkp http://www.clarisonicmiawe.com zuhbm [url=http://www.clarisonicmiasalewe.com]clarisonic mia[/url] qzmlce http://www.clarisonicmiasalewe.com crgu

Anonymous said...

fusuw [url=http://www.salelouisvuitton-no1.com]louis vuitton outlet[/url] sektvh http://www.salelouisvuitton-no1.com rlcje [url=http://www.get-louisvuittonoutlet.com]cheap louis vuitton[/url] frgfxx http://www.get-louisvuittonoutlet.com oroqg [url=http://www.pick-louisvuittonoutlet.com]louis vuitton handbags[/url] kornni http://www.pick-louisvuittonoutlet.com qkae [url=http://www.foxlouisvuitton.com]louis vuitton bags[/url] qofcgx http://www.foxlouisvuitton.com rzqzg [url=http://www.lo-louisvuittonoutlet.com]louis vuitton outlet[/url] yzyhjh http://www.lo-louisvuittonoutlet.com qxlfh [url=http://www.locheaplouisvuitton.com]louis vuitton sale[/url] nwlphb http://www.locheaplouisvuitton.com ejua

Anonymous said...

mlnmi [url=http://www.pickcelinehandbags.com]celine handbags[/url] hcwpyu http://www.pickcelinehandbags.com vzaku [url=http://www.savecelinehandbags.com]canada handbags[/url] dzqznz http://www.savecelinehandbags.com kzczo [url=http://www.savecelinebags.com]celine bag[/url] hhrloq http://www.savecelinebags.com ihyl [url=http://www.goodcelinebags.com]celine bag[/url] bnmihs http://www.goodcelinebags.com snrio [url=http://www.goodcelinehandbags.com]cheap celine bag[/url] exzukn http://www.goodcelinehandbags.com lrtwq [url=http://www.onlinecelinebags.com]celine bags[/url] gxxevz http://www.onlinecelinebags.com zyqx

Anonymous said...

tttwb [url=http://www.cheaper-montblancpens.com]Mont Blanc Pens[/url] akpgun http://www.cheaper-montblancpens.com pvjag [url=http://www.montblancpensincheap.com]Mont Blanc[/url] judtsh http://www.montblancpensincheap.com vmkot [url=http://www.montblanconlines.com]Mont Blanc Pens[/url] xspkfs http://www.montblanconlines.com pwms [url=http://www.cheapmontblancpens.co.uk]Mont Blanc Pen[/url] blmzzx http://www.cheapmontblancpens.co.uk dyqrz [url=http://www.salemontblancpens.co.uk]Mont Blanc[/url] wycktj http://www.salemontblancpens.co.uk kfyqc [url=http://www.montblanconlines.co.uk]Mont Blanc Pens tmexae http://www.montblanconlines.co.uk kkvs

Anonymous said...

jgloe [url=http://www.mulberrybagswe.co.uk]mulberry outlet[/url] mlhze http://www.mulberrybagswe.co.uk gidvt [url=http://www.mulberryoutletwe.co.uk]mulberry handbags[/url] ozxvh http://www.mulberryoutletwe.co.uk wsqjk [url=http://www.im-mulberrybags.co.uk]mulberry handbags[/url] asrcj http://www.im-mulberrybags.co.uk ctpoz [url=http://www.im-mulberryoutlet.co.uk]mulberry handbags[/url] jnlqx http://www.im-mulberryoutlet.co.uk qmnwu [url=http://www.pay-mulberrybags.co.uk]mulberry handbags[/url] yqjte http://www.pay-mulberrybags.co.uk cvyt [url=http://www.online-mulberry.co.uk]mulberry handbags[/url] rpjau http://www.online-mulberry.co.uk wfokr

Anonymous said...

Hello. And Bye. Thank you very much.

Anonymous said...

uupicx http://www.capemaycitypolice.com/MonstBeatsByDre.html pnccmq http://www.capemaycitypolice.com/cheapnikeairmaxb.html kkudsh http://www.chancemccann.com/cheap-airjordans.html dqhllk http://www.oceancityseafood.com/celinehandbags.html kebffw http://maryhelverson.com/celinehandbagsb.html nnxqqq http://www.oceancityseafood.com/cheapnikeshoesb.html egftwb [url=http://www.capemaycitypolice.com/MonstBeatsByDre.html]dr dre beats[/url] dyqjt [url=http://www.capemaycitypolice.com/cheapnikeairmaxb.html]cheap nike shoes[/url] uqnuu [url=http://www.chancemccann.com/cheap-airjordans.html]cheap jordans[/url] kndsi [url=http://www.oceancityseafood.com/celinehandbags.html]celine bags[/url] ujqknh [url=http://maryhelverson.com/celinehandbagsb.html]celine bag[/url] falt [url=http://www.oceancityseafood.com/cheapnikeshoesb.html]nike air max[/url] ercu

Anonymous said...

lbrogs http://www.capemaycitypolice.com/MonstBeatsByDre.html uqhmwx http://www.capemaycitypolice.com/cheapnikeairmaxb.html lvbbfn http://www.chancemccann.com/cheap-airjordans.html dacazv http://www.oceancityseafood.com/celinehandbags.html hgggvv http://maryhelverson.com/celinehandbagsb.html ghrdoe http://www.oceancityseafood.com/cheapnikeshoesb.html xcqrgs [url=http://www.capemaycitypolice.com/MonstBeatsByDre.html]cheap beats by dre[/url] dsfzz [url=http://www.capemaycitypolice.com/cheapnikeairmaxb.html]air max shoes[/url] webau [url=http://www.chancemccann.com/cheap-airjordans.html]cheap jordan shoes[/url] wxrgu [url=http://www.oceancityseafood.com/celinehandbags.html]celine bags[/url] etmpuz [url=http://maryhelverson.com/celinehandbagsb.html]celine bag[/url] okto [url=http://www.oceancityseafood.com/cheapnikeshoesb.html]nike air max[/url] kahd

Anonymous said...

lwthsk http://www.capemaycitypolice.com/MonstBeatsByDre.html rpvkct http://www.capemaycitypolice.com/cheapnikeairmaxb.html uizano http://www.chancemccann.com/cheap-airjordans.html uijnac http://www.oceancityseafood.com/celinehandbags.html eatqzh http://maryhelverson.com/celinehandbagsb.html pwatiq http://www.oceancityseafood.com/cheapnikeshoesb.html aufwyq [url=http://www.capemaycitypolice.com/MonstBeatsByDre.html]cheap beats by dre[/url] ncwrw [url=http://www.capemaycitypolice.com/cheapnikeairmaxb.html]nike air max[/url] mwyzq [url=http://www.chancemccann.com/cheap-airjordans.html]cheap jordan shoes[/url] peoft [url=http://www.oceancityseafood.com/celinehandbags.html]celine bag[/url] dxhezx [url=http://maryhelverson.com/celinehandbagsb.html]celine bags[/url] rndy [url=http://www.oceancityseafood.com/cheapnikeshoesb.html]air max shoes[/url] upfd