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
11 PostgreSQL replikacija, wal shipping, streaming, host standby
\r
14 https://www.postgresql.org/docs/9.6/static/warm-standby.html
\r
16 # napraviti ssh autorizaciju za postgresql korisnika bez passworda
\r
18 [root@edozvola-db-01 pgsql]# sudo -u postgres bash
\r
21 bash-4.2$ ssh-keygen
\r
22 Generating public/private rsa key pair.
\r
23 Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
\r
24 Created directory '/var/lib/pgsql/.ssh'.
\r
25 Enter passphrase (empty for no passphrase):
\r
26 Enter same passphrase again:
\r
27 Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
\r
30 bash-4.2$ cp -v .ssh/id_rsa.pub .ssh/authorized_keys
\r
31 ‘.ssh/id_rsa.pub’ -> ‘.ssh/authorized_keys’
\r
34 ## prekopirati isti kljuc i authoriorized keys na slave:
\r
37 [root@edozvola-db-01 pgsql]# rsync -rav .ssh edozvola-db-02:`pwd`
\r
38 root@edozvola-db-02's password:
\r
39 sending incremental file list
\r
52 # kreirati testnu bazu i tablicu
\r
54 [root@edozvola-db-01 ~]# sudo -u postgres createdb test
\r
55 [root@edozvola-db-01 ~]# sudo -u postgres psql test
\r
56 test=# create table test1 ( id serial, a text, b int ) ;
\r
57 test=# insert into test1 (a,b) values ('foo',42) ;
\r
59 test=# insert into test1 (a,b) values ('bar',1234) ;
\r
64 # nfs share za wal fileove
\r
66 [root@edozvola-db-pgpool ~]# yum install nfs-utils nfs4-acl-tools
\r
67 [root@edozvola-db-pgpool ~]# systemctl enable nfs-server.service
\r
68 Created symlink from /etc/systemd/system/multi-user.target.wants/nfs-server.service to /usr/lib/systemd/system/nfs-server.service.
\r
69 [root@edozvola-db-pgpool ~]# systemctl start nfs-server.service
\r
71 [root@edozvola-db-pgpool ~]# mkdir -p /shared/pgsql
\r
72 [root@edozvola-db-pgpool ~]# chown postgres /shared/pgsql/
\r
74 [root@edozvola-db-pgpool ~]# vi /etc/exports
\r
76 [root@edozvola-db-pgpool ~]# cat /etc/exports
\r
77 /shared/pgsql edozvola-db-01(rw,no_root_squash) edozvola-db-02(rw,no_root_squash)
\r
79 [root@edozvola-db-pgpool ~]# exportfs -va
\r
80 exporting edozvola-db-01:/shared/pgsql
\r
81 exporting edozvola-db-02:/shared/pgsql
\r
85 # mount na database serverima
\r
87 [root@edozvola-db-01 ~]# yum install nfs-utils nfs4-acl-tools
\r
88 [root@edozvola-db-01 ~]# mkdir -p /shared/pgsql
\r
89 [root@edozvola-db-01 ~]# vi /etc/fstab
\r
91 [root@edozvola-db-01 ~]# grep shared /etc/fstab
\r
92 edozvola-db-pgpool:/shared/pgsql /shared/pgsql nfs defaults 0 0
\r
94 [root@edozvola-db-01 ~]# mount /shared/pgsql/
\r
96 ## provjeriti da li postgresql korisnik moze pisati po nfs share-u
\r
98 [root@edozvola-db-01 ~]# sudo -u postgres touch /shared/pgsql/test-01
\r
99 [root@edozvola-db-01 ~]# ls -al /shared/pgsql/test-01
\r
100 -rw-r--r-- 1 postgres postgres 0 Aug 18 13:57 /shared/pgsql/test-01
\r
101 [root@edozvola-db-01 ~]# rm -f /shared/pgsql/test-01
\r
103 ## isto ponoviti na edozvola-db-02!
\r
109 # podesiti wal archiving
\r
111 https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
\r
114 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf
\r
115 wal_level = replica # same as hot_standby
\r
117 # https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
\r
118 synchronous_commit = on
\r
121 archive_command = 'test ! -f /shared/pgsql/%f && cp %p /shared/pgsql/%f'
\r
123 max_wal_senders = 3
\r
124 max_replication_slots = 3
\r
129 [root@edozvola-db-01 ~]# systemctl restart postgresql-9.6.service
\r
133 # kreirati replication korisnika
\r
135 [root@edozvola-db-01 ~]# sudo -u postgres psql
\r
137 postgres=# create user replication with password 'replication123' login replication ;
\r
141 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/pg_hba.conf
\r
144 host replication replication 10.200.1.62/24 md5
\r
146 ## netmask je /24 da mozemo imati istu konfiguraciju na oba node-a
\r
152 # testirati replikaciju
\r
153 [root@edozvola-db-01 ~]# sudo -u postgres psql test
\r
154 test=# insert into test1 (a,b) values ('baz',3);
\r
160 napraviti slave instancu
\r
164 [root@edozvola-db-02 ~]# systemctl stop postgresql-9.6.service
\r
169 [root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_start_backup('to-slave')"
\r
172 [root@edozvola-db-01 data]# rsync -rav --delete /var/lib/pgsql/9.6/data/ edozvola-db-02:/var/lib/pgsql/9.6/data/
\r
175 [root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_stop_backup()"
\r
180 [root@edozvola-db-02 data]# cp ~/recovery.conf .
\r
181 [root@edozvola-db-02 data]# chown postgres recovery.conf
\r
184 [root@edozvola-db-02 data]# systemctl start postgresql-9.6.service
\r
185 # ili ovo dolje za bolji debugging:
\r
186 [root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
\r
190 promote slave to master
\r
191 da bi svi podaci zavrsili na slave-u, na masteru je dobro izvrsiti komandu checkpoint
\r
196 [root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
\r
215 [root@edozvola-db-02 ~]# cat recovery.conf
\r
216 standby_mode = 'on'
\r
217 primary_conninfo = 'host=10.200.1.61 port=5432 user=replication password=replication123'
\r
218 restore_command = 'cp /shared/pgsql/%f %p'
\r
219 archive_cleanup_command = 'pg_archivecleanup /shared/pgsql %r'
\r
227 [root@edozvola-db-01 pgsql]# pwd
\r
231 ## promoviranje slave-a u master
\r
233 [root@edozvola-db-01 pgsql]# cat promote-slave-to-master.sh
\r
235 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
\r
238 ## kreiranje slave instance od mastera
\r
240 [root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh
\r
243 # WARNING: this script depends that two database nodes have IPs which and in x1 and x2
\r
244 # 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
249 there=`hostname -s | tr 12 21`
\r
251 my_ip=`ip addr | grep 10.200.1. | sed 's/^.*\(10\.200\.1\.[0-9]*\)\/.*$/\1/'`
\r
252 other_ip=`echo $my_ip | tr 12 21`
\r
254 echo "Create slave on $here $my_ip using data from master on $there $other_ip"
\r
255 echo "[enter] to start"
\r
258 #systemctl stop postgresql-9.6.service || true
\r
259 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data stop || true
\r
261 echo "select pg_start_backup('slave')" | ssh $there psql
\r
263 rsync -rav --delete --exclude pg_xlog $there:/var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data/
\r
265 echo "select pg_stop_backup()" | ssh $there psql
\r
267 test -e /var/lib/pgsql/9.6/data/recovery.done || ( echo "ERROR: no recovery.done !" && exit 1 )
\r
268 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
273 #systemctl start postgresql-9.6.service
\r
274 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
\r