From 9378e506deedce3157ae710b2a543bea8e026338 Mon Sep 17 00:00:00 2001
From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Sat, 19 Aug 2017 11:01:50 +0200
Subject: [PATCH] =?utf8?q?bilje=C5=A1ke=20konvertirane=20u=20text=20file?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 edozvola-dpavlin.txt | 317 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 317 insertions(+)
 create mode 100644 edozvola-dpavlin.txt

diff --git a/edozvola-dpavlin.txt b/edozvola-dpavlin.txt
new file mode 100644
index 0000000..38ef4e6
--- /dev/null
+++ b/edozvola-dpavlin.txt
@@ -0,0 +1,317 @@
+yum install etckeeper
+cd /etc/
+git config --global user.email "dpavlin@rot13.org"
+git config --global user.name "Dobrica Pavlinusic"
+git commit -m base
+
+
+
+https://www.postgresql.org/docs/9.6/static/warm-standby.html
+
+# napraviti ssh autorizaciju za postgresql korisnika bez passworda
+
+[root@edozvola-db-01 pgsql]# sudo -u postgres bash
+bash-4.2$ pwd
+/var/lib/pgsql
+bash-4.2$ ssh-keygen
+Generating public/private rsa key pair.
+Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
+Created directory '/var/lib/pgsql/.ssh'.
+Enter passphrase (empty for no passphrase):
+Enter same passphrase again:
+Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
+
+
+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:
+
+
+[root@edozvola-db-01 pgsql]# rsync -rav .ssh edozvola-db-02:`pwd`
+root@edozvola-db-02's password:
+sending incremental file list
+.ssh/
+.ssh/id_rsa
+.ssh/id_rsa.pub
+.ssh/known_hosts
+
+
+
+
+
+
+
+
+kreirati testnu bazu i tablicu
+[root@edozvola-db-01 ~]# sudo -u postgres createdb test
+[root@edozvola-db-01 ~]# sudo -u postgres psql test
+test=# create table test1 ( id serial, a text, b int ) ;
+test=# insert into test1 (a,b) values ('foo',42) ;
+INSERT 0 1
+test=# insert into test1 (a,b) values ('bar',1234) ;
+INSERT 0 1
+
+
+nfs share za wal fileove
+[root@edozvola-db-pgpool ~]# yum install nfs-utils nfs4-acl-tools
+[root@edozvola-db-pgpool ~]# systemctl enable nfs-server.service
+Created symlink from /etc/systemd/system/multi-user.target.wants/nfs-server.service to /usr/lib/systemd/system/nfs-server.service.
+[root@edozvola-db-pgpool ~]# systemctl start nfs-server.service
+
+
+[root@edozvola-db-pgpool ~]# mkdir -p /shared/pgsql
+[root@edozvola-db-pgpool ~]# chown postgres /shared/pgsql/
+
+
+[root@edozvola-db-pgpool ~]# vi /etc/exports
+
+
+[root@edozvola-db-pgpool ~]# cat /etc/exports
+/shared/pgsql edozvola-db-01(rw,no_root_squash) edozvola-db-02(rw,no_root_squash)
+
+
+[root@edozvola-db-pgpool ~]# exportfs -va
+exporting edozvola-db-01:/shared/pgsql
+exporting edozvola-db-02:/shared/pgsql
+
+
+mount na database serverima
+[root@edozvola-db-01 ~]# yum install nfs-utils nfs4-acl-tools
+[root@edozvola-db-01 ~]# mkdir -p /shared/pgsql
+[root@edozvola-db-01 ~]# vi /etc/fstab
+
+
+[root@edozvola-db-01 ~]# grep shared /etc/fstab
+edozvola-db-pgpool:/shared/pgsql        /shared/pgsql   nfs     defaults        0 0
+
+
+[root@edozvola-db-01 ~]# mount /shared/pgsql/
+
+
+provjeriti da li postgresql korisnik moze pisati po nfs share-u
+
+
+[root@edozvola-db-01 ~]# sudo -u postgres touch /shared/pgsql/test-01
+[root@edozvola-db-01 ~]# ls -al /shared/pgsql/test-01
+-rw-r--r-- 1 postgres postgres 0 Aug 18 13:57 /shared/pgsql/test-01
+[root@edozvola-db-01 ~]# rm -f /shared/pgsql/test-01
+
+
+isto ponoviti na edozvola-db-02!
+
+
+
+
+
+
+
+
+
+
+
+
+podesiti wal archiving
+
+
+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 = 1 
+max_replication_slots = 1
+archive_command = 'test ! -f /shared/pgsql/%f && cp %p /shared/pgsql/%f'
+hot_standby = on
+
+
+
+
+
+
+
+
+[root@edozvola-db-01 ~]# systemctl restart postgresql-9.6.service
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+kreirati replication korisnika
+[root@edozvola-db-01 ~]# sudo -u postgres psql
+
+
+postgres=# create user replication with password 'replication123' login replication ;
+CREATE ROLE
+
+
+[root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/pg_hba.conf
+
+
+host    replication     replication     10.200.1.62/24          md5
+
+
+netmask je /24 da mozemo imati istu konfiguraciju na oba node-a
+
+
+
+
+
+
+testirati replikaciju
+[root@edozvola-db-01 ~]# sudo -u postgres psql test
+test=# insert into test1 (a,b) values ('baz',3);
+INSERT 0 1
+
+
+
+
+napraviti slave instancu
+db-01 => db-02
+
+
+[root@edozvola-db-02 ~]# systemctl stop postgresql-9.6.service
+
+
+
+
+[root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_start_backup('to-slave')"
+
+
+[root@edozvola-db-01 data]# rsync -rav --delete /var/lib/pgsql/9.6/data/ edozvola-db-02:/var/lib/pgsql/9.6/data/
+
+
+[root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_stop_backup()"
+
+
+
+
+[root@edozvola-db-02 data]# cp ~/recovery.conf .
+[root@edozvola-db-02 data]# chown postgres recovery.conf
+
+
+[root@edozvola-db-02 data]# systemctl start postgresql-9.6.service
+# ili ovo dolje za bolji debugging:
+[root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
+server starting
+
+
+promote slave to master
+da bi svi podaci zavrsili na slave-u, na masteru je dobro izvrsiti komandu checkpoint
+
+
+
+
+[root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+[root@edozvola-db-02 ~]# cat recovery.conf
+standby_mode = 'on'
+primary_conninfo = 'host=10.200.1.61 port=5432 user=replication password=replication123'
+restore_command = 'cp /shared/pgsql/%f %p'
+archive_cleanup_command = 'pg_archivecleanup /shared/pgsql %r'
+
+
+
+
+skripte
+promoviranje slave-a u master
+
+
+[root@edozvola-db-01 pgsql]# pwd
+/var/lib/pgsql
+[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
+[root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh
+#!/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
+
+
+
+
+#systemctl start postgresql-9.6.service
+/usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
+
+
+
+
+
+
+hot_standby?
-- 
2.20.1