Thread:
My objective is to setup and test replication, starting with a Master and Slave-1 server, and once
replication has worked, to add an additional Slave-2 server for replication. All of the replication
configuration was done strictly via pgAdmin3 v8.0-beta-5.
M / \ v v
S1 S2
After I have been successful at getting a simple master/slave replication setup to work, I proceeded
to add Slave-2 and noticed many unexpected replicaton objects additions added to both the Master
and Slave-1 configurations as well as to the new Slave-2 server as shown in the link to the jpg image
(of 36KB):
http://www.cdkkt.com/images/pgAdmin/replication1.jpg
Adding Slave-2 results:
======================
The Master now has two Replication Sets. The first "Hardware" set shows Sequences(0) as before,
the Tables(1) shows the public.cars object as before, and finally the Subscritions(1) shows the Slave
Node with the same properties as before. So far it looks like there are no changes made here and all
looks good.
The second "Hardware" shows Subscriptions(0) under it. Seems that I am unable to added a new
subscription here.
======================
As for Slave-1, the first "Hardware" set, Subscriptions(1) is shown directly under it, and directly under
Subscriptions(1) is the Slave Node. Under Slave Node are two objects, Sequences(0) and Tables(0).
As for trying to create New Sequences or Tables, the only menu items available for these objects are:
"Refresh" and "object list report". However, New Subscriptions can be created at Subscriptions(1) or
at Slave Node.
The second "Hardware" set shows three objects: Sequences(0), Tables(0), and Subscriptions(0).
Choosing a New Table menu item will result in an empty list items for this table but you can type
whatever you want in the fields for this table, but you will not be able to commit it. The same is
true for the Sequences(0). A New Subscription can also be added but shows:
Orgin: 2
Provider: 2 - Slave Node
Receiver: 1 - Master Node, 5 - Slave Node (dropdown list)
Can Forward: Unchecked
You can OK to save these settings, but you cannot get rid of these once committed.
======================
As for Slave-2, and since we are adding a NEW slave here, two Hardware replication sets were
created. The first shows "Hardware", with Subscriptions(1) having a Slave Node subscription
with:
Provider ID: 1
Provider Name: Master Node
Receiver ID: 2
Recievier Name: Slave Node
Active: Yes
May Forward: Yes
Is forwarded: No
Under the Slave Node object, there are two objects under this, and these are Sequences(0) and
Tables(0). The only menu items for both of these objects are: "Refresh" and "object list report".
The second "Hardware" object shows only the Subscriptions(0) object. Selecting New Subscription
shows:
Origin: 2
Provider: 5 - Slave Node
Reciever: 1 - Master Node, 2 - Slave Node (dropdown list)
Can forward: Unchecked
Clicking: OK shows an error:
"ERROR: Slony-I: subscribeSet(): provider 5 is not an active forwarding node for replication set 3"
Clicking: OK after selecting Reciever as: 2 - Slave shows error:
"ERROR: Slony-I: subscribeSet(): set origin and received cannot be identical"
Sequences and Tables branches was moved directly under 'Slave Node', as shown. As for the additional
added Replication Sets with (0) Sequences, Tables, and Subscriptions, they cannot be deleted, the
missing tables and Sequences cannot be added, and Sequences, Tables, and Subscriptions cannot
be edited.
By the way, the Master to Slave-1 replication is still running fine at this time. But I am unable to
get the Slave-2 replication configuation setup and joined to the replication group.
Hmm... can someone tell me how it is possible to setup a Master and two-Slave replication via
pgAdmin3 and is it possible to do?
Thanks!
Dan
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.13.30/1030 - Release Date: 9/25/2007 8:02 AM
Daniel B. Thurman wrote:
> Hmm... can someone tell me how it is possible to setup a Master and two-Slave replication via
> pgAdmin3 and is it possible to do?
Yes, it is possible, and here's how (I'll be posting this on my blog and
adding it to the pgAdmin docs):
Setting up a multi-node Slony replication cluster using pgAdmin
===============================================================
In this example, a master server is setup with two direct slaves. The
software used is as follows:
- PostgreSQL 8.2.5
- pgAdmin III 1.8 beta 5
- Slony-I 1.2.11
Everything is running on a single machine with Windows XP Pro as the
operating system. The Postgres pgbench utility is used to generate the
test schema and workload.
1) Create 3 databases, master, slave1 and slave2 and ensure pl/pgsql is setup in each.
2) Create a pgbench schema in the master database:
> pgbench -i -U postgres master
3) Add a primary key called history_pkey to the history table on the tid, bid and aid columns.
4) Create a schema-only dump of the master database, and load it into slave1 and slave2:
> pg_dump -s -U postgres master > schema.sql > psql -U postgres slave1 < schema.sql > psql -U postgres slave2 <
schema.sql
5) Create Slony config files for each slon engine (daemon on *nix). The files should contain just the following two
lines:
cluster_name='pgbench' conn_info='host=127.0.0.1 port=5432 user=postgres dbname=master'
Create a file for each database, adjusting the dbname parameter as required and adding any other connection
optionsthat may be needed.
6) (Windows only) Install the Slony-I service:
> slon -regservice Slony-I
7) Register each of the engines (this is only necessary on Windows - on *nix the slon daemons may be started
individuallyand given the path to the config file on the command line using the -f option):
> slon -addengine Slony-I C:\slony\master.conf > slon -addengine Slony-I C:\slony\slave1.conf > slon -addengine
Slony-IC:\slony\slave2.conf
8) In pgAdmin under the Replication node in the master database, create a new Slony-I cluster using the following
options:
Join existing cluster: Unchecked Cluster name: pgbench Local node: 1 Master node
Adminnode: 99 Admin node
9) Under the Replication node, create a Slony-I cluster in each of the slave databases using the following options:
Join existing cluster: Checked Server: <Select the server containing the master db> Database:
master Cluster name: pgbench Local node: 10 Slave node 1 Admin node:
99- Admin node
and:
Join existing cluster: Checked Server: <Select the server containing the master db> Database:
master Cluster name: pgbench Local node: 20 Slave node 2 Admin node:
99- Admin node
10) Create Paths on the master to both slaves, and on each slave back to the master. Create the paths under each node
onthe master, using the connection strings specified in the slon config files. Note that future restructuring of
thecluster may require additional paths to be defined.
11) Create a Replication Set on the master using the following settings:
ID: 1 Comment: pgbench set
12) Add the tables to the replication set using the following settings:
Table: public.accounts ID: 1 Index: accounts_pkey
Table: public.branches ID: 2 Index: branches_pkey
Table: public.history ID: 3 Index: history_pkey
Table: public.tellers ID: 4 Index: tellers_pkey
14) On the master node, create a new subscription for each slave using the following options:
Origin: 1 Provider: 1 - Master node Receiver: 10 - Slave node 1
Origin: 1 Provider: 1 - Master node Receiver: 20 - Slave node 2
15) Start the slon service (or daemons on *nix):
> net start Slony-I
Initial replication should begin and can be monitored on the statistics
tab in pgAdmin for each node. The pgbench utility may be run against the
master database to generate a test workload.
Regards, Dave
Daniel B. Thurman wrote: > Thanks for some details and it helps, except the following step: > > 10) Create Paths on the master to both slaves, and on each slave back to > the master. Create the paths under each node on the master, using > the connection strings specified in the slon config files. Note that > future restructuring of the cluster may require additional paths to > be defined. > > Can you please elaborate? > > I am not clear if by "master" you mean "master server" or "master node" > and likewise for "slave" you mean "slave server" or "slave node". Technically node (or database). That tutorial was written using a single server which is stated at the beginning so there is no master or slave server. > It is not clear if I need only to update the paths only on the > master-server and if any work needs to be done for the paths on > the slave-servers. Check the slony docs for more info on paths, but essentially you need paths on each node pointing to every other node it might talk to. So in the example given, the master has paths to both slaves, and each slave has a path to the master. If you restructure the cluster, and make a slave the master, you will need to add a path on that node to the remaining slave, and on the remaining slave to the new master. Regards, Dave
>Daniel B. Thurman wrote: >> Thanks for some details and it helps, except the following step: >> >> 10) Create Paths on the master to both slaves, and on each >slave back to >> the master. Create the paths under each node on the master, using >> the connection strings specified in the slon config >files. Note that >> future restructuring of the cluster may require >additional paths to >> be defined. >> >> Can you please elaborate? >> >> I am not clear if by "master" you mean "master server" or >"master node" >> and likewise for "slave" you mean "slave server" or "slave node". > >Technically node (or database). That tutorial was written >using a single >server which is stated at the beginning so there is no master or slave >server. > >> It is not clear if I need only to update the paths only on the >> master-server and if any work needs to be done for the paths on >> the slave-servers. > >Check the slony docs for more info on paths, but essentially you need >paths on each node pointing to every other node it might talk >to. So in >the example given, the master has paths to both slaves, and each slave >has a path to the master. If you restructure the cluster, and make a >slave the master, you will need to add a path on that node to the >remaining slave, and on the remaining slave to the new master. > >Regards, Dave > >No virus found in this incoming message. >Checked by AVG Free Edition. >Version: 7.5.488 / Virus Database: 269.13.33/1034 - Release >Date: 9/27/2007 5:00 PM > Oh my. My arrangement is for 1 master (on one server) and two slaves (on two other servers). With a little insight and with your help, I am able to replicate the master to the two other slaves now! I did this so that I can have harware redundancy and seperate databases scattered around in my environment. Thank you for all of your help and most importantly, your patience with a really ignorant newbie! ;) Kind Regards, Dan No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.488 / Virus Database: 269.13.33/1034 - Release Date: 9/27/2007 5:00 PM
> ------- Original Message ------- > From: "Daniel B. Thurman" <dant@cdkkt.com> > To: "Dave Page" <dpage@postgresql.org> > Sent: 28/09/07, 18:24:00 > Subject: RE: [pgadmin-support] > > Oh my. > > My arrangement is for 1 master (on one server) and two slaves (on two other > servers). With a little insight and with your help, I am able to replicate > the master to the two other slaves now! I did this so that I can have harware > redundancy and seperate databases scattered around in my environment. > > Thank you for all of your help and most importantly, your patience with a > really ignorant newbie! ;) No worries - glad it's all working now. Regards, Dave