1 # instalirati etckeeper da čuva /etc/ direktorij u git-u
\r
2 # (nije na produkcijskom enviromentu)
\r
4 yum install etckeeper
\r
6 git config --global user.email "dpavlin@rot13.org"
\r
7 git config --global user.name "Dobrica Pavlinusic"
\r
12 https://www.postgresql.org/docs/9.6/static/warm-standby.html
\r
14 # napraviti ssh autorizaciju za postgresql korisnika bez passworda
\r
16 [root@edozvola-db-01 pgsql]# sudo -u postgres bash
\r
19 bash-4.2$ ssh-keygen
\r
20 Generating public/private rsa key pair.
\r
21 Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
\r
22 Created directory '/var/lib/pgsql/.ssh'.
\r
23 Enter passphrase (empty for no passphrase):
\r
24 Enter same passphrase again:
\r
25 Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
\r
28 bash-4.2$ cp -v .ssh/id_rsa.pub .ssh/authorized_keys
\r
29 ‘.ssh/id_rsa.pub’ -> ‘.ssh/authorized_keys’
\r
32 ## prekopirati isti kljuc i authoriorized keys na slave:
\r
35 [root@edozvola-db-01 pgsql]# rsync -rav .ssh edozvola-db-02:`pwd`
\r
36 root@edozvola-db-02's password:
\r
37 sending incremental file list
\r
50 # kreirati testnu bazu i tablicu
\r
52 [root@edozvola-db-01 ~]# sudo -u postgres createdb test
\r
53 [root@edozvola-db-01 ~]# sudo -u postgres psql test
\r
54 test=# create table test1 ( id serial, a text, b int ) ;
\r
55 test=# insert into test1 (a,b) values ('foo',42) ;
\r
57 test=# insert into test1 (a,b) values ('bar',1234) ;
\r
62 # nfs share za wal fileove
\r
64 [root@edozvola-db-pgpool ~]# yum install nfs-utils nfs4-acl-tools
\r
65 [root@edozvola-db-pgpool ~]# systemctl enable nfs-server.service
\r
66 Created symlink from /etc/systemd/system/multi-user.target.wants/nfs-server.service to /usr/lib/systemd/system/nfs-server.service.
\r
67 [root@edozvola-db-pgpool ~]# systemctl start nfs-server.service
\r
69 [root@edozvola-db-pgpool ~]# mkdir -p /shared/pgsql
\r
70 [root@edozvola-db-pgpool ~]# chown postgres /shared/pgsql/
\r
72 [root@edozvola-db-pgpool ~]# vi /etc/exports
\r
74 [root@edozvola-db-pgpool ~]# cat /etc/exports
\r
75 /shared/pgsql edozvola-db-01(rw,no_root_squash) edozvola-db-02(rw,no_root_squash)
\r
77 [root@edozvola-db-pgpool ~]# exportfs -va
\r
78 exporting edozvola-db-01:/shared/pgsql
\r
79 exporting edozvola-db-02:/shared/pgsql
\r
83 # mount na database serverima
\r
85 [root@edozvola-db-01 ~]# yum install nfs-utils nfs4-acl-tools
\r
86 [root@edozvola-db-01 ~]# mkdir -p /shared/pgsql
\r
87 [root@edozvola-db-01 ~]# vi /etc/fstab
\r
89 [root@edozvola-db-01 ~]# grep shared /etc/fstab
\r
90 edozvola-db-pgpool:/shared/pgsql /shared/pgsql nfs defaults 0 0
\r
92 [root@edozvola-db-01 ~]# mount /shared/pgsql/
\r
94 ## provjeriti da li postgresql korisnik moze pisati po nfs share-u
\r
96 [root@edozvola-db-01 ~]# sudo -u postgres touch /shared/pgsql/test-01
\r
97 [root@edozvola-db-01 ~]# ls -al /shared/pgsql/test-01
\r
98 -rw-r--r-- 1 postgres postgres 0 Aug 18 13:57 /shared/pgsql/test-01
\r
99 [root@edozvola-db-01 ~]# rm -f /shared/pgsql/test-01
\r
101 ## isto ponoviti na edozvola-db-02!
\r
107 # podesiti wal archiving
\r
109 https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
\r
112 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf
\r
113 wal_level = replica
\r
115 # https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
\r
116 synchronous_commit = on
\r
119 archive_command = 'test ! -f /shared/pgsql/%f && cp %p /shared/pgsql/%f'
\r
121 max_wal_senders = 3
\r
122 max_replication_slots = 3
\r
127 [root@edozvola-db-01 ~]# systemctl restart postgresql-9.6.service
\r
131 # kreirati replication korisnika
\r
133 [root@edozvola-db-01 ~]# sudo -u postgres psql
\r
135 postgres=# create user replication with password 'replication123' login replication ;
\r
139 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/pg_hba.conf
\r
142 host replication replication 10.200.1.62/24 md5
\r
144 ## netmask je /24 da mozemo imati istu konfiguraciju na oba node-a
\r
150 # testirati replikaciju
\r
151 [root@edozvola-db-01 ~]# sudo -u postgres psql test
\r
152 test=# insert into test1 (a,b) values ('baz',3);
\r
158 napraviti slave instancu
\r
162 [root@edozvola-db-02 ~]# systemctl stop postgresql-9.6.service
\r
167 [root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_start_backup('to-slave')"
\r
170 [root@edozvola-db-01 data]# rsync -rav --delete /var/lib/pgsql/9.6/data/ edozvola-db-02:/var/lib/pgsql/9.6/data/
\r
173 [root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_stop_backup()"
\r
178 [root@edozvola-db-02 data]# cp ~/recovery.conf .
\r
179 [root@edozvola-db-02 data]# chown postgres recovery.conf
\r
182 [root@edozvola-db-02 data]# systemctl start postgresql-9.6.service
\r
183 # ili ovo dolje za bolji debugging:
\r
184 [root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
\r
188 promote slave to master
\r
189 da bi svi podaci zavrsili na slave-u, na masteru je dobro izvrsiti komandu checkpoint
\r
194 [root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
\r
213 [root@edozvola-db-02 ~]# cat recovery.conf
\r
214 standby_mode = 'on'
\r
215 primary_conninfo = 'host=10.200.1.61 port=5432 user=replication password=replication123'
\r
216 restore_command = 'cp /shared/pgsql/%f %p'
\r
217 archive_cleanup_command = 'pg_archivecleanup /shared/pgsql %r'
\r
225 [root@edozvola-db-01 pgsql]# pwd
\r
229 ## promoviranje slave-a u master
\r
231 [root@edozvola-db-01 pgsql]# cat promote-slave-to-master.sh
\r
233 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
\r
236 ## kreiranje slave instance od mastera
\r
238 [root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh
\r
242 # WARNING: this script depends that two database nodes have IPs which and in x1 and x2
\r
243 # tr 12 21 does swap from currnet node number/ip to other one!
\r
246 test "`whoami`" != "postgres" && echo "$0 needs to be run as postgres user!" && exit 1
\r
250 there=`hostname -s | tr 12 21`
\r
253 my_ip=`ip addr | grep 10.200.1. | sed 's/^.*\(10\.200\.1\.[0-9]*\)\/.*$/\1/'`
\r
254 other_ip=`echo $my_ip | tr 12 21`
\r
257 echo "Create slave on $here $my_ip using data from master on $there $other_ip"
\r
258 echo "[enter] to start"
\r
262 #systemctl stop postgresql-9.6.service || true
\r
263 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data stop || true
\r
266 echo "select pg_start_backup('slave')" | ssh $there psql
\r
269 rsync -rav --delete --exclude pg_xlog $there:/var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data/
\r
272 echo "select pg_stop_backup()" | ssh $there psql
\r
275 test -e /var/lib/pgsql/9.6/data/recovery.done || ( echo "ERROR: no recovery.done !" && exit 1 )
\r
276 cat /var/lib/pgsql/9.6/data/recovery.done | sed -e s/$my_ip/$other_ip/ > /var/lib/pgsql/9.6/data/recovery.conf
\r
281 #systemctl start postgresql-9.6.service
\r
282 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
\r