db configuration master/slave streaming

diff --git a/db.txt b/db.txt
new file mode 100644 (file)
index 0000000..bcf6395
--- /dev/null
+++ b/db.txt
@@ -0,0 +1,123 @@
+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/
+
+vi /var/lib/pgsql/10/data/postgresql.conf
+i
+archive_mode = on               # 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 ;
+
+
+
+
+[root@slave 10]# mkdir /var/lib/pgsql/10/archive.base/
+