# 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" git commit -m base PostgreSQL replikacija, wal shipping, streaming, host standby 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 # same as hot_standby # 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 [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 # recovery.conf [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 [root@edozvola-db-01 pgsql]# pwd /var/lib/pgsql ## 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 [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