From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Sat, 19 Aug 2017 10:26:02 +0000 (+0200)
Subject: cleanup docs, extract scripts for postgresql
X-Git-Url: http://git.rot13.org/?p=edozvola;a=commitdiff_plain;h=6100bbebebfc8e9a8f8bf606b7b03cfc1dd74043;hp=a4bd3c62f64524aea3ef82cf443455b546931f03

cleanup docs, extract scripts for postgresql
---

diff --git a/edozvola-dpavlin.txt b/edozvola-dpavlin.txt
index c6877e0..c248649 100644
--- a/edozvola-dpavlin.txt
+++ b/edozvola-dpavlin.txt
@@ -1,4 +1,7 @@
-yum install etckeeper
+# instalirati etckeeper da čuva /etc/ direktorij u git-u
+# (nije na produkcijskom enviromentu)
+
+yum install etckeeper
 cd /etc/
 git config --global user.email "dpavlin@rot13.org"
 git config --global user.name "Dobrica Pavlinusic"
@@ -26,7 +29,7 @@ bash-4.2$ cp -v .ssh/id_rsa.pub .ssh/authorized_keys
 ‘.ssh/id_rsa.pub’ -> ‘.ssh/authorized_keys’
 
 
-prekopirati isti kljuc i authoriorized keys na slave:
+## prekopirati isti kljuc i authoriorized keys na slave:
 
 
 [root@edozvola-db-01 pgsql]# rsync -rav .ssh edozvola-db-02:`pwd`
@@ -108,40 +111,27 @@ https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
 
 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf
 wal_level = replica
-archive_mode = on
-max_wal_senders = 3 
-max_replication_slots = 3
-archive_command = 'test ! -f /shared/pgsql/%f && cp %p /shared/pgsql/%f'
-hot_standby = on
-
-
 
+# https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
+synchronous_commit = on
 
+archive_mode = on
+archive_command = 'test ! -f /shared/pgsql/%f && cp %p /shared/pgsql/%f'
 
+max_wal_senders = 3 
+max_replication_slots = 3
 
+hot_standby = on
 
 
 [root@edozvola-db-01 ~]# systemctl restart postgresql-9.6.service
 
 
 
+# kreirati replication korisnika
 
-
-
-
-
-
-
-
-
-
-
-
-
-kreirati replication korisnika
 [root@edozvola-db-01 ~]# sudo -u postgres psql
 
-
 postgres=# create user replication with password 'replication123' login replication ;
 CREATE ROLE
 
@@ -151,15 +141,13 @@ CREATE ROLE
 
 host    replication     replication     10.200.1.62/24          md5
 
-
-netmask je /24 da mozemo imati istu konfiguraciju na oba node-a
+## netmask je /24 da mozemo imati istu konfiguraciju na oba node-a
 
 
 
 
 
-
-testirati replikaciju
+# testirati replikaciju
 [root@edozvola-db-01 ~]# sudo -u postgres psql test
 test=# insert into test1 (a,b) values ('baz',3);
 INSERT 0 1
@@ -231,26 +219,22 @@ archive_cleanup_command = 'pg_archivecleanup /shared/pgsql %r'
 
 
 
-skripte
-promoviranje slave-a u master
+# skripte
 
 
 [root@edozvola-db-01 pgsql]# pwd
 /var/lib/pgsql
-[root@edozvola-db-01 pgsql]# cat promote-slave-to-master.sh
-#!/bin/sh -xe
-
 
 
+## promoviranje slave-a u master
 
+[root@edozvola-db-01 pgsql]# cat promote-slave-to-master.sh
+#!/bin/sh -xe
 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
 
 
+## kreiranje slave instance od mastera
 
-
-
-
-kreiranje slave instance od mastera
 [root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh
 #!/bin/sh -e
 
diff --git a/pg-scripts/create-pgsql-slave.sh b/pg-scripts/create-pgsql-slave.sh
new file mode 100755
index 0000000..e2703e7
--- /dev/null
+++ b/pg-scripts/create-pgsql-slave.sh
@@ -0,0 +1,30 @@
+#!/bin/sh -e
+
+# WARNING: this script depends that two database nodes have IPs which and in x1 and x2
+# tr 12 21 does swap from currnet node number/ip to other one!
+
+test "`whoami`" != "postgres" && echo "$0 needs to be run as postgres user!" && exit 1
+
+here=`hostname -s`
+there=`hostname -s | tr 12 21`
+
+my_ip=`ip addr | grep 10.200.1. | sed 's/^.*\(10\.200\.1\.[0-9]*\)\/.*$/\1/'`
+other_ip=`echo $my_ip | tr 12 21`
+
+echo "Create slave on $here $my_ip using data from master on $there $other_ip"
+echo "[enter] to start"
+read
+
+#systemctl stop postgresql-9.6.service || true
+/usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data stop || true
+
+echo "select pg_start_backup('slave')" | ssh $there psql
+
+rsync -rav --delete --exclude pg_xlog $there:/var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data/
+
+echo "select pg_stop_backup()" | ssh $there psql
+
+test -e /var/lib/pgsql/9.6/data/recovery.done || ( echo "ERROR: no recovery.done !" && exit 1 )
+cat /var/lib/pgsql/9.6/data/recovery.done | sed -e s/$my_ip/$other_ip/ > /var/lib/pgsql/9.6/data/recovery.conf
+
+
diff --git a/pg-scripts/promote-slave-to-master.sh b/pg-scripts/promote-slave-to-master.sh
new file mode 100755
index 0000000..9f69c13
--- /dev/null
+++ b/pg-scripts/promote-slave-to-master.sh
@@ -0,0 +1,2 @@
+#!/bin/sh -xe
+/usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote