How to Create Materialized View Refresh Procedure and Grant User or Role in Oracle Database?

CREATE MATERIALIZE VIEW REFRESH PROCEDURE

CREATE OR REPLACE PROCEDURE SYS.REFRESH_MV (P_MATERIALIZE_VIEW_NAME VARCHAR2)
AS
BEGIN
    DBMS_SNAPSHOT.REFRESH (P_MATERIALIZE_VIEW_NAME, 'F');
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_SNAPSHOT.REFRESH (P_MATERIALIZE_VIEW_NAME, 'C');
END;
/

REFRESH MATERIALIZE VIEW COMMAND

EXEC SYS.REFRESH_MV('SCHEMA_NAME.MATERIALIZE_VIEW_NAME')

GRANT EXECUTE REFRESH MATERIALIZE PROCEDURE ANY USER OR ROLE

GRANT EXECUTE ON SYS.REFRESH_MV TO SERHAT;

So any user we authorize can refresh materialize view 🙂

How To Enable RSH on Linux 7?

INSTALL BELOW RPMS.

[root@serhatcelik tmp]# rpm -ivh rsh-server-0.17-79.el7.x86_64.rpm
warning: rsh-server-0.17-79.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:rsh-server-0.17-79.el7 ################################# [100%]
[root@serhatcelik tmp]#

[root@serhatcelik tmp]# rpm -ivh rsh-0.17-79.el7.x86_64.rpm
warning: rsh-0.17-79.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:rsh-0.17-79.el7 ################################# [100%]
[root@serhatcelik tmp]#

[root@serhatcelik tmp]# rpm -ivh xinetd-2.3.15-14.el7.x86_64.rpm
warning: xinetd-2.3.15-14.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing… ################################# [100%]
Updating / installing…
1:xinetd-2:2.3.15-14.el7 ################################# [100%]
[root@serhatcelik tmp]#

EDIT /etc/xinetd.d/rsh FILE LIKE BELOW. ALLOW SERVERS TO RSH THIS SERVER VIA ONLY_FROM STATEMENT.

[root@serhatcelik ~]# more /etc/xinetd.d/rsh
service shell
{
disable = no
socket_type = stream
wait = no
user = root
log_on_success += USERID
log_on_failure += USERID
server = /usr/sbin/in.rshd
only_from = 10.X.X.XXX
}
[root@serhatcelik ~]#

START & CHECK XINETD SERVICE

