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

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