git.rot13.org Git - edozvola/blob - db.txt

   1 qemu-img create -f qcow2 -o backing_file=db-backing.qcow2 -F qcow2 db-slave.qcow2
   2
   3
   4
   5 # network config
   6
   7 [dpavlin@localhost ~]$ cat /etc/sysconfig/network-scripts/ifcfg-eth0
   8 DEVICE=eth0
   9 IPADDR=10.200.1.10
  10 NETMASK=255.255.255.0
  11 ONBOOT=yes
  12 NAME=eth0
  13
  14 # install postgresql
  15
  16 yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  17
  18 [root@localhost dpavlin]# yum install postgresql10-server
  19
  20
  21 # initdb
  22
  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.
  26
  27 The database cluster will be initialized with locales
  28   COLLATE:  hr_HR.utf8
  29   CTYPE:    en_US.UTF-8
  30   MESSAGES: en_US.UTF-8
  31   MONETARY: en_US.UTF-8
  32   NUMERIC:  en_US.UTF-8
  33   TIME:     en_US.UTF-8
  34 The default database encoding has accordingly been set to "UTF8".
  35 The default text search configuration will be set to "english".
  36
  37 Data page checksums are disabled.
  38
  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
  49
  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.
  53
  54 Success. You can now start the database server using:
  55
  56     /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data/ -l logfile start
  57
  58
  59
  60 # systemd
  61
  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
  70 [
  71
  72 # streaming replication
  73
  74 https://www.cybertec-postgresql.com/en/setting-up-postgresql-streaming-replication/
  75
  76 sudo vi /var/lib/pgsql/10/data/postgresql.conf
  77
  78 ...
  79
  80 [root@slave data]# su postgres
  81 bash-4.2$ pwd
  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/"
  90
  91
  92
  93 dpavlin=# select pg_create_physical_replication_slot('slave_slot') ;
  94  pg_create_physical_replication_slot
  95 -------------------------------------
  96  (slave_slot,)
  97 (1 row)
  98
  99
 100 # wal archive for pitr
 101
 102 https://www.postgresql.org/docs/10/continuous-archiving.html
 103
 104 archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
 105
 106
 107 [root@slave 10]# mkdir -p /var/lib/pgsql/10/archive/
 108 chown postgres:postgres /var/lib/pgsql/10/archive/
 109
 110 vi /var/lib/pgsql/10/data/postgresql.conf
 111 i
 112
 113 # https://www.postgresql.org/docs/10/warm-standby.html#CONTINUOUS-ARCHIVING-IN-STANDBY
 114
 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'
 117
 118
 119 [root@slave 10]# systemctl restart postgresql-10
 120
 121 dpavlin=# insert into test(t) select i from generate_series(100, 150) as i ;
 122
 123
 124
 125 dpavlin=# SELECT pg_start_backup('label', false, false);
 126 - fs backup
 127 SELECT * FROM pg_stop_backup(false, true);
 128
 129
 130
 131
 132
 133 # tuning
 134
 135 https://www.enterprisedb.com/blog/tuning-debian-ubuntu-postgresql
 136
 137 ## /etc/sysctl.conf
 138
 139 vm.swappiness = 10
 140 vm.dirty_expire_centisecs = 500
 141 vm.dirty_writeback_centisecs = 250
 142 vm.dirty_ratio = 10
 143 vm.dirty_background_ratio = 3
 144 vm.overcommit_memory=2
 145 net.ipv4.tcp_timestamps=0
 146
 147 /dev/mapper/pgdata-01-data /pgdata xfs  defaults,noatime,nodiratime 1 1
 148
 149 To activate it immediately, run:
 150
 151 mount -o remount,noatime,nodiratime /pgdata
 152
 153
 154 # compressed archive_command
 155
 156 https://www.postgresql.org/docs/10/continuous-archiving.html
 157
 158 archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'.
 159
 160 systemctl reload postgresql-10
 161
 162 /usr/pgsql-10/share/recovery.conf.sample
 163
 164 restore_command = 'gunzip < /var/lib/pgsql/10/archive/%f > %p'
 165
 166 !!! full_page_writes on master for pg_basebackup on slave
 167 !!! logging_collector on if archive_command script used
 168
 169 ## create full backup
 170
 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
 173
 174
 175
 176 ## restore backup
 177
 178 mkdir /tmp/r
 179 chown postgres:postgres /tmp/r
 180 chmod 700 /tmp/r
 181
 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/
 184
 185 cp /var/lib/pgsql/10/recovery.conf /tmp/r/
 186 vi /tmp/r/recovery.conf
 187
 188 restore_command = 'gunzip < /var/lib/pgsql/10/archive/%f > %p'
 189
 190
 191 cat postgresql.conf | grep -v archive_mode | grep -v archive_command > postgresql.conf.new && mv postgresql.conf.new postgresql.conf
 192
 193 su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r start -o "-p 5433"'
 194
 195 su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r stop'
 196
 197
 198 # wal introspection
 199
 200 ## current wal filename on master
 201
 202 SELECT pg_walfile_name(pg_current_wal_lsn());
 203
 204 ## switch to next wal file
 205
 206 dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
 207      pg_walfile_name
 208 --------------------------
 209  000000010000000000000020
 210 (1 row)
 211
 212 dpavlin=# select pg_switch_wal();
 213  pg_switch_wal
 214 ---------------
 215  0/20B48BA8
 216 (1 row)
 217
 218 dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
 219      pg_walfile_name
 220 --------------------------
 221  000000010000000000000021
 222 (1 row)
 223
 224 ## pg_waldump
 225
 226 https://habr.com/en/company/postgrespro/blog/496150/
 227
 228 dpavlin=# SELECT pg_current_wal_insert_lsn();
 229  pg_current_wal_insert_lsn
 230 ---------------------------
 231  0/25000140
 232 (1 row)
 233
 234 [root@slave pg_wal]# /usr/pgsql-10/bin/pg_waldump -p /var/lib/pgsql/10/data/pg_wal/ -s 0/25000140
 235
 236 # TODO
 237
 238 - diskovi
 239 - archive_command na dr
 240
 241
 242 - cdu backup strategy (archive na nfs?)
 243 - sysctl.conf na svim db-ovima
 244 - fs noatime,nodiratime na svim db-ovima
 245 - monitoring db-ova
 246 - reboot db-ova
 247 - pristup na grafanu
 248 - shared screen na serveru (ssh -R ?)
 249 - test pitr recovery
 250 - failover (promjena ip-ja u aplikaciji?)
 251