[root@serhatcelik ~]# service xinetd start
Redirecting to /bin/systemctl start xinetd.service
[root@serhatcelik ~]#
[root@serhatcelik ~]# service xinetd status
Redirecting to /bin/systemctl status xinetd.service
◠xinetd.service - Xinetd A Powerful Replacement For Inetd
Loaded: loaded (/usr/lib/systemd/system/xinetd.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2021-03-22 13:20:40 +03; 53s ago
Process: 4936 ExecStart=/usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid $EXTRAOPTIONS (code=exited, status=0/SUCCESS)
Main PID: 4937 (xinetd)
Tasks: 1
CGroup: /system.slice/xinetd.service
└─4937 /usr/sbin/xinetd -stayalive -pidfile /var/run/xinetd.pid

Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing discard
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing discard
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing echo
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing echo
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing tcpmux
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing time
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: removing time
Mar 22 13:20:40 serhatcelik.xxx.com.tr systemd[1]: Started Xinetd A Powerful Replacement For Inetd.
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: xinetd Version 2.3.15 started with libwrap loadavg labeled-networking options compiled in.
Mar 22 13:20:40 serhatcelik.xxx.com.tr xinetd[4937]: Started working: 1 available service
[root@serhatcelik ~]#

RUN BELOW COMMANDS AND RELOAD SERVICE

[root@serhatcelik ~]# chkconfig rsh on
Note: Forwarding request to 'systemctl enable rsh.socket'.
Created symlink from /etc/systemd/system/sockets.target.wants/rsh.socket to /usr/lib/systemd/system/rsh.socket.
[root@serhatcelik ~]#
[root@serhatcelik ~]# echo "rsh" >> /etc/securetty
[root@serhatcelik ~]#
[root@serhatcelik ~]# perl -pi -e 's?auth\s+required\s+pam_securetty.so?auth\tsufficient\tpam_securetty.so?' /etc/pam.d/rsh
[root@serhatcelik ~]#
[root@serhatcelik ~]# service xinetd reload
Redirecting to /bin/systemctl reload xinetd.service
[root@serhatcelik ~]#

How to Avoid DBA Granted Users (Except SYS/SYSYEM) to Bypass an After Logon Trigger?

All DBA granted users bypass the logon trigger and are able to connect the database. This is the correct behavior and is designed to make it so your database doesn’t get into a state whereby NO ONE can log in. It is a feature.

If you want to prohibit connection any user;

1.   Build a customized DBA role
2.   Exclude below system privilege and roles from your customized dba role

SYSTEM PRIVILEGE
ADMINISTER DATABASE TRIGGER

ROLES
DATAPUMP_IMP_FULL_DATABASE
IMP_FULL_DATABASE

SAMPLE AFTER LOGON TRIGGER
CREATE OR REPLACE TRIGGER SYS.BLOCK_DB_TOOLS
    AFTER LOGON
    ON DATABASE
DECLARE
    V_MACHINE      SYS.v_$session.machine%TYPE;
    V_IP           SYS.v_$session.SCHEMANAME%TYPE;
    V_CNT_IP       NUMBER := 0;
    V_OSUSER       SYS.v_$session.OSUSER%TYPE;
    V_CNT_OSUSER   NUMBER := 0;
    V_SCHEMA       SYS.v_$session.SCHEMANAME%TYPE;
    V_CNT_SCHEMA   NUMBER := 0;
    V_APPS         SYS.v_$session.program%TYPE;
    V_CNT_APPS     NUMBER := 0;
BEGIN

    /*
    THE IP, OS_USER AND DB_USER INFORMATION OF THE USERS SHOULD BE UPDATED

    IP           OS_USER                     DB_USER
    10.X.X.X     serhat.celik                serhatcelik
    10.X.X.X     celik.serhat                celikserhat
    */

-- HOSTNAME, IP ADRES, OS USER, DB USER AND APPS INFORMATION ARE TAKEN
SELECT SYS_CONTEXT ('USERENV', 'HOST'),
       SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
       SYS_CONTEXT ('USERENV', 'OS_USER'),
       SYS_CONTEXT ('USERENV', 'SESSION_USER'),
       SYS_CONTEXT ('USERENV', 'MODULE')
  INTO V_MACHINE,
       V_IP,
       V_OSUSER,
       V_SCHEMA,
       V_APPS
  FROM DUAL;

-- CHECK IP ADDRESSES
SELECT COUNT (SYS_CONTEXT ('USERENV', 'IP_ADDRESS'))
  INTO V_CNT_IP
  FROM DUAL
 WHERE SYS_CONTEXT ('USERENV', 'IP_ADDRESS') IN ('10.X.X.X', '10.X.X.X');

-- CHECK OSUSERS
SELECT COUNT (LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')))
  INTO V_CNT_OSUSER
  FROM DUAL
 WHERE LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')) IN
           ('serhat.celik', 'celik.serhat');

-- CHECK DBUSERS
IF    LOWER (SYS_CONTEXT ('USERENV', 'SESSION_USER')) IN ('serhatcelik')
   OR LOWER (SYS_CONTEXT ('USERENV', 'SESSION_USER')) IN ('celikserhat')
THEN
    V_CNT_SCHEMA := 1;
ELSE
    V_CNT_SCHEMA := 0;
END IF;

-- DB APPLICATION TOOLS ARE CHECKED
SELECT COUNT (UPPER (SYS_CONTEXT ('USERENV', 'MODULE')))
  INTO V_CNT_APPS
  FROM DUAL
 WHERE    UPPER (SYS_CONTEXT ('USERENV', 'MODULE')) LIKE '%TOAD%'
       OR UPPER (SYS_CONTEXT ('USERENV', 'MODULE')) LIKE '%SQLNAV%'
       OR UPPER (SYS_CONTEXT ('USERENV', 'MODULE')) LIKE '%PLSQLDEV%'
       OR UPPER (SYS_CONTEXT ('USERENV', 'MODULE')) LIKE '%SQLPLUS%'
       OR UPPER (SYS_CONTEXT ('USERENV', 'MODULE')) LIKE
              '%SQL%DEVELOPER%';

-- APPS INFORMATION IS SET TO 0 TO ALLOW TOAD CONNECTION OF PERSONNEL TO BE EXCLUDED FROM LOGIN CONTROL
IF LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')) IN
       ('serhat.celik', 'celik.serhat')
THEN
    V_CNT_APPS := 0;
END IF;

-- ALLOW IIS SERVER (W3WP.EXE-IIS WORKER PROCESS) OR ANOTHER SERVERS
IF     SYS_CONTEXT ('USERENV', 'IP_ADDRESS') IN ('10.X.X.X')
   AND LOWER (SYS_CONTEXT ('USERENV', 'MODULE')) IN ('w3wp.exe')
THEN
    V_CNT_IP := 1;
    V_CNT_OSUSER := 1;
    V_CNT_SCHEMA := 1;
    V_CNT_APPS := 0;
END IF;

-- CONNECTIONS OF DATABASE SERVERS BELONG TO THE DBA TEAM ARE ALLOWED
IF (    LOWER (SYS_CONTEXT ('USERENV', 'HOST')) IN
            ('hostname.xxx.com.tr')
    AND SYS_CONTEXT ('USERENV', 'IP_ADDRESS') IN ('10.X.X.X')
    AND LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')) IN ('oracle'))
THEN
    V_CNT_IP := 1;
    V_CNT_OSUSER := 1;
    V_CNT_SCHEMA := 1;
    V_CNT_APPS := 0;
END IF;

-- DBA TEAM IS PROVIDED TO CONNECT ALL SCHEMAS WITH THEIR OS_USERS
IF    LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')) IN ('serhat.celik')
   OR LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')) IN ('celik.serhat')
THEN
    V_CNT_IP := 1;
    V_CNT_OSUSER := 1;
    V_CNT_SCHEMA := 1;
    V_CNT_APPS := 0;
END IF;

-- IP ADDRESS, OS USER, DB USER AND DATE RANGE MUST BE ENTERED FOR TEMPORARY AUTHORIZATIONS
IF     SYS_CONTEXT ('USERENV', 'IP_ADDRESS') IN ('10.X.X.X')
   AND LOWER (SYS_CONTEXT ('USERENV', 'OS_USER')) IN ('serhat.celik')
   AND LOWER (SYS_CONTEXT ('USERENV', 'SESSION_USER')) IN ('serhatcelik')
   AND SYSDATE BETWEEN TO_DATE ('19/03/2021 08:00',
                                'DD/MM/YYYY  HH24:MI')
                   AND TO_DATE ('19/03/2021 18:00',
                                'DD/MM/YYYY  HH24:MI')
THEN
    V_CNT_IP := 1;
    V_CNT_OSUSER := 1;
    V_CNT_SCHEMA := 1;
    V_CNT_APPS := 0;
END IF;

/* USERS;
   1. IF NOT IN THE AUTHORIZED IP LIST,
   2. OR IF NOT IN THE AUTHORIZED OS_USER,
   3. OR IF NOT IN THE AUTHORIZED DB_USER,
   4. OR IF APPS LIKE TOAD, ETC. NOT ALLOWED TO DATABASE CONNECTION*/

IF V_CNT_IP = 0 OR V_CNT_OSUSER = 0 OR V_CNT_SCHEMA = 0 OR V_CNT_APPS = 1
THEN
    INSERT INTO SYS.LOGON_AUDIT_2021 (LOGON_DATE,
                                      MACHINE,
                                      IP,
                                      OSUSER,
                                      DBUSER,
                                      PROGRAM,
                                      STATUS)
         VALUES (SYSDATE,
                 V_MACHINE,
                 V_IP,
                 V_OSUSER,
                 V_SCHEMA,
                 V_APPS || ' - NOT ALLOWED APPS',
                 'FAILED');

    COMMIT;

    PACK_SEND_MAIL.SEND_MAIL (
        'unsuccessfull@xxx.com.tr',
        'dba@xxx.com.tr',
        NULL,
        NULL,
        'XXXDB Unsuccessfull Login Attempt',
           V_OSUSER
        || ' '
        || V_MACHINE
        || ' ('
        || V_IP
        || ')'
        || ' From Machine '
        || V_SCHEMA
        || ' With User And '
        || V_APPS
        || ' With Application Try To Connect To The Database.',
        NULL,
        NULL,
        NULL,
        0);

    RAISE_APPLICATION_ERROR (
        -20001,
        'You do not have access to the database. PLEASE CONTACT YOUR DATABASE MANAGER (dba@xxx.com.tr)');
ELSE
    INSERT INTO SYS.LOGON_AUDIT_2021 (LOGON_DATE,
                                      MACHINE,
                                      IP,
                                      OSUSER,
                                      DBUSER,
                                      PROGRAM,
                                      STATUS)
         VALUES (SYSDATE,
                 V_MACHINE,
                 V_IP,
                 V_OSUSER,
                 V_SCHEMA,
                 V_APPS,
                 'SUCCESS');

    COMMIT;

    PACK_SEND_MAIL.SEND_MAIL (
        'successfull@xxx.com.tr',
        'dba@xxx.com.tr',
        NULL,
        NULL,
        'XXXDB Successfull Login Attempt',
           V_OSUSER
        || ' '
        || V_MACHINE
        || ' ('
        || V_IP
        || ')'
        || ' From Machine '
        || V_SCHEMA
        || ' With User And '
        || V_APPS
        || ' With Application Connected To The Database.',
        NULL,
        NULL,
        NULL,
        0);
END IF;
END;
/
LOGON_AUDIT_2021 TABLE CREATE SCRIPT
CREATE TABLE SYS.LOGON_AUDIT_2021
(
LOGON_DATE DATE,
MACHINE VARCHAR2(255 BYTE),
IP VARCHAR2(255 BYTE),
OSUSER VARCHAR2(255 BYTE),
DBUSER VARCHAR2(255 BYTE),
PROGRAM VARCHAR2(255 BYTE),
STATUS VARCHAR2(255 BYTE)
)
TABLESPACE SYSTEM
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX SYS.LOGON_AUDIT_2021_U01 ON SYS.LOGON_AUDIT_2021
(STATUS, PROGRAM, DBUSER, OSUSER, IP,
MACHINE, LOGON_DATE)
LOGGING
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE SYS.LOGON_AUDIT_2021 ADD (
CONSTRAINT ASGDBA_LOGON_AUDIT_2021_U01
UNIQUE (STATUS, PROGRAM, DBUSER, OSUSER, IP, MACHINE, LOGON_DATE)
USING INDEX SYS.LOGON_AUDIT_2021_U01
ENABLE VALIDATE);

Step By Step Oracle VM Server Installation And Configuration

1. ORACLE VM SERVER INSTALLATION

2. CREATING VIRTUAL SERVER FOR ORACLE VM MANAGER SOFTWARE

At this stage, we will activate virtual server on the Oracle VM Server using a template.

Creating a Path on OVM Server

[root@R12APPOVM ~]# mkdir -p /OVS/seed_pool

Copy OVM_MANAGER_OL79_TEMPLATE and Extract

[root@R12APPOVM ~]# cd /OVS/seed_pool/
[root@R12APPOVM ~]# tar zxvf OVM_MANAGER_OL79_TEMPLATE.tar.gz

Directories and Files Are Seen as in the Example Below

[root@R12APPOVM ~]# cd /OVS/seed_pool/
[root@R12APPOVM seed_pool]#
[root@R12APPOVM seed_pool]# ls -lrt
total 4
drwxr-xr-x 2 root root 4096 Mar  8 11:34 OVMM_OL79_TEMPLATE
[root@R12APPOVM seed_pool]#
[root@R12APPOVM seed_pool]# cd OVMM_OL79_TEMPLATE
[root@R12APPOVM OVMM_OL79_TEMPLATE]#
[root@R12APPOVM OVMM_OL79_TEMPLATE]# ls -lrt
total 35651596
-rw------- 1 root root         348 Mar  8 11:34 vm.cfg
-rw------- 1 root root 15032385536 Mar  9 10:36 os_disc.img
-rw------- 1 root root 21474836480 Mar  9 10:36 apps_disc.img
[root@R12APPOVM OVMM_OL79_TEMPLATE]#

Path Addresses in vm.cfg Content are Organized

[root@R12APPOVM ~]# vi /OVS/seed_pool/OVMM_OL79_TEMPLATE/vm.cfg
bootloader = '/usr/bin/pygrub'
disk = ['file:/OVS/seed_pool/OVMM_OL79_TEMPLATE/os_disc.img,xvda,w', 'file:/OVS/seed_pool/OVMM_OL79_TEMPLATE/apps_disc.img,xvdc,w']
memory = '8192'
name = 'OVMM_OL79_TEMPLATE'
vcpus = 2
on_crash = 'restart'
on_reboot = 'restart'
vfb = ['type=vnc,vncunused=1,vnclisten=0.0.0.0']
vif = ['type=netfront']
timer_mode = 0
[root@R12APPOVM ~]# more /OVS/seed_pool/OVMM_OL79_TEMPLATE/vm.cfg
bootloader = '/usr/bin/pygrub'
disk = ['file:/OVS/seed_pool/OVMM_OL79_TEMPLATE/os_disc.img,xvda,w', 'file:/OVS/seed_pool/OVMM_OL79_TEMPLATE/apps_disc.img,xvdc,w']
memory = '8192'
name = 'OVMM_OL79_TEMPLATE'
vcpus = 2
on_crash = 'restart'
on_reboot = 'restart'
vfb = ['type=vnc,vncunused=1,vnclisten=0.0.0.0']
vif = ['type=netfront']
timer_mode = 0
[root@R12APPOVM ~]#

Network-bridge Parameter in xend-config.sxp File on OVM Server Comment-Out

[root@R12APPOVM ~]# vi /etc/xen/xend-config.sxp
#(network-script network-bridge)
 (network-script network-bridge)

Xend Services is Started on the OVM Server

[root@R12APPOVM ~]# /etc/init.d/xend start
[root@R12APPOVM ~]# /etc/init.d/xendomains start

Check whether the Virtual Server is Open or Not

[root@R12APPOVM ~]# xm list
Name                                        ID   Mem VCPUs      State   Time(s)
0004fb0000060000f1ed0fc8e0cc1f21            15 65539     4     -b----    450.5
Domain-0                                     0 11446    16     r-----  37785.0
[root@R12APPOVM ~]#

Start Virtual Server With The Following Commands

[root@R12APPOVM ~]# cd /OVS/seed_pool/OVMM_OL79_TEMPLATE/
[root@R12APPOVM OVMM_OL79_TEMPLATE]#
[root@R12APPOVM OVMM_OL79_TEMPLATE]# ls -lrt
total 35651596
-rw------- 1 root root         348 Mar  8 11:34 vm.cfg
-rw------- 1 root root 15032385536 Mar  9 10:53 os_disc.img
-rw------- 1 root root 21474836480 Mar  9 10:53 apps_disc.img
[root@R12APPOVM OVMM_OL79_TEMPLATE]#
[root@R12APPOVM OVMM_OL79_TEMPLATE]# xm create vm.cfg
Using config file "./vm.cfg".
Started domain OVMM_OL79_TEMPLATES (id=2)

Check Again whether the Virtual Server is Open or Not

[root@R12APPOVM ~]# xm list
Name                                        ID   Mem VCPUs      State   Time(s)
0004fb0000060000f1ed0fc8e0cc1f21            15 65539     4     -b----    452.0
Domain-0                                     0 11446    16     r-----  37823.6
OVMM_OL79_TEMPLATE                          14  8192     2     -b----   3874.5
[root@R12APPOVM ~]#

Check VNCSERVER Port Information in order to Give IP to Virtual Server

[root@R12APPOVM ~]# xm list
Name                                        ID   Mem VCPUs      State   Time(s)
0004fb0000060000f1ed0fc8e0cc1f21            15 65539     4     -b----    453.2
Domain-0                                     0 11446    16     r-----  37838.5
OVMM_OL79_TEMPLATE                          14  8192     2     -b----   3885.4
[root@R12APPOVM ~]#
[root@R12APPOVM ~]# xm list -l OVMM_OL79_TEMPLATE | grep location
            (location 2)
            (location 0.0.0.0:5900)
[root@R12APPOVM ~]#

Provide Connection to Virtual Server via VNCSERVER with the OVM Server IP and 5900 Port Information.

While Connected to the Virtual Server via VNCSERVER, Complete IP Settings and Restart Network Service

3. ORACLE VM MANAGER SOFTWARE INSTALLATION

We installed Oracle VM Server. OL 7.9 virtual server is activated using template on Oracle VM Server. Lastly, Oracle VM Manager software will be installed on the virtual server.

User, Group and Folder are Created on the Virtual Server Installed Using Template

[root@r12appovmm ~]# groupadd dba
[root@r12appovmm ~]# useradd -g dba oracle
[root@r12appovmm ~]# passwd oracle
[root@r12appovmm ~]# mkdir /u01
[root@r12appovmm ~]# mkdir -p /media/cdrom

Download and upload ovmm-3.4.6-installer-OracleLinux-b2105.iso file to the Virtual Server Installed Using Template

OVM ISO File is Mounted to the Virtual Server Installed Using Template

[root@r12appovmm ~]# mount -o loop,ro /tmp/ovmm-3.4.6-installer-OracleLinux-b2105.iso /media/cdrom

Mount apps_disc.img Disc Copied to /OVS/seed_pool/OVMM_OL79_TEMPLATE/ on the OVM Server to the Virtual Server Installed Using Template

[root@r12appovmm ~]# mount /dev/xvdc1 /u01
[root@r12appovmm ~]# df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             3.9G     0  3.9G   0% /dev
tmpfs                4.0G     0  4.0G   0% /dev/shm
tmpfs                4.0G  9.3M  3.9G   1% /run
tmpfs                4.0G     0  4.0G   0% /sys/fs/cgroup
/dev/mapper/ol-root   11G  5.9G  4.3G  58% /
/dev/xvda1           2.0G  283M  1.6G  16% /boot
tmpfs                800M     0  800M   0% /run/user/0
/dev/xvdc1            20G  4.9G   14G  27% /u01
tmpfs                800M   40K  800M   1% /run/user/1001
[root@r12appovmm ~]#

The app folder is deleted if it exists in the Virtual Server that we have activated by using the Template.

[root@r12appovmm ~]# cd /u01
[root@r12appovmm ~]# rm -rf app

For Oracle VM Manager Software Installation, Go to /media/cdrom Directory and Run createOracle.sh

[root@r12appovmm ~]# cd /media/cdrom
[root@r12appovmm cdrom]# ./createOracle.sh
Adding group 'oinstall' with gid '54323' ...
groupadd: group oinstall exists
Adding group 'dba'
groupadd: group dba exists
Adding user 'oracle' with user id '54322', initial login group 'dba', supplementary group 'oinstall' and  home directory '/home/oracle' ...
User 'oracle' already exists ...
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba) context=root:system_r:unconfined_t:SystemLow-SystemHigh
Creating user 'oracle' succeeded ... 
For security reasons, no default password was set for user 'oracle'. If you wish to login as the 'oracle' user, you will need to set a password for this account.
Verifying user 'oracle' OS prerequisites for Oracle VM Manager ...
oracle    soft    nofile        8192
oracle    hard    nofile        65536
oracle    soft    nproc        2048
oracle    hard    nproc        16384
oracle    soft    stack        10240
oracle    hard    stack        32768
oracle    soft    core        unlimited
oracle    hard    core        unlimited
Setting  user 'oracle' OS limits for Oracle VM Manager ...
Altered file /etc/security/limits.conf
Original file backed up at /etc/security/limits.conf.orabackup
Verifying & setting of user limits succeeded ... 
Changing '/u01' permission to 755 ...
Modifying iptables for OVM
Adding rules to enable access to:
     7002  : Oracle VM Manager https
       123 : NTP
     10000 : Oracle VM Manager CLI Tool
service iptables status: stop
Applying iptables firewall rules: [  OK  ]
Loading additional iptables modules: ip_conntrack_netbios_ns [  OK  ]
Saving firewall rules to /etc/sysconfig/iptables: [  OK  ]
Flushing firewall rules: [  OK  ]
Setting chains to policy ACCEPT: filter [  OK  ]
Unloading iptables modules: [  OK  ]
Applying iptables firewall rules: [  OK  ]
Loading additional iptables modules: ip_conntrack_netbios_ns [  OK  ]
Flushing firewall rules: [  OK  ]
Setting chains to policy ACCEPT: filter [  OK  ]
Unloading iptables modules: [  OK  ]
Rules added.

Run runInstaller.sh for Oracle VM Manager Software Setup

[root@r12appovmm cdrom]# ./runInstaller.sh
Oracle VM Manager Release 3.4.6 Installer

Oracle VM Manager Installer log file:
/var/log/ovmm/ovm-manager-3-install-2020-09-14-101245.log

Please select an installation type:
   1: Install
   2: Upgrade
   3: Uninstall
   4: Help

   Select Number (1-4): 1

Verifying installation prerequisites ...
*** WARNING: Recommended memory for the Oracle VM Manager server installation using Local MySql DB is 7680 MB RAM

Starting production with local database installation ...

One password is used for all users created and used during the installation.
Enter a password for all logins used during the installation: 
Enter a password for all logins used during the installation (confirm): 

Please enter your fully qualified domain name, e.g. ovs123.us.oracle.com, (or IP address) of your management server for SSL certification generation 10.X.X.XXX [dr-ovmm.xxx.com]:  dr-ovmm.xxx.com

Verifying configuration ...

Start installing Oracle VM Manager:
   1: Continue
   2: Abort

   Select Number (1-2): 1

Step 1 of 7 : Database Software ...
Installing Database Software...
Retrieving MySQL Database 5.6 ...
Unzipping MySQL RPM File ...
Installing MySQL 5.6 RPM package ...
Configuring MySQL Database 5.6 ...
Installing MySQL backup RPM package ...

Step 2 of 7 : Java ...
Installing Java ...

Step 3 of 7 : WebLogic and ADF ...
Retrieving Oracle WebLogic Server 12c and ADF ...
Installing Oracle WebLogic Server 12c and ADF ...
Applying patches to Weblogic ...
Applying patch to ADF ...

Step 4 of 7 : Oracle VM ...
Installing Oracle VM Manager Core ...
Retrieving Oracle VM Manager Application ...
Extracting Oracle VM Manager Application ...

Retrieving Oracle VM Manager Upgrade tool ...
Extracting Oracle VM Manager Upgrade tool ...
Installing Oracle VM Manager Upgrade tool ...

Retrieving Oracle VM Manager CLI tool ...
Extracting Oracle VM Manager CLI tool...
Installing Oracle VM Manager CLI tool ...
Installing Oracle VM Manager WLST Scripts ...

Step 5 of 7 : Domain creation ...
Creating domain ...

Step 6 of 7 : Oracle VM Tools ...

Retrieving Oracle VM Manager Shell & API ...
Extracting Oracle VM Manager Shell & API ...
Installing Oracle VM Manager Shell & API ...

Retrieving Oracle VM Manager Wsh tool ...
Extracting Oracle VM Manager Wsh tool ...
Installing Oracle VM Manager Wsh tool ...

Retrieving Oracle VM Manager Tools ...
Extracting Oracle VM Manager Tools ...
Installing Oracle VM Manager Tools ...

Retrieving ovmcore-console ...
Installing ovmcore-console RPM package ...
Copying Oracle VM Manager shell to '/usr/bin/ovm_shell.sh' ...
Installing ovm_admin.sh in '/u01/app/oracle/ovm-manager-3/bin' ...
Installing ovm_upgrade.sh in '/u01/app/oracle/ovm-manager-3/bin' ...

Step 7 of 7 : Start OVM Manager ...
Enabling Oracle VM Manager service ...
Shutting down Oracle VM Manager instance ...
Starting Oracle VM Manager instance ...

Please wait while WebLogic configures the applications...
Trying to connect to core via ovmwsh (attempt 1 of 20) ...
Trying to connect to core via ovm_shell (attempt 1 of 5)...
Oracle VM Manager installed.

Installation Summary
--------------------
Database configuration:
  Database type               : MySQL
  Database host name          : localhost
  Database name               : ovs
  Database listener port      : 49500
  Database user               : ovs

Weblogic Server configuration:
  Administration username     : weblogic

Oracle VM Manager configuration:
  Username                    : admin
  Core management port        : 54321
  UUID                        : 0004fb0000010000da74f5c57553476b

Passwords:
There are no default passwords for any users. The passwords to use for Oracle VM Manager, Database, and Oracle WebLogic Server have been set by you during this installation. In the case of a default install, all passwords are the same.

Oracle VM Manager UI:
  https://dr-ovmm.xxx.com:7002/ovm/console
Log in with the user 'admin', and the password you set during the installation.

For more information about Oracle Virtualization, please visit:
  http://www.oracle.com/virtualization/

3.2.10/3.2.11 Oracle VM x86 Servers and SPARC agent 3.3.1 managed Servers are no longer supported in Oracle VM Manager 3.4. Please upgrade your Server to a more current version for full support
For instructions, see the Oracle VM 3.4 Installation and Upgrade guide. 

Oracle VM Manager installation complete.

Please remove configuration file /tmp/ovm_configOtF8i-.

Start Oracle VM Manager Services

[root@r12appovmm ~]# service ovmm_mysql start
Starting OVMM MySQL..                                      [  OK  ]
[root@r12appovmm ~]# /etc/init.d/mysql start
Starting MySQL.                                            [  OK  ]
[root@r12appovmm ~]# /sbin/service ovmm start
Starting Oracle VM Manager  

Installation Successfully Finished. The URL to Connect to OVM Manager Appears in Output.

https://10.X.X.X:7002/ovm/console/faces/login.jspx

Oracle Multitenant Architecture (Container-Pluggable Database)

WHAT IS ORACLE MULTITENANT?

Oracle Multitenant enables an Oracle Database to function as a container database (CDB). A CDB consolidates multiple pluggable databases (PDB), a portable collection of schemas, schema objects, and non-schema objects. Whether deployed on-premises or in the cloud, with Oracle Multitenant, applications run unchanged in self-contained PDBs, improving resource utilization, management, and overall security.

ORACLE MULTITENANT ARCHITECTURE

Before Oracle 12c, each database created on a server was associated with a separate instance. Therefore, it was necessary to allocate a separate SGA area for each database.

With the Multitenant Architecture that comes with Oracle 12c, many databases can be managed by a single instance. In a multitenant structure, background processes, shared memory, oracle metadata, redolog, controlfile and undo tablespaces are common. Application data, tablespaces, local users, roles and local metadata are kept in PDB. If necessary, temp tablespaces can be defined on the basis of PDB.

CDB (Container Database): The actual owner of the instance hosting the PDBs.

Root (CDB$ROOT): It is the core of the CDB, contains all Oracle supplied metadata like packages, core dictionary objects, and common users. There is only one root container for a CDB. Control Files, Redo Logs, and Undo Files are owned by ROOT Container as these database files are instance specific.

Seed (PDB$SEED): It is a template database which can be used to create new PDBs. Oracle copies mandatory datafiles (common datafiles) for creation of PDB from this SEED Database. This is by default in READ-ONLY mode and cannot be altered or modified.

PDB (Pluggable Database): It is like a database in 11g version with local schemas and schema objects under a CDB. PDBs added by the user share the same SGA and background processes.

  • Custom PDBs can be created for various applications, teams or functionalities
  • There can be zero or more PDB’s in a CDB
  • One PDB can be associated with only one CDB at a time.
  • Support for up to 4,096 (4k) pluggable databases per container database in Oracle Exadata, Oracle Cloud, and 252 pluggable databases per container database on other platforms
  • Every PDB has its own set of SYSTEM/SYSAUX/TEMP tablespaces. If we don’t assign any TEMP tabalespace to PDB it will use the TEMP of ROOT Container
  • When a Container database instance is started all PDB’s comes automatically in mounted state. We will need to manually open the PDB’s into Read-Write mode
  • In the earlier version of Databases we have 3 level of dictionary view relationship (USER_ ALL_ DBA_ ) but with the introduction of the CDB one more layer of view has increased (CDB_ )

    CDB_% >>>> All objects of CDB across PDBs
    DBA_% >>>> All objects in container or PDB
    ALL_% >>>> Objects accessible by Current User
    USER_% >>> Objects owned by Current User
ADVANTAGES OF ORACLE MULTITENANT ARCHITECTURE
  • Lower Cost: Shared use of resources between PDBs reduces costs, less physical servers and less management costs.
  • Rapid Cloning: Easier and faster transfer of data and code. When we unplug the PDB and plug it into another CDB, we ensure that the data is moved quickly. Plug / ungplug technique is very close to transportable tablespace logic.
  • Manage Many As One: Performing patching, backups, configuration, and upgrades centrally. Performance tuning process is easy as it is working on a single SGA. IT teams retain granular control when necessary, such as performing point-in-time recovery (PITR) at the individual pluggable database level.
  • Easy Authorization: CDB and PDB users.
DB INSTALLATION OPTIONS

If we do not want to take advantage of the multitenant features, we can set up and manage our database with the like 11g database features we are used to with the old architecture.

In response to the transition to this architecture in the future, we can install with 1 CDB and 1PDB during the installation and use the Multitenant architecture in single tenant configuration. We do not pay a Multitenant license fee for this option.

Finally, we can fully use the Multitenant feature with the configuration called as multiple PDB per CDB, for this configuration you must have a Multitenant License.

CONTAINER-PLUGGABLE DATABASE COMMANDS

CHECK CDB OR NON-CDB

[root@node1 ~]# su - oracle
Last login: Sun Mar 7 17:17:41 +03 2021 on pts/0
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus sys/welcome123@CDBTEST as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 17:30:28 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name, cdb, con_id from v$database;
NAME-------CDB---CON_ID
CDBTEST----YES---0
SQL>

CONNECT TO CDB

[root@node1 ~]# su - oracle
Last login: Sun Mar 7 16:57:54 +03 2021 on pts/0
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus sys/welcome123@CDBTEST as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 17:05:16 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show con_name;
CON_NAME
CDB$ROOT
SQL>

CHECKING THE STATUS OF PDBS

[root@node1 ~]# su - oracle
Last login: Sun Mar 7 17:11:14 +03 2021
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus sys/welcome123@CDBTEST as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 17:17:45 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> set linesize 999;
SQL>
SQL> select name, open_mode from v$pdbs;
NAME----------OPEN_MODE
PDB$SEED------READ ONLY
PDBTEST1------MOUNTED
SQL>

CONNECT TO PDB USING ALTER SESSION COMMAND

[root@node1 ~]# su - oracle
Last login: Sun Mar 7 17:30:26 +03 2021 on pts/0
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus sys/welcome123@CDBTEST as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 7 17:40:42 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show con_name;
CON_NAME
CDB$ROOT
SQL>
SQL> set linesize 999;
SQL>
SQL> select name, open_mode from v$pdbs;
NAME----------OPEN_MODE
PDB$SEED------READ ONLY
PDBTEST1------MOUNTED
SQL>
SQL> alter session set container=PDBTEST1;
Session altered.
SQL>
SQL> show con_name;
CON_NAME
PDBTEST1
SQL>

PostgreSQL Backup/Recovery Operations with Barman

If you do not have a ready-made PostreSQL database environment to test the restore / recovery processes, you can create your test environment by following the Manuel/Offline PostgreSQL 12 Installation on Linux 7 article.

You can add the database server you want to backup to the BARMAN server by following the steps of Step By Step PostgreSQL Barman Installation and Configuration on Linux 7 for restore / recovery testing.

In this document, a database server named pg12test will be used for restore / recovery tests.

CONTROLS OF THE DATABASE SERVER ADDED TO THE BARMAN SERVER

SYNTAX: barman list-server

[root@barman_backup_server ~]# su - barman
Last login: Wed Feb 24 09:25:14 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman list-server
pg12test - 10.X.X.X PG12TEST Database (Streaming-Only)
-bash-4.2$ 

SYNTAX: barman check [server_name]

[root@barman_backup_server ~]# su - barman
Last login: Wed Feb 24 09:13:29 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman check pg12test
Server pg12test:
PostgreSQL: OK
superuser or standard user with backup privileges: OK
PostgreSQL streaming: OK
wal_level: OK
replication slot: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 1 backups, expected at least 1)
ssh: OK (PostgreSQL server)
systemid coherence: OK
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archiver errors: OK
-bash-4.2$

SYNTAX: barman status [server_name]

[root@barman_backup_server ~]# su - barman
Last login: Wed Feb 24 09:20:31 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman status pg12test
Server pg12test:
Description: 10.X.X.X PG12TEST Database (Streaming-Only)
Active: True
Disabled: False
PostgreSQL version: 12.6
Cluster state: in production
pgespresso extension: Not available
Current data size: 24.3 MiB
PostgreSQL Data directory: /var/lib/pgsql/12/data
Current WAL segment: 000000010000000000000017
Passive node: False
Retention policies: enforced (mode: auto, retention: REDUNDANCY 2, WAL retention: MAIN)
No. of available backups: 1
First available backup: 20210224T085214
Last available backup: 20210224T085214
Minimum redundancy requirements: satisfied (1/1)
-bash-4.2$
DATABASE BACKUP PROCESS

SYNTAX: barman backup [server_name] –wait

–wait, -w
Backup waits for all necessary WAL files to be archived.

[root@barman_backup_server ~]# su - barman
Last login: Wed Feb 24 09:34:40 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman list-server
pg12test - 10.X.X.X PG12TEST Database (Streaming-Only)
-bash-4.2$
-bash-4.2$ barman backup pg12test --wait
Starting backup using rsync-concurrent method for server pg12test in /backup/barman/pg12test/base/20210224T100920
Backup start at LSN: 0/18000028 (000000010000000000000018, 00000028)
Starting backup copy via rsync/SSH for 20210224T100920 (4 jobs)
Copy done (time: less than one second)
Asking PostgreSQL server to finalize the backup.
Backup size: 24.2 MiB. Actual size on disk: 9.6 KiB (-99.96% deduplication ratio).
Backup end at LSN: 0/18000100 (000000010000000000000018, 00000100)
Backup completed (start time: 2021-02-24 10:09:20.661131, elapsed time: 1 second)
Waiting for the WAL file 000000010000000000000018 from server 'pg12test'
Processing xlog segments from streaming for pg12test
000000010000000000000017
Processing xlog segments from streaming for pg12test
000000010000000000000018
-bash-4.2$
LISTING DATABASE BACKUPS

SYNTAX: barman list-backup [server_name]

[root@barman_backup_server ~]# su - barman
Last login: Wed Feb 24 10:08:46 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman list-backup pg12test
pg12test 20210224T100920 - Wed Feb 24 10:09:22 2021 - Size: 40.2 MiB - WAL Size: 0 B
pg12test 20210224T085214 - Wed Feb 24 08:52:17 2021 - Size: 40.2 MiB - WAL Size: 32.0 MiB
-bash-4.2$
SAMPLE RESTORE / RECOVERY TEST PROCEDURES

With the following steps, we will make the backup of the database PG12TEST available by performing a restore / recovery process on the BARMAN server.

We installed PostgreSQL 12 database on the BARMAN server following Step By Step PostgreSQL Barman Installation and Configuration on Linux 7 . It can be controlled with the following commands.

[root@barman_backup_server ~]# ps -ef | grep postmaster
postgres 31139     1  0 13:22 ?        00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
root     31181 18990  0 13:23 pts/0    00:00:00 grep --color=auto postmaster
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl status postgresql-12.service | grep port
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.318 +03 [31139] LOG:  listening on IPv6 address "::1", port 5432
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.318 +03 [31139] LOG:  listening on IPv4 address "127.0.0.1", port 5432
[root@barman_backup_server ~]#

Close the database running on the BARMAN server from the /var/lib/pgsql/12/data directory and port 5432.

[root@barman_backup_server ~]# systemctl stop postgresql-12
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl status postgresql-12
◠postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Wed 2021-02-24 13:34:08 +03; 26s ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 31139 ExecStart=/usr/pgsql-12/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
  Process: 31131 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 31139 (code=exited, status=0/SUCCESS)

Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.317 +03 [31139] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (...), 64-bit
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.318 +03 [31139] LOG:  listening on IPv6 address "::1", port 5432
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.318 +03 [31139] LOG:  listening on IPv4 address "127.0.0.1", port 5432
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.320 +03 [31139] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.321 +03 [31139] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432" 
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.331 +03 [31139] LOG:  redirecting log output to logging collector process
Feb 24 13:22:48 barman_backup_server.xxx.com.tr postmaster[31139]: 2021-02-24 13:22:48.331 +03 [31139] HINT:  Future log output will appear in directory "log".
Feb 24 13:22:48 barman_backup_server.xxx.com.tr systemd[1]: Started PostgreSQL 12 database server.
Feb 24 13:34:08 barman_backup_server.xxx.com.tr systemd[1]: Stopping PostgreSQL 12 database server...
Feb 24 13:34:08 barman_backup_server.xxx.com.tr systemd[1]: Stopped PostgreSQL 12 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@barman_backup_server ~]#

