Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 6 hours 31 min ago

Dbvisit Standby Archive Log Daemon

Mon, 2018-04-09 07:27

Dbvisit Standby version 8 comes with a nice feature, a daemon, which gives the benefit to send and apply the archive log automatically in the background. Bypassing the system scheduling, the daemon will facilitate customer RPO (Recovery Point Objective) and RTO (Recovery Time Objective) fine tuning. Monitoring to apply logs to the Standby only when needed, will also optimize use of resources. Originally available for the Linux based environments, the feature has been made available for the Windows based platforms starting 8.0.06. This blog will cover its implementation and show its benefit.

Demo databases environments have been easily managed thanks to DBI DMK tool.

Environment
DBVP : Primary Server
DBVS : Standby Server
DBVPDB_SITE1 : Primary database
DBVPDB_SITE2 : Physical Standby database

 

Daemon start/stop/status
oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start
Starting Dbvisit Daemon...
Started successfully.

oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 11546.

oracle@DBVP:/home/oracle/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop
Stopping Dbvisit Daemon...
Successfully stopped.

 

Automatic startup
In order to start the daemon automatically at boot,  and easily manage its status, we will create a dbvlogdaemon Service.
[root@DBVP ~]# vi /etc/systemd/system/dbvlogdaemon.service

[root@DBVP ~]# cat /etc/systemd/system/dbvlogdaemon.service
[Unit]
Description=DB Visit log daemon Service
After=oracle.service

[Service]
Type=simple
RemainAfterExit=yes
User=oracle
Group=oinstall
Restart=always
ExecStart=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start
ExecStop=/u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D stop

[Install]
WantedBy=multi-user.target

[root@DBVP ~]# chmod 644 /etc/systemd/system/dbvlogdaemon.service

[root@DBVP ~]# systemctl daemon-reload

[root@DBVP ~]# systemctl enable dbvlogdaemon.service

Of course this would not avoid impact in case of daemon crash which could be simulated with a kill command.

 

Check running daemon
oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ps -ef | grep dbvctl | grep -v grep
oracle    4299     1  0 08:25 ?        00:00:02 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start

oracle@DBVP:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 4299.

oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ps -ef | grep dbvctl | grep -v grep
oracle    4138     1  0 08:25 ?        00:00:01 /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -D start

oracle@DBVS:/u01/app/dbvisit/standby/ [DBVPDB] ./dbvctl -d DBVPDB_SITE1 -D status
Dbvisit Daemon process is running with pid 4138.

 

Daemon Parameter
# DMN_DBVISIT_INTERVAL     - interval in sec for dbvisit schedule on source
# DMN_MONITOR_INTERVAL     - interval in sec for log monitor schedule on source
# DMN_DBVISIT_TIMEOUT      - max sec for a dbvisit process to complete on source
# DMN_MONITOR_TIMEOUT      - max sec for a monitor process to complete on source
# DMN_MONITOR_LOG_NUM      - number of logs to monitor on source
# DMN_MAX_FAIL_NOTIFICATIONS - max number of emails sent on failure on source
# DMN_BLACKOUT_STARTTIME   - blackout window start time HH:MI on source
# DMN_BLACKOUT_ENDTIME     - blackout window end time HH:MI on source
# DMN_DBVISIT_INTERVAL_DR  - interval in sec for dbvisit schedule on destination
# DMN_MONITOR_INTERVAL_DR  - interval in sec for log monitor schedule on destination
# DMN_DBVISIT_TIMEOUT_DR   - max sec for a dbvisit process to complete on destination
# DMN_MONITOR_TIMEOUT_DR   - max sec for a monitor process to complete on destination
# DMN_MONITOR_LOG_NUM_DR   - number of logs to monitor on destination
# DMN_MAX_FAIL_NOTIFICATIONS_DR - max number of emails sent on failure on destination
# DMN_BLACKOUT_STARTTIME_DR- blackout window start time HH:MI on destination
# DMN_BLACKOUT_ENDTIME_DR  - blackout window end time HH:MI on destination

With the daemon, we can pause the archive send/apply process using the DMN_BLACKOUT parameters.

To setup our lab we will act on the most important parameters :
  • DMN_MONITOR_INTERVAL  (Primary) and DMN_MONITOR_INTERVAL_DR (Standby).
    The Monitor Interval will give the frequency for Dbvisit to check for new archive log and only act if existing.
  • DMN_DBVISIT_INTERVAL (Primary) and DMN_DBVISIT_INTERVAL_DR (Standby)
    The Dbvisit Interval will give the frequency for Dbvisit to force a send/apply process. This action will be dependent of the LOGSWITCH DCC parameter. Recommendation is not to go below 5 minutes.
oracle@DBVP:/oracle/u01/app/dbvisit/standby/conf/ [DBVPDB] pwd
/oracle/u01/app/dbvisit/standby/conf

oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] egrep 'DMN_DBVISIT_INTERVAL|DMN_MONITOR_INTERVAL' dbv_DBVPDB_SITE1.env
# DMN_DBVISIT_INTERVAL     - interval in sec for dbvisit schedule on source
# DMN_MONITOR_INTERVAL     - interval in sec for log monitor schedule on source
# DMN_DBVISIT_INTERVAL_DR  - interval in sec for dbvisit schedule on destination
# DMN_MONITOR_INTERVAL_DR  - interval in sec for log monitor schedule on destination
DMN_DBVISIT_INTERVAL = 300
DMN_MONITOR_INTERVAL = 60
DMN_DBVISIT_INTERVAL_DR = 300
DMN_MONITOR_INTERVAL_DR = 60

 

The LOGSWITCH parameter determines if a database log switch (alter system switch logfile) should be trigger at Dbvisit execution.
N (default value) : Only if there are no new archive logs to transfer.
Y : At every execution, independently of the archive log creation.
I(Ignore) : Never. To be use with caution.

A daemon restart is mandatory post DDC configuration file updates.
[root@DBVP ~]# service dbvlogdaemon stop
Redirecting to /bin/systemctl stop dbvlogdaemon.service
[root@DBVP ~]# service dbvlogdaemon start
Redirecting to /bin/systemctl start dbvlogdaemon.service

[root@DBVS ~]# service dbvlogdaemon stop
Redirecting to /bin/systemctl stop dbvlogdaemon.service
[root@DBVS ~]# service dbvlogdaemon start
Redirecting to /bin/systemctl start dbvlogdaemon.service

 

Send and apply archive log demo
Get current date and primary current sequence.
SQL> select sysdate from dual;

SYSDATE
-------------------
2018/03/28 12:30:50

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
           179

Generate a Dbvisit gap report.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21393)
dbvctl started on DBVP: Wed Mar 28 12:30:57 2018
=============================================================

Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281230:
-------------------------------------------------------------
Destination database on DBVS is at sequence: 178.
Source database on DBVP is at log sequence: 179.
Source database on DBVP is at archived log sequence: 178.
Dbvisit Standby last transfer log sequence: 178.
Dbvisit Standby last transfer at: 2018-03-28 12:29:14.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:01:27.


=============================================================
dbvctl ended on DBVP: Wed Mar 28 12:31:06 2018
=============================================================

No archive log needs to be send and apply on the standby. Both databases are in sync.


Generate logfile switch
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

Check current date and primary database current sequence.
SQL> select sysdate from dual;

SYSDATE
-------------------
2018/03/28 12:31:29

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
           182

Generate new Dbvisit gap reports.
oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21454)
dbvctl started on DBVP: Wed Mar 28 12:31:38 2018
=============================================================

Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281231:
-------------------------------------------------------------
Destination database on DBVS is at sequence: 178.
Source database on DBVP is at log sequence: 182.
Source database on DBVP is at archived log sequence: 181.
Dbvisit Standby last transfer log sequence: 178.
Dbvisit Standby last transfer at: 2018-03-28 12:29:14.

Archive log gap for thread 1:  3.
Transfer log gap for thread 1: 3.
Standby database time lag (DAYS-HH:MI:SS): +00:02:27.


=============================================================
dbvctl ended on DBVP: Wed Mar 28 12:31:47 2018
=============================================================
We can see that we have 3 new archive logs to transfer and to apply on the standby.
There is a 3 sequences lag between both databases.

oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21571)
dbvctl started on DBVP: Wed Mar 28 12:32:19 2018
=============================================================
Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281232:
-------------------------------------------------------------
Destination database on DBVS is at sequence: 178.
Source database on DBVP is at log sequence: 182.
Source database on DBVP is at archived log sequence: 181.
Dbvisit Standby last transfer log sequence: 181.
Dbvisit Standby last transfer at: 2018-03-28 12:32:13.
Archive log gap for thread 1:  3.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:02:27.
=============================================================
dbvctl ended on DBVP: Wed Mar 28 12:32:27 2018
=============================================================
3 archive logs has been automatically transferred by the daemon to the standby in the next minute.

oracle@DBVP:/u01/app/dbvisit/standby/conf/ [DBVPDB] /u01/app/dbvisit/standby/dbvctl -d DBVPDB_SITE1 -i
=============================================================
Dbvisit Standby Database Technology (8.0.16_0_g4e0697e2) (pid 21679)
dbvctl started on DBVP: Wed Mar 28 12:33:00 2018
=============================================================

Dbvisit Standby log gap report for DBVPDB_SITE1 thread 1 at 201803281233:
-------------------------------------------------------------
Destination database on DBVS is at sequence: 181.
Source database on DBVP is at log sequence: 182.
Source database on DBVP is at archived log sequence: 181.
Dbvisit Standby last transfer log sequence: 181.
Dbvisit Standby last transfer at: 2018-03-28 12:32:13.

Archive log gap for thread 1:  0.
Transfer log gap for thread 1: 0.
Standby database time lag (DAYS-HH:MI:SS): +00:01:13.


=============================================================
dbvctl ended on DBVP: Wed Mar 28 12:33:09 2018
=============================================================

Another minute later the standby daemon applied the new archive logs. Both databases are on sync.

 

Conclusion

Dbvisit new daemon feature is adding real flexibility in sending and applying archive logs, and help improving customer RPO and RTO. We still might want to keep a daily crontab gap report with email to be sent to a DBA team. This will ensure to monitor daemon keep alive.

Logswitch and sending archive logs to standby consumes real system resource. Dbvisit daemon will also help fine tuning the use of the resource.

Note that the daemon processes must be restarted after each daylight saving clock change.

 

Cet article Dbvisit Standby Archive Log Daemon est apparu en premier sur Blog dbi services.

Automating Oracle Linux Installation with Kickstart

Fri, 2018-04-06 08:10
Automating Oracle Linux Installation with Kickstart Kickstart ?

If you need to setup from scratch several Oracle Linux systems for your Oracle databases, it can be boring to repeat the install tasks again and again on each servers.
Automation and standardization are the keys.
Kickstart can provide an easy way to accomplish mass deployment.

Kickstart configuration files

Kickstart will use a Kickstart configuration file to perform the deployment.
Maintaining ready to go Kickstart configurations is easy.
We will use in our demo an ftp server to store and access our configuration file.

Direct access to the video:
51

Lets go ! Fisrt install an ftp server

On an oralinux 7.2 server, just type following command to install an ftp server + an ftp client

yum install vsftpd ftp lftp

53

Then adapt timeout parameter to avoid disconnection when deploying your server.
Be sure anonymous access is enable.

[root@localhost ~]# sed '/^#/d' /etc/vsftpd/vsftpd.conf 
anonymous_enable=YES
local_enable=YES
write_enable=YES
local_umask=022
dirmessage_enable=YES
xferlog_enable=YES
connect_from_port_20=YES
xferlog_std_format=YES
idle_session_timeout=6000
data_connection_timeout=1200
listen=NO
listen_ipv6=YES
pam_service_name=vsftpd
userlist_enable=YES
tcp_wrappers=YES

and start your ftpd server.

systemctl start vsftpd

Then put your kickstart configuration file in it. i will explain the file later:

vi /var/ftp/pub/myksfile.ks

And copy/paste the whole content. I will explain the file later:

########################################################################
########################################################################
##                                                                    ##
##               Kickstart for OEL7 :  olg.dbi-services.com           ##
##                                                                    ##
########################################################################
########################################################################

# install through HTTP
########################################################################
install
cdrom


# locale settings
########################################################################
lang en_US.UTF-8
keyboard --vckeymap=ch --xlayouts='ch'
timezone --utc Europe/Zurich


# X is not configured on the installed system. 
########################################################################
skipx


# installation mode
########################################################################
text
reboot --eject


# Partition table initialization
########################################################################
zerombr


# Network configuration
# Oracle Linux 7: How to modify Network Interface names (Doc ID 2080965.1)
########################################################################
### network --device eth0 --bootproto static --ip 192.168.56.102 --netmask 255.255.255.0 --gateway 192.168.56.1 --nameserver it.dbi-services.com --hostname olg.dbi-services.com net.ifnames=0


# security settings
########################################################################
rootpw      toor
firewall    --enabled --ssh
selinux   --enforcing
authconfig  --enableshadow --passalgo=sha512


# Partitioning and bootloader
########################################################################
# only 1 disk presented to the O.S during installation time
# net.ifnames=0 to use eth name for network devices
bootloader      --location=mbr  --append="nofb quiet splash=quiet crashkernel=auto net.ifnames=0"
firstboot       --disable
clearpart       --all          --initlabel
part /boot      --fstype xfs   --ondisk=/dev/sda --size=512
part swap       --size=2048   --ondisk=/dev/sda
part pv.01      --size=100     --ondisk=/dev/sda --grow
volgroup RHELVG pv.01
logvol /        --fstype xfs   --name=RootLV   --vgname=RHELVG --size=8196
logvol /usr     --fstype xfs   --name=UsrLV    --vgname=RHELVG --size=2048
logvol /tmp     --fstype xfs   --name=TmpLV    --vgname=RHELVG --size=2048
logvol /var     --fstype xfs   --name=VarLV    --vgname=RHELVG --size=4096
logvol /var/log/audit     --fstype xfs   --name=AuditLV    --vgname=RHELVG --size=2048
logvol /opt     --fstype xfs   --name=OptLV    --vgname=RHELVG --size=2048
logvol /home    --fstype xfs   --name=HomeLV   --vgname=RHELVG --size=2048
logvol /u01     --fstype xfs   --name=u01LV    --vgname=RHELVG --size=2048



