1 qemu-img create -f qcow2 -o backing_file=db-backing.qcow2 -F qcow2 db-slave.qcow2
7 [dpavlin@localhost ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth0
16 yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
18 [root@localhost dpavlin]# yum install postgresql10-server
23 [root@localhost data]# su postgres -c "/usr/pgsql-10/bin/initdb --lc-collate=hr_HR.utf8 -D /var/lib/pgsql/10/data/"
24 The files belonging to this database system will be owned by user "postgres".
25 This user must also own the server process.
27 The database cluster will be initialized with locales
34 The default database encoding has accordingly been set to "UTF8".
35 The default text search configuration will be set to "english".
37 Data page checksums are disabled.
39 fixing permissions on existing directory /var/lib/pgsql/10/data ... ok
40 creating subdirectories ... ok
41 selecting default max_connections ... 100
42 selecting default shared_buffers ... 128MB
43 selecting default timezone ... Europe/Zagreb
44 selecting dynamic shared memory implementation ... posix
45 creating configuration files ... ok
46 running bootstrap script ... ok
47 performing post-bootstrap initialization ... ok
48 syncing data to disk ... ok
50 WARNING: enabling "trust" authentication for local connections
51 You can change this by editing pg_hba.conf or using the option -A, or
52 --auth-local and --auth-host, the next time you run initdb.
54 Success. You can now start the database server using:
56 /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start
62 [root@localhost data]# systemctl status postgresql-10
63 ● postgresql-10.service - PostgreSQL 10 database server
64 Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; disabled; vendor preset: disabled)
65 Active: inactive (dead)
66 Docs: https://www.postgresql.org/docs/10/static/
67 [root@localhost data]# systemctl enable postgresql-10
68 Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-10.service to /usr/lib/systemd/system/postgresql-10.service.
69 [root@localhost data]# systemctl start postgresql-10
72 # streaming replication
74 https://www.cybertec-postgresql.com/en/setting-up-postgresql-streaming-replication/
76 sudo vi /var/lib/pgsql/10/data/postgresql.conf
80 [root@slave data]# su postgres
82 /var/lib/pgsql/10/data
83 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
84 pg_basebackup: invalid option -- 'C'
85 Try "pg_basebackup --help" for more information.
86 bash-4.2$ pg_basebackup -h 10.200.1.200 -U repuser -D /var/lib/pgsql/10/data/ --checkpoint=fast -R --slot=slave_slot
87 pg_basebackup: could not send replication command "START_REPLICATION": ERROR: replication slot "slave_slot" does not exist
88 pg_basebackup: child process exited with exit code 1
89 pg_basebackup: removing contents of data directory "/var/lib/pgsql/10/data/"
93 dpavlin=# select pg_create_physical_replication_slot('slave_slot') ;
94 pg_create_physical_replication_slot
95 -------------------------------------
100 # wal archive for pitr
102 https://www.postgresql.org/docs/10/continuous-archiving.html
104 archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f' # Unix
107 [root@slave 10]# mkdir -p /var/lib/pgsql/10/archive/
108 chown postgres:postgres /var/lib/pgsql/10/archive/
110 vi /var/lib/pgsql/10/data/postgresql.conf
113 # https://www.postgresql.org/docs/10/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY
115 archive_mode = always # enables archiving; off, on, or always
116 archive_command = 'test ! -f /var/lib/pgsql/10/archive/%f && cp %p /var/lib/pgsql/10/archive/%f'
119 [root@slave 10]# systemctl restart postgresql-10
121 dpavlin=# insert into test(t) select i from generate_series(100, 150) as i ;
125 dpavlin=# SELECT pg_start_backup('label', false, false);
127 SELECT * FROM pg_stop_backup(false, true);
135 https://www.enterprisedb.com/blog/tuning-debian-ubuntu-postgresql
140 vm.dirty_expire_centisecs = 500
141 vm.dirty_writeback_centisecs = 250
143 vm.dirty_background_ratio = 3
144 vm.overcommit_memory=2
145 net.ipv4.tcp_timestamps=0
147 /dev/mapper/pgdata-01-data /pgdata xfs defaults,noatime,nodiratime 1 1
149 To activate it immediately, run:
151 mount -o remount,noatime,nodiratime /pgdata
154 # compressed archive_command
156 https://www.postgresql.org/docs/10/continuous-archiving.html
158 archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'.
160 systemctl reload postgresql-10
162 /usr/pgsql-10/share/recovery.conf.sample
164 restore_command = 'gunzip < /var/lib/pgsql/10/archive/%f > %p'
166 !!! full_page_writes on master for pg_basebackup on slave
167 !!! logging_collector on if archive_command script used
169 ## create full backup
171 [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
172 119004/119067 kB (99%), 1/1 tablespace
179 chown postgres:postgres /tmp/r
182 tar xvf /tmp/test-2021-09-11/base.tar.gz -C /tmp/r/
183 tar xvf /tmp/test-2021-09-11/pg_wal.tar.gz -C /tmp/r/pg_wal/
185 cp /var/lib/pgsql/10/recovery.conf /tmp/r/
186 vi /tmp/r/recovery.conf
188 cat postgresql.conf | grep -v archive_mode | grep -v archive_command > postgresql.conf.new && mv postgresql.conf.new postgresql.conf
190 su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r start -o "-p 5433"'
192 su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r stop'
197 ## current wal filename on master
199 SELECT pg_walfile_name(pg_current_wal_lsn());
201 ## switch to next wal file
203 dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
205 --------------------------
206 000000010000000000000020
209 dpavlin=# select pg_switch_wal();
215 dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
217 --------------------------
218 000000010000000000000021
226 - archive_command na dr
229 - cdu backup strategy (archive na nfs?)
230 - sysctl.conf na svim db-ovima
231 - fs noatime,nodiratime na svim db-ovima
235 - shared screen na serveru (ssh -R ?)
237 - failover (promjena ip-ja u aplikaciji?)