Rename the data directory in the BARMAN server /var/lib/pgsql/12/ directory to data_ORIGINAL.

[root@barman_backup_server 12]# cd /var/lib/pgsql/12/
[root@barman_backup_server 12]#
[root@barman_backup_server 12]# ls -lrt
total 12
drwxrwxrwx.  2 postgres postgres 4096 Feb 11 04:16 backups
-rwxrwxrwx.  1 postgres postgres  923 Feb 22 17:31 initdb.log
drwx------. 20 postgres postgres 4096 Feb 24 13:34 data
[root@barman_backup_server 12]#
[root@barman_backup_server 12]# mv data data_ORIGINAL
[root@barman_backup_server 12]#
[root@barman_backup_server 12]# ls -lrt
total 12
drwxrwxrwx.  2 postgres postgres 4096 Feb 11 04:16 backups
-rwxrwxrwx.  1 postgres postgres  923 Feb 22 17:31 initdb.log
drwx------. 20 postgres postgres 4096 Feb 24 13:34 data_ORIGINAL
[root@barman_backup_server 12]#

Create a database named restore_test in the database PG12TEST that we will back up. We will test if it will come after the restore / recovery process on the BARMAN server.

[root@pg12_testdb ~]# su - postgres
Last login: Wed Feb 24 13:44:42 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.6)
Type "help" for help.

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8193 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(3 rows)