# packages + RPMs
########################################################################
%packages
@base

# system components
device-mapper-multipath
kexec-tools
lvm2
e4fsprogs
sg3_utils
lsscsi
dstat
ntp
perl
postfix
bc

# VI
vim-common
vim-enhanced

# SELINUX
setroubleshoot
setroubleshoot-server
setroubleshoot-plugins

%end


# POST installations tasks
########################################################################
%post

modprobe --first-time bonding
# VLAN kernel module
# modprobe --first-time 8021q

# configure bond
################
echo "DEVICE=bond0
TYPE=Bond
BONDING_MASTER=yes
BOOTPROTO=static
IPADDR=192.168.56.149
NETMASK=255.255.255.0
GATEWAY=192.168.56.1
BONDING_OPTS=\"mode=active-backup miimon=100\"
ONPARENT=yes
ONBOOT=yes" > /etc/sysconfig/network-scripts/ifcfg-bond0

echo "DEVICE=eth0
ONBOOT=yes
MASTER=bond0
BOOTPROTO=none
NM_CONTROLLED=no
SLAVE=yes" > /etc/sysconfig/network-scripts/ifcfg-eth0

echo "DEVICE=eth1
ONBOOT=yes
MASTER=bond0
BOOTPROTO=none
NM_CONTROLLED=no
SLAVE=yes" > /etc/sysconfig/network-scripts/ifcfg-eth1

echo "DEVICE=eth2
ONBOOT=yes
BOOTPROTO=dhcp
NM_CONTROLLED=no
" > /etc/sysconfig/network-scripts/ifcfg-eth2

rm -f /etc/sysconfig/network-scripts/ifcfg-en*

systemctl restart network
systemctl stop NetworkManager.service
systemctl disable NetworkManager.service


# Switch to Postfix
###################
alternatives --set mta  /usr/sbin/sendmail.postfix


# HOSTS FILE
############
cat >> /etc/hosts <> /etc/ntp.conf

# DNS config
#############
cat > /etc/resolv.conf < /etc/postfix/main.cf < /etc/postfix/master.cf <> /etc/postfix/generic
postmap /etc/postfix/generic



# user management + SUDO privilege delegation
########################################################################
adduser admora
echo toor | passwd admora --stdin

echo "admora    ALL=NOPASSWD: ALL
#admora  ALL = NOPASSWD: /bin/su - oracle , /bin/su -" >> /etc/sudoers 


# Enable services
########################################################################
systemctl enable ntpd.service
systemctl start ntpd.service
systemctl enable ntpdate.service


# Oracle +Nagios prereqs
########################################################################
yum -y install oracle-rdbms-server-11gR2-preinstall oracle-rdbms-server-12cR1-preinstall oracle-database-server-12cR2-preinstall
yum -y install openssl openssl-devel
yum -y install net-tools
# as of ALUA RHEL7.4 incompatibilities (stay on 7.2 and lock repo. later)
#yum -y update


# Oracle tuned configuration
########################################################################
mkdir -p /usr/lib/tuned/dbiOracle
cat > /usr/lib/tuned/dbiOracle/tuned.conf < /sys/class/fc_host/host1/issue_lip
echo 1 > /sys/class/fc_host/host2/issue_lip

echo "# Format:
# alias wwid
#
LUN_ORAFRA 360030d90466abf0660191bde985bba15
LUN_ORADBF 360030d906382c2065827918ddb6506da" >> /etc/multipath/bindings

cat > /etc/multipath.conf <<EOF

defaults {
   polling_interval 60
         }

blacklist {
 devnode "^sd[a]"
        devnode "^(zram|ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*"
        devnode "^hd[a-z]"
        devnode "^cciss!c[0-9]d[0-9]*"
}
blacklist_exceptions {
 wwid "360030d90466abf0660191bde985bba15"
 wwid "360030d906382c2065827918ddb6506da"
 #vendor   "DataCore"
 #product   "Virtual Disk"
              }
devices {
 device {
   vendor    "DataCore"
   product   "Virtual Disk"
   path_checker   tur
   prio     alua
   failback   10
   no_path_retry   fail

   dev_loss_tmo   infinity
   fast_io_fail_tmo  5

   rr_min_io_rq    100
   # Alternative option – See notes below
   # rr_min_io  100

   path_grouping_policy  group_by_prio
   # Alternative policy - See notes below
   # path_grouping_policy failover

    # optional - See notes below
   user_friendly_names yes
                 }
         }
EOF

systemctl reload multipathd

# final post steps (Bugs, security)
####################################
systemctl disable rdma.service
touch /.autorelabel
dracut -f 

%end

Test that you can access anonymously to your file through ftp with your browser
ftp://192.168.56.101/pub/myksfile.ks
52
Or via an ftp client

$ lftp ftp://192.168.56.101
lftp 192.168.56.101:~> cat /pub/myksfile.ks
You can now deploy your Oracle Linux server for a new database:

When you arrive on the installation screen,
22

Booting from dvd, press ESC to get the boot prompt and type
For the demo, I’m using Virtual Box VM, + 1 dvd drive for the ISO file i have downloaded from the oracle site: V100082-01.iso (oralinux7.2)

linux ks=ftp://192.168.56.101/pub/myksfile.ks

Then press ENTER as shown in this demo:
51

Here, if you don’t get RNETLINK answers: File exists, something is wrong in your network configuration.
07

At this step, if you see the green line, it’s mean you entered in anaconda and that your installation process is ongoing.
55

If you receive some Pane errors, once again, something is wrong in the network configuration. This is the hard part. Depending of the customer infrastructure, you could nee to set up ip manually.
Below 2 examples: one using a static IP configuration and the other a VLAN configuration.

static IP configuration
linux ip=192.168.56.102 netmask=255.255.255.0 gateway=192.168.56.1 servername=it.dbi-services.com ks=ftp://192.168.56.101/pub/myksfile.ks net.ifnames=0
static IP configuration with use of VLAN (VLANID=27 in this example)
linux ip=192.168.56.102 netmask=255.255.255.128 gateway=192.168.56.1 servername=it.dbi-services.com ks=ftp://192.168.56.1/myksfile.ks net.ifnames=0 vlan=VLAN27.27:eth0

Anaconda will now perform the partitioning part:
04

For the demo, I’m using a 40G disk. If you don’t give enough space, or if you have done some errors in your configuration, you will be prompt to fix the configuration issues. You would better restart the installation from the beginning.

# Partitioning and bootloader
########################################################################
# only 1 disk presented to the O.S during installation time
# net.ifnames=0 to use eth name for network devices
bootloader      --location=mbr  --append="nofb quiet splash=quiet crashkernel=auto net.ifnames=0"
firstboot       --disable
clearpart       --all          --initlabel
part /boot      --fstype xfs   --ondisk=/dev/sda --size=512
part swap       --size=2048   --ondisk=/dev/sda
part pv.01      --size=100     --ondisk=/dev/sda --grow
volgroup RHELVG pv.01
logvol /        --fstype xfs   --name=RootLV   --vgname=RHELVG --size=8196
logvol /usr     --fstype xfs   --name=UsrLV    --vgname=RHELVG --size=2048
logvol /tmp     --fstype xfs   --name=TmpLV    --vgname=RHELVG --size=2048
logvol /var     --fstype xfs   --name=VarLV    --vgname=RHELVG --size=4096
logvol /var/log/audit     --fstype xfs   --name=AuditLV    --vgname=RHELVG --size=2048
logvol /opt     --fstype xfs   --name=OptLV    --vgname=RHELVG --size=2048
logvol /home    --fstype xfs   --name=HomeLV   --vgname=RHELVG --size=2048
logvol /u01     --fstype xfs   --name=u01LV    --vgname=RHELVG --size=2048

When the partitioning part is finish, the package installation process will begin.
25

You can add personalize the packages you want install from the dvd.

# packages + RPMs
########################################################################
%packages
@base

# system components
device-mapper-multipath
kexec-tools
lvm2
e4fsprogs
sg3_utils
lsscsi
dstat
ntp
perl
postfix
bc

During the installation, you can TAB between console to get more information on what’s going on.
Console 2 permit you to type shell commands:

For the demo, I’m using 3 Ethernet cards: 2 for the bonding, 1 NAT for internet connection.
With ip a command, i can see which the interface names and IP i’m currently using during the installation process:
54
Because I set net.ifnames=0, eth will be used after rebooting for my netcard interfaces name. I will configure them in the POST installations tasks.

 bootloader      --location=mbr  --append="nofb quiet splash=quiet crashkernel=auto net.ifnames=0

Switching between Console 1 / Console 3 / Console 5 permit to see what anaconda is doing. Interesting part it the %%post message.
It means you are in the POST installations tasks.
21

Configuration files of your system can be modified.
In my demo, i will configure bonding, postfix, and multipathing and yum install oracle-database-server-12cR2-preinstall package with dependencies !
21

The script coming from the kickstart configuration file is stored in the /tmp folders. It is called /tmp/ks-script-JeYnWI.log
After reboot, you can inspect it if you like to.

 

Cet article Automating Oracle Linux Installation with Kickstart est apparu en premier sur Blog dbi services.

Password Validation in MySQL

Thu, 2018-04-05 05:05
Introduction on validate_password plugin

Since version 5.6.6 MySQL provides a new security plugins named Password Validation Plugin. The password-validation plugin aims to test passwords strength and improve security. The goal of this blog is to provide you a short overview of the functionalities provided through this plugin and illustrate these functionalities with concrete examples.

As explained into the documentation The validate_password plugin implements two capabilities:

1. The plugin checks the password against the current password policy and rejects the password if it is weak
2. The VALIDATE_PASSWORD_STRENGTH() SQL function assesses the strength of potential passwords. The function takes a password argument and returns an integer from 0 (weak) to 100 (strong).

validate_password plugin implements three level of password checking that are described below:

  • LOW – policy tests password length only.
  • MEDIUM (Default) – policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character
  • STRONG – policy adds the condition that password substrings of length 4 or longer must not match words in the dictionary file, if one has been specified.

validate_password plugin provides several checks that can be seen using the show variables command:

SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0,01 sec)

Tests with validate_password.policy=LOW

First let’s set the validate_password.policy to LOW to check which tests are done by the plugin. It should only check password length.

SET GLOBAL validate_password.policy=LOW;

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 8     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
create user 'steulet'@'localhost' identified by '1234567';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'steulet'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0,01 sec)

 

Tests with validate_password.policy=MEDIUM

MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase character, 1 uppercase character, and 1 special (nonalphanumeric) character

SET GLOBAL validate_password.policy=MEDIUM;
Query OK, 0 rows affected (0,00 sec)

SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------------------+-------------------------------------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file |  |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------------------------------------+
7 rows in set (0.00 sec)
create user 'hueber'@'localhost' identified by '12345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'hueber'@'localhost' identified by '1234567L';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'hueber'@'localhost' identified by '123456zL';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'hueber'@'localhost' identified by '12345!zL';
Query OK, 0 rows affected (0.01 sec)

 

Tests with validate_password.policy=STRONG

In order to check the validate_password.policy=STRONG I uploaded a password file used for brute force attack. You can download this file from: https://github.com/danielmiessler/SecLists/blob/master/Passwords/Most-Popular-Letter-Passes.txt

SET GLOBAL validate_password.dictionary_file='/u01/mysqldata/mysqld2/PasswordList';
Query OK, 0 rows affected (0,00 sec)
SET GLOBAL validate_password.policy=strong;
Query OK, 0 rows affected (0,00 sec)
SHOW VARIABLES LIKE 'validate_password.%';
+--------------------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------------------+-------------------------------------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | /u01/mysqldata/mysqld2/PasswordList |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | STRONG |
| validate_password.special_char_count | 1 |
+--------------------------------------+-------------------------------------+

7 rows in set (0.00 sec)

create user 'neuhaus'@'localhost' identified by 'Manager1;';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

create user 'neuhaus'@'localhost' identified by 'Password1;';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

If I decrease the validate_password.policy to medium, the plugin doesn’t check the dictionary file anymore:

SET GLOBAL validate_password.policy=medium;
Query OK, 0 rows affected (0,00 sec)

create user 'neuhaus'@'localhost' identified by 'Password1;';
Query OK, 0 rows affected (0,00 sec)
Function VALIDATE_PASSWORD_STRENGTH()

As explained above the validate_password_strength test a password and returns an integer from 0 (weak) to 100 (strong) representing the password strength.

select VALIDATE_PASSWORD_STRENGTH('abcd');
+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abcd') |
+------------------------------------+
| 25 |
+------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('password');
+----------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('password') |
+----------------------------------------+
| 50 |
+----------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('Manager1!');
+-----------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('Manager1!') |
+-----------------------------------------+
| 75 |
+-----------------------------------------+
1 row in set (0.00 sec)
select VALIDATE_PASSWORD_STRENGTH('aZbq!1)m8N');
+------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('aZbq!1)m8N') |
+------------------------------------------+
| 100 |
+------------------------------------------+
1 row in set (0.00 sec)

 

 

Cet article Password Validation in MySQL est apparu en premier sur Blog dbi services.

PgBackRest : Dedicated Backup Host

Sun, 2018-04-01 11:59

In previous blogs (blog1 and blog2) we saw a configuration of PgBackRest where the tool is installed in the same server that also hosts the PostgreSQL cluster. This configuration is fine if we have a single database server. But in the case that we have many database servers, it is more suitable to have a dedicated server for backups. This will also separate the backups and WAL archive from databases server
In this article we will see how to configure PgBackRest in an environment with 2 databases servers.
We present below the configuration we will use. We suppose that PgBackRest is already installed on both servers.
pgservertools: dedicated backup hosts
pgserver1: database server
pgserver2: database server
schema
As we can see we have two clusters running on each database server (pgserver1 and pgserver2). Note that we can have as many clusters we want on each server.
pgserver1 : cluster1

postgres=# show data_directory ;
  data_directory
-------------------
 /u90/pgdata/pg101
(1 row)

postgres=# show port;
 port
------
 5432
(1 row)

postgres=#

