POSTGRESQL-13 INSTALLATION (OL-7, Centos-7, RedHat-7)

İşletim sistemi olarak Rathat 7.4 kullanacağız. (Centos -7 ve Oracle Linux 7 içinde aynı)

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
 
# sudo yum install -y postgresql13-server postgresql13 postgresql13-contrib 

/etc/hosts dosyasına ip ve hostname kısmını ekliyoruz

echo 150.150.100.71 psgdbm1 >> /etc/hosts

Database dosyalarımız için klasörümüzü oluşturalım ve yetkilendirmesini yapalım ayrııca postgres userına parola oluşturalım.

# mkdir -p /database/data
# id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
# chown postgres:postgres -R /database/
# passwd postgres
# su - postgres

postgres .bash_profile dosyasını düzenleyelim.

PGDATA=/database/data
export PGDATA
PATH=$PATH:/usr/pgsql-13/bin
export PATH

Postgresql servislerini çlıştırmadan önce data dosyasının yerini postgresql servis dosyasında environment PGDATA kısmını aşağıdaki gibi değiştirelim.

# vi /usr/lib/systemd/system/postgresql-13.service

Environment=PGDATA=/database/data/

şimdi postgres kullanıcı ile aşağıdaki komutu veriyoruz.

$ initdb
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 locale "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 /database/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Istanbul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    pg_ctl -D /database/data -l logfile start

/database/data dizinine database kurulumumuz yapıldı. Şimdi bazı dosyaları inceleyelim. potgres userı ile $PGDATA dizini altındaki dosyaları inclersek;

$ cd $PGDATA
$ ls
base              pg_commit_ts   pg_logical    pg_serial     pg_subtrans  pg_wal                postmaster.opts
current_logfiles  pg_dynshmem    pg_multixact  pg_snapshots  pg_tblspc    pg_xact               postmaster.pid
global            pg_hba.conf    pg_notify     pg_stat       pg_twophase  postgresql.auto.conf
log               pg_ident.conf  pg_replslot   pg_stat_tmp   PG_VERSION   postgresql.conf

Database client bağlantıları için izinleri girebildiğimiz pg_hba.conf dosyasını düzenleyelim. Bu dosyanın en altına aşağıdaki satırı ekleyerek tüm IP aralıkları için bağlantı izni sağlıyoruz. Ayrıca trust diyerek şifresiz olarak erişim vermiş oluyoruz. Eğer trust yerine md5 eklersek bağlantı için şifre kullanımı isteyecektir

$ vi pg_hba.conf
host    all             all             0.0.0.0/0               trust

Diğer bir ayar yapacağımız dosya ise postgresql.conf dosyasıdır; Bu dosya içerisinde listener ayarlarını “localhos” tan alıp tüm bağlantıları dinlemesi için “*” ile değiştiriyoruz. Ayrıca listener portunuda 5432 olarak açıyoruz.

$ vi postgresql.conf

listen_addresses = '*'
port = 5432

Root ile firewall 5432 portu için izin sağlıyoruz

# firewall-cmd --add-port=5432/tcp --permanent
success
# firewall-cmd --reload
success

Postgresql servisini tekrar çalıştıralım

# systemctl restart postgresql-13
# systemctl status  postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-02-17 16:33:53 +03; 1min 35s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 2308 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 2314 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           ├─2314 /usr/pgsql-13/bin/postmaster -D /database/data/
           ├─2317 postgres: logger
           ├─2319 postgres: checkpointer
           ├─2320 postgres: background writer
           ├─2321 postgres: walwriter
           ├─2322 postgres: autovacuum launcher
           ├─2323 postgres: stats collector
           └─2324 postgres: logical replication launcher

Feb 17 16:33:48 psgdbm1 systemd[1]: Starting PostgreSQL 13 database server...
Feb 17 16:33:48 psgdbm1 postmaster[2314]: 2021-02-17 16:33:48.399 +03 [2314] LOG:  redirecting log output ...ocess
Feb 17 16:33:48 psgdbm1 postmaster[2314]: 2021-02-17 16:33:48.399 +03 [2314] HINT:  Future log output will...log".
Feb 17 16:33:53 psgdbm1 systemd[1]: Started PostgreSQL 13 database server.
Hint: Some lines were ellipsized, use -l to show in full.

Bu şekilde çalıştırmak yerine pg_ctl komutunu da kullanabiliriz

[postgres@bfmnd1 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@bfmnd1 ~]$ pg_ctl start
waiting for server to start....2022-05-06 17:49:26.976 +03 [36250] LOG:  redirecting log output to logging collector process
2022-05-06 17:49:26.976 +03 [36250] HINT:  Future log output will appear in directory "log".
 done
server started