postgres=#
postgres=# create database restore_test;
CREATE DATABASE
postgres=#
postgres=# \l+
                                                                     List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
--------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8193 kB | pg_default | default administrative connection database
 restore_test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8049 kB | pg_default |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | unmodifiable empty database
              |          |          |             |             | postgres=CTc/postgres |         |            |
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | default template for new databases
              |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

postgres=#

Create a UNLOGGED table for testing purposes by connecting to the database named restore_test in the database PG12TEST that we will back up.

Since the changes in UNLOGGED tables are not written to WAL (Write-Ahead-Log) files, writing performance is higher than normal tables. We will see this in the tests we will do.

At the same time, we will test whether the unlogged tables in the database in noarchivelog mode are included in the backups taken with BARMAN after our restore / recovery operations.

[root@pg12_testdb ~]# su - postgres
Last login: Wed Feb 24 13:50:54 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.6)
Type "help" for help.

postgres=# \l+
                                                                     List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
--------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8193 kB | pg_default | default administrative connection database
 restore_test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8049 kB | pg_default |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | unmodifiable empty database
              |          |          |             |             | postgres=CTc/postgres |         |            |
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | default template for new databases
              |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

postgres=#
postgres=# \c restore_test
You are now connected to database "restore_test" as user "postgres".
restore_test=#
restore_test=# create unlogged table unlogged_test_table(n int,n1 int);
CREATE TABLE
restore_test=#
restore_test=# \d unlogged_test_table
    Unlogged table "public.unlogged_test_table" 
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 n      | integer |           |          |
 n1     | integer |           |          |