pgserver1 : cluster2

postgres=# show data_directory ;
   data_directory
--------------------
 /u90/pgdata2/pg101
(1 row)

postgres=# show port;
 port
------
 5433
(1 row)

postgres=#

pgserver2 : cluster1

postgres=# show data_directory ;
  data_directory
-------------------
 /u90/pgdata/pg101
(1 row)

postgres=# show port;
 port
------
 5432
(1 row)

postgres=#

pgserver2 : cluster2

postgres=# show data_directory ;
   data_directory
--------------------
 /u90/pgdata2/pg101
(1 row)

postgres=# show port;
 port
------
 5433
(1 row)

postgres=#

Now let’s create on pgservertools a dedicated user who will own the repository. It is not recommended to use user postgres. We will use backupowner as user.

[root@pgservertools ~]# useradd -m backupowner

As PgBackRest requires communication between the hosts without password, we have to configure ssh keys between user backupowner (on pgservertools) and users postgres (on pgserver1 and pgserver2).
On pgservertools, let’s generate keys for user backupowner

[backupowner@pgservertools ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/backupowner/.ssh/id_rsa): y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in y.
Your public key has been saved in y.pub.
The key fingerprint is:
SHA256:drIaCe1aMZSPOmQkNdYfn5WLm/iE4sJS2YKGj6EF38M backupowner@pgservertools.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|    +.       .   |
|   o ....   o    |
|  . . o. o + .   |
|.  o o o. + .    |
| + ++o= So.o     |
|o =oE+o=o++      |
|.* oo+=..o       |
|o o o+.o  .      |
|   ....          |
+----[SHA256]-----+
[backupowner@pgservertools ~]$

Do the same on pgserver1 for user postgres

