pg_waldump example

diff --git a/db.txt b/db.txt
new file mode 100644 (file)
index 0000000..270de65
--- /dev/null
+++ b/db.txt
@@ -0,0 +1,251 @@
+qemu-img create -f qcow2 -o backing_file=db-backing.qcow2 -F qcow2 db-slave.qcow2
+
+
+
+# network config
+
+[dpavlin@localhost ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth0
+DEVICE=eth0
+IPADDR=10.200.1.10
+NETMASK=255.255.255.0
+ONBOOT=yes
+NAME=eth0
+
+# install postgresql
+
+yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
+
+[root@localhost dpavlin]# yum install postgresql10-server
+
+
+# initdb
+
+[root@localhost data]# su postgres -c "/usr/pgsql-10/bin/initdb --lc-collate=hr_HR.utf8 -D /var/lib/pgsql/10/data/"
+The files belonging to this database system will be owned by user "postgres".
+This user must also own the server process.
+
+The database cluster will be initialized with locales
+  COLLATE:  hr_HR.utf8
+  CTYPE:    en_US.UTF-8
+  MESSAGES: en_US.UTF-8
+  MONETARY: en_US.UTF-8
+  NUMERIC:  en_US.UTF-8
+  TIME:     en_US.UTF-8
+The default database encoding has accordingly been set to "UTF8".
+The default text search configuration will be set to "english".
+
+Data page checksums are disabled.
+
+fixing permissions on existing directory /var/lib/pgsql/10/data ... ok
+creating subdirectories ... ok
+selecting default max_connections ... 100
+selecting default shared_buffers ... 128MB
+selecting default timezone ... Europe/Zagreb
+selecting dynamic shared memory implementation ... posix
+creating configuration files ... ok
+running bootstrap script ... ok
+performing post-bootstrap initialization ... ok
+syncing data to disk ... ok
+
+WARNING: enabling "trust" authentication for local connections
+You can change this by editing pg_hba.conf or using the option -A, or
+--auth-local and --auth-host, the next time you run initdb.
+
+Success. You can now start the database server using:
+
+    /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start
+
+
+
+# systemd
+
+[root@localhost data]# systemctl status postgresql-10
+● postgresql-10.service - PostgreSQL 10 database server
+   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; disabled; vendor preset: disabled)
+   Active: inactive (dead)
+     Docs: https://www.postgresql.org/docs/10/static/
+[root@localhost data]# systemctl enable postgresql-10
+Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-10.service to /usr/lib/systemd/system/postgresql-10.service.
+[root@localhost data]# systemctl start postgresql-10
+[
+
+# streaming replication
+
+https://www.cybertec-postgresql.com/en/setting-up-postgresql-streaming-replication/
+
+sudo vi /var/lib/pgsql/10/data/postgresql.conf
+
+...
+
+[root@slave data]# su postgres
+bash-4.2$ pwd
+/var/lib/pgsql/10/data
+bash-4.2$ pg_basebackup -h 10.200.1.200 -U repuser -D /var/lib/pgsql/10/data/ --checkpoint=fast -R --slot=slave_slot -C
+pg_basebackup: invalid option -- 'C'
+Try "pg_basebackup --help" for more information.
+bash-4.2$ pg_basebackup -h 10.200.1.200 -U repuser -D /var/lib/pgsql/10/data/ --checkpoint=fast -R --slot=slave_slot
+pg_basebackup: could not send replication command "START_REPLICATION": ERROR:  replication slot "slave_slot" does not exist
+pg_basebackup: child process exited with exit code 1
+pg_basebackup: removing contents of data directory "/var/lib/pgsql/10/data/"
+
+
+
+dpavlin=# select pg_create_physical_replication_slot('slave_slot') ;
+ pg_create_physical_replication_slot
+-------------------------------------
+ (slave_slot,)
+(1 row)
+
+
+# wal archive for pitr
+
+https://www.postgresql.org/docs/10/continuous-archiving.html
+
+archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
+
+
+[root@slave 10]# mkdir -p /var/lib/pgsql/10/archive/
+chown postgres:postgres /var/lib/pgsql/10/archive/
+
+vi /var/lib/pgsql/10/data/postgresql.conf
+i
+
+# https://www.postgresql.org/docs/10/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY
+
+archive_mode = always               # enables archiving; off, on, or always
+archive_command = 'test ! -f /var/lib/pgsql/10/archive/%f && cp %p /var/lib/pgsql/10/archive/%f'
+
+
+[root@slave 10]# systemctl restart postgresql-10
+
+dpavlin=# insert into test(t) select i from generate_series(100, 150) as i ;
+
+
+
+dpavlin=# SELECT pg_start_backup('label', false, false);
+- fs backup
+SELECT * FROM pg_stop_backup(false, true);
+
+
+
+
+
+# tuning
+
+https://www.enterprisedb.com/blog/tuning-debian-ubuntu-postgresql
+
+## /etc/sysctl.conf
+
+vm.swappiness = 10
+vm.dirty_expire_centisecs = 500
+vm.dirty_writeback_centisecs = 250
+vm.dirty_ratio = 10
+vm.dirty_background_ratio = 3
+vm.overcommit_memory=2
+net.ipv4.tcp_timestamps=0
+
+/dev/mapper/pgdata-01-data /pgdata xfs         defaults,noatime,nodiratime 1 1
+
+To activate it immediately, run:
+
+mount -o remount,noatime,nodiratime /pgdata
+
+
+# compressed archive_command
+
+https://www.postgresql.org/docs/10/continuous-archiving.html
+
+archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'.
+
+systemctl reload postgresql-10
+
+/usr/pgsql-10/share/recovery.conf.sample
+
+restore_command = 'gunzip < /var/lib/pgsql/10/archive/%f > %p'
+
+!!! full_page_writes on master for pg_basebackup on slave
+!!! logging_collector on if archive_command script used
+
+## create full backup
+
+[root@slave 10]# pg_basebackup --host localhost --username postgres --progress --format t --gzip --write-recovery-conf --pgdata /tmp/test-2021-09-11/ --label test-2021-09-11
+119004/119067 kB (99%), 1/1 tablespace
+
+
+
+## restore backup
+
+mkdir /tmp/r
+chown postgres:postgres /tmp/r
+chmod 700 /tmp/r
+
+tar xvf /tmp/test-2021-09-11/base.tar.gz -C /tmp/r/
+tar xvf /tmp/test-2021-09-11/pg_wal.tar.gz -C /tmp/r/pg_wal/
+
+cp /var/lib/pgsql/10/recovery.conf /tmp/r/
+vi /tmp/r/recovery.conf
+
+restore_command = 'gunzip < /var/lib/pgsql/10/archive/%f > %p'
+
+
+cat postgresql.conf | grep -v archive_mode | grep -v archive_command > postgresql.conf.new && mv postgresql.conf.new postgresql.conf
+
+su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r start -o "-p 5433"'
+
+su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r stop'
+
+
+# wal introspection
+
+## current wal filename on master
+
+SELECT pg_walfile_name(pg_current_wal_lsn());
+
+## switch to next wal file
+
+dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
+     pg_walfile_name
+--------------------------
+ 000000010000000000000020
+(1 row)
+
+dpavlin=# select pg_switch_wal();
+ pg_switch_wal
+---------------
+ 0/20B48BA8
+(1 row)
+
+dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
+     pg_walfile_name
+--------------------------
+ 000000010000000000000021
+(1 row)
+
+## pg_waldump
+
+https://habr.com/en/company/postgrespro/blog/496150/
+
+dpavlin=# SELECT pg_current_wal_insert_lsn();
+ pg_current_wal_insert_lsn
+---------------------------
+ 0/25000140
+(1 row)
+
+[root@slave pg_wal]# /usr/pgsql-10/bin/pg_waldump -p /var/lib/pgsql/10/data/pg_wal/ -s 0/25000140
+
+# TODO
+
+- diskovi
+- archive_command na dr
+
+
+- cdu backup strategy (archive na nfs?)
+- sysctl.conf na svim db-ovima
+- fs noatime,nodiratime na svim db-ovima
+- monitoring db-ova
+- reboot db-ova
+- pristup na grafanu
+- shared screen na serveru (ssh -R ?)
+- test pitr recovery 
+- failover (promjena ip-ja u aplikaciji?)
+

diff --git a/edozvola-db-0x.txt b/edozvola-db-0x.txt
index abf71a6..0e18989 100644 (file)
--- a/edozvola-db-0x.txt
+++ b/edozvola-db-0x.txt
@@ -52,5 +52,5 @@ host    all             all             10.200.1.60/32           trust
 \r
 systemctl enable postgresql-9.6\r
 systemctl start postgresql-9.6\r
-   \r
-reboot
\ No newline at end of file
+\r
+reboot\r

diff --git a/edozvola-pgpool.txt b/edozvola-pgpool.txt
index a080057..6884579 100644 (file)
--- a/edozvola-pgpool.txt
+++ b/edozvola-pgpool.txt
@@ -26,7 +26,7 @@ yum install http://www.pgpool.net/yum/rpms/3.6/redhat/rhel-7-x86_64/pgpool-II-pg
 \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
-# dpavlin -- zapravo ne trebju za pgpool-II-pg96 paket\r
+# dpavlin -- zapravo ne trebju postgresql96-server i pgpool-II-96* jer imamo pgpool-II-pg96 paket\r
 \r
 vim /etc/pgpool-II/pgpool.conf # u konfi je samo promijeno:\r
 \r
@@ -45,11 +45,10 @@ http://www.pgpool.net/docs/latest/en/html/restrictions.html
 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]# systemctl enable pgpool\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
