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?)