[postgres@pgserver1 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Created directory '/home/postgres/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
15:cf:78:47:ef:e5:ab:29:b7:25:59:03:de:de:88:be postgres@pgserver1.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
|          .   .  |
|           = . . |
|          o +.. o|
|         . ...oo.|
|        S    . +o|
|             .+.+|
|            .o.+.|
|           o .=  |
|            E=.  |
+-----------------+
[postgres@pgserver1 ~]$

And on pgserver2 for user postgres

[postgres@pgserver2 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Created directory '/home/postgres/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:ZKov+TdohBLvaO/pSYIl+Tk6iWfUMx1Lqixb8ByaZzI postgres@pgserver2.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|                 |
|        o        |
| ..   o+         |
|+ o+ =.oS        |
| @ooB.+          |
|E+@=o= .         |
|=BB+++o o        |
|oB +*+o. .       |
+----[SHA256]-----+
[postgres@pgserver2 ~]$

Now let’s exchange keys between servers.

[backupowner@pgservertools .ssh]$ ssh-copy-id postgres@pgserver1
[backupowner@pgservertools .ssh]$ ssh-copy-id postgres@pgserver2
[postgres@pgserver1 .ssh]$ ssh-copy-id backupowner@pgservertools
[postgres@pgserver2 .ssh]$ ssh-copy-id backupowner@pgservertools

And then let’s test connection

[backupowner@pgservertools ~]$ ssh postgres@pgserver1 date
Tue Feb 20 11:42:06 CET 2018
[backupowner@pgservertools ~]$ ssh postgres@pgserver2 date
Tue Feb 20 11:42:10 CET 2018
 [postgres@pgserver1 .ssh]$ ssh backupowner@pgservertools date
Tue Feb 20 11:42:54 CET 2018
[postgres@pgserver2 .ssh]$ ssh backupowner@pgservertools date
Tue Feb 20 11:43:23 CET 2018

Ok now that everything is fine for trusted connections, let’s configure the pgbackrest.conf files. We present below contents of our files on the 3 servers. We can notice that encryption is used (see previous blogs)

pgservertools

[root@pgservertools ~]# cat /etc/pgbackrest.conf
[pgserver1pgdata]
db1-host=pgserver1
db1-path=/u90/pgdata/pg101
db1-user=postgres

[pgserver1pgdata2]
db1-host=pgserver1
db1-path=/u90/pgdata2/pg101
db1-user=postgres
db1-port=5433
[pgserver2pgdata]
db1-host=pgserver2
db1-path=/u90/pgdata/pg101
db1-user=postgres

[pgserver2pgdata2]
db1-host=pgserver2
db1-path=/u90/pgdata2/pg101
db1-user=postgres
db1-port=5433

[global]
repo-path=/u90/backup
retention-full=2
repo-cipher-pass=dkN28km/CltmsbzkDdKahmwXctr0GJd/9F8tegBXBWASULhVatNXauMMKWUslax1
repo-cipher-type=aes-256-cbc [root@pgservertools etc]#

pgserver1

[postgres@pgserver1 ~]$ cat /etc/pgbackrest.conf
[pgserver1pgdata]
db1-path=/u90/pgdata/pg101
db1-socket-path=/tmp

[pgserver1pgdata2]
db1-path=/u90/pgdata2/pg101
db1-port=5433
db1-socket-path=/tmp

[global]
backup-host=pgservertools
backup-user=backupowner
log-level-file=detail
[postgres@pgserver1 ~]$

pgserver2

[root@pgserver2 postgres]# cat /etc/pgbackrest.conf
[pgserver1pgdata]
db1-path=/u90/pgdata/pg101
db1-socket-path=/tmp

[pgserver1pgdata2]
db1-path=/u90/pgdata2/pg101
db1-port=5433
db1-socket-path=/tmp

[global]
backup-host=pgservertools
backup-user=backupowner
log-level-file=detail
[root@pgserver2 postgres]#

The next step is to create the stanzas
pgserver1: 2 stanzas pgserver1pgdata and pgserver1pgdata2

[backupowner@pgservertools ~]$  pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 stanza-create
2018-02-21 15:21:42.815 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver1 --db1-path=/u90/pgdata/pg101 --db1-port=5432 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver1pgdata
2018-02-21 15:21:46.881 P00   INFO: stanza-create command end: completed successfully

[backupowner@pgservertools ~]$  pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 stanza-create
2018-02-21 15:23:39.116 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver1 --db1-path=/u90/pgdata2/pg101 --db1-port=5433 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver1pgdata2
2018-02-21 15:23:41.360 P00   INFO: stanza-create command end: completed successfully
[backupowner@pgservertools ~]$

pgserver2: 2 stanzas pgserver2pgdata and pgserver2pgdata2

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 stanza-create
2018-02-27 13:22:47.710 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver2 --db1-path=/u90/pgdata/pg101 --db1-port=5432 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver2pgdata
2018-02-27 13:22:49.624 P00   INFO: stanza-create command end: completed successfully

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 stanza-create
2018-02-27 13:23:01.323 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver2 --db1-path=/u90/pgdata2/pg101 --db1-port=5433 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver2pgdata2
2018-02-27 13:23:03.233 P00   INFO: stanza-create command end: completed successfully
[backupowner@pgservertools pgserver1pgdata]$

And now we can do a backup of any our cluster using the corresponding stanza

pgserver1pgdata

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 backup

pgserver1pgdata2

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 backup

pgserver2pgdata

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 backup

pgserver2pgdata2

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 backup

An example of getting info about backup

[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail  info
stanza: pgserver1pgdata
    status: ok

    db (current)
        wal archive min/max (10-1): 000000010000000000000018 / 000000010000000000000018

        full backup: 20180221-155755F
            timestamp start/stop: 2018-02-27 11:45:51 / 2018-02-27 11:46:18
            wal start/stop: 000000010000000000000018 / 000000010000000000000018
            database size: 30MB, backup size: 30MB
            repository size: 3.5MB, repository backup size: 3.5MB
[backupowner@pgservertools pgserver1pgdata]$

Conclusion
In this blog we have seen how PgbackRest can be be used in an environment with multiple database servers.

 

Cet article PgBackRest : Dedicated Backup Host est apparu en premier sur Blog dbi services.

After IoT, IoP makes its way to the database

Sat, 2018-03-31 21:00

At each new Oracle version, I like to check what’s new, not only from the documentation, but also from exposed internals. I look (and sometimes diff) on catalog views definitions, undocumented parameters, and even the new C functions in the libraries. At last Oak Table World, I was intrigued by this V$SQLFN_METADATA view explained by Vit Spinka when digging into the internals of how execution plans are stored. This view has entries with all SQL functions, and a VERSION column going from ‘V6 Oracle’ to ‘V11R1 Oracle’. The lastest functions has an ‘INVALID’ entry and we also can see some functions with ‘SQL/DS’. Well, now that we have Oracle 18c on the Oracle Cloud, I came back to this view to see if anything is new, listing the highest FUNC_ID at the top and the first row attired my attention:


SQL> select * from V$SQLFN_METADATA order by 1 desc fetch first 10 rows only;
 
FUNC_ID NAME MINARGS MAXARGS DATATYPE VERSION ANALYTIC AGGREGATE OFFLOADABLE DISP_TYPE USAGE DESCR CON_ID
------- ---- ------- ------- -------- ---------- -------- --------- ----------- --------- ----- ----- ------
1148 TO_DOG_YEAR 1 4 NUMERIC V13 Oracle NO NO YES NORMAL TO_DOG_YEAR 0
1147 JSON_MERGEPATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_MERGEPATCH 0
1146 JSON_PATCH 4 0 UNKNOWN INVALID NO NO NO NORMAL JSON_PATCH 0
1145 ROUND_TIES_TO_EVEN 1 2 NUMERIC INVALID NO NO YES NORMAL ROUND_TIES_TO_EVEN 0
1144 CON_ID_TO_CON_NAME 1 0 UNKNOWN INVALID NO NO NO NORMAL CON_ID_TO_CON_NAME 0
1143 TIMESTAMP_TO_NUMBER 1 1 UNKNOWN INVALID NO NO YES NORMAL TIMESTAMP_TO_NUMBER 0
1142 TO_UTC_TIMESTAMP_TZ 1 0 UNKNOWN INVALID NO NO YES NORMAL TO_UTC_TIMESTAMP_TZ 0
1141 OPTSYSAPPROXRANK 1 0 UNKNOWN INVALID NO NO NO NORMAL Internal evaluation function for multiple approx_rank's 0
1140 APPROX_RANK 1 1 NUMERIC INVALID NO YES NO NORMAL APPROX_RANK 0
1139 APPROX_SUM 1 2 NUMERIC INVALID NO YES NO NORMAL APPROX_SUM 0

Because those functions are SQL functions, I searched this ‘TO_DOG_YEAR’ on Google to see whether a new ANSI SQL function was implemented. But finally came upon something I didn’t expect: Dog Years Calculator. The trends in databases are really going crazy these times. All focus is on developers. XML, JSON, Docker… and now a function to calculate your age in dog years.
But afterall, it makes sense. IoT (not ‘Index Organized Table’ but ‘Internet Of Things’) is coming with sensors everywhere. And it is not only ‘things’ but it comes to living beings. I have read recently about ‘Internet of Pets’ where collars equipped with sensors detect where your domestic animal go and when he is hungry.

Let’s test it. Tomorrow, my elder kid has his 13th birthday. Now Oracle can tell me that he will be 65 in dog years:

SQL> select to_dog_year(date'2005-04-02') from dual;
 
TO_DOG_YEAR(DATE'2005-04-02')
-----------------------------
65

Yes, here I learn that the calculation is a bit more complex than just multiplying by 7. Of course, adding a SQL standard function would not make sense if it was just a multiplication.

But it seems to be even more complex. I searched for the C functions behind this one:

[oracle@CLOUD18C ~]$ nm /u01/app/oracle/product/18.0.0/dbhome_1/bin/oracle | grep -iE "dog.*year"
000000001452e073 r KNCLG_TODOGYEAR
0000000003ffcf40 T LdiJDaysDogYear
000000000f3170c0 T LdiJulianDogYear
000000000f316fc0 T LdiJulianDogYeararr
000000000f3170f0 t LdiJulianDogYeari
000000000f606e10 T OCIPConvertDateDogYearTime
000000000ebf2380 t qerxjConvertDogYearTime
0000000010de19e0 t qjsngConvStructDogYear
0000000010de0320 T qjsngNumberDogYearDty
0000000010de06f0 T sageStringDogYearDty
0000000010de7110 T sageplsDogYear
000000000bc5cd80 t sagerwAddDogYearTime
0000000010bad3c0 T qmxtgrConvSaxDogYear
0000000010bad400 T qmxtgrConvSaxDogYear_internal
00000000025ae090 T qosDateTimeDogYear
0000000004f22b60 T xsCHDogYeartime
000000000438c230 T nlsBreedDogYear
000000000438bb50 t nlsBreedDogYearCmn
000000000438c060 T nlsBreedDogYearTime
000000000438bc50 T nlsBreedDogYear
00000000044d1da0 T xvopAddDTDurDogYear
00000000044d1ac0 T xvopAddYMDurDogYear

Those ‘nlsBreed’ functions ring a bell and I checked if there are new values in V$NLS_VALID_VALUES

SQL> select distinct parameter from V$NLS_VALID_VALUES;
 
PARAMETER
----------------------------------------------------------------
TERRITORY
CHARACTERSET
BREED
LANGUAGE
SORT

That ‘BREED’ is a new one, with a lot of interesting values:

CaptureBREED

And here is my example using this new NLS parameter.

SQL> select to_dog_year(date'2005-04-02','','NLS_BREED=Saint Bernard') from dual;
 
TO_DOG_YEAR(DATE'2005-04-02',
----------------------------
96

Note that I’ve no idea about the second parameter, I had to put a ‘null’ for it to be able to mention the NLS one, or I got a ‘ORA-00909: invalid number of arguments’.

I have to say that, for a DBA focused on the core database functions, it is hard to understand that new features go on things like this TO_DOG_YEAR function. But being realistic, it is clear that the budget for new features go into the new direction: all for developers, big data, IoT… Of course we can write those functions in PL/SQL or maybe one day with JavaScript thanks to the Multi-Lingual Engine currently in beta. But IoT is also about performance, and a standard function avoids context switches.

 

Cet article After IoT, IoP makes its way to the database est apparu en premier sur Blog dbi services.

sqlplus and its column output

Sat, 2018-03-31 20:12

During tuning or normal DBA activities one of the most annoying things is sqlplus with its default column output when running queries. I.e. even after setting a linesize of 1000 and a pagesize of 1000 the output may look as follows in your preferred terminal emulation (putty here):

no_terminator

Most people address this by using other tools like sqldeveloper (or sqlcl with sqlformat ansiconsole). However, a lot of people still use sqlplus.

How can you address the output-format-issue with sqlplus?
Actually you can format columns of course and specify only the columns you would like to see (instead of “select *”):


SQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
 
SCHEMANAME OSUSER
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
CBLEILE oracle
 
SQL> column schemaname format a32
SQL> column osuser format a32
SQL> select schemaname, osuser from v$session where sid=(select sid from v$mystat where rownum=1);
 
SCHEMANAME OSUSER
-------------------------------- --------------------------------
CBLEILE oracle

But that’s annoying if you have to do it for a couple of columns for every sql-statement you interactively type in.

The better alternative is to use a terminal emulation, which allows horizontal scrolling. Tanel Poder describes it in his “AOT Preparation Session” on Youtube: https://www.youtube.com/watch?v=tC81PMO7ODw.
Interestingly horizontal scrolling is available on Windows for ages. I.e. using cmd.exe or the Powershell you can define a “Screen Buffer Size” for the “Width” of the Window (in Properties -> Layout). Best is to set it under “Defaults” so that it’s available every time you open a cmd.exe or Powershell window.

But what to use on e.g. Linux? Most terminal emulation software does not allow horizontal scrolling. Actually there are only very few terminal emulation products available, which allow it. One of the freely available products is terminator ( https://github.com/software-jessies-org/jessies/wiki/Terminator ). After downloading the rpm on my Oracle Enterprise Linux server I installed it as follows:


[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
error: Failed dependencies:
/usr/bin/ruby is needed by org.jessies.terminator-27.171.7083-2.x86_64
[root@localhost terminator]# yum install ruby
[root@localhost terminator]# rpm -i org.jessies.terminator.x86_64.rpm
[root@localhost terminator]# terminator

The prereqs for terminator are an installed Java Runtime Environment (JRE -> you may use the one provided within your ORACLE_HOME) and Ruby (as you can see above).
I started terminator once as root to install the necessary global terminfo. After that I can use it as e.g. the oracle-user:


[oracle@localhost ~]$ terminator &

There are no wrapped lines due to the terminal size anymore:

terminator1

And you can scroll to the right:

terminator2

Running sqlcl with sqlformat set to ansiconsole the Terminator terminal emulation also helps for queries with many columns, which do not fit on the screen:

terminator_sqlcl1

Scrolled to the right:

terminator_sqlcl2

Besides the formatting issue, it’s of course recommended to use the utility rlwrap (readline wrapper) with sqlplus on Linux for command line history (get previous command lines with the up and down key) and easy command line editing. Actually rlwrap is much better than the history available in sqlplus of 12.2. I do recommend watching the youtube video from Tanel Poder mentioned above, because he also shows that rlwrap can be configured to provide command completion (for Oracle PLSQL-objects and reserved words) as well.

When installing the dbi services DMK Management Kit ( http://www.dbi-services.com/dmk ) on Linux then rlwrap is of course included.

So sqlcl is cool, but sqlplus also still has its right to exist ;-)

 

Cet article sqlplus and its column output est apparu en premier sur Blog dbi services.

Docker: efficiently building images for large software

Sat, 2018-03-31 11:30

I see increasing demand to build a Docker image for the Oracle Database. But the installation process for Oracle does not really fit the Docker way to install by layers: you need to unzip the distribution, install from it to the Oracle Home, remove the things that are not needed, strop the binaries,… Before addressing those specific issues, here are the little tests I’ve done to show how the build layers increase the size of the image.

I’m starting with an empty docker repository on XFS filesystem:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 33M 80G 1% /var/lib/docker

add, copy, rename and append

For the example, I create a 100MB file in the context:

[root@VM121 docker]# mkdir -p /var/tmp/demo
[root@VM121 docker]# dd if=/dev/urandom of=/var/tmp/demo/file0.100M count=100 bs=1M

Here his my docker file:

FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M

The 1st step starts with an alpine image
The 2nd step sets the working directory
The 3rd step adds a 100M file from the context
The 4th step copies the file, so that we have 200M in two files
The 5th step removes the previous file, so that we have 100M in one file
The 6th step renames the file, staying with only one 100M file
The 7th step appends 100M to the file, leaving 200M in one file

Here is the build with default option:

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo

The context, my 100M files is send first:

Sending build context to Docker daemon 104.9MB

And here are my 7 steps:

Step 1/7 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/7 : WORKDIR /var/tmp
Removing intermediate container 93d1b5f21bb9
---> 131b3e6f34e7
Step 3/7 : ADD file0.100M .
---> 22ca0b2f6424
Step 4/7 : RUN cp file0.100M file1.100M
---> Running in b4b1b9c7e29b
Removing intermediate container b4b1b9c7e29b
---> 8c7290a5c87e
Step 5/7 : RUN rm file0.100M
---> Running in 606e2c73d456
Removing intermediate container 606e2c73d456
---> 5287e66b019c
Step 6/7 : RUN mv file1.100M file2.100M
---> Running in 10a9b379150e
Removing intermediate container 10a9b379150e
---> f508f426f70e
Step 7/7 : RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Running in 9dcf6d80642c
100+0 records in
100+0 records out
Removing intermediate container 9dcf6d80642c
---> f98304641c54
Successfully built f98304641c54
Successfully tagged franck/demo:latest

So, what’s the size of my docker repository after my image with this 200M file?

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 538M 80G 1% /var/lib/docker

I have more than 500MB here.

Actually, besides the alpine image downloaded, which is only 4MB, the image I have build is 538MB:

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest f98304641c54 Less than a second ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

We can better understand this size by looking at intermediate images:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest f98304641c54 1 second ago 528MB
<none> <none> f508f426f70e 27 seconds ago 319MB
<none> <none> 5287e66b019c 36 seconds ago 214MB
<none> <none> 8c7290a5c87e 37 seconds ago 214MB
<none> <none> 22ca0b2f6424 42 seconds ago 109MB
<none> <none> 131b3e6f34e7 47 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

The first one, ’22ca0b2f6424′ is from the step 3 which added the 100MB file
The second one ‘8c7290a5c87e’ is from the 4th step which copied the file, bringing the image to 200MB
The third one ‘5287e66b019c’ is from the 5th step which removed the file. I didn’t increase the size but didn’t remove anything either.
The fourth one ‘f508f426f70e’ is from the 6th step which renamed the file. But this, for docker, is like copying to a new layer and that adds 100MB
Finally, the 7th step appended only 100MB, but this finally resulted to copy the full 200MB file to the new layer

We can see all those operations, and size added at each step, from the image history:

[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
f98304641c54 1 second ago /bin/sh -c dd if=/dev/urandom of=file2.100M … 210MB
f508f426f70e 27 seconds ago /bin/sh -c mv file1.100M file2.100M 105MB
5287e66b019c 36 seconds ago /bin/sh -c rm file0.100M 0B
8c7290a5c87e 37 seconds ago /bin/sh -c cp file0.100M file1.100M 105MB
22ca0b2f6424 42 seconds ago /bin/sh -c #(nop) ADD file:339435a18aeeb1b69… 105MB
131b3e6f34e7 47 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

All in one RUN

One workaround is to run everything in the same layer. Personally, I don’t like it because I don’t get the point of using a Dockerfile for just running one script.
So, here is the Dockerfile with only one RUN command:

FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M \
&& rm file0.100M \
&& mv file1.100M file2.100M \
&& dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M

The build is similar except that there are fewer steps:

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo
Sending build context to Docker daemon 104.9MB
Step 1/4 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/4 : WORKDIR /var/tmp
Removing intermediate container 707644c15547
---> d4528b28c85e
Step 3/4 : ADD file0.100M .
---> e26215766e75
Step 4/4 : RUN cp file0.100M file1.100M && rm file0.100M && mv file1.100M file2.100M && dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Running in 49c2774851f4
100+0 records in
100+0 records out
Removing intermediate container 49c2774851f4
---> df614ac1b6b3
Successfully built df614ac1b6b3
Successfully tagged franck/demo:latest

This leaves us with a smaller space usage::

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 340M 80G 1% /var/lib/docker

The image is smaller, but still larger than the final state (a 300MB image for only one 200MB file):

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest df614ac1b6b3 Less than a second ago 319MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

This is because we have grouped the RUN steps, but the ADD has its own layer, adding a file that is removed later:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest df614ac1b6b3 Less than a second ago 319MB
<none> <none> e26215766e75 20 seconds ago 109MB
<none> <none> d4528b28c85e 22 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
df614ac1b6b3 Less than a second ago /bin/sh -c cp file0.100M file1.100M … 210MB
e26215766e75 20 seconds ago /bin/sh -c #(nop) ADD file:fe0262a4b800bf66d… 105MB
d4528b28c85e 22 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

This is the kind of issue we have when building an Oracle Database image. We need to ADD the zip file for the database distribution, and the latest bundle patch. It is removed later but still takes space on the image. Note that one workaround to avoid the ADD layer can be to get the files from an NFS or HTTP server with wget or curl in a RUN layer rather than an ADD one. There’s an example on Stefan Oehrli blog post.

–squash

With the latest versions of docker, there’s an easy way to flatten all those intermediary images at the end.
Here I’ve 18.03 and enabled experimental features:

[root@VM121 docker]# docker info
Containers: 0
Running: 0
Paused: 0
Stopped: 0
Images: 8
Server Version: 18.03.0-ce
Storage Driver: overlay2
Backing Filesystem: xfs
...
 
[root@VM121 docker]# cat /etc/docker/daemon.json
{
"experimental": true
}

I start with the same as before but just add –squash to the build command

[root@VM121 docker]# docker image build --squash -t franck/demo /var/tmp/demo

The output is similar but the image is an additional one, reduced down to the size of my final state (with one 200MB file):

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 Less than a second ago 214MB
<none> <none> c3058e598b0a 3 seconds ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

The intermediate image list shows that all was done as without ‘–squash’ but with an additional set which reduced the size:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 Less than a second ago 214MB
<none> <none> c3058e598b0a 3 seconds ago 528MB
<none> <none> 1f14d93a592e 23 seconds ago 319MB
<none> <none> 7563d40b650b 27 seconds ago 214MB
<none> <none> 8ed15a5059bd 28 seconds ago 214MB
<none> <none> 24b11b9026ce 31 seconds ago 109MB
<none> <none> 382bb71a6a4a 33 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

This step is visible in the image history as a ‘merge’ step:

[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
2ab439a723c4 Less than a second ago 210MB merge sha256:c3058e598b0a30c606c1bfae7114957bbc62fca85d6a70c2aff4473726431394 to sha256:3fd9065eaf02feaf94d68376da52541925650b81698c53c6824d92ff63f98353
<missing> 3 seconds ago /bin/sh -c dd if=/dev/urandom of=file2.100M … 0B
<missing> 23 seconds ago /bin/sh -c mv file1.100M file2.100M 0B
<missing> 27 seconds ago /bin/sh -c rm file0.100M 0B
<missing> 28 seconds ago /bin/sh -c cp file0.100M file1.100M 0B
<missing> 31 seconds ago /bin/sh -c #(nop) ADD file:14cef588b48ffbbf1… 0B
<missing> 33 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
<missing> 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

However, even if I have a smaller final image, my filesystem usage is even larger with this additional 210MB:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 739M 80G 1% /var/lib/docker

Let’s prune it to get rid of those intermediate images:

[root@VM121 docker]# docker image prune -f
Deleted Images:
deleted: sha256:c3058e598b0a30c606c1bfae7114957bbc62fca85d6a70c2aff4473726431394
deleted: sha256:37ed4826d70def1978f9dc0ddf42618d951f65a79ce30767ac3a5037d514f8af
deleted: sha256:1f14d93a592eb49a210ed73bf65e6886fcec332786d54b55d6b0e16fb8a8beda
deleted: sha256:c65cf4c70aed04e9b57e7a2a4fa454d3c63f43c32af251d8c86f6f85f44b1757
deleted: sha256:7563d40b650b2126866e8072b8df92d5d7516d86b25a2f6f99aa101bb47835ba
deleted: sha256:31ee5456431e903cfd384b1cd7ccb7918d203dc73a131d4ff0b9e6517f0d51cd
deleted: sha256:8ed15a5059bd4c0c4ecb78ad77ed75da143b06923d8a9a9a67268c62257b6534
deleted: sha256:6be91d85dec6e1bda6f1c0d565e98dbf928b4ea139bf9cb666455e77a2d8f0d9
deleted: sha256:24b11b9026ce738a78ce3f7b8b5d86ba3fdeb15523a30a7c22fa1e3712ae679a
deleted: sha256:c0984945970276621780a7888adfde9c6e6ca475c42af6b7c54f664ad86f9c9f
deleted: sha256:382bb71a6a4a7ddec86faa76bb86ea0c1a764e5326ad5ef68ce1a6110ae45754
 
Total reclaimed space: 524.3MB

Now having only the squashed image:

[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 2ab439a723c4 32 minutes ago 214MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 237M 80G 1% /var/lib/docker

multi-stage build

Finally, you can do something similar to an intermediate squash using multi-stage build.

Here is my Dockerfile:

FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
 
FROM alpine:latest
WORKDIR /var/tmp
COPY --from=staging /var/tmp .

With multi-stage build, we can start the second stage from a different image, and add more steps, but here I just start with the same alpine image and copy the final layer of the previous build.

We see something very similar to the –squash one:

[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 55f329385f8c Less than a second ago 214MB
<none> <none> fd26a00db784 8 seconds ago 528MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# docker image ls -a
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 55f329385f8c 1 second ago 214MB
<none> <none> fd26a00db784 9 seconds ago 528MB
<none> <none> 9bf5be367b63 32 seconds ago 319MB
<none> <none> 531d78833ba8 35 seconds ago 214MB
<none> <none> 05dd68114743 36 seconds ago 214MB
<none> <none> b9e5215a9fc8 39 seconds ago 109MB
<none> <none> ab332f486793 41 seconds ago 4.15MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

The history of the last stage shows the copy of 210MB from the previous one:

[root@VM121 docker]# docker image history franck/demo
IMAGE CREATED CREATED BY SIZE COMMENT
55f329385f8c 1 second ago /bin/sh -c #(nop) COPY dir:2b66b5c36eff5b51f… 210MB
ab332f486793 41 seconds ago /bin/sh -c #(nop) WORKDIR /var/tmp 0B
3fd9065eaf02 2 months ago /bin/sh -c #(nop) CMD ["/bin/sh"] 0B
<missing> 2 months ago /bin/sh -c #(nop) ADD file:093f0723fa46f6cdb… 4.15MB

The usage of filesystem is similar to the –squash one. Even if we reduced the final image, all the intermediate states had to be stored:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 737M 80G 1% /var/lib/docker

That looks good, if you accept to use a large intermediate space while building the image, which gives you the possibility to debug without re-running from the beginning, thanks to the layers in cache. However, you have still the inefficiency that each time you try the build, the context will be sent again even when not needed. And that is long with a 3GB .zip in the case of Oracle Database installation. Unfortunately, if you add the file to the .dockerignore once you know you have the ADD steps in cache, the next build attempt will not use the caches anymore. I would love to see a per-stage .dockerignore file for multi-stage builds. Or simply have docker realize that some files in the context will not be needed by the COPY or ADD that are not in cache yet.

Sending the whole context at each build attempt, when debugging your Dockerfile, is not efficient at all and looks like punch-card time compilation where people sent the cards to be compiled during the night. One syntax error on the first line and you go for another day.

One solution is to have all the required files in an NFS or HTTPd server and get them with ADD from the URL as mentioned earlier.

Multi-stage with multi-contexts

Another solution is to put all COPY or ADD from context in one Dockerfile to build the image containing all required files, and then build your image from it (and squash it at the end).

Here is my first Dockerfile, just adding the files from the context:

[root@VM121 docker]# ls /var/tmp/demo
Dockerfile file0.100M nocontext
[root@VM121 docker]# cat /var/tmp/demo/Dockerfile
FROM alpine:latest as staging
WORKDIR /var/tmp
ADD file0.100M .

I build this ‘staging’ image:

[root@VM121 docker]# docker image build -t franck/stage0 /var/tmp/demo
Sending build context to Docker daemon 104.9MB
Step 1/3 : FROM alpine:latest as staging
latest: Pulling from library/alpine
ff3a5c916c92: Pull complete
Digest: sha256:7df6db5aa61ae9480f52f0b3a06a140ab98d427f86d8d5de0bedab9b8df6b1c0
Status: Downloaded newer image for alpine:latest
---> 3fd9065eaf02
Step 2/3 : WORKDIR /var/tmp
Removing intermediate container 0eeed8e0cfd2
---> a5db3b29c8e1
Step 3/3 : ADD file0.100M .
---> 2a34e1e981be
Successfully built 2a34e1e981be
Successfully tagged franck/stage0:latest

This one is the minimal one:

[root@VM121 docker]# docker image ls
+ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/stage0 latest 2a34e1e981be Less than a second ago 109MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB
 
[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 139M 80G 1% /var/lib/docker

Now, I don’t need to send this context anymore during further development of my Dockerfile.
I’ve added the following steps to a Dockerfile in another directory:

[root@VM121 docker]# ls /var/tmp/demo/nocontext/
Dockerfile
[root@VM121 docker]# cat /var/tmp/demo/nocontext/Dockerfile
FROM franck/stage0 as stage1
WORKDIR /var/tmp
RUN cp file0.100M file1.100M
RUN rm file0.100M
RUN mv file1.100M file2.100M
RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
FROM alpine:latest
WORKDIR /var/tmp

Here is the build, using multi-stage to get a squashed final image (you can also use –squash)

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo/nocontext
 
Sending build context to Docker daemon 2.048kB
Step 1/9 : FROM franck/stage0 as stage1
---> 2a34e1e981be
Step 2/9 : WORKDIR /var/tmp
Removing intermediate container eabf57a8de05
...
Successfully built 82478bfa260d
Successfully tagged franck/demo:latest

At that point, there’s no advantage on space used as I keep all layers for easy Dockerfile development:

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 738M 80G 1% /var/lib/docker
 
[root@VM121 docker]# docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
franck/demo latest 82478bfa260d About a minute ago 214MB
<none> <none> 5772ad68d208 About a minute ago 528MB
franck/stage0 latest 2a34e1e981be About a minute ago 109MB
alpine latest 3fd9065eaf02 2 months ago 4.15MB

But now, if I want to add an additional step:

[root@VM121 docker]# cat >> /var/tmp/demo/nocontext/Dockerfile <<< 'RUN chmod a+x /var/tmp'

I can re-build quickly, using cached layers, and without the need to send the context again:

[root@VM121 docker]# docker image build -t franck/demo /var/tmp/demo/nocontext
Sending build context to Docker daemon 2.048kB
Step 1/10 : FROM franck/stage0 as stage1
---> 2a34e1e981be
Step 2/10 : WORKDIR /var/tmp
---> Using cache
---> fa562926cc2b
Step 3/10 : RUN cp file0.100M file1.100M
---> Using cache
---> 31ac716f4d61
Step 4/10 : RUN rm file0.100M
---> Using cache
---> d7392cf51ad9
Step 5/10 : RUN mv file1.100M file2.100M
---> Using cache
---> 4854e503885b
Step 6/10 : RUN dd if=/dev/urandom of=file2.100M seek=100 count=100 bs=1M
---> Using cache
---> 5772ad68d208
Step 7/10 : FROM alpine:latest
---> 3fd9065eaf02
Step 8/10 : WORKDIR /var/tmp
---> Using cache
---> a5db3b29c8e1
Step 9/10 : COPY --from=stage1 /var/tmp .
---> Using cache
---> 82478bfa260d
Step 10/10 : RUN chmod a+x /var/tmp
---> 4a69ee40a938
Successfully built 4a69ee40a938
Successfully tagged franck/demo:latest

Once I’m ok with my final image, I can remove the intermediate ones:

[root@VM121 docker]# docker image prune -f
Deleted Images:
deleted: sha256:5772ad68d20841197d1424f7c64edd21704e4c7b470acb2193de51ae8741385d
deleted: sha256:bab572d749684d126625a74be4f01cc738742f9c112a940391e3533e61dd55b9
deleted: sha256:4854e503885b4057809fe2867a743ae7898e3e06b329229519fdb5c9d8b10ac1
deleted: sha256:de4acb90433c30d6a21cc3b4483adbd403d8051f3c7c31e6bc095a304606355a
deleted: sha256:d7392cf51ad99d5d0b7a1a18d8136905c87bc738a5bc94dec03e92f5385bf9c8
deleted: sha256:f037e7f973f4265099402534cd7ba409f35272701166d59a1be8e5e39508b07c
deleted: sha256:31ac716f4d61f0048a75b8de6f18757970cf0670a0a3d711e4386bf098b32041
deleted: sha256:2dccb363c5beb4daf45586383df6454b198f824d52676f70318444c346c0fe9a
deleted: sha256:fa562926cc2b3cb56400e1068984bb4048f56713a3cf6dcfa3cf6d945023ebc4
 
Total reclaimed space: 419.4MB

And the staging one:

[root@VM121 docker]# docker image rm franck/stage0
Untagged: franck/stage0:latest
Deleted: sha256:2a34e1e981be9154c31c5ee7eb942cc121267f4416b6fe502ab93f2dceafd98c
Deleted: sha256:b996a1bdc829167f16dcbe58b717284764470661c3116a6352f15012e1dff07c

Finally, I optimized the developement of the Dockerfile and finished with the minimal size.

[root@VM121 docker]# df -hT /var/lib/docker
Filesystem Type Size Used Avail Use% Mounted on
/dev/sdc xfs 80G 237M 80G 1% /var/lib/docker

So what?

I’m always surprised by the lack of efficiency when building an image with a Dockerfile. Any serious application deployment involves several intermediate files and the way docker build is layered inflates the size and the time required. Efficient layering and snapshotting work at block level. Here, at file level, any byte of data modified in a file, even metadata such as the file name, is a whole file copy. But for common applications, the installation steps are not as simple adding new files. You may have files appended, object files added to libraries, then compiled, the stripped…

In this post, I tested some recent features, such as multi-stage build and the experimental –squash, as well as a simple manual multi-stage build. Of course, you can do everything in the same layers, and even not use Dockerfiles at all, but then why using Docker? There’s also the Packer approach that I’ve not tested yet. However, I like the Docker approach, but only when used correctly. Deploying an application, like Oracle Database, should use the layered build in the following way: additional steps for new options or new updates. This means that the files must be built elsewhere, in a staging container, and added in one step. And to be efficient, the context should be sent only when needed: when a non-cached ADD or COPY requires it.

 

Cet article Docker: efficiently building images for large software est apparu en premier sur Blog dbi services.

Docker-CE on Oracle Enterprise Linux 7

Thu, 2018-03-29 15:32

Here is how I install the latest Docker version on Oracle Linux 7. You find several blog posts about it which all install ‘docker-engine’. But things move fast in this agile world and docker package name has changed. The Community Edition is now ‘docker-ce’ and you want this one to run the latest version.

I’m on OEL 7.4 but should also wotj on RHEL 7:
[root@VM188 yum]# cat /etc/oracle-release
Oracle Linux Server release 7.4

docker-engine

If you enable [ol7_addons] you can install ‘docker-engine:’

# yum-config-manager --enable ol7_addons
# yum info docker-engine
Loaded plugins: ulninfo
Available Packages
Name : docker-engine
Arch : x86_64
Version : 17.06.2.ol
Release : 1.0.1.el7
Size : 21 M
Repo : ol7_addons/x86_64
Summary : The open-source application container engine
URL : https://dockerproject.org
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.

But forget it. That’s 17.06 which is 6 months old. You should consider Docker as a puppy. 6 month in puppy’s years is like 3 human years. So many changes happened.

You can remove all those old things:

# yum remove docker docker-common docker-selinux docker-engine

docker-ce

I’ve not found ‘docker-ce’ on OL7 repositories, as only the Enterprise Edition is there. Then I added the CentOS repo (with yum-config-manager that you can get with yum-utils if you don’t have it already):

yum -y install yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo

Once done, I’ve just installed Docker Community Edition with:

yum -y install docker-ce

And, at that time I got the version 17.12 easily:

[root@VM188 ~]# yum info docker-ce
Loaded plugins: ulninfo
Installed Packages
Name : docker-ce
Arch : x86_64
Version : 17.12.0.ce
Release : 1.el7.centos
Size : 123 M
Repo : installed
From repo : docker-ce-stable
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.
...

But now there’s a new version available:

Available Packages
Name : docker-ce
Arch : x86_64
Version : 18.03.0.ce
Release : 1.el7.centos
Size : 35 M
Repo : docker-ce-stable/x86_64
Summary : The open-source application container engine
URL : https://www.docker.com
License : ASL 2.0
Description : Docker is an open source project to build, ship and run any application as a
: lightweight container.

The problem is that if you want to install docker-ce in this latest version, you will now get:

Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be installed
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Finished Dependency Resolution
Error: Package: docker-ce-18.03.0.ce-1.el7.centos.x86_64 (docker-ce-stable)
Requires: pigz
You could try using --skip-broken to work around the problem
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
2:microcode_ctl-2.1-22.5.0.3.el7_4.x86_64 has missing requires of kernel

(Ok Google, this is what you need to index…)

pigz

Starting from version 18.02 there’s a new dependency on ‘pigz’ for parallel gzip.

To get this ‘pigz’ package from the OL7 repository you need to enable EPEL in /etc/yum.repos.d/public-yum-ol7.repo

[ol7_developer_EPEL] name=Oracle Linux $releasever Developement Packages ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

Now, I’m able to install the latest docker-ce:

[root@VM188 yum.repos.d]# yum install docker-ce
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package docker-ce.x86_64 0:17.12.0.ce-1.el7.centos will be updated
---> Package docker-ce.x86_64 0:18.03.0.ce-1.el7.centos will be an update
--> Processing Dependency: pigz for package: docker-ce-18.03.0.ce-1.el7.centos.x86_64
--> Running transaction check
---> Package pigz.x86_64 0:2.3.4-1.el7 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================
Updating:
docker-ce x86_64 18.03.0.ce-1.el7.centos docker-ce-stable 35 M
Installing for dependencies:
pigz x86_64 2.3.4-1.el7 ol7_developer_EPEL 80 k
 
Transaction Summary
================================================================================================================================
Install ( 1 Dependent package)
Upgrade 1 Package
 
Total download size: 35 M
Is this ok [y/d/N]: y

Oracle Database on Docker

You may wonder why I install Docker on Oracle Linux rather than CentOS. The MOS Doc ID 2216342.1 mentions that Oracle will support customers running Oracle Database (single instance) in Docker containers running on Oracle Linux 7 with UEK4 or Red Hat Enterprise Linux 7.

If you want to validate your Docker install for running Oracle Database, the easiest is to use the image build script provided by Oracle:

git clone https://github.com/oracle/docker-images.git
cd ./docker-images/OracleDatabase/SingleInstance/dockerfiles/
# download and move linuxx64_12201_database.zip is in 12.2.0.1 subdirectory
sh buildDockerImage.sh -v 12.2.0.1 -e

Those are maintained by Gerald Venzl, Oracle product manager for database development, so they are obviously the best way to run Oracle Database on Docker. You can read all related best practices from the same author. Once you have that running, you have validated your environment and you can customize further if you want.

 

Cet article Docker-CE on Oracle Enterprise Linux 7 est apparu en premier sur Blog dbi services.

Insert…on conflict with partitions finally works in PostgreSQL 11

Thu, 2018-03-29 09:11

Another partitioning improvement for PostgreSQL 11: Insert…on conflict is now supported (for most cases) in PostgreSQL 11 thanks to this commit. Lets see how it works.

We’ll again use the slightly modified little list partitioned table from the last post, here in PostgreSQL 10:

postgres=# select version();
                                                          version                                       Insert                    
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)
 
postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# create unique index iu1 on part_1 (a);
CREATE INDEX
postgres=# create unique index iu2 on part_2 (a);
CREATE INDEX
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['beer'::character varying(5)])))
Indexes:
    "iu1" UNIQUE, btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = ANY (ARRAY['wine'::character varying(5)])))
Indexes:
    "iu2" UNIQUE, btree (a)

Insert some sample data:

postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'beer');
INSERT 0 1
postgres=# select * from part;
 a | b | list 
---+---+------
 1 | 1 | beer
 2 | 2 | beer
(2 rows)

When you try the following in PostgreSQL 10 it will fail:

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
ERROR:  ON CONFLICT clause is not supported with partitioned tables

Doing that directly on the partition is working:

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
ERROR:  ON CONFLICT clause is not supported with partitioned tables
postgres=# insert into part_1(a,b,list) values (1,2,'beer') on conflict (a) do update set b = 2;
INSERT 0 1
postgres=# select * from part;
 a | b | list 
---+---+------
 2 | 2 | beer
 1 | 2 | beer
(2 rows)

But who wants to do that directly on the partition? For sure this is a big limitation and not very useful. That got fixed now with PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, b int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# alter table part add constraint pk1 primary key (a,list);
ALTER TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 b      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "pk1" PRIMARY KEY, btree (a, list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')


postgres=# insert into part(a,b,list) values (1,1,'beer');
INSERT 0 1
postgres=# insert into part(a,b,list) values (2,2,'wine');
INSERT 0 1
postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set b = 2;
INSERT 0 1

Cool. But be aware that there still is a case which is not working:

postgres=# select * from part;
 a | b | list 
---+---+------
 1 | 2 | beer
 2 | 2 | wine
(2 rows)

postgres=# insert into part(a,b,list) values (1,2,'beer') on conflict (a,list) do update set list='wine';
ERROR:  invalid ON UPDATE specification
DETAIL:  The result tuple would appear in a different partition than the original tuple.

When you do an on conflict clause that would require to move the row into another partition (updating the partition key) it will fail.

 

Cet article Insert…on conflict with partitions finally works in PostgreSQL 11 est apparu en premier sur Blog dbi services.

How reimaging detects the ODA hardware?

Thu, 2018-03-29 08:48

A long time ago, ODA referred to the only model that existed (nearly). Actually, not so long time ago, as ODA X3, X4 and X5 were released between 2013 and 2015. And it was quite the same hardware.

Now there is 3 current models in the X7 range, and they were 4 in the previous generation (X6).

ODA is now divided in 2 main lines. High Availability models, understand 2 nodes and a shared storage like the first generations. And lite models, with only 1 node and local NVMe SSD, obviously more afordable than HA models.

Beginning with X6, lite models came with a brand new ISO for reimaging, including the new odacli, a higher level administration tool (compared to oakcli), and X6-2HA remained with the same ISO as previous gen models.

But now, X7 range saw the adoption of odacli for all models. And if you stick with bare metal, ISO is now the same for reimaging all the range. Quite convenient isn’t it?

I recently asked myself: how the reimaging process determines the correct model?

First of all, you can look at your ODA model with odaadmcli:

odaadmcli show env_hw
BM ODA X7-2 Medium

 

As you may know, reimaging process is fully automatic, and you have nothing to provide unless the correct ISO.

If ODA X7-S has only one Xeon CPU, nodes for ODA X7-M and X7-2HA are barelly the same, so what differs from them?

I first thought it was somewhere hardcoded, but it doesn’t seems to. ODA X7 is just the same hardware as the multi-purpose Oracle Server X7-2. Among the ISO files, I found a script that detects correct model by counting the number of local NVMe SSD (quite smart because HA model has only M2 SSD disks for the system and no NVMe SSDs), but it was not used during the reimaging…

Looking deeper on the system side, I found that model was simply part of the grub.conf boot options:

kernel /vmlinuz-4.1.12-94.4.1.el6uek.x86_64 ro root=/dev/mapper/VolGroupSys-LogVolRoot rd_NO_LUKS rd_MD_UUID=1ee3fdbc:3fdcdcf4:3a28f182:1a674b68 rd_LVM_LV=VolGroupSys/LogVolRoot rd_LVM_LV=VolGroupSys/LogVolSwap SYSFONT=latarcyrheb-sun16 pci=noaer crashkernel=256M@64M loglevel=3 panic=60 transparent_hugepage=never biosdevname=1 ipv6.disable=1 debug audit=1 intel_idle.max_cstate=1 nofloppy nomce numa=off console=ttyS0,115200n8 console=tty0 KEYBOARDTYPE=pc KEYTABLE=us rd_NO_DM LANG=en_US.UTF-8 PRODUCT=ORACLE_SERVER_X7-2 TYPE=X7_2_LITE_M

I discovered that odacli is based on this information. If you remove these settings, your ODA will be considered as a HA model, and odacli will crash:

DCS-10002: Invalid hardware platform

Okay, but it doesn’t tell me how the reimaging process decides which model to put in the grub.conf parameters…

Actually, grub.conf is configured at deployment with something from the ILOM, the “SP System identifier” located in the settings under the ILOM hostname:

blog_X7_SP_identifier

As you can see, this field can be edited, and you can put everything in it (didn’t tried EXADATA…). Unfortunatly, it’s just below the “SP Hostname” and some people would probably like to change this identifier in the same time they are feeding the hostname. But it’s a bad idea because your ODA would not be correctly deployed for the next time you’ll need to reimage!

Be aware of that and please do not touch this identifier. Keep it as it is.

Notes :
– reimaging was done with patch 23530609 (version 12.2.1.2.0)
– default hostname for the ODA ILOM is… the SP System Identifier

 

Cet article How reimaging detects the ODA hardware? est apparu en premier sur Blog dbi services.

The WAL segement size becomes changeable in PostgreSQL 11

Tue, 2018-03-27 09:37

Up to PostgreSQL 10 the wal segment size was fixed once PostgreSQL was compiled and installed. When you wanted to have something else than the default you needed to tell that to the configure command when building from source, e.g.

postgres@pgbox:/home/postgres/postgresql/ [PGDEV] ./configure --with-wal-segsize=64MB

For PostgreSQL 11 two commits landed (1 and 2) which change this. Lets have a look.

When you check the current development documentation for initdb you’ll notice a new parameter called “–wal-segsize”. This allows to change the WAL segment size from the default of 16MB when you initialize you new cluster:

postgres@pgbox:/home/postgres/ [PGDEV] initdb --wal-segsize=32 /var/tmp/aa
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 locales
  COLLATE:  en_US.utf8
  CTYPE:    en_US.utf8
  MESSAGES: en_US.utf8
  MONETARY: de_CH.UTF-8
  NUMERIC:  de_CH.UTF-8
  TIME:     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.

creating directory /var/tmp/aa ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

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 /var/tmp/aa -l logfile start

Btw: You can also use pg_controldata to get the size of the WAL segments:

postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment"
Bytes per WAL segment:                33554432
postgres@pgbox:/home/postgres/ [PGDEV] echo "33554432/1024/1024" | bc
32

The other commit added the same option to pg_resetwal.

postgres@pgbox:/home/postgres/ [PGDEV] pg_resetwal -D /var/tmp/aa/ --wal-segsize=64
Write-ahead log reset
postgres@pgbox:/home/postgres/ [PGDEV] pg_controldata | grep "Bytes per WAL segment"
Bytes per WAL segment:                67108864
postgres@pgbox:/home/postgres/ [PGDEV] echo "67108864/1024/1024" | bc
64

So, hopefully this September when PostgreSQL 11 should be released, you do not need to install additional binaries for changing the WAL segment size. This will also allow major upgrades with pg_upgrade to a new cluster with a different WAL segment size. Nice, makes things easier.

 

Cet article The WAL segement size becomes changeable in PostgreSQL 11 est apparu en premier sur Blog dbi services.

The same MAC address for two interfaces on the same host?

Mon, 2018-03-26 23:50

Yes, sounds strange, but exactly this is what we faced today. In the end it is clear and not strange anymore but for a moment we really thought we are hitting a bug in the operating system, which was RedHat 7.4. For the little demo for this post I’ll use CentOS 7.4 but this should not really matter. Lets go.

The situation we faced looked like this:

root@:/etc/sysconfig/network-scripts/ [] ip a
1: lo:  mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: enp0s3:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:d6:95:ab brd ff:ff:ff:ff:ff:ff
    inet 10.0.2.15/24 brd 10.0.2.255 scope global dynamic enp0s3
       valid_lft 86295sec preferred_lft 86295sec
    inet6 fe80::a00:27ff:fed6:95ab/64 scope link 
       valid_lft forever preferred_lft forever
3: enp0s8:  mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 08:00:27:5c:b0:e5 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.99/24 brd 192.168.22.255 scope global enp0s8
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe5c:b0e5/64 scope link 
       valid_lft forever preferred_lft forever
4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe85:2d8/64 scope link tentative 
       valid_lft forever preferred_lft forever

As you can see the interfaces enp0s8 and enp0s10 have exactly the same MAC address. In addition to that the bonding interface bond0 has the same mac address as well:

4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fe85:2d8/64 scope link tentative 
       valid_lft forever preferred_lft forever

This was not so scary as we knew that the bonding interface will get one of the mac addresses of the slave interfaces. But both slaves had the same mac address, how that? It all depends on the bonding options. In our case:

root@:/etc/sysconfig/network-scripts/ [] cat ifcfg-bond0 
DEVICE=bond0
TYPE=Bond
BONDING_MASTER=yes
BOOTPROTO=static
IPADDR=192.168.22.223
NETMASK=255.255.255.0
GATEWAY=192.168.22.1
ONBOOT=yes
BONDING_OPTS="mode=active-backup miimon=100"

What you need to know is that there is another parameter/option called fail_over_mac. The default value for this option (as we did not explicitly set it we are using the default) is 0/none which means all slave interfaces will use the same MAC address. The original MAC addresses can be seen here:

root@:/etc/sysconfig/network-scripts/ [] cat /proc/net/bonding/bond0 
Ethernet Channel Bonding Driver: v3.7.1 (April 27, 2011)

Bonding Mode: fault-tolerance (active-backup)
Primary Slave: None
Currently Active Slave: enp0s9
MII Status: up
MII Polling Interval (ms): 100
Up Delay (ms): 0
Down Delay (ms): 0

Slave Interface: enp0s9
MII Status: up
Speed: 1000 Mbps
Duplex: full
Link Failure Count: 0
Permanent HW addr: 08:00:27:85:02:d8
Slave queue ID: 0

Slave Interface: enp0s10
MII Status: up
Speed: 1000 Mbps
Duplex: full
Link Failure Count: 0
Permanent HW addr: 08:00:27:e4:1c:63
Slave queue ID: 0

When we change that to 1/active and restart the network:

root@:/etc/sysconfig/network-scripts/ [] sed -i 's/BONDING_OPTS="mode=active-backup miimon=100"/BONDING_OPTS="mode=active-backup miimon=100 fail_over_mac=active"/g' ifcfg-bond0 
root@:/etc/sysconfig/network-scripts/ [] systemctl restart network

… we will have different MAC addresses:

4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever
    inet6 fe80::a00:27ff:fee4:1c63/64 scope link tentative 
       valid_lft forever preferred_lft forever

The downside of this approach is that the MAC address will change when the active interface goes down. The currently active interface is enp0s9 with the following MAC address:

root@:/etc/sysconfig/network-scripts/ [] cat /proc/net/bonding/bond0 | grep -i active
Bonding Mode: fault-tolerance (active-backup) (fail_over_mac active)
Currently Active Slave: enp0s9
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 enp0s9
4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff

The bonding interface has the same MAC address:

root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 bond0
4: enp0s9:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:85:02:d8 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever

When we bring the currently active interface (enp0s9) down the MAC address of the bonding interface will switch to the MAC address of enp0s10 (which then becomes the active interface):

root@:/etc/sysconfig/network-scripts/ [] ifdown enp0s9
Device 'enp0s9' successfully disconnected.
root@:/etc/sysconfig/network-scripts/ [] ip a | grep -A 1 bond0
5: enp0s10:  mtu 1500 qdisc pfifo_fast master bond0 state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
6: bond0:  mtu 1500 qdisc noqueue state UP qlen 1000
    link/ether 08:00:27:e4:1c:63 brd ff:ff:ff:ff:ff:ff
    inet 192.168.22.223/24 brd 192.168.22.255 scope global bond0
       valid_lft forever preferred_lft forever

The third an last option for fail_over_mac is 3/follow which will change the MAC address of the slave to the bonding’s MAC address when it becomes active. Documentation here.

 

Cet article The same MAC address for two interfaces on the same host? est apparu en premier sur Blog dbi services.

When you execute the same statement thousands of times -> prepare it

Mon, 2018-03-26 04:13

Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing of statements and only planning and executing will happen. Lets see how that works.

To generate some sample data I used pgbench with a scale factor of 100 which gives me 10’000’000 rows in the pgbench_accounts table:

postgres@pgbox:/home/postgres/ [PGDEV] pgbench -i -s 100 bench
postgres@pgbox:/home/postgres/ [PGDEV] psql bench

psql (11devel)
Type "help" for help.

bench=# select count(*) from pgbench_accounts;
  count   
----------
 10000000
(1 row)

bench=# \d pgbench_accounts
              Table "public.pgbench_accounts"
  Column  |     Type      | Collation | Nullable | Default 
----------+---------------+-----------+----------+---------
 aid      | integer       |           | not null | 
 bid      | integer       |           |          | 
 abalance | integer       |           |          | 
 filler   | character(84) |           |          | 
Indexes:
    "pgbench_accounts_pkey" PRIMARY KEY, btree (aid)

Lets assume we have the following simple query:

bench=# select count(*) from pgbench_accounts where aid = 11111;
 count 
-------
     1
(1 row)

As said at the beginning of this post PostgreSQL will need to parse that statement. Using explain with the right options you are able to see how much time was spend on planning:

bench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.060..0.063 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.034..0.039 rows=1 loops=1)
         Index Cond: (aid = 11111)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.150 ms
 Execution Time: 0.133 ms
(8 rows)

Planning this statement takes more time then executing it. Now assume you want to execute that very same statement one thousand times:

bench=# \t
bench=# select 'select count(*) from pgbench_accounts where aid = 11111;' from generate_series(1,1000) i; \g test.sql
bench=# \! cat test.sql | head
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
 select count(*) from pgbench_accounts where aid = 11111;
...

When we execute that we force PostgreSQL to parse all of those 1000 statements:

bench=# \timing
Timing is on.
bench=# \! time psql -f test.sql bench

real	0m0.148s
user	0m0.031s
sys	0m0.015s

What you can do to avoid that is to prepare the statement:

bench=# prepare tt as select count(*) from pgbench_accounts where aid = 11111;
PREPARE

Once it is prepared you can execute it:

bench=# execute tt;
 count 
-------
     1
(1 row)

… and you can also explain it:

bench=# explain(analyze,buffers) execute tt;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.066..0.069 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.037..0.043 rows=1 loops=1)
         Index Cond: (aid = 11111)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.021 ms
 Execution Time: 0.269 ms
(8 rows)

You’ll notice that the planning time went down quite a lot compared to the not prepared statement:

bench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.167..0.172 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.030..0.037 rows=1 loops=1)
         Index Cond: (aid = 11111)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.167 ms
 Execution Time: 0.248 ms
(8 rows)

When you do that one thousand times now:

bench=# \t
bench=# select 'execute tt;' from generate_series(1,1000) i; \g test.sql
bench=# \! sed -i '1s/^/prepare tt as select count(*) from pgbench_accounts where aid = 11111;/' test.sql
bench=# \! time psql -f test.sql bench

real	0m0.095s
user	0m0.031s
sys	0m0.010s

… execution time will come down. It is not much in that simple case but this is due to the simple statement. Btw: As prepared statements only life for the time of the session the sed command adds the prepare statement to the top of file, and preparing takes time as well. Without that execution time would be even less.

When your values change in the where clause you can do it like this:

bench=# prepare tt as select count(*) from pgbench_accounts where aid = $1;
PREPARE
Time: 0.571 ms
bench=# execute tt (123);
     1

For completeness: When you want to get rid of a prepared statement without losing your session there is deallocate:

bench=# deallocate tt;
DEALLOCATE
Time: 0.623 ms
 

Cet article When you execute the same statement thousands of times -> prepare it est apparu en premier sur Blog dbi services.

PostgreSQL – logical replication with pglogical

Fri, 2018-03-23 11:31

Although PostgreSQL 10 integrate native logical replication (have a look here or here), it is always interesting to be aware of alternative solutions to the available in-core features.
One of those is called pglogical. It’s a Postgres extension developed by 2ndQuadrant, major contributor to PostgreSQL development.
pg_logical
The goal of this blog post is to discover how to install pglogical and configure it in order to generate simple logical replication cases.

Installation

To get the extension we should start with RPM repository installation :
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10] sudo yum install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-3.noarch.rpm
Loaded plugins: fastestmirror
pglogical-rhel-1.0-3.noarch.rpm | 8.3 kB 00:00:00
Examining /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm: pglogical-rhel-1.0-3.noarch
Marking /var/tmp/yum-root-oH8huK/pglogical-rhel-1.0-3.noarch.rpm to be installed
...
...
...
Installed:
pglogical-rhel.noarch 0:1.0-3


Complete!
postgres@ppas02:/u01/app/postgres/product/10/db_1/ [PG10]

Once installed, we can add the extension inside our Postgres instance :
postgres=# CREATE EXTENSION pglogical;
2017-12-18 16:24:39.079 CET [4327] ERROR: pglogical is not in shared_preload_libraries
2017-12-18 16:24:39.079 CET [4327] STATEMENT: CREATE EXTENSION pglogical;
ERROR: pglogical is not in shared_preload_libraries
postgres=#

Oops… the pglogical library must be loaded when the cluster starts :
postgres=# alter system set shared_preload_libraries = 'pglogical';
ALTER SYSTEM
postgres=#

Restart the cluster to take it in account :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ stop
waiting for server to shut down....2017-12-18 16:28:03.895 CET [4447] LOG: received fast shutdown request
2017-12-18 16:28:03.902 CET [4447] LOG: aborting any active transactions
2017-12-18 16:28:03.923 CET [4447] LOG: worker process: logical replication launcher (PID 4455) exited with exit code 1
2017-12-18 16:28:03.923 CET [4449] LOG: shutting down
2017-12-18 16:28:03.940 CET [4447] LOG: database system is shut down
done
server stopped
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] pg_ctl -D /u02/pgdata/PG10/ start
waiting for server to start....2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv4 address "0.0.0.0", port 5420
2017-12-18 16:28:10.405 CET [4654] LOG: listening on IPv6 address "::", port 5420
2017-12-18 16:28:10.407 CET [4654] LOG: listening on Unix socket "/tmp/.s.PGSQL.5420"
2017-12-18 16:28:10.416 CET [4655] LOG: database system was shut down at 2017-12-18 16:28:03 CET
2017-12-18 16:28:10.426 CET [4654] LOG: database system is ready to accept connections
2017-12-18 16:28:10.430 CET [4661] LOG: starting pglogical supervisor
2017-12-18 16:28:10.435 CET [4663] LOG: manager worker [4663] at slot 0 generation 1 detaching cleanly
2017-12-18 16:28:10.439 CET [4664] LOG: manager worker [4664] at slot 0 generation 2 detaching cleanly
2017-12-18 16:28:10.444 CET [4665] LOG: manager worker [4665] at slot 0 generation 3 detaching cleanly
done
server started
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10]

