Hi
Jap! Slony-Skripts kann ich posten. Schwieriger wirds, wenn neue nodes dazukommen.
Slony ist asynchron und je nach DB kompliziert zu konfigurieren! Man sollte wissen was man tut! Ansonsten (wenn man überhaupt keine Ahnung hat) ein DRBD Cluster aufsetzen und die checkpoint_segments hochsetzen.
- Variablen erstmal:
HOST1="192.168.101.10"
HOST2="192.168.101.11"
DBNAME="testdb"
TABLE1="sltest1"
TABLE2="sltest2"
CLUSTERNAME="slonytest"
SLONY_USER="postgres"
INTERVAL="10"
- Datenbank und Tabellen auf Masternode erstellen:
createdb -U postgres -O postgres -h $HOST1 $DBNAME
createdb -U postgres -O postgres -h $HOST2 $DBNAME
psql -U postgres -h $HOST1 -c 'create language plpgsql;' -d $DBNAME
psql -U postgres -h $HOST1 -c "create table $TABLE1 (id serial primary key, name varchar(30));" -d $DBNAME
psql -U postgres -h $HOST1 -c "create table $TABLE2 (id serial primary key, name varchar(30));" -d $DBNAME
pg_dump -s -U postgres -h $HOST1 $DBNAME | psql -U postgres -h $HOST2 $DBNAME
- Cluster initialisieren:
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER';
init cluster (
id = 1,
comment = 'Node 1'
);
table add key (
node id = 1,
fully qualified name = '${TABLE1}'
);
table add key (
node id = 1,
fully qualified name = '${TABLE2}'
);
create set (
id = 1,
origin = 1,
comment = '${SETNAME}'
);
set add table (
set id = 1,
origin = 1,
id = 1,
fully qualified name = '${TABLE1}',
comment = 'Table test1'
);
set add table (
set id = 1,
origin = 1,
id = 2,
fully qualified name = '${TABLE2}',
comment = 'Table test2'
);
store node (
id = 2,
comment = 'Node 2'
);
store path (
server = 1,
client = 2,
conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER'
);
store path (
server = 2,
client = 1,
conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER'
);
store listen (
origin = 1,
provider = 1,
receiver = 2
);
store listen (
origin = 2,
provider = 2,
receiver = 1
);
_EOF_
# store listen wird bei neueren Versionen automatisch gesetzt!
- slon-daemons auf beiden hosts starten:
root@node1:/ nohup slon slonycluster "dbname=${DBNAME} user=${SLONY_USER} host=${HOST1}" &
root@node2:/ nohup slon slonycluster "dbname=${DBNAME} user=${SLONY_USER} host=${HOST2}" &
- Von node1 nach node2 syncen geht so:
while true; do
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER';
subscribe set (
id = 1,
provider = 1,
receiver = 2,
forward = no
);
_EOF_
sleep $INTERVAL
done
- tabellen zumüllen:
while true; do
psql -U postgres -d $DBNAME -h $HOST1 -c "insert into $TABLE1 (name) values ("blabla");
sleep 0.025
done
- gucken ob die nodes nahezu auf gleichem stand sind:
psql -U postgres -d $DBNAME -h $HOST1 -c "select count(*) from $TABLE1;"
psql -U postgres -d $DBNAME -h $HOST2 -c "select count(*) from $TABLE1;"
psql -U postgres -d $DBNAME -h $HOST1 -c "select count(*) from $TABLE2;"
psql -U postgres -d $DBNAME -h $HOST2 -c "select count(*) from $TABLE2;"
- failover wenn node1 noch ein bisschen lebt und noch syncen kann (transaktionen können währendessen weiterlaufen! die tabellen werden gelockt bis der sync vollständig von n1 auf n2 gegangen ist):
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER';
lock set (id = 1, origin = 1);
wait for event (origin = 1, confirmed = 2);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2);
_EOF_
- failback wenn node1 noch nicht per “drop node” aus dem cluster geschmissen wurde und wieder fit ist:
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER';
lock set (id = 1, origin = 2);
wait for event (origin = 2, confirmed = 1);
move set (id = 1, old origin = 2, new origin = 1);
wait for event (origin = 2, confirmed = 1);
_EOF_
- failover wenn node1 total im eimer ist und nicht mehr syncen kann (hier gehen transaktionen evtl. verloren, da slony asynchron ! ):
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER';
failover ( id = 1, backup node = 2);
drop node ( id = 1, event node = 2);
_EOF_
- failback wenn node1 aus dem cluster gelöscht wurde und wieder fit ist:
psql -U postgres -h $HOST1 -c "drop schema _$CLUSTER cascade;" -d $DBNAME
dropdb -h $HOST1 -U postgres $DBNAME
createdb -h $HOST1 -U postgres $DBNAME
psql -U postgres -h $HOST1 -c "create language plpgsql;" -d $DBNAME
pg_dump -s -U postgres -h $HOST2 $DBNAME | psql -U postgres -h $HOST1 $DBNAME
slonik <<_EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER';
store node (
id = 1,
comment = 'Node 1',
event node = 2
);
store path (
server = 2,
client = 1,
conninfo = 'dbname=$DBNAME host=$HOST1 user=$SLONY_USER'
);
store path (
server = 1,
client = 2,
conninfo = 'dbname=$DBNAME host=$HOST2 user=$SLONY_USER'
);
store listen (
origin = 2,
provider = 2,
receiver = 1
);
store listen (
origin = 1,
provider = 1,
receiver = 2
);
_EOF_
Hoffe damit kann man was anfangen .
Gruß
pingufreak