pgpool updates

diff --git a/edozvola-dpavlin.txt b/edozvola-dpavlin.txt
index c248649..b8ab38b 100644 (file)
--- a/edozvola-dpavlin.txt
+++ b/edozvola-dpavlin.txt
@@ -8,6 +8,8 @@ git config --global user.name "Dobrica Pavlinusic"
 git commit -m base\r
 \r
 \r
 git commit -m base\r
 \r
 \r

+PostgreSQL replikacija, wal shipping, streaming, host standby\r
+\r

 \r
 https://www.postgresql.org/docs/9.6/static/warm-standby.html\r
 \r
 \r
 https://www.postgresql.org/docs/9.6/static/warm-standby.html\r
 \r


@@ -110,7 +112,7 @@ https://www.postgresql.org/docs/9.6/static/continuous-archiving.html
 \r
 \r
 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf\r
 \r
 \r
 [root@edozvola-db-01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf\r

-wal_level = replica\r
+wal_level = replica # same as hot_standby\r

 \r
 # https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT\r
 synchronous_commit = on\r
 \r
 # https://www.postgresql.org/docs/9.6/static/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT\r
 synchronous_commit = on\r


@@ -208,7 +210,7 @@ da bi svi podaci zavrsili na slave-u, na masteru je dobro izvrsiti komandu check
 \r
 \r
 \r
 \r
 \r
 \r

-\r
+# recovery.conf\r

 \r
 [root@edozvola-db-02 ~]# cat recovery.conf\r
 standby_mode = 'on'\r
 \r
 [root@edozvola-db-02 ~]# cat recovery.conf\r
 standby_mode = 'on'\r


@@ -238,40 +240,30 @@ archive_cleanup_command = 'pg_archivecleanup /shared/pgsql %r'
 [root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh\r
 #!/bin/sh -e\r
 \r
 [root@edozvola-db-01 pgsql]# cat create-pgsql-slave.sh\r
 #!/bin/sh -e\r
 \r

-\r

 # WARNING: this script depends that two database nodes have IPs which and in x1 and x2\r
 # tr 12 21 does swap from currnet node number/ip to other one!\r
 \r
 # WARNING: this script depends that two database nodes have IPs which and in x1 and x2\r
 # tr 12 21 does swap from currnet node number/ip to other one!\r
 \r

-\r

 test "`whoami`" != "postgres" && echo "$0 needs to be run as postgres user!" && exit 1\r
 \r
 test "`whoami`" != "postgres" && echo "$0 needs to be run as postgres user!" && exit 1\r
 \r

-\r

 here=`hostname -s`\r
 there=`hostname -s | tr 12 21`\r
 \r
 here=`hostname -s`\r
 there=`hostname -s | tr 12 21`\r
 \r

-\r

 my_ip=`ip addr | grep 10.200.1. | sed 's/^.*\(10\.200\.1\.[0-9]*\)\/.*$/\1/'`\r
 other_ip=`echo $my_ip | tr 12 21`\r
 \r
 my_ip=`ip addr | grep 10.200.1. | sed 's/^.*\(10\.200\.1\.[0-9]*\)\/.*$/\1/'`\r
 other_ip=`echo $my_ip | tr 12 21`\r
 \r

-\r

 echo "Create slave on $here $my_ip using data from master on $there $other_ip"\r
 echo "[enter] to start"\r
 read\r
 \r
 echo "Create slave on $here $my_ip using data from master on $there $other_ip"\r
 echo "[enter] to start"\r
 read\r
 \r

-\r

 #systemctl stop postgresql-9.6.service || true\r
 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data stop || true\r
 \r
 #systemctl stop postgresql-9.6.service || true\r
 /usr/pgsql-9.6/bin/pg_ctl --pgdata=/var/lib/pgsql/9.6/data stop || true\r
 \r

-\r

 echo "select pg_start_backup('slave')" | ssh $there psql\r
 \r
 echo "select pg_start_backup('slave')" | ssh $there psql\r
 \r

-\r

 rsync -rav --delete --exclude pg_xlog $there:/var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data/\r
 \r
 rsync -rav --delete --exclude pg_xlog $there:/var/lib/pgsql/9.6/data/ /var/lib/pgsql/9.6/data/\r
 \r

-\r

 echo "select pg_stop_backup()" | ssh $there psql\r
 \r
 echo "select pg_stop_backup()" | ssh $there psql\r
 \r

-\r

 test -e /var/lib/pgsql/9.6/data/recovery.done || ( echo "ERROR: no recovery.done !" && exit 1 )\r
 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
 \r
 test -e /var/lib/pgsql/9.6/data/recovery.done || ( echo "ERROR: no recovery.done !" && exit 1 )\r
 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
 \r


@@ -286,4 +278,3 @@ cat /var/lib/pgsql/9.6/data/recovery.done | sed -e s/$my_ip/$other_ip/ > /var/li
 \r
 \r
 \r
 \r
 \r
 \r

-hot_standby?\r

diff --git a/edozvola-pgpool.txt b/edozvola-pgpool.txt
index f311bf8..a080057 100644 (file)
--- a/edozvola-pgpool.txt
+++ b/edozvola-pgpool.txt
@@ -25,7 +25,8 @@ yum install https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.
 yum install http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-pg96-3.6.5-1pgdg.rhel7.x86_64.rpm\r
 \r
 # instaliran postgres i pgpool (postgres je instaliran jer treba pgpool-u)\r
 yum install http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-pg96-3.6.5-1pgdg.rhel7.x86_64.rpm\r
 \r
 # instaliran postgres i pgpool (postgres je instaliran jer treba pgpool-u)\r

-yum install postgresql96 postgresql96-server pgpool-II-96 pgpool-II-96-extensions\r
+#yum install postgresql96 postgresql96-server pgpool-II-96 pgpool-II-96-extensions\r
+# dpavlin -- zapravo ne trebju za pgpool-II-pg96 paket\r

 \r
 vim /etc/pgpool-II/pgpool.conf # u konfi je samo promijeno:\r
 \r
 \r
 vim /etc/pgpool-II/pgpool.conf # u konfi je samo promijeno:\r
 \r


@@ -33,3 +34,26 @@ listen_addresses = '*'
 port = 5432\r
 \r
 reboot\r
 port = 5432\r
 \r
 reboot\r

+\r
+# konfiguracija pgpool-II za read balancing\r
+\r
+http://www.pgpool.net/docs/latest/en/html/restrictions.html\r
+\r
+## create data directory za back-end-e\r
+\r
+[root@edozvola-db-pgpool ~]# mkdir -v -p /var/lib/pgsql/edozvola-db-0{1,2}\r
+mkdir: created directory ‘/var/lib/pgsql/edozvola-db-01’\r
+mkdir: created directory ‘/var/lib/pgsql/edozvola-db-02’\r
+\r
+[root@edozvola-db-pgpool pgpool-II-96]# chmod 644 /usr/lib/systemd/system/pgpool-II-96.service\r
+[root@edozvola-db-pgpool pgpool-II-96]# systemctl enable pgpool-II-96\r
+[root@edozvola-db-pgpool pgpool-II-96]# mkdir -p /var/run/pgpool/\r
+[root@edozvola-db-pgpool pgpool-II-96]# mkdir -p /var/log/pgpool/\r
+[root@edozvola-db-pgpool pgpool-II-96]# systemctl start pgpool-II-96\r
+\r
+[root@edozvola-db-pgpool pgpool-II-96]# psql -c "show pool_nodes" --host edozvola-db-pgpool --user postgres template1\r
+\r
+\r
+http://www.pgpool.net/docs/latest/en/html/runtime-config-load-balancing.html\r
+\r
+\r