Once the cluster restarted with the library, we may noticed a new OS process :
postgres@ppas01:/u01/app/postgres/product/10/db_1/share/ [PG10] ps -ef | grep pglogical
postgres 5001 4994 0 16:33 ? 00:00:00 postgres: bgworker: pglogical supervisor

We should now be able to create the extension :
postgres=# CREATE EXTENSION pglogical;
CREATE EXTENSION
postgres=#
postgres=# \dx+ pglogical
Objects in extension "pglogical"
Object description
------------------------------------------------------------------------------------------
function pglogical.alter_node_add_interface(name,name,text)
function pglogical.alter_node_drop_interface(name,name)
function pglogical.alter_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.alter_subscription_add_replication_set(name,name)
function pglogical.alter_subscription_disable(name,boolean)
function pglogical.alter_subscription_enable(name,boolean)
function pglogical.alter_subscription_interface(name,name)
function pglogical.alter_subscription_remove_replication_set(name,name)
function pglogical.alter_subscription_resynchronize_table(name,regclass,boolean)
function pglogical.alter_subscription_synchronize(name,boolean)
function pglogical.create_node(name,text)
function pglogical.create_replication_set(name,boolean,boolean,boolean,boolean)
function pglogical.create_subscription(name,text,text[],boolean,boolean,text[],interval)
function pglogical.drop_node(name,boolean)
function pglogical.drop_replication_set(name,boolean)
function pglogical.drop_subscription(name,boolean)
function pglogical.pglogical_gen_slot_name(name,name,name)
function pglogical.pglogical_max_proto_version()
function pglogical.pglogical_min_proto_version()
function pglogical.pglogical_node_info()
function pglogical.pglogical_version()
function pglogical.pglogical_version_num()
function pglogical.queue_truncate()
function pglogical.replicate_ddl_command(text,text[])
function pglogical.replication_set_add_all_sequences(name,text[],boolean)
function pglogical.replication_set_add_all_tables(name,text[],boolean)
function pglogical.replication_set_add_sequence(name,regclass,boolean)
function pglogical.replication_set_add_table(name,regclass,boolean,text[],text)
function pglogical.replication_set_remove_sequence(name,regclass)
function pglogical.replication_set_remove_table(name,regclass)
function pglogical.show_repset_table_info(regclass,text[])
function pglogical.show_subscription_status(name)
function pglogical.show_subscription_table(name,regclass)
function pglogical.synchronize_sequence(regclass)
function pglogical.table_data_filtered(anyelement,regclass,text[])
function pglogical.wait_slot_confirm_lsn(name,pg_lsn)
table pglogical.depend
table pglogical.local_node
table pglogical.local_sync_status
table pglogical.node
table pglogical.node_interface
table pglogical.queue
table pglogical.replication_set
table pglogical.replication_set_seq
table pglogical.replication_set_table
table pglogical.sequence_state
table pglogical.subscription
view pglogical.tables
(48 rows)