+[root@edozvola-db-pgpool pgpool-II-96]# systemctl start pgpool\r
 \r
 [root@edozvola-db-pgpool pgpool-II-96]# psql -c "show pool_nodes" --host edozvola-db-pgpool --user postgres template1\r
 \r

diff --git a/run.sh b/run.sh
index 7a1c7d6..d628674 100755 (executable)
--- a/run.sh
+++ b/run.sh
@@ -1,8 +1,11 @@
 #!/bin/sh -x
 
-sudo brctl show br200 || sudo brctl addbr br200
+sudo brctl show br200 || (
+sudo brctl addbr br200
 sudo ip link set br200 up
 sudo ifconfig br200 10.200.1.1 netmask 255.255.255.0
+)
+
 case $1 in
        1)
        sudo kvm -m 1024 -hda edozvola-db-01.qcow2 -hdb edozvola-db-01-sdb.qcow2 -vnc :1 -netdev tap,br=br200,id=e1,script=ifup-br200.sh -device virtio-net-pci,netdev=e1,mac=aa:00:10:20:01:61 -netdev user,id=e2 -device virtio-net-pci,netdev=e2 -monitor stdio -pidfile /tmp/edozvola-db-01.pid
@@ -13,4 +16,11 @@ case $1 in
        0)
        sudo kvm -m 1024 -hda edozvola-db-pgpool.qcow2 -vnc :0 -netdev tap,br=br200,id=e1,script=ifup-br200.sh -device virtio-net-pci,netdev=e1,mac=aa:00:10:20:01:60 -netdev user,id=e2 -device virtio-net-pci,netdev=e2 -monitor stdio -pidfile /tmp/edozvola-db-pgpool.pid
        ;;