restore_test=#

Create a NORMAL (LOGGED) table for testing purposes by connecting to the database named restore_test in the database PG12TEST that we will back up.

[root@pg12_testdb ~]# su - postgres
Last login: Wed Feb 24 14:01:05 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.6)
Type "help" for help.

postgres=# \l+
                                                                     List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
--------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8193 kB | pg_default | default administrative connection database
 restore_test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8193 kB | pg_default |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | unmodifiable empty database
              |          |          |             |             | postgres=CTc/postgres |         |            |
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | default template for new databases
              |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

postgres=#
postgres=# \c restore_test
You are now connected to database "restore_test" as user "postgres".
restore_test=#
restore_test=# create table logged_test_table(n int,n1 int);
CREATE TABLE
restore_test=#
restore_test=# \d logged_test_table
         Table "public.logged_test_table" 
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 n      | integer |           |          |
 n1     | integer |           |          |

restore_test=#

We observe the time by making an insert operation for our unlogged table (unlogged_test_table). We also entered 3000000 lines of data. After the restore / recovery, we will check if these records in the unlogged table have come / not.

[root@pg12_testdb ~]# su - postgres
Last login: Wed Feb 24 14:20:52 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.6)
Type "help" for help.

postgres=# \c restore_test
You are now connected to database "restore_test" as user "postgres".
restore_test=#
restore_test=# \timing
Timing is on.
restore_test=#
restore_test=# insert into unlogged_test_table values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1054.425 ms (00:01.054)
restore_test=#
restore_test=# insert into unlogged_test_table values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1073.998 ms (00:01.074)
restore_test=#
restore_test=# insert into unlogged_test_table values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1035.370 ms (00:01.035)
restore_test=#
restore_test=# select count(*) from unlogged_test_table;
  count
