pg_waldump example

[edozvola] / db.txt

diff --git a/db.txt b/db.txt
index 9e30e12..270de65 100644 (file)
--- a/db.txt
+++ b/db.txt
@@ -123,11 +123,129 @@ 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);
 
 
 
 
-[root@slave 10]# mkdir /var/lib/pgsql/10/archive.base/
+
+# 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?)