The wal_level parameter must be set to ‘logical’ for logical replication :
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)

And the pg_hba file must contains an entry to allow connections for replication purpose.
Important : all the steps we have seen until now must be done on the standby side as well.

What’s next ?

Logical replication with Postgres is based on several components :
– Provider : the primary node/cluster
– Subscriber : the standby node/cluster, on which data will be replicated
– Replication set : collection of table you want to replicate
– Subscription : runs the replication, based on the replication set

We’ll start by creating the provider on the primary side :
postgres=# select pglogical.create_node (node_name := 'provider1', dsn := 'host=192.168.22.37 port=5420 dbname=postgres');
create_node
-------------
2976894835
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-----------+------------+-----------------------------------------------
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)

Notice again a new OS process :
postgres@ppas01:/home/postgres/ [PG10] ps -ef | grep pglogical
postgres 1796 1788 0 15:13 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4566 1788 0 16:05 ? 00:00:00 postgres: bgworker: pglogical manager 13211

On the standby side, we create the subscriber :
postgres=# select pglogical.create_node(node_name := 'subscriber1', dsn := 'host=192.168.22.38 port=5420 dbname=postgres');
create_node
-------------
330520249
(1 row)
postgres=# select * from pglogical.node_interface;
if_id | if_name | if_nodeid | if_dsn
------------+-------------+-----------+----------------------------------------------
2049915666 | subscriber1 | 330520249 | host=192.168.22.38 port=5420 dbname=postgres
2402836775 | provider1 | 2976894835 | host=192.168.22.37 port=5420 dbname=postgres
(1 row)