---------
 3000000
(1 row)

Time: 275.446 ms
restore_test=#

We observe the time by performing an insert operation for our normal (logged) table (logged_test_table). We also entered 3000000 lines of data. After restore / recovery, we will check if these records in the table have come or not.

[root@pg12_testdb ~]# su - postgres
Last login: Wed Feb 24 14:34:11 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.6)
Type "help" for help.

postgres=# \c restore_test
You are now connected to database "restore_test" as user "postgres".
restore_test=#
restore_test=# \timing
Timing is on.
restore_test=#
restore_test=# insert into logged_test_table values (generate_series(1,1000000));
INSERT 0 1000000
Time: 1910.701 ms (00:01.911)
restore_test=#
restore_test=# insert into logged_test_table values (generate_series(1,1000000));
INSERT 0 1000000
Time: 2333.320 ms (00:02.333)
restore_test=#
restore_test=# insert into logged_test_table values (generate_series(1,1000000));
INSERT 0 1000000
Time: 2232.540 ms (00:02.233)
restore_test=#
restore_test=# select count(*) from logged_test_table;
  count
---------
 3000000
(1 row)

Time: 355.734 ms
restore_test=#

We created a new database named restore_test in PG12TEST database that we will back up. In addition, we created tables named logged_test_table and unlogged_test_table in the restore_test database and added 3000000 records to both tables separately. We can now make backups.

Take backups and list them on the BARMAN server.

