X-Git-Url: http://git.rot13.org/?p=edozvola;a=blobdiff_plain;f=db.txt;h=270de658bc299996ba8c333bd516d3be807adb71;hp=bcf639590118e6a41c929171fcfff0d642944883;hb=HEAD;hpb=9c703b239ddb26811140b2592e067796e23de2cc

diff --git a/db.txt b/db.txt
index bcf6395..270de65 100644
--- a/db.txt
+++ b/db.txt
@@ -105,10 +105,14 @@ archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/arch
 
 
 [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
-archive_mode = on               # enables archiving; off, on, or always
+
+# 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'
 
 
@@ -118,6 +122,130 @@ 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
+
 
-[root@slave 10]# mkdir /var/lib/pgsql/10/archive.base/
+- 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?)