What about replication set ? There is 3 existing by default with a different DML replication behavior for each :
postgres=# select * from pglogical.replication_set;
set_id | set_nodeid | set_name | replicate_insert | replicate_update | replicate_delete | replicate_truncate
------------+------------+---------------------+------------------+------------------+------------------+--------------------
290045701 | 2976894835 | default | t | t | t | t
3461245231 | 2976894835 | default_insert_only | t | f | f | t
2465411902 | 2976894835 | ddl_sql | t | f | f | f

We can easily add our own :
postgres=# select pglogical.create_replication_set('my_set', true, true, true, true);
create_replication_set
------------------------
1521346531

To start the replication we have to create a subscription (using the replication set we created) from the standby side :
postgres=# select pglogical.create_subscription(subscription_name := 'subscription1', provider_dsn := 'host=192.168.22.37 port=5420 dbname=postgres', replication_sets := '{my_set}');
create_subscription
---------------------
1763399739
(1 row)


postgres=# select subscription_name, status FROM pglogical.show_subscription_status();
subscription_name | status
-------------------+-------------
subscription1 | replicating

Have a look in the log file… replication started with the correct provider and subscription :
2018-02-02 15:16:14.234 CET [5488] LOG: starting apply for subscription subscription1
2018-02-02 15:17:54.547 CET [5502] LOG: starting pglogical database manager for database postgres

postgres@ppas02:/u02/pgdata/PG10/ [PG10] ps -ef | grep pglogical
postgres 3113 3105 0 10:01 ? 00:00:00 postgres: bgworker: pglogical supervisor
postgres 4587 3105 0 13:47 ? 00:00:00 postgres: bgworker: pglogical manager 13211
postgres 5060 3105 0 15:06 ? 00:00:00 postgres: bgworker: pglogical apply 13211:1763399739

The last step is to add the tables in the replication set we created :
postgres=# select pglogical.replication_set_add_all_tables('my_set', ARRAY['public']);
replication_set_add_all_tables
--------------------------------
t
(1 row)

The insert/update/delete operations run against the primary server are now replicated to the standby one.

 

Cet article PostgreSQL – logical replication with pglogical est apparu en premier sur Blog dbi services.

Updating the partition key now works in PostgreSQL 11

Fri, 2018-03-23 11:00

In the last post about partitioning improvements in PostgreSQL 11 we talked about unique indexes and primary keys on partitioned tables. Both did not work in PostgreSQL 10 but now do in PostgreSQL 11. Another operation that did not work in PostgreSQL 10 and does now in PostgreSQL 11 is: Updating the partition key, which means that a row will move from one partition to another. Lets have a look at that.

We’ll use the same little list partitioned table as in the last post and start with PostgreSQL 10:

postgres=# select version();
                                                          version                                       Insert                     
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Insert some data:

postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10

So we have ten beers and ten wines.

postgres=# select count(*) from only part_1;
 count 
-------
    10
(1 row)

postgres=# select count(*) from only part_2;
 count 
-------
    10
(1 row)

What happens if we update the partition key because we like more beer than wine in PostgreSQL 10?

postgres=# update part set list = 'beer' where a = 15;
ERROR:  new row for relation "part_2" violates partition constraint
DETAIL:  Failing row contains (15, beer).

Not so good. Lets try the same in PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# insert into part (a,list) select i,'beer' from generate_series(1,10) i;
INSERT 0 10
postgres=# insert into part (a,list) select i,'wine' from generate_series(11,20) i;
INSERT 0 10
postgres=# update part set list = 'beer' where a = 15;
UPDATE 1

Excellent, just works.

 

Cet article Updating the partition key now works in PostgreSQL 11 est apparu en premier sur Blog dbi services.

Hash Partitioning in PostgreSQL 11

Fri, 2018-03-23 07:45

PostgreSQL 10 comes with partition support. But Hash Partitioning was not supported. PostgreSQL 11 will support Hash Partition.
In the documentation we can read
The table is partitioned by specifying a modulus and a remainder for each partition. Each partition will hold the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder.
For this demonstration let’s create a table with a hash partition

drop table dept;
create table dept (id  int primary key) partition by hash(id) ; 

Now let’s create for example 10 partitions