[root@barman_backup_server 12]# su - barman
Last login: Wed Feb 24 10:59:36 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman list-server
pg12test - 10.X.X.X PG12TEST Database (Streaming-Only)
-bash-4.2$
-bash-4.2$ barman backup pg12test --wait
Starting backup using rsync-concurrent method for server pg12test in /backup/barman/pg12test/base/20210224T150826
Backup start at LSN: 0/27000028 (000000010000000000000027, 00000028)
Starting backup copy via rsync/SSH for 20210224T150826 (4 jobs)
Copy done (time: 3 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 239.6 MiB. Actual size on disk: 215.4 MiB (-10.09% deduplication ratio).
Backup end at LSN: 0/27000138 (000000010000000000000027, 00000138)
Backup completed (start time: 2021-02-24 15:08:26.543852, elapsed time: 4 seconds)
Waiting for the WAL file 000000010000000000000027 from server 'pg12test'
Processing xlog segments from streaming for pg12test
        000000010000000000000026
Processing xlog segments from streaming for pg12test
        000000010000000000000027
-bash-4.2$
-bash-4.2$ barman list-backup pg12test
pg12test 20210224T150826 - Wed Feb 24 15:08:31 2021 - Size: 255.6 MiB - WAL Size: 0 B
pg12test 20210224T104326 - Wed Feb 24 10:43:28 2021 - Size: 48.1 MiB - WAL Size: 208.0 MiB
-bash-4.2$

A directory is created on the BARMAN server where the barman user can perform the restoration process and the necessary authorizations are given.

[root@barman_backup_server ~]# mkdir /backup/restored_db/
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# chown -R barman:barman /backup/
[root@barman_backup_server ~]#

On the BARMAN server, we start the restore process of the backup of the database named PG12TEST with the id number 20210224T150826 to the /backup/restored_db/pg12test_data/ directory.

SYNTAX: barman recover [Server_Name] [Backup_ID] [Recovery_Directory]

!!! 256MB BACKUP RESTORED IN 2 SECONDS !!!

[root@barman_backup_server ~]# su - barman
Last login: Wed Feb 24 15:07:26 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ barman list-backup pg12test
pg12test 20210224T150826 - Wed Feb 24 15:08:31 2021 - Size: 255.6 MiB - WAL Size: 0 B
pg12test 20210224T104326 - Wed Feb 24 10:43:28 2021 - Size: 48.1 MiB - WAL Size: 208.0 MiB
-bash-4.2$
-bash-4.2$ barman recover pg12test 20210224T150826 /backup/restored_db/pg12test_data/
Starting local restore for server pg12test using backup 20210224T150826
Destination directory: /backup/restored_db/pg12test_data/
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.

Recovery completed (start time: 2021-02-24 15:21:51.842384, elapsed time: 2 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
-bash-4.2$

The directory where the PG12TEST database is restored on the BARMAN server (/backup/restored_db/pg12test_data) is moved under the /var/lib/pgsql/12/ directory as the name data and its ownership is changed to postgres user.

[root@barman_backup_server ~]# cd /backup/restored_db/
[root@barman_backup_server restored_db]#
[root@barman_backup_server restored_db]# ls -lrt
total 4
drwx------. 20 barman barman 4096 Feb 24 15:21 pg12test_data
[root@barman_backup_server restored_db]#
[root@barman_backup_server restored_db]# mv pg12test_data /var/lib/pgsql/12/data
[root@barman_backup_server restored_db]#
[root@barman_backup_server restored_db]# cd /var/lib/pgsql/12/
[root@barman_backup_server 12]#
[root@barman_backup_server 12]# ls -lrt
total 16
drwxrwxrwx.  2 postgres postgres 4096 Feb 11 04:16 backups
-rwxrwxrwx.  1 postgres postgres  923 Feb 22 17:31 initdb.log
drwx------. 20 postgres postgres 4096 Feb 24 13:34 data_ORIGINAL
drwx------. 20 barman   barman   4096 Feb 24 15:21 data
[root@barman_backup_server 12]#
[root@barman_backup_server 12]# chown -R postgres:postgres data
[root@barman_backup_server 12]#
[root@barman_backup_server 12]# ls -lrt
total 16
drwxrwxrwx.  2 postgres postgres 4096 Feb 11 04:16 backups
-rwxrwxrwx.  1 postgres postgres  923 Feb 22 17:31 initdb.log
drwx------. 20 postgres postgres 4096 Feb 24 13:34 data_ORIGINAL
drwx------. 20 postgres postgres 4096 Feb 24 15:21 data
[root@barman_backup_server 12]#

The postgresql service on the BARMAN server is opened and its status is checked.

[root@barman_backup_server ~]# systemctl start postgresql-12
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl status postgresql-12
◠postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2021-02-24 15:32:19 +03; 12s ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 3827 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 3835 (postmaster)
    Tasks: 8
   CGroup: /system.slice/postgresql-12.service
           ├─3835 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
           ├─3839 postgres: logger
           ├─3841 postgres: checkpointer
           ├─3842 postgres: background writer
           ├─3843 postgres: walwriter
           ├─3844 postgres: autovacuum launcher
           ├─3845 postgres: stats collector
           └─3846 postgres: logical replication launcher

Feb 24 15:32:18 barman_backup_server.xxx.com.tr systemd[1]: Starting PostgreSQL 12 database server...
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.038 +03 [3835] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (G...4), 64-bit
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.039 +03 [3835] LOG:  listening on IPv4 address "0.0.0.0", port 5432
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.039 +03 [3835] LOG:  listening on IPv6 address "::", port 5432
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.041 +03 [3835] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.042 +03 [3835] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432" 
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.055 +03 [3835] LOG:  redirecting log output to logging collector process
Feb 24 15:32:19 barman_backup_server.xxx.com.tr postmaster[3835]: 2021-02-24 15:32:19.055 +03 [3835] HINT:  Future log output will appear in directory "log".
Feb 24 15:32:19 barman_backup_server.xxx.com.tr systemd[1]: Started PostgreSQL 12 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@barman_backup_server ~]#

On the database server named PG12TEST restored in BARMAN server;

The database named restore_test EXISTS,
The table named logged_test_table EXISTS,
The table named unlogged_test_table APPEAR THAT THE CONTENTS ARE NULL.

[root@barman_backup_server ~]# su - postgres
Last login: Wed Feb 24 11:08:01 +03 2021 on pts/2
-bash-4.2$
-bash-4.2$ psql
psql (12.6)
Type "help" for help.

postgres=# \l+
                                                                     List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
--------------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8193 kB | pg_default | default administrative connection database
 restore_test | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 112 MB  | pg_default |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | unmodifiable empty database
              |          |          |             |             | postgres=CTc/postgres |         |            |
 template1    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8049 kB | pg_default | default template for new databases
              |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

postgres=#
postgres=# \c restore_test;
You are now connected to database "restore_test" as user "postgres".
restore_test=#
restore_test=# \dt+
                            List of relations
 Schema |        Name         | Type  |  Owner   |  Size   | Description
--------+---------------------+-------+----------+---------+-------------
 public | logged_test_table   | table | postgres | 104 MB  |
 public | unlogged_test_table | table | postgres | 0 bytes |
(2 rows)

restore_test=# select count(*) from logged_test_table;
  count
---------
 3000000
(1 row)

restore_test=# select count(*) from unlogged_test_table;
 count
-------
     0
(1 row)

restore_test=#

Manuel/Offline PostgreSQL 12 Installation on Linux 7

INSTALLING REQUIRED RPMs

[root@barman_backup_server tmp]# rpm -ivh postgresql12-libs-12.4-1PGDG.rhel7.x86_64.rpm
warning: postgresql12-libs-12.4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql12-libs-12.4-1PGDG.rhel################################# [100%]
[root@barman_backup_server tmp]#
[root@barman_backup_server tmp]# rpm -ivh postgresql12-12.4-1PGDG.rhel7.x86_64.rpm
warning: postgresql12-12.4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql12-12.4-1PGDG.rhel7    ################################# [100%]
[root@barman_backup_server tmp]#
[root@barman_backup_server tmp]# rpm -ivh postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm
warning: postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql12-server-12.4-1PGDG.rh################################# [100%]
[root@barman_backup_server tmp]#

INITIALIZING THE DATABASE

[root@barman_backup_server ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

[root@barman_backup_server ~]#

SERVICE ACTIVATION OPERATION

[root@barman_backup_server ~]# systemctl enable postgresql-12.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl start postgresql-12.service
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl status postgresql-12.service
◠postgresql-12.service - PostgreSQL 12 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-02-15 11:34:25 +03; 13s ago
     Docs: https://www.postgresql.org/docs/12/static/
  Process: 6589 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 6597 (postmaster)
    Tasks: 8
   CGroup: /system.slice/postgresql-12.service
           ├─6597 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
           ├─6599 postgres: logger
           ├─6601 postgres: checkpointer
           ├─6602 postgres: background writer
           ├─6603 postgres: walwriter
           ├─6604 postgres: autovacuum launcher
           ├─6605 postgres: stats collector
           └─6606 postgres: logical replication launcher

Feb 15 11:34:25 barman_backup_server systemd[1]: Starting PostgreSQL 12 database server...
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.634 +03 [6597] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150...39), 64-bit
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.635 +03 [6597] LOG:  listening on IPv6 address "::1", port 5432
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.635 +03 [6597] LOG:  listening on IPv4 address "127.0.0.1", port 5432
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.637 +03 [6597] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.640 +03 [6597] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432" 
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.659 +03 [6597] LOG:  redirecting log output to logging collector process
Feb 15 11:34:25 barman_backup_server postmaster[6597]: 2021-02-15 11:34:25.659 +03 [6597] HINT:  Future log output will appear in directory "log".
Feb 15 11:34:25 barman_backup_server systemd[1]: Started PostgreSQL 12 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@barman_backup_server ~]#

MAKING NECESSARY SETTINGS FOR CONNECTION

