1 yum install etckeeper
\r
3 git config --global user.email "dpavlin@rot13.org"
\r
4 git config --global user.name "Dobrica Pavlinusic"
\r
9 https://www.postgresql.org/docs/9.6/static/warm-standby.html
\r
11 # napraviti ssh autorizaciju za postgresql korisnika bez passworda
\r
13 [root@edozvola-db-01 pgsql]# sudo -u postgres bash
\r
16 bash-4.2$ ssh-keygen
\r
17 Generating public/private rsa key pair.
\r
18 Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
\r
19 Created directory '/var/lib/pgsql/.ssh'.
\r
20 Enter passphrase (empty for no passphrase):
\r
21 Enter same passphrase again:
\r
22 Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
\r
25 bash-4.2$ cp -v .ssh/id_rsa.pub .ssh/authorized_keys
\r
26 ‘.ssh/id_rsa.pub’ -> ‘.ssh/authorized_keys’
\r
29 prekopirati isti kljuc i authoriorized keys na slave:
\r
32 [root@edozvola-db-01 pgsql]# rsync -rav .ssh edozvola-db-02:`pwd`
\r
33 root@edozvola-db-02's password:
\r
34 sending incremental file list
\r
47 # kreirati testnu bazu i tablicu
\r
49 [root@edozvola-db-01 ~]# sudo -u postgres createdb test
\r
50 [root@edozvola-db-01 ~]# sudo -u postgres psql test
\r
51 test=# create table test1 ( id serial, a text, b int ) ;
\r
52 test=# insert into test1 (a,b) values ('foo',42) ;
\r
54 test=# insert into test1 (a,b) values ('bar',1234) ;
\r
59 # nfs share za wal fileove
\r
61 [root@edozvola-db-pgpool ~]# yum install nfs-utils nfs4-acl-tools
\r
62 [root@edozvola-db-pgpool ~]# systemctl enable nfs-server.service
\r
63 Created symlink from /etc/systemd/system/multi-user.target.wants/nfs-server.service to /usr/lib/systemd/system/nfs-server.service.
\r
64 [root@edozvola-db-pgpool ~]# systemctl start nfs-server.service
\r
66 [root@edozvola-db-pgpool ~]# mkdir -p /shared/pgsql
\r
67 [root@edozvola-db-pgpool ~]# chown postgres /shared/pgsql/
\r
69 [root@edozvola-db-pgpool ~]# vi /etc/exports
\r
71 [root@edozvola-db-pgpool ~]# cat /etc/exports
\r
72 /shared/pgsql edozvola-db-01(rw,no_root_squash) edozvola-db-02(rw,no_root_squash)
\r
74 [root@edozvola-db-pgpool ~]# exportfs -va
\r
75 exporting edozvola-db-01:/shared/pgsql
\r
76 exporting edozvola-db-02:/shared/pgsql
\r
80 # mount na database serverima
\r
82 [root@edozvola-db-01 ~]# yum install nfs-utils nfs4-acl-tools
\r
83 [root@edozvola-db-01 ~]# mkdir -p /shared/pgsql
\r
84 [root@edozvola-db-01 ~]# vi /etc/fstab
\r
86 [root@edozvola-db-01 ~]# grep shared /etc/fstab
\r
87 edozvola-db-pgpool:/shared/pgsql /shared/pgsql nfs defaults 0 0
\r
89 [root@edozvola-db-01 ~]# mount /shared/pgsql/
\r
91 ## provjeriti da li postgresql korisnik moze pisati po nfs share-u
\r
93 [root@edozvola-db-01 ~]# sudo -u postgres touch /shared/pgsql/test-01
\r
94 [root@edozvola-db-01 ~]# ls -al /shared/pgsql/test-01
\r
95 -rw-r--r-- 1 postgres postgres 0 Aug 18 13:57 /shared/pgsql/test-01
\r
96 [root@edozvola-db-01 ~]# rm -f /shared/pgsql/test-01
\r
98 ## isto ponoviti na edozvola-db-02!
\r
104 # podesiti wal archiving
\r
106 https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
\r
109 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf
\r
110 wal_level = replica
\r
112 max_wal_senders = 3
\r
113 max_replication_slots = 3
\r
114 archive_command = 'test ! -f /shared/pgsql/%f && cp %p /shared/pgsql/%f'
\r
124 [root@edozvola-db-01 ~]# systemctl restart postgresql-9.6.service
\r
141 kreirati replication korisnika
\r
142 [root@edozvola-db-01 ~]# sudo -u postgres psql
\r
145 postgres=# create user replication with password 'replication123' login replication ;
\r
149 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/pg_hba.conf
\r
152 host replication replication 10.200.1.62/24 md5
\r
155 netmask je /24 da mozemo imati istu konfiguraciju na oba node-a
\r
162 testirati replikaciju
\r
163 [root@edozvola-db-01 ~]# sudo -u postgres psql test
\r
164 test=# insert into test1 (a,b) values ('baz',3);
\r
170 napraviti slave instancu
\r
174 [root@edozvola-db-02 ~]# systemctl stop postgresql-9.6.service
\r
179 [root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_start_backup('to-slave')"
\r
182 [root@edozvola-db-01 data]# rsync -rav --delete /var/lib/pgsql/9.6/data/ edozvola-db-02:/var/lib/pgsql/9.6/data/
\r
185 [root@edozvola-db-01 data]# sudo -u postgres psql -c "select pg_stop_backup()"
\r
190 [root@edozvola-db-02 data]# cp ~/recovery.conf .
\r
191 [root@edozvola-db-02 data]# chown postgres recovery.conf
\r
194 [root@edozvola-db-02 data]# systemctl start postgresql-9.6.service
\r
195 # ili ovo dolje za bolji debugging:
\r
196 [root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
\r
200 promote slave to master
\r
201 da bi svi podaci zavrsili na slave-u, na masteru je dobro izvrsiti komandu checkpoint
\r
206 [root@edozvola-db-02 data]# sudo -u postgres /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
\r
225 [root@edozvola-db-02 ~]# cat recovery.conf
\r
226 standby_mode = 'on'
\r
227 primary_conninfo = 'host=10.200.1.61 port=5432 user=replication password=replication123'
\r
228 restore_command = 'cp /shared/pgsql/%f %p'
\r
229 archive_cleanup_command = 'pg_archivecleanup /shared/pgsql %r'
\r
235 promoviranje slave-a u master
\r
238 [root@edozvola-db-01 pgsql]# pwd
\r
240 [root@edozvola-db-01 pgsql]# cat promote-slave-to-master.sh
\r
246 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data promote
\r
253 kreiranje slave instance od mastera
\r
254 [root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh
\r
258 # WARNING: this script depends that two database nodes have IPs which and in x1 and x2
\r
259 # tr 12 21 does swap from currnet node number/ip to other one!
\r
262 test "`whoami`" != "postgres" && echo "$0 needs to be run as postgres user!" && exit 1
\r
266 there=`hostname -s | tr 12 21`
\r
269 my_ip=`ip addr | grep 10.200.1. | sed 's/^.*\(10\.200\.1\.[0-9]*\)\/.*$/\1/'`
\r
270 other_ip=`echo $my_ip | tr 12 21`
\r
273 echo "Create slave on $here $my_ip using data from master on $there $other_ip"
\r
274 echo "[enter] to start"
\r
278 #systemctl stop postgresql-9.6.service || true
\r
279 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data stop || true
\r
282 echo "select pg_start_backup('slave')" | ssh $there psql
\r
285 rsync -rav --delete --exclude pg_xlog $there:/var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data/
\r
288 echo "select pg_stop_backup()" | ssh $there psql
\r
291 test -e /var/lib/pgsql/9.6/data/recovery.done || ( echo "ERROR: no recovery.done !" && exit 1 )
\r
292 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
297 #systemctl start postgresql-9.6.service
\r
298 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data start
\r