+       master)
+       sudo kvm -m 1024 -hda db-master.qcow2 -vnc :0 -netdev tap,br=br200,id=e1,script=ifup-br200.sh -device virtio-net-pci,netdev=e1,mac=aa:00:10:20:01:60 -netdev user,id=e2 -device virtio-net-pci,netdev=e2 -monitor stdio -pidfile /tmp/db-master.pid
+       ;;
+       # qemu-img create -f qcow2 -o backing_file=db-backing.qcow2 -F qcow2 db-slave.qcow2
+       slave)
+       sudo kvm -m 1024 -hda db-slave.qcow2 -vnc :1 -netdev tap,br=br200,id=e1,script=ifup-br200.sh -device virtio-net-pci,netdev=e1,mac=aa:00:10:20:01:61 -netdev user,id=e2 -device virtio-net-pci,netdev=e2 -monitor stdio -pidfile /tmp/db-slave.pid
+       ;;
 esac

diff --git a/wal-archive/restore.sh b/wal-archive/restore.sh
new file mode 100755 (executable)
index 0000000..983272b
--- /dev/null
+++ b/wal-archive/restore.sh
@@ -0,0 +1,30 @@
+#!/bin/sh -xe
+
+basebackup=test-2021-09-11
+restore=/tmp/r
+
+mkdir $restore
+chown postgres:postgres $restore
+chmod 700 $restore
+
+tar xvf /tmp/test-2021-09-11/base.tar.gz -C $restore/
+tar xvf /tmp/test-2021-09-11/pg_wal.tar.gz -C $restore/pg_wal/
+
+cp /var/lib/pgsql/10/recovery.conf $restore/
+vi $restore/recovery.conf
+
+cd $restore
+cat postgresql.conf | grep -v archive_mode | grep -v archive_command > postgresql.conf.new && mv postgresql.conf.new postgresql.conf
+
+su postgres -c '/usr/pgsql-10/bin/pg_ctl -D '$restore' start -o "-p 5433"'
+
+cat > $restore.destroy << __SHELL__
+su postgres -c '/usr/pgsql-10/bin/pg_ctl -D $restore stop'
+rm -Rf $restore
+__SHELL__
+
+cd -
+psql -U postgres -p 5433
+
+su postgres -c '/usr/pgsql-10/bin/pg_ctl -D '$restore' stop'
+rm -Rf $restore