[root@barman_backup_server ~]# su - postgres
-bash-4.2$
-bash-4.2$ psql
psql (12.4)
Type "help" for help.

postgres=# SELECT version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

postgres=#
[root@barman_backup_server ~]# vi /var/lib/pgsql/12/data/pg_hba.conf
host    all             all             0.0.0.0/0               md5
[root@barman_backup_server ~]# vi /var/lib/pgsql/12/data/postgresql.conf
listen_addresses = '*'          # what IP address(es) to listen on;
[root@barman_backup_server ~]# systemctl stop firewalld
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl disable firewalld
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# systemctl status firewalld
◠firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@barman_backup_server ~]#
[root@barman_backup_server ~]# su - postgres
Last login: Mon Feb 15 11:42:54 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.4)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#
postgres=# alter user postgres with password 'postgres';
ALTER ROLE
postgres=#
[root@barman_backup_server ~]# systemctl restart postgresql-12
[root@barman_backup_server ~]#

In order for the configuration changes to take effect, the following command can be issued without restarting the DB.

[root@barman_backup_server ~]# su - postgres
Last login: Mon Feb 15 11:54:51 +03 2021 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (12.4)
Type "help" for help.

postgres=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#

Step By Step PostgreSQL Barman Installation and Configuration on Linux 7

This document includes the steps to install the BARMAN software on a different server that does not have databases. The purpose is to make our backups available in case the database server becomes unavailable for any reason.

PROCEDURES TO BE DONE ON THE BARMAN SERVER

Operating system information of the server on which BARMAN will be installed is displayed.

yum install -y epel-release && yum makecache fast command is executed.

I did my tests on Centos 7.6 and the command below worked directly. Oracle Linux 7.9 gives an error as below.

To get past the above error, we will install Extra Packages for Enterprise Linux (or EPEL). The following commands are executed respectively.

After installing Extra Packages for Enterprise Linux (or EPEL), run the command yum install -y epel-release && yum makecache fast which we get error.

A repo download link is created by going to https://www.postgresql.org/download/linux/redhat/ and entering PostgreSQL version and platform (OS) information.

PostgreSQL repository is installed by running sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

yum makecache fast command is executed.

curl https://dl.2ndquadrant.com/default/release/get/12/rpm | sudo bash command can be accessed and run by following the screens below.

Add the line exclude = barman* to the bottom of all blocks in /etc/yum.repos.d/pgdg-redhat-all.repo

EXAMPLE

[pgdg-common]
name=PostgreSQL common RPMs for RHEL/CentOS $releasever – $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
exclude=barman*

yum makecache fast command is executed again.

BARMAN is installed with yum install -y barman command.

PostgreSQL 12 database is installed.

PostgreSQL 12 database is initialized.

PostgreSQL 12 database service is enabled + started and its status is checked.

/etc/barman/barman.conf -> Global configuration file. It contains general backup configurations such as log file, backup user, backup directory.

The original version of barman.conf is backed up.

After being backed up, the following parameter values are updated.

In the conf file, directories that are specified to be backup (barman_home = / backup / barman) are created and their authorities are given.

TRANSACTIONS TO BE TAKEN ON THE DATABASE SERVER WHICH WE WANT TO BACKUP

In order for our PostgreSQL database to accept incoming connections, listen_addresses = ‘*’ must be in the postgresql.conf file in /var/lib/pgsql/12/data/ directory by default.

The file path may differ depending on the version and the installed directory.

Sample output is as follows.

If the listen_addresses value is not *, it must be set and the postgresql service must be restarted for the changes to take effect.

We create the barman user with superuser right for backup operations and streaming_barman user with replication right for streaming operations.

In order for the BARMAN server to connect to the database and take backups, we add the following lines, which contain the BARMAN server’s IP to the pg_hba.conf file.

Note: In the default installation of PostgreSQL 12 version, pg_hba.conf file is located in >>> /var/lib/pgsql/12/data/pg_hba.conf directory.

PostgreSQL service needs reload operation for a change made in pg_hba.conf file to take effect. By connecting with psql, we can do it with the following command.

Access rules to the database can be controlled as follows.

THE FOLLOWING PROCESSES WILL BE PERFORMED ON WHICH SERVER, I WRITE IN THE TITLE SECTION.

We can check the access of the barman user we created with the following command.

BARMAN BACKUP SERVER >>> POSTGRESQL DB

For streaming operations, we can check the connection of the streaming_barman user which has replication rights as follows.

BARMAN BACKUP SERVER >>> POSTGRESQL DB

In order for barman and streaming_barman users to connect to the database from which we will take backup from the barman server without the need to enter a password, the following operations are done.

In the .pgpass file, we enter the IP of the database to be backed up, the barman user/password and the streaming_barman user/password.

TO BE MADE IN BARMAN BACKUP SERVER

With the barman user, a connection test is made to the database server from which we will take a backup from the BARMAN server without the need to enter a password.

BARMAN BACKUP SERVER >>> POSTGRESQL DB

With the streaming_barman user, a connection test is made to the database server from which we will take a backup from the BARMAN server without the need to enter a password.

BARMAN BACKUP SERVER >>> POSTGRESQL DB

SSH-KEY is created by passing to the barman user on the BARMAN server

SSH-KEY is created by passing to the postgres user on the database server that we will take a backup.

Passing to postgres user on the database server that we will take a backup

Created .ssh / authorized_keys file

The following key which we have created with the barman user on the BARMAN server, is pasted into an application like notepad++ and made into a single line

SINGLE-LINE KEY IS ENTERED IN THE .ssh/authorized_keys FILE OF USER OF postgres ON THE DATABASE SERVER

SSH test is made to PostgreSQL database server from which we will take a backup from BARMAN server.

THE POSTGRESQL DATABASE AUTHORIZED OPERATING SYSTEM USER (POSTGRES) PASSWORD MUST BE READY.

We create a configuration file for the server that we want to backup in the /etc/barman.d/ directory of the BARMAN server.

Ownership of the configuration file created for the server that we will take a backup on the BARMAN server is changed to be the barman.

We check if the server that we added to the BARMAN server to backup is listed / not listed.

The status of the server we have added to the BARMAN server to back up is controlled by the command barman check server_name

SYNTAX: barman check [server_name]

Replication slot: FAILED (replication slot ‘barman’ doesn’t exist. Please execute ‘barman receive-wal –create-slot dwh’). To resolve the error, the following command is run and a slot with the name of the barman is created on the PostgreSQL Database (DWH) that we want to backup.

To verify WAL archiving process, run the following command.

Source: https://dba.stackexchange.com/questions/166195/wal-archive-failed-please-make-sure-wal-shipping-is-setup

After the above processes, the final situation is as below.

WAL archive: FAILED and recieve-wal running FAILED errors are gone.

THE ERROR IS EXISTING AT THE SSH CONNECTION.

The following commands are run on the database server attempting to SSH to fix the connection error.

Source: https://serverfault.com/questions/598058/ssh-permission-denied-publickey-gssapi-with-mic-password

After the above operations, the error is gone in the barman check [server_name] command.

Minimum redundancy requirements: FAILED (have 0 backups, expected at least 1) error can be ignored. Because we haven’t taken any backup yet.

We take our first backup with the barman backup [server_name] –wait command.

After backup, barman check [server_name] output is as below.

The backups we received are listed with the barman list-backup [server_name] command .

The status of the server we have backed up is checked with barman status [server_name] command.

PostgreSQL Logged Vs. Unlogged Tables

While transactions in regular (logged) tables are written to WAL files,  changes in unlogged tables are not written to WAL files.

Writing performance is higher than regular (logged) tables, since writing to WAL files does not occur in unlogged tables.

If you wish to back up unlogged tables, they probably should not be unlogged, because an unlogged table is erased completely if PostgreSQL or the server crashes or shuts down unexpectedly.

Unlogged tables are never included in pg_basebackup, streaming replication or WAL archiving backup and PITR. There is no option to include them, because to include them they’d have to be logged, and then they wouldn’t be unlogged tables anymore.

Unlogged table contents are not sent to standby (replica) servers.

Unlogged tables are always included in dumps (pg_dump) unless you explicitly specify the –no-unlogged-table-data command line option.

Unlogged tables are also included in file-system level backups taken when the databases server is stopped after a clean shutdown.