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 cat postgresql.conf | grep -v archive_mode | grep -v archive_command > postgresql.conf.new && mv postgresql.conf.new postgresql.conf
 189
 190 su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r start -o "-p 5433"'
 191
 192 su postgres -c '/usr/pgsql-10/bin/pg_ctl -D /tmp/r stop'
 193
 194
 195 # wal introspection
 196
 197 ## current wal filename on master
 198
 199 SELECT pg_walfile_name(pg_current_wal_lsn());
 200
 201 ## switch to next wal file
 202
 203 dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
 204      pg_walfile_name
 205 --------------------------
 206  000000010000000000000020
 207 (1 row)
 208
 209 dpavlin=# select pg_switch_wal();
 210  pg_switch_wal
 211 ---------------
 212  0/20B48BA8
 213 (1 row)
 214
 215 dpavlin=# SELECT pg_walfile_name(pg_current_wal_lsn());
 216      pg_walfile_name
 217 --------------------------
 218  000000010000000000000021
 219 (1 row)
 220
 221
 222
 223 # TODO
 224
 225 - diskovi
 226 - archive_command na dr
 227
 228
 229 - cdu backup strategy (archive na nfs?)
 230 - sysctl.conf na svim db-ovima
 231 - fs noatime,nodiratime na svim db-ovima
 232 - monitoring db-ova
 233 - reboot db-ova
 234 - pristup na grafanu
 235 - shared screen na serveru (ssh -R ?)
 236 - test pitr recovery
 237 - failover (promjena ip-ja u aplikaciji?)
 238