create table dept_1 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 0);
create table dept_2 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 1);
create table dept_3 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 2);
create table dept_4 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 3);
create table dept_5 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 4);
create table dept_6 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 5);
create table dept_7 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 6);
create table dept_8 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 7);
create table dept_9 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 8);
create table dept_10 partition of dept FOR VALUES WITH (MODULUS 10, REMAINDER 9);

We can verify that partitions are created using the \d+ command

(postgres@[local]:5432) [test] > \d+ dept
                                   Table "public.dept"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | De
scription
--------+---------+-----------+----------+---------+---------+--------------+---
----------
 id     | integer |           | not null |         | plain   |              |
Partition key: HASH (id)
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
Partitions: dept_1 FOR VALUES WITH (modulus 10, remainder 0),
            dept_10 FOR VALUES WITH (modulus 10, remainder 9),
            dept_2 FOR VALUES WITH (modulus 10, remainder 1),
            dept_3 FOR VALUES WITH (modulus 10, remainder 2),
            dept_4 FOR VALUES WITH (modulus 10, remainder 3),
            dept_5 FOR VALUES WITH (modulus 10, remainder 4),
            dept_6 FOR VALUES WITH (modulus 10, remainder 5),
            dept_7 FOR VALUES WITH (modulus 10, remainder 6),
            dept_8 FOR VALUES WITH (modulus 10, remainder 7),
            dept_9 FOR VALUES WITH (modulus 10, remainder 8)

(postgres@[local]:5432) [test] >

Now let’s insert some rows in the table dept

(postgres@[local]:5432) [test] > insert into dept (select generate_series(0,200000));
INSERT 0 200001
(postgres@[local]:5432) [test] >

We can verify that rows are not in the base table

(postgres@[local]:5432) [test] > select count(*) from  only dept ;
 count
-------
     0
(1 row)

(postgres@[local]:5432) [test] >

But that row are in the partitions

(postgres@[local]:5432) [test] > select count(*) from  dept ;
 count
--------
 200001
(1 row)

What we can also observe it that rows are uniformly distributed among partitions. This distribution is automatically done by the hash algorithm.

(postgres@[local]:5432) [test] > select count(*) from  only dept_1 ;
 count
-------
 19982
(1 row)
                                   
(postgres@[local]:5432) [test] > select count(*) from  only dept_2 ;
 count
-------
 20199
(1 row)

(postgres@[local]:5432) [test] > select count(*) from  only dept_3 ;
 count
-------
 19770
(1 row)

(postgres@[local]:5432) [test] > select count(*) from  only dept_5 ;
 count
-------
 20068
(1 row)

(postgres@[local]:5432) [test] >
 

Cet article Hash Partitioning in PostgreSQL 11 est apparu en premier sur Blog dbi services.

What is the maximum in list size in PostgreSQL?

Fri, 2018-03-23 04:53

Yesterday, while being at a customer, an interesting question popped up: What is the maximum of in list values in PostgreSQL? I couldn’t answer although I never read somewhere that there is a limit. The following is for fun only and I am not saying that creating huge in lists is a good idea. Lets go.

The version I tested is PostgreSQL 10:

postgres=# select version(), now();
                                                  version                                                   |              now              
------------------------------------------------------------------------------------------------------------+-------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit | 2018-03-21 18:29:50.269369+01
(1 row)

postgres=# create table t1 ( a varchar(10));
CREATE TABLE

We will use a very simple bash script to build the in list and execute the result in PostgreSQL:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

statement='select * from t1 where a in ('

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        statement="${statement} '${i}',"
    elif [ "${i}" == "${count}" ]; then
        statement="${statement} '${i}');"
    fi
done

psql -c "${statement}" postgres

Lets start with 100:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100
 a 
---
(0 rows)

Time: 0.983 ms

1000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000
 a 
---
(0 rows)

Time: 1.525 ms

10000:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 10000
 a 
---
(0 rows)

Time: 11.549 ms

… takes a much longer (because of the bash script which fully occupies my virtual core):

./t.sh: line 15: /u01/app/postgres/product/10/db_0/bin/psql: Argument list too long

So there at least is a limit with psql. Lets try by creating a sql script and execute that instead:

postgres@pgbox:/home/postgres/ [PG10] cat t.sh 
#!/bin/bash
count=$1

echo 'select * from t1 where a in (' > ttt.sql

for (( i=1; i<=$count; i++ ))
do  
    if [ "${i}" -lt "${count}" ]; then
        echo  "'${i}'," >> ttt.sql
    elif [ "${i}" == "${count}" ]; then
        echo "'${i}');" >> ttt.sql
    fi
done

psql -f ttt.sql postgres

This way of stringing together the statement is much more efficient than building the list by concatenating everything into one variable. Does it still work?

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 100000
 a 
---
(0 rows)

Time: 155.986 ms

Not a problem, one more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 1000000
 a 
---
(0 rows)

Time: 14211.766 ms (00:14.212)

Still works. So now we could say: lets stop, who in the world will pass one million values into an in list. On the other hand, lets have fun and double:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 2000000
 a 
---
(0 rows)

Time: 3993.091 ms (00:03.993)

One more:

postgres@pgbox:/home/postgres/ [PG10] ./t.sh 3000000
psql:ttt.sql:3000001: ERROR:  out of memory
DETAIL:  Failed on request of size 524288.
Time: 3026.925 ms (00:03.027)

Ok, now I am hitting some limits, but probably not those of PostgreSQL. I’ll test further when I have more time for that :)

 

Cet article What is the maximum in list size in PostgreSQL? est apparu en premier sur Blog dbi services.

Local partitioned indexes in PostgreSQL 11

Thu, 2018-03-22 02:23

When declarative partitioning was introduced with PostgreSQL 10 this was a big step forward. But as always with big new features some things do not work in PostgreSQL 10 which now get resolved in PostgreSQL 11. One of those are local partitioned indexes. To make it easier to understand lets start with an example in PostgreSQL 10.

A very simple list partitioned table:

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

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

In PostgreSQL 10 what happens when we create an index on the partitioned table?

postgres=# create index i_test on part (a);
ERROR:  cannot create index on partitioned table "part"

You just can not do it. But you can create indexes on the partitions directly:

postgres=# create index i_test_1 on part_1 (a);
CREATE INDEX
postgres=# create index i_test_2 on part_2 (a);
CREATE INDEX

Lets do the same test with PostgreSQL 11:

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# create table part ( a int, list varchar(5) ) partition by list (list);
CREATE TABLE
postgres=# create table part_1 partition of part for values in ('beer');
CREATE TABLE
postgres=# create table part_2 partition of part for values in ('wine');
CREATE TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

Try to create the index on the partitioned table:

postgres=# create index i_test on part (a);
CREATE INDEX
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_a_idx" btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           |          |         | plain    |              | 
 list   | character varying(5) |           |          |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_a_idx" btree (a)

The index is cascaded down to all the partitions in PostgreSQL 11 which is really nice. As a side effect of this, when you try this in PostgreSQL 10:

postgres=# alter table part add constraint part_pk primary key(a,list);
ERROR:  primary key constraints are not supported on partitioned tables
LINE 1: alter table part add constraint part_pk primary key(a,list);
                             ^

… you will get an error message telling you that primary keys are not supported on partitioned tables. The same applies here, you can do that on the partitions directly:

postgres=# alter table part_1 add constraint part1_pk primary key(a,list);
ALTER TABLE
postgres=# alter table part_2 add constraint part2_pk primary key(a,list);
ALTER TABLE

Now in PostgreSQL 11 this works as well:

postgres=# alter table part add constraint part_pk primary key(a,list);
ALTER TABLE
postgres=# \d+ part
                                          Table "public.part"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition key: LIST (list)
Indexes:
    "part_pk" PRIMARY KEY, btree (a, list)
    "i_test" btree (a)
Partitions: part_1 FOR VALUES IN ('beer'),
            part_2 FOR VALUES IN ('wine')

postgres=# \d+ part_1
                                         Table "public.part_1"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('beer')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'beer'::character varying(5)))
Indexes:
    "part_1_pkey" PRIMARY KEY, btree (a, list)
    "part_1_a_idx" btree (a)

postgres=# \d+ part_2
                                         Table "public.part_2"
 Column |         Type         | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+----------------------+-----------+----------+---------+----------+--------------+-------------
 a      | integer              |           | not null |         | plain    |              | 
 list   | character varying(5) |           | not null |         | extended |              | 
Partition of: part FOR VALUES IN ('wine')
Partition constraint: ((list IS NOT NULL) AND ((list)::text = 'wine'::character varying(5)))
Indexes:
    "part_2_pkey" PRIMARY KEY, btree (a, list)
    "part_2_a_idx" btree (a)

Quite some improvements to show up in PostgreSQL 11.

 

Cet article Local partitioned indexes in PostgreSQL 11 est apparu en premier sur Blog dbi services.

pg_basebackup and redirecting progress messages to a file

Wed, 2018-03-21 11:32

Recently I came over that commit and wondered what that is about. The answer is quite simple but I didn’t know that this issue existed. Basically it is about how progress messages are written to screen and how they are written to a file. Lets have a look.

When your run pg_basebackup with progress messages and in verbose mode the output looks like this:

postgres@pgbox:/home/postgres/ [PG10] pg_basebackup --pgdata=/var/tmp/aa --verbose --progress 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/30000028 on timeline 1
pg_basebackup: starting background WAL receiver
593320/593320 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 4/30000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

You’ll notice that the highlighted line always is overwritten on screen until we reach one hundred percent. Looking at that line when pg_basebackup is running will give you an estimate on how long it will take and you will see which file it is currently working on. When you do the same thing but kick it in the background like this:

postgres@pgbox:/home/postgres/ [PG10] pg_basebackup --version
pg_basebackup (PostgreSQL) 10.0 
postgres@pgbox:/home/postgres/ [PG10] mkdir /var/tmp/aa
postgres@pgbox:/home/postgres/ [PG10] nohup pg_basebackup --pgdata=/var/tmp/aa --verbose --progress  > /tmp/a.log 2>&1  &

… you will have the same output in the log file:

postgres@pgbox:/home/postgres/ [PG10] cat -f /tmp/a.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 4/28000028 on timeline 1
pg_basebackup: starting background WAL receiver
593315/593315 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 4/28000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

Somehow that was not considered very useful so the commit mentioned above changed that:

postgres@pgbox:/home/postgres/ [PGDEV] pg_basebackup --version
pg_basebackup (PostgreSQL) 11devel
postgres@pgbox:/home/postgres/ [PGDEV] nohup pg_basebackup --pgdata=/var/tmp/aa --verbose --progress  > /tmp/a.log 2>&1  &
postgres@pgbox:/home/postgres/ [PGDEV] cat /tmp/a.log
nohup: ignoring input
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_29846"
     0/184659 kB (0%), 0/1 tablespace (/var/tmp/aa/backup_label           )
  1705/184659 kB (0%), 0/1 tablespace (/var/tmp/aa/base/1/1249            )
  4697/184659 kB (2%), 0/1 tablespace (/var/tmp/aa/base/1/2657            )
  8395/184659 kB (4%), 0/1 tablespace (/var/tmp/aa/base/13276/1255        )
 20601/184659 kB (11%), 0/1 tablespace (/var/tmp/aa/base/13277/2670        )
 30614/184659 kB (16%), 0/1 tablespace (/var/tmp/aa/base/16395/2607_vm     )
 45367/184659 kB (24%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
 54743/184659 kB (29%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
 74327/184659 kB (40%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
118807/184659 kB (64%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
146647/184659 kB (79%), 0/1 tablespace (/var/tmp/aa/base/16395/16424       )
175197/184659 kB (94%), 0/1 tablespace (/var/tmp/aa/base/16395/16432       )
184668/184668 kB (100%), 0/1 tablespace (/var/tmp/aa/global/pg_control      )
184668/184668 kB (100%), 0/1 tablespace (/var/tmp/aa/global/pg_control      )
184668/184668 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/E000168
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed

When you redirect the output into a file you can see much more steps in the log file compared to what we saw before (only a single line which is overwritten all the time). Seems to be a good change.

 

Cet article pg_basebackup and redirecting progress messages to a file est apparu en premier sur Blog dbi services.

PostgreSQL 11: Procedures and transaction control

Tue, 2018-03-20 11:58

Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top of that there was another commit which enables transaction control inside procedures which can be quite interesting. Lets see how that works.

Up to PostgreSQL 10 the only choice to have something like a procedure is to create a function returning void:

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

postgres=# CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   RAISE NOTICE 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# set client_min_messages = 'NOTICE';
SET
postgres=# select dummy_func(1);
NOTICE:  id is 1
 dummy_func 
------------
 
(1 row)

When you tried to do something like this it was not possible:

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE dummy_proc (id int) AS $$

But now in the PostgreSQL development version you can do it (CREATE PROCEDURE):

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call dummy_proc(1);
NOTICE:  id is 1
CALL

Also notice that you need to use call to execute a procedure. Using select as you would do it for a function will not work:

postgres=# select dummy_proc(1);
ERROR:  dummy_proc(integer) is a procedure
LINE 1: select dummy_proc(1);
               ^
HINT:  To call a procedure, use CALL.

On top of the commit which introduced procedures there was another one (see the beginning of this post) which introduced transaction control for procedures. As the name implies this can be useful when you want to control transactions inside the procedure, e.g.:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;

When you execute this it will violate the primary key with the third insert:

postgres=# call dummy_proc2(1);
NOTICE:  id is 1
NOTICE:  id is 2
NOTICE:  id is 2
ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(2) already exists.
CONTEXT:  SQL statement "insert into t1 (a) values (l_id)"
PL/pgSQL function dummy_proc2(integer) line 13 at SQL statement

… but because we can now commit (or rollback) in between we did not lose all the records:

postgres=# select * from t1;
 a 
---
 1
 2
(2 rows)

Trying to do the same with a function in PostgreSQL 10 will not work:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE FUNCTION dummy_func2 (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select dummy_func2(1);
NOTICE:  id is 1
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dummy_func2(integer) line 7 at SQL statement
postgres=# select * from t1;
 a 
---
(0 rows)

Nice features …

 

Cet article PostgreSQL 11: Procedures and transaction control est apparu en premier sur Blog dbi services.

Pages