Ayrıca 32GB bir veritabanı için de best practice kurulum ayarları aşağıdaki ayarları örnek alarak ayarlayabilirsiniz;

Postgres Best Practice
	These practices only guides you at the beginning of installation. You must monitor the system, ,get security, SLA and other requirements, and also the parameters may change according to your environments.


1-Recommended hardware specifications for a medium database to begin with;

    RAM = 32GB
    CPU = 8 core
    DISK = must be allocated based on data size. LVM must be configured.


If different physical disks can be defined for the cluster directory and the wal directory, they must be on separate disks.  Even if there is more than one database on an instance, separate tablespaces and separate disks should be defined for each database.

In the installation, an additional contrib package must be installed in the server package.


2-postgresql.conf  parameters that need to be changed after installation

For 32GB server

listen_addresses = '*'
max_connections = 150
shared_buffer: 8GB   # (must be maximum one in four of total RAM )
shared_preload_libraries = 'pg_stat_statements'
maintenance_work_mem=1GB 
bgwriter_delay = 10ms                 
bgwriter_lru_maxpages = 1000           
bgwriter_lru_multiplier = 4.0     
wal_level = replica 
wal_log_hints = on    # (if checksum not  enabled)
max_wal_size = 12GB
min_wal_size = 6GB
checkpoint_completion_target = 0.7
checkpoint_timeout =10
archive_mode = on 
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
wal_keep_segments = 1000
log_filename = 'postgresql-%a.log'
log_min_duration_statement = 1000 
log_line_prefix = '%m [%p] app=%a  user=%u db=%d host=%h pid=%p '
log_lock_waits = on
log_temp_files = 0
track_io_timing = on 
autovacuum = on         #(It is not recommended to turn off)
log_autovacuum_min_duration = 0
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01 
effective_cache_size= 24GB


In addition to the master server, there must be at least one sync replica server. If slots are to be used in replication, their losses should be considered.

3-Security Best Practices

1-Postgres user must be disabled for remote access.
2-Superuser authority should not be defined for users coming from applications.
3-Detailed access permissions must be defined from pg_hba.conf
4-Port must be used other than the postgresql default port.
5-The database version should be kept up to date with the minor and major updates.
6-Separate users and authorizations should be defined for  replication backup users.
7. encrypt sensitive data with pgcrypto extension
8-Application user should not have super user account
9-do not give any privileges to public
10- revoke connect role from public
11- do not create objects in public schema
12-If it is possible for your application drop public schema
13- Give least privileges to application user that the application only can work.
14- use pg_audit extension if default auditing is not adequate for clients requirements


4-Recommended extensions to use

Default extensions to be installed:

1-pg_stat_statements
2-pg_repack
 

Plugins to be installed as needed:

            a- foreign data wrappers
            b- pg_audit
            c- chkpass: determines password quality of user postgres
      

5-PostgreSQL system log analysis;

pgbadger



6-Connection Management:

If there is no pooling mechanism, pgbouncer can be installed.


7-  Monitoring Tools

 	a-pg_top
 	b-pg_activity 
	c-pgadmin
	d-PMM (Percona Monitoring Tool) or Zabbix 

8-Backup and Recovery Strategies
We recommend one server for monitoring database and backup server.
We recommend archive mode on, and put archive log to network attached servers,
where your standby servers and backup server could read these archive logs.

It is recommended to use pg_probackup or pgbackrest as a backup tool.
They provide parallelism and incremental backup mechanisms. 
Schedule backups using cron utility’s of linux


9-HA
It is recommended to use the BFM high availability solution.

10- Development Tools
dbeaver

pgadmin

 

11. Linux Kernel Settings

SET Below parameters in sysctl.conf

kernel.shmall (SHMALL) 2 * <size of RAM in the default system page size>
kernel.sem (SEMMNI)  256 * <size of RAM in GB>

For big memorys Use hugepages for linux
script to quantify Huge Pages
#!/bin/bash
pid=`head -1 $PGDATA/postmaster.pid`
echo "Pid:            $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak:            $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize:   $hps kB"
hp=$((peak/hps))
echo Set Huge Pages:     $hp

set vm.nr_hugepages in /etc/sysctl.conf
set huge_pages=on in postgresql.conf

set below parameter in /etc/sysctl.conf
vm.swappiness default 60 set the value to 1 if you want to minimize swapping
vm.overcommit_memory  A value of 2 for vm.overcommit_memory yields better performance for PostgreSQL
vm.dirty_background_ratio  default value 10  if system is write intensive lower the value 

 
vm.swappines=1
vm.overcommit_memory=2

kernel.shmmax=34359738368
kernel.shmall=8388608
vm.nr_hugepages= 4500

Bir sonraki yazımızda mimari özelliklerini inceleyeceğiz.