Yann Neuhaus

Subscribe to Yann Neuhaus feed
dbi services technical blog
Updated: 3 hours 56 min ago

Two techniques for cloning a repository filestore, part II

Fri, 2019-01-18 03:27

This is part II of a two-part article. In part I, we introduced the Documentum repository file structure and saw a first way to transfer content files from one filesystem to another using the repository to get their full path on disk. We saw how to generate an easy to parallelize set of rsync commands to completely or partially copy a repository’s content files. Here, we’ll show another way to do that which does not imply querying the repository.
Before this, however, we need test data, i.e. sub-directories with files on disk. The good thing is that those don’t have to be linked to documents in the repository, they can just be loose files, even empty ones, scattered over Documentum-style sub-trees, and they are easy to produce.

Creating a test sub-tree

The usual test repository is an out of the box, practically empty one; therefore we need to create a dense Documentum-like sub-tree with lots of directories (up to 128 x 256 x 256 = 8128K ones per filestore, but we won’t go so far) and a few files, say 1 per subdirectory. No need to actually create related documents in a repository since we want to perform the copy of the files from outside the repository. As said above, the number of files is not really important as only their parent directory (and indirectly, its content) is being passed to rsync. A one-file terminal sub-directory would be enough for it to trigger its transfer. Also, their size does not matter either (we don’t want to measure the transfer speed, just to prepare performant rsync statements), so 0-byte files will do perfectly.
The short python program below creates a Documentum-style subdirectory.

#! /usr/bin/python3
import datetime
import time
import sys
import os
import random
import multiprocessing
import multiprocessing.pool
import functools
import traceback
import getopt

# creates a Documentum tree at a given filesystem location using multi-processes for speed;
# Usage:
#   ./make-tree.py root_point
# Example:
#   ./make-tree.py "/home/dmadmin/documentum/data/dmtest/content_storage01/0000c35c/80"

# prerequisite to prevent the error OSError: [Errno 24] Too many open files if too many processes are forked;
# ulimit -n 10000

# 12/2018, C. Cervini, dbi-services;

def Usage():
   print("""Generates a dense Documentum-style sub-tree (i.e. a 3-level subtree with files only in the last one) with 255 x 256 sub-directories and 256 x 256 x 100 empty files under a given sub-directory;
Randomization of number of subdirectories and number of files at each level is possible within given limits;
This is useful to test different tree walking algorithms.
Created directories and files under the root directory have hexadecimal names, i.e. 00, 01, ... ff.
Usage:
   make-tree.py [-h|--help] | [-d|--dry-run] | [-r|--root-dir ]
A dry-run will just print to stdout the command for the nodes to be created instead of actually creating them, which is useful to process them later by some more efficient parallelizing tool;
Example:
   make-tree.py --root-dir "dmtest/content_storage01/0000c35c/80"
If a forest is needed, just invoke this program in a shell loop, e.g.:
   for i in 80 81 82 83 84 85; do
      make-tree.py --root-dir "dmtest/content_storage01/0000c35c/${i}" &
   done
This command will start 6 O/S python processes each creating a subtree named 80 .. 85 under the root directory.
The created 6-tree forest will have the following layout:
dmtest/content_storage01/0000c35c                                                d
   /80                                                                           d
      /00                                                                        d
         /00                                                                     d
            /00                                                                  f
            /01                                                                  f
            ...                                                                ...
            /63                                                                  f
         /01
            /00
            /01
            ...
            /63
         ...
         /ff
            /00
            /01
            ...
            /63
      /01
         /00
            /00
            /01
            ...
            /63
         /01
            /00
            /01
            ...
            /63
         ...
         /ff
            /00
            /01
            ...
            /63
      ...
      /ff
         /00
            /00
            /01
            ...
            /63
         /01
            /00
            /01
            ...
            /63
         ...
         /ff
            /00
            /01
            ...
            /63
   /81
   ...
   /82
   ...
   /83
   ...
   /84
   ...
   /85
   ...
It will contain 6 x 256 x 256 directories and 6 x 256 x 256 x 1 files, unless randomization is requested, see the gp.* parameters;
   """)

# this function cannot be local to make_tree(), though it is only used there, because of the following error:
#     Can't pickle local object 'make_tree..make_files'
# the error prevents it to be invoked as a callback;
# actually, functions used in processes must have been entirely defined prior their usage; this implies that functions cannot be fork processes that calls themselves;
# therefore, the master function make_tree is needed that detaches the processes that execute the functions defined earlier, make_files and make_level;
# moreover, processes in a pool are daemonic and are not allowed to fork other processes;
# we must therefore use a global pool of processes allocated in the master function;
def make_files(dir):
   if gp.bRandomFiles:
      nbFiles = random.randint(gp.minFiles, gp.maxFiles)
   else:
      nbFiles = gp.maxFiles
   for nf in range(nbFiles):
      fullPath = dir + "/" + (gp.fileFormat % nf)
      if gp.bTest:
         print(f"touch {fullPath}")
      else:
         try:
            fd = os.open(fullPath, os.O_CREAT); os.close(fd)
         except:
           traceback.print_exc()
           print("ignoring ...")
   return nbFiles

# ditto;
def make_level(dir):
   """
   create a directory level under dir;
   """
   global gp

   if gp.bRandomSubDirs:
      nbSubDirs = random.randint(gp.minSubDirs, gp.maxSubDirs)
   else:
      nbSubDirs = gp.maxSubDirs
   level_dirs = []
   for nd in range(nbSubDirs):
      subDir = dir + "/" + (gp.dirFormat % nd)
      if gp.bTest:
         print("mkdir", subDir)
      else:
         try:
            os.mkdir(subDir)
         except:
            traceback.print_exc()
            print("ignoring ...")
      level_dirs.append(subDir)
   return level_dirs

# the master function;
# it creates 2 levels of directories and then empty files under the deep-most directories;
def make_tree(root):
   global gp, stats
   sub_level_dirs = []

   # list_dirs contains a list of the values returned by parallelized calls to function make_level, i.e. a list of lists;
   # get_dirs is called as a callback by map_async at job completion;
   def get_dirs(list_dirs):
      global stats
      nonlocal sub_level_dirs
      for l in list_dirs:
         stats.nb_created_dirs += len(l)
         sub_level_dirs.extend(l)

   # nb_files contains a list of the values returned by parallelized calls to function make_files, i.e. a list of numbers;
   # get_nb_files is called as a callback by map_async at job completion;
   def get_nb_files(nb_files):
      global stats
      stats.nb_dirs_at_bottom += len(nb_files)
      stats.nb_created_files += functools.reduce(lambda x, y: x + y, nb_files)
      stats.nb_created_dirs_with_files += functools.reduce(lambda x, y: x + y, [1 if x > 0 else 0 for x in nb_files])

   # callback for error reporting;
   def print_error(error):
      print(error)

   # first directory level;
   level_dirs = make_level(root)
   stats.nb_created_dirs += len(level_dirs)

   # 2nd directory level;
   sub_level_dirs = []
   gp.pool = multiprocessing.pool.Pool(processes = gp.maxWorkers)
   gp.pool.map_async(make_level, level_dirs, len(level_dirs), callback = get_dirs, error_callback = print_error)
   gp.pool.close()
   gp.pool.join()

   # make dummy files at the bottom-most directory level;
   gp.pool = multiprocessing.pool.Pool(processes = gp.maxWorkers)
   gp.pool.map_async(make_files, sub_level_dirs, len(sub_level_dirs), callback = get_nb_files, error_callback = print_error)
   gp.pool.close()
   gp.pool.join()

# -----------------------------------------------------------------------------------------------------------
if __name__ == "__main__":
# main;

   # global parameters;
   # we use a typical idiom to create a cheap namespace;
   class gp: pass
   
   # dry run or real thing;
   gp.bTest = 0
   
   # root directory;
   gp.root = None
   try:
       (opts, args) = getopt.getopt(sys.argv[1:], "hdr:", ["help", "dry-run", "root-dir="])
   except getopt.GetoptError:
      print("Illegal option")
      print("./make-tree.py -h|--help | [-d|--dry-run] | [-r|--root-dir ]")
      sys.exit(1)
   for opt, arg in opts:
      if opt in ("-h", "--help"):
         Usage()
         sys.exit()
      elif opt in ("-d", "--dry-run"):
         gp.bTest = 1
      elif opt in ("-r", "--root-dir"):
         gp.root = arg
   if None == gp.root:
      print("Error: root_dir must be specified")
      print("Use -h or --help for help")
      sys.exit()

   # settings for Documentum;
   # nb sub-directories below the "80";
   gp.maxLevels = 2
   
   # nb tree depth levels;
   gp.maxDepth = gp.maxLevels + 1
   
   # random nb sub-directories in each level;
   # maximum is 256 for Documentum;
   gp.bRandomSubDirs = 0
   gp.minSubDirs = 200
   gp.maxSubDirs = 256
   
   # random nb files in each level;
   # maximum is 256 for Documentum;
   gp.bRandomFiles = 0
   gp.minFiles = 0
   gp.maxFiles = 1
   
   # node names' format;
   gp.dirFormat = "%02x"
   gp.fileFormat = "%02x"
   
   # maximum numbers of allowed processes in the pool; tasks will wait until some processes are available;
   # caution not to choose huge values for disk I/Os are saturated and overall performance drops to a crawl;
   gp.maxWorkers = 40
   
   # again but for the counters;
   class stats: pass
   stats.nb_dirs_at_bottom = 0
   stats.nb_created_files = 0
   stats.nb_created_dirs = 0
   stats.nb_created_dirs_with_files = 0

   # initialize random number generator;
   random.seed()
         
   startDate = datetime.datetime.now()
   print(f"started building a {gp.maxDepth}-level deep tree starting at {gp.root} at {startDate}")
   if not gp.bTest:
      try:
         os.makedirs(gp.root)
      except:
         pass
   make_tree(gp.root)
   print("{:d} created files".format(stats.nb_created_files))
   print("{:d} created dirs".format(stats.nb_created_dirs))
   print("{:d} created dirs at bottom".format(stats.nb_dirs_at_bottom))
   print("{:d} total created dirs with files".format(stats.nb_created_dirs_with_files))
   print("{:d} total created nodes".format(stats.nb_created_dirs + stats.nb_created_files))

   endDate = datetime.datetime.now()
   print(f"Ended building subtree {gp.root} at {endDate}")
   print(f"subtree {gp.root} built in {(endDate - startDate).seconds} seconds, or {time.strftime('%H:%M:%S', time.gmtime((endDate - startDate).seconds))} seconds")

No special attention as been given to the user interface and most settings are hard-coded in the script; their values are currently set to produce a dense (256 x 256 = 64K sub-directories/filestore) Documentum-style directory sub-tree each with just 1 zero-byte file. It is pointless, but possible, to have more files here since we only want to generate a set of rsync commands, one for each non empty subdirectory.
Examples of execution:
Create a dense 64K sub-tree at relative path clone/dmtest/content_storage01/0000c35a/86, with one empty file leaf in each:

./make-tree-dctm4.py --root-dir clone/dmtest/content_storage01/0000c35a/86
started building a 3-level deep tree starting at clone/dmtest/content_storage01/0000c35a/86 at 2018-12-31 18:47:20.066665
65536 created files
65792 created dirs
65536 created dirs at bottom
65536 total created dirs with files
131328 total created nodes
Ended building subtree clone/dmtest/content_storage01/0000c35a/86 at 2018-12-31 18:47:34.612075
subtree clone/dmtest/content_storage01/0000c35a/86 built in 14 seconds, or 00:00:14 seconds

Sequentially create a 6-tree forest starting at relative path clone/dmtest/content_storage01/0000c35a:

for i in 80 81 82 83 84 85; do
./make-tree-dctm4.py --root-dir clone/dmtest/content_storage01/0000c35a/${i}
done
started building a 3-level deep tree starting at clone/dmtest/content_storage01/0000c35a/80 at 2018-12-31 18:41:31.933329
65536 created files
65792 created dirs
65536 created dirs at bottom
65536 total created dirs with files
131328 total created nodes
Ended building subtree clone/dmtest/content_storage01/0000c35a/80 at 2018-12-31 18:41:39.694346
subtree clone/dmtest/content_storage01/0000c35a/80 built in 7 seconds, or 00:00:07 seconds
started building a 3-level deep tree starting at clone/dmtest/content_storage01/0000c35a/81 at 2018-12-31 18:41:39.738200
65536 created files
65792 created dirs
65536 created dirs at bottom
65536 total created dirs with files
131328 total created nodes
Ended building subtree clone/dmtest/content_storage01/0000c35a/81 at 2018-12-31 18:42:14.166057
subtree clone/dmtest/content_storage01/0000c35a/81 built in 34 seconds, or 00:00:34 seconds
...
subtree clone/dmtest/content_storage01/0000c35a/84 built in 22 seconds, or 00:00:22 seconds
started building a 3-level deep tree starting at clone/dmtest/content_storage01/0000c35a/85 at 2018-12-31 18:43:06.644111
65536 created files
65792 created dirs
65536 created dirs at bottom
65536 total created dirs with files
131328 total created nodes
Ended building subtree clone/dmtest/content_storage01/0000c35a/85 at 2018-12-31 18:43:41.527459
subtree clone/dmtest/content_storage01/0000c35a/85 built in 34 seconds, or 00:00:34 seconds

So, the test creation script is quite quick with the current 40 concurrent workers. Depending on your hardware, be careful with that value because the target disk can easily be saturated and stop responding, especially if each tree of a forest is created concurrently.
Just out of curiosity, how long would it take to ls to navigate this newly created forest ?

# clear the disk cache first;
sudo sysctl vm.drop_caches=3
vm.drop_caches = 3
 
time ls -1R clone/dmtest/content_storage01/0000c35a/* | wc -l
1840397
real 2m27,959s
user 0m4,291s
sys 0m19,587s
 
# again without clearing the disk cache;
time ls -1R clone/dmtest/content_storage01/0000c35a/* | wc -l
1840397
real 0m2,791s
user 0m0,941s
sys 0m1,950s

ls is very fast because there is just one leaf file in each sub-tree; it would be whole different story with well filled terminal sub-directories. Also, the cache has a tremendous speed up effect and the timed tests will always take care to clear them before a new run. The command above is very effective for this purpose.

Walking the trees

As written above, the main task before generating the rsync commands is to reach the terminal sub-directories. Let’s see if the obvious “find” command could be used at first, e.g.:

loc_path=/home/documentum/data/dmtest/./content_storage_01
find ${loc_path} -type d | gawk -v FS='/' -v max_level=11 '{if (max_level == NF) print}'
/home/documentum/data/dmtest/./content_storage_01/0000c350/80/00/00
/home/documentum/data/dmtest/./content_storage_01/0000c350/80/00/01
...
/home/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0b
...

find stopped at the last subdirectory, just before the files, as requested.
Can we get rid of the extra-process used by the gawk filter ? Let’s try this:

find ${loc_path} -mindepth 4 -maxdepth 4 -type d
/home/documentum/data/dmtest/./content_storage_01/0000c350/80/00/00
/home/documentum/data/dmtest/./content_storage_01/0000c350/80/00/01
...
/home/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0b
...

We can, good. The -mindepth and -maxdepth parameters, and the -type filter, let us jump directly to the directory level of interest, which is exactly what we want.

The “find” command is very fast; e.g. on the test forest above:

time find ${loc_path} -mindepth 4 -maxdepth 4 -type d | wc -l
458752
 
real 0m10,423s
user 0m0,536s
sys 0m2,450s

10 seconds for the forest’s 458’000 terminal directories, with disk cache emptied beforehand, impressing. If those directories were completely filled, they would contain about 117 millions files, a relatively beefy repository. Thus, find is a valuable tool, also because it is directed to stop before reaching the files. Finally, Documentum’s unusual file layout does not look so weird now, does it ? Let’s therefore use find to generate the rsync commands on the terminal sub-directories it returns:

find $loc_path -mindepth 4 -maxdepth 4 -type d | xargs -i echo "rsync -avzR {} dmadmin@${dest_machine}:{}" > migr

Example of execution:

find $loc_path -mindepth 4 -maxdepth 4 -type d | xargs -i echo "rsync -avzR {} dmadmin@new_host:/some/other/dir/dmtest"
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/ea dmadmin@new_host:/some/other/dir/dmtest
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/5e dmadmin@new_host:/some/other/dir/dmtest
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/aa dmadmin@new_host:/some/other/dir/dmtest
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/cc dmadmin@new_host:/some/other/dir/dmtest
...

Subsequently, the commands in migr could be executed in parallel N at a time, with N a reasonable number that won’t hog the infrastructure.
The same gawk script showed in part I could be used here:

find $loc_path -mindepth 4 -maxdepth 4 -type d | gawk -v nb_rsync=10 -v dest=dmadmin@new_machine:/some/other/place/dmtest 'BEGIN {
print "\#!/bin/bash"
}
{
printf("rsync -avzR %s %s &\n", $0, dest)
if (0 == ++nb_dirs % nb_rsync)
print "wait"
}
END {
if (0 != nb_dirs % nb_rsync)
print "wait"
}' > migr.sh

Output:

rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/ea dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/5e dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/aa dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/cc dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/e5 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/bd dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/1d dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/61 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/39 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/75 dmadmin@new_machine:/some/other/place/dmtest &
wait
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/6d dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/d2 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/8c dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/a1 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/84 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/b8 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/a4 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/27 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/fe dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/e6 dmadmin@new_machine:/some/other/place/dmtest &
wait
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/4f dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/84/ea/82 dmadmin@new_machine:/some/other/place/dmtest &
...
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/86/95/92 dmadmin@new_machine:/some/other/place/dmtest &
wait
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/86/95/20 dmadmin@new_machine:/some/other/place/dmtest &
rsync -avzR /home/documentum/data/dmtest/./content_storage01/0000c35a/86/95/95 dmadmin@new_machine:/some/other/place/dmtest &
wait
 
chmod +x migr.sh
nohup ./migr.sh &

The good thing with rsync in archive mode is that the commands can be interrupted anytime; once relaunched, they will quickly resume the transfers where they left since they work incrementally.
Here, no SQL statements to correct the dm_locations, if needed, because we don’t connect to the repository to get the necessary information. It is not a big deal to produce them in case of need, however, just don’t forget.

Feeling uncomfortable because of the many produced rsync commands ? As in part I, just tell find to stop one level above the terminal sub-directory using “-mindepth 3 -maxdepth 3″ at the cost of some speed decrease, though.

Any faster find alternative ?

As fast as find is when walking a docbase’s directories with lots of filestores, it can still take a long time despite the depth and type limits. Like we asked before for rsync, is there any way to speed it up by running several find commands concurrently on their own directory partition ? Such a find could be launched on sub-directory /home/dmadmin/data/dmtest/filestore_1, and others on /home/dmadmin/data/dmtest/filestore_i, all simultaneously. E.g.:

slice=0; for fs in dmtest/./*; do
(( slice += 1 ))
(
find $fs -mindepth 4 -maxdepth 4 -type d | gawk -v nb_rsync=10 -v dest=dmadmin@new_machine:/some/other/place/dmtest 'BEGIN {
print "\#!/bin/bash"
}
{
printf("rsync -avzR %s %s &\n", $0, dest)
if (0 == ++nb_dirs % nb_rsync)
print "wait"
}
END {
if (0 != nb_dirs % nb_rsync)
print "wait"
}' > migr${slice}.sh
chmod +x migr${slice}.sh
./migr${slice}.sh
) &
done

But in such approach, the degree of parallelism is limited by the number of sub-directories to explore (i.e. here, the number of filestores in the repository). What about the script below instead ?

#! /usr/bin/python3
import datetime
import time
import sys
import os
import multiprocessing
import multiprocessing.pool
import getopt

# walks a Documentum tree at a given filesystem location using multi-processes for speed;
# C. Cervini, dbi-services.com, 12/2018;

def Usage():
   print("""Walks a sub-tree and prints the files' full path;
Usage:
walk-tree.py [-h|--help] | [-p |--print] [-r|--root-dir ] [-v|--verbose] [-m|--max_level]
-p |--print for outputting the files found; default action is do nothing, which is handy for just having a count of objects;
-r|--root-dir is the starting directory to explore;
-v|--verbose for outputting timing and count information; off by default;
-m|--max_level stops recursion at the max_level-th tree level, not included; like find's -maxdepth parameter;
Example:
walk-tree.py --print --root-dir ./dmtest/content_storage01/0000c35d/80
walk-tree.py --verbose --root-dir ./dmtest/content_storage01/0000c35d/80
""")

# this function cannot be local to do_tree(), though it is only used there, because of the following error:
#     Can't pickle local object 'do_tree..do_level'
# the error prevents it to be invoked as a local callback;
# actually, functions used in processes must have been entirely defined prior to their usage; this implies that functions cannot fork processes that calls themselves;
# therefore, the master function do_tree is needed that detaches the processes that execute the functions defined earlier, do_level here;
# moreover, processes in a pool are daemonic and are not allowed to fork other processes;
# we must therefore use a global pool of processes allocated in the master function;
def do_level(dir):
   """
   lists the sub-directores and files under dir;
   """
   global gp
   result = {'nb_files': 0, 'nb_dirs': 0, 'level_dirs': []}
   if gp.max_level > 0 and len() - gp.root_level > gp.max_level:
      #print("too deep, returning")
      if gp.bPrint:
         print(dir)
      return result
   for node in os.listdir(dir):
      fullpath = os.path.join(dir, node)
      if os.path.isdir(fullpath):
         result['nb_dirs'] += 1
         result['level_dirs'].append(fullpath)
      elif os.path.isfile(fullpath):
         if gp.bPrint:
            print(fullpath)
         result['nb_files'] += 1
   return result

# the master function;
def do_tree(root):
   global gp, stats
   sub_level_dirs = []

   # list_dirs contains a list of the values returned by parallelized calls to function do_level, i.e. a list of dictionaries;
   # get_dirs is invoked as a callback by map_async once all the processes in the pool have terminated executing do_level() and returned their result;
   def get_dirs(list_dirs):
      global stats
      nonlocal sub_level_dirs
      for l in list_dirs:
         stats.nb_dirs += len(l['level_dirs'])
         stats.nb_files += l['nb_files']
         stats.nb_dirs_with_files += (1 if l['nb_files'] > 0 else 0)
         sub_level_dirs.extend(l['level_dirs'])

   # callback for error reporting;
   def print_error(error):
      print(error)

   # first directory level;
   level_data = do_level(root)
   stats.nb_files += level_data['nb_files']
   stats.nb_dirs += len(level_data['level_dirs'])
   stats.nb_dirs_with_files += (1 if level_data['nb_files'] > 0 else 0)

   # all the other directory sub-levels;
   while level_data['nb_dirs'] > 0:
      sub_level_dirs = []
      gp.pool = multiprocessing.pool.Pool(processes = gp.maxWorkers)
      gp.pool.map_async(do_level, level_data['level_dirs'], len(level_data['level_dirs']), callback = get_dirs, error_callback = print_error)
      gp.pool.close()
      gp.pool.join()
      level_data['nb_files'] = 0
      level_data['nb_dirs'] = len(sub_level_dirs) 
      level_data['level_dirs'] = sub_level_dirs

# -----------------------------------------------------------------------------------------------------------
if __name__ == "__main__":
   # main;
   # global parameters;
   # we use a typical idiom to create a cheap but effective namespace for the global variables used as execution parameters;
   class gp: pass

   # root directory;
   gp.root = None
   gp.bPrint = False
   gp.bVerbose = False
   gp.root_level = 0
   gp.max_level = 0
   try:
       (opts, args) = getopt.getopt(sys.argv[1:], "hpr:m:v", ["help", "print", "root-dir=", "verbose", "max_level="])
   except getopt.GetoptError:
      print("Illegal option")
      print("./walk-tree.py -h|--help | [-p | --print] [-r|--root-dir ] [-v|--verbose] [-m|--max_level]")
      sys.exit(1)
   for opt, arg in opts:
      if opt in ("-h", "--help"):
         Usage()
         sys.exit()
      elif opt in ("-p", "--print"):
         gp.bPrint = True
      elif opt in ("-r", "--root-dir"):
         gp.root = arg
      elif opt in ("-v", "--verbose"):
         gp.bVerbose = True
      elif opt in ("-m", "--max_level"):
         try:
            gp.max_level = int(arg)
         except:
            print("invalid value for max_level")
            sys.exit()
   if None == gp.root:
      print("Error: root_dir must be specified")
      print("Use -h or --help for help")
      sys.exit()
   gp.root_level = len()

   # maximum numbers of allowed processes; tasks will wait until some processes are available;
   # caution not to choose huge values for disk I/Os are saturated and overall performance drops to a crawl;
   gp.maxWorkers = 50

   # again but for the counters;
   class stats: pass
   stats.nb_files = 0
   stats.nb_dirs = 0
   stats.nb_dirs_with_files = 0

   startDate = datetime.datetime.now()
   if gp.bVerbose:
      print(f"started walking {gp.root} at {startDate}")
   do_tree(gp.root)
   endDate = datetime.datetime.now()
   if gp.bVerbose:
      print("{:d} found files".format(stats.nb_files))
      print("{:d} found dirs".format(stats.nb_dirs))
      print("{:d} total found nodes".format(stats.nb_dirs + stats.nb_files))
      print("{:d} total found dirs with files".format(stats.nb_dirs_with_files))
      print(f"Ended walking subtree {gp.root} at {endDate}")
      print(f"subtree {gp.root} walked in {(endDate - startDate).seconds} seconds, or {time.strftime('%H:%M:%S', time.gmtime((endDate - startDate).seconds))} seconds")

Here, we have a pool of workers which receives tasks to explore sub-directories up to a given depth, just like find’s maxdepth. But is it any faster than a sequential find ? On my test laptop with an external USB 3.1 spinning drive, find gives the best result:

sudo sysctl vm.drop_caches=3; time find dmtest/content_storage01 -mindepth 4 -maxdepth 4 -type d | wc -l
vm.drop_caches = 3
1787628
 
real 35m19,989s
user 0m9,316s
sys 0m43,632s

The python script is very close but lags 2 minutes behind with 35 workers:

sudo sysctl vm.drop_caches=3; time ./walk-tree4.py -v --root-dir dmtest/content_storage01 -m 3
vm.drop_caches = 3
started walking dmtest/content_storage01 at 2019-01-01 17:47:39.664421
0 found files
1797049 found dirs
1797049 total found nodes
0 total found dirs with files
Ended walking subtree dmtest/content_storage01 at 2019-01-01 18:25:18.437296
subtree dmtest/content_storage01 walked in 2258 seconds, or 00:37:38 seconds
real 37m38,996s
user 0m34,138s
sys 1m11,747s

Performance gets worst when the number of concurrent workers is increased, likely because the disk is saturated. It was not designed for such intensive use in the first place. Whether with find or the python script, most of the execution time is spent waiting for the I/Os to complete, with very little time spent in user or system code. Obviously, there is little to optimize here, except switch to a faster disks sub-system, e.g. beginning with an SSD drive. Even a find’s parallel equivalent would not bring any improvement, it would only put more pressure on the disk and be counter-productive. But on real production infrastructures, with large enough disk bandwidth, if may be worth parallelizing if the volume is large, and that’s where the script can make a difference.

So, which one is better ?

Both alternatives generates more or less the same rsync commands to execute later; the only difference is the source of the information to produce those commands: the repository in the first alternative and the filesystem in the second.
The first alternative looks simpler and cleaner because it works from a repository and get its information directly from it. But if one wants to be absolutely, positively sure not to forget any content file, the second alternative is better as it works directly from the filesystem; since no queries are run for each of the contents, a lot of time is saved, even despite the required disk walking. It is ideal when an exact clone is needed, orphans, and possibly garbage, included. Its python script variant is interesting in that it can readily take advantage of the faster I/Os through an easy to set concurrency level.

Cet article Two techniques for cloning a repository filestore, part II est apparu en premier sur Blog dbi services.

Two techniques for cloning a repository filestore, part I

Fri, 2019-01-18 03:26

I must confess that my initial thought for the title was “An optimal repository filestore copy”. Optimal, really ? Relatively to what ? Which variable(s) define(s) the optimality ? Speed/time to clone ? Too dependent on the installed hardware and software, and the available resources and execution constraints. Simplicity to do it ? Too simple a method can result in a very long execution time while complexity can give a faster solution but be fragile, and vice-versa. Besides, simplicity is a relative concept; a solution may look simple to someone and cause nightmares to some others. Beauty ? I like that one but no, too fuzzy too. Finally, I settled for the present title for it is neutral and up to the point. I leave it up to the reader to judge if the techniques are optimal or simple or beautiful. I only hope that they can be useful to someone.
This article has two parts. In each, I’ll give an alternative for copying a repository’s filestores from one filesystem to another. Actually, both techniques are very similar, they just differ in the way the the content files’ path locations are determined. But let’s start.

A few simple alternatives

Cloning a Documentum repository is a well-known procedure nowadays. Globally, it implies to first create a placeholder docbase for the clone and then to copy the meta-data stored in the source database, e.g. through an export/import, usually while the docbase is stopped, plus the document contents, generally stored on disks. If a special storage peripheral is used, such as a Centera CAS or a NAS, there might be a fast, low-level way to clone to content files directly at the device level, check with the manufacturer.
If all we want is an identical copy of the whole contents’ filesystem, the command dd could be used, e.g. supposing that the source docbase and the clone docbase both use for their contents a dedicated filesystem mounted on /dev/sda1 respectively on /dev/sdb1:

sudo dd if=/dev/mapper/vol01 bs=1024K of=/dev/mapper/vol02

The clone’s filesystem /dev/mapper/vol02 could be mounted temporarily on the source docbase’s machine for the copy and later dismounted. If this is not possible, dd can be used over the network, e.g.:

# from the clone machine;
ssh root@source 'dd if=/dev/mapper/vol01 bs=1024K | gzip -1 -' | zcat | dd of=/dev/mapper/vol02
 
# from the source machine as root;
dd if=/dev/mapper/vol01 bs=1024K | gzip -1 - | ssh dmadmin@clone 'zcat | dd of=/dev/mapper/vol02'

dd, and other partition imaging utilities, perform a block by block mirror copy of the source, which is much faster than working at the file level, although it depends on the percentage of used space (if the source filesystem is almost empty, dd will spend most of its time copying unused blocks, which is useless. Here, a simple file by file copy would be more effective). If it is not possible to work at this low level, e.g. filesystems are not dedicated to repositories’ contents or the types of the filesystems differ, then a file by file copy is required. Modern disks are quite fast, especially for deep-pocket companies using SSD, and so file copy operations should be acceptably quick. A naive command such as the one below could even be used to copy the whole repository’s content (we suppose that the clone will be on the same machine):

my_docbase_root=/data/Documentum/my_docbase
dest_path=/some/other/or/identical/path/my_docbase
cp -rp ${my_docbase_root} ${dest_path}/../.

If $dest_path differs from $my_docbase_root, don’t forget to edit the dm_filestore’s dm_locations of the clone docbase accordingly.

If confidentiality is requested and/or the copy occurs across a network, scp is recommended as it also encrypts the transferred data:

scp -rp ${my_docbase_root} dmadmin@${dest_machine}:${dest_path}/../.

The venerable tar command could also be used, on-the-fly and without creating an intermediate archive file, e.g.:

( cd ${my_docbase_root}; tar cvzf - * ) | ssh dmadmin@${dest_machine} "(cd ${dest_path}/; tar xvzf - )"

Even better, the command rsync could be used as it is much more versatile and efficient (and still secure too if configured to use ssh, which is the default), especially if the copy is done live several times in advance during the days preceding the kick-off of the new docbase; such copies will be performed incrementally and will execute quickly, providing an easy way to synchronize the copy with the source. Example of use:

rsync -avz --stats ${my_docbase_root}/ dmadmin@{dest_machine}:${dest_path}

The trailing / in the source path means copy the content of ${loc_path} but not the directory itself as we assumed it already exists.
Alternatively, we can include the directory too:

rsync -avz --stats ${my_docbase_root} dmadmin@{dest_machine}:${dest_path}/../.

If we run it from the destination machine, the command changes to:

rsync -avz --stats dmadmin@source_machine:${my_docbase_root}/ ${dest_path}/.

The –stats option is handy to obtain a summary of the transferred files and the resulting performance.

Still, if the docbase is large and contains millions to hundreds of millions of documents, copying them to another machine can take some time. If rsync is used, repeated executions will just copy over modified or new documents and optionally remove the deleted ones if the archiving mode is requested (the -a option above) but the first run will take time anyway. Logically, reasonably taking advantage of the available I/O bandwidth by having several rsync running at once should reduce the time to clone the filestore, shouldn’t it ? Is it possible to apply here a divide-and-conquer technique and process each part simultaneously ? It is, and here is how.

How Documentum stores the contents on disk

Besides the sheer volume of documents and possibly the limited network and disk I/O bandwidth, one reason the copy can take a long time, independently from the tools used, is the peculiar way Documentum stores its contents on disk, with all the content files exclusively at the bottom of a 6-level deep sub-tree with the following structure (assuming $my_docbase_root has the same value as above; the letter at column 1 means d for directory and f for file):

cd $my_docbase_root
d filestore_1
d <docbase_id>, e.g. 0000c350
d 80 starts with 80 and increases by 1 up to ff, for a total of 2^7 = 128 sub-trees;
d 00 up to 16 ^ 16 = 256 directories directly below 80, from 00 to ff
d 00 again, up to 256 directories directly below, from 00 to ff, up to 64K total subdirectories at this level; let's call these innermost directories "terminal directories";
f 00[.ext] files are here, up to 256 files, from 00 to ff per terminal directory
f 01[.ext] f ...
f ff[.ext] d 01
f 00[.ext] f 01[.ext] f ...
f ff[.ext] d ...
d ff
f 00[.ext] f 01[.ext] f ...
f ff[.ext] d 01
d ...
d ff
d 81
d 00
d 01
d ...
d ff
d ...
d ff
d 00
d 01
d ...
d ff
f 00[.ext] f 01[.ext] f ...
f ff[.ext]

The content files on disk may have an optional extension and are located exclusively at the extremities of their sub-tree; there are no files in the intermediate sub-directories. Said otherwise, the files are the leaves of a filestore directory tree.
All the nodes have a 2-digit lowercase hexadecimal name, from 00 to ff, possibly with holes in the sequences when sub-directories or deleted documents have been swept by the DMClean job. With such a layout, each filestore can store up to (2^7).(2^8).(2^8).(2^8) files, i.e. 2^31 files or a bit more than 2.1 billions files. Any number of filestores can be used for virtually an “unlimited” number of content files. However, since each content object has an 16-digit hexadecimal id whose only last 8 hexadecimal digits are really distinctive (and directly map to the filesystem path of the content file, see below), a docbase can effectively contain “only” up to 16^8 files, i.e. 2^32 content files or slightly more than 4.2 billions files distributed among all the filestores. Too few ? There is hope, aside from spawning new docbases. The knowledge base note here explains how “galactic” r_object_id are allocated if more than 4 billions documents are present in a repository, so it should be possible to have literally gazillions of documents in a docbase. It is not clear though whether this galactic concept is implemented yet or whether it has ever been triggered once, so let us stay with our feet firmly on planet Earth for the time being.

It should be emphasized that such a particular layout in no way causes a performance penalty in accessing the documents’contents from within the repository because their full path can easily be computed by the content server out of their dm_content.data_ticket attribute (a signed decimal number), e.g. as shown with the one-liner:

data_ticket=-2147440384
echo $data_ticket | gawk '{printf("%x\n", $0 + 4294967296)}'
8000a900

or, more efficiently entirely in the bash shell:

printf "%x\n" $(($data_ticket + 4294967296))
8000a900

This value is now split apart by groups of 2 hex digits with a slash as separator: 80/00/a9/00
To compute the full path, the filestore’s location and the docbase id still need to be prepended to the above partial path, e.g. ${my_docbase_root}/filestore_01/0000c350/80/00/a9/00. Thus, knowing the r_object_id of a document, we can find its content file on the filesystem as shown (or, preferably, using the getpath API function) and knowing the full path of a content file makes it possible to find back the document (or documents as the same content can be shared among several documents) in the repository it belongs to. To be complete, the explanation still needs the concept of filestore and its relationship with a location but let’s stop digressing (check paragraph 3 below for a hint) and focus back to the subject at hand. We have now enough information to get us started.

As there are no files in the intermediate levels, it is necessary to walk the entire tree to reach them and start their processing, which is very time-consuming. Depending on your hardware, a ‘ls -1R’ command can takes hours to complete on a set of fairly dense sub-trees. A contrario, this is an advantage for processing through rsync because rsync is able to create all the necessary sub-path levels (aka “implied directories” in rsync lingo) if the -R|–relative option is provided, as if a “mkdir -p” were issued; thus, in order to optimally copy an entire filestore, it would be enough to rsync only the terminal directories, once identified, and the whole sub-tree would be recreated implicitly. In the illustration above, the rsync commands for those paths are:

cd ${my_docbase_root}
rsync -avzR --stats filestore_01/80/00/00 dmadmin@{dest_machine}:${dest_path}
rsync -avzR --stats filestore_01/80/00/01 dmadmin@{dest_machine}:${dest_path}
rsync -avzR --stats filestore_01/80/00/ff dmadmin@{dest_machine}:${dest_path}
rsync -avzR --stats filestore_01/ff/ff/00 dmadmin@{dest_machine}:${dest_path}

In rsync ≥ v2.6.7, it is even possible to restrict the part within the source full path that should be copied remotely, so no preliminary cd is necessary, e.g.:

rsync -avzR --stats ${my_docbase_root}/./filestore_01/80/00/00 dmadmin@{dest_machine}:${dest_path}

Note the /./ path component, it marks the start of the relative path to reproduce remotely. This command will create the directory ${dest_path}/filestore_01/80/00/00 on the remote host and copy its content there.
Path specification can be quite complicated, so use the -n|–dry-run and -v|–verbose (or even -vv for more details) options to have a peek at rsync’s actions before they are applied.

With the -R option, we get to transfer only the terminal sub-directories AND their original relative paths, efficiency and convenience !
We potentially replace millions of file by file copy commands with only up to 128 * 64K directory copy commands per filestore, which is much more concise and efficient.

However, if there are N content files to transfer, at least ⌈N/256⌉ such rsync commands will be necessary, e.g. a minimum of 3’900 commands for 1 million files, subject of course to their distribution in the sub-tree (some lesser dense terminal sub-directories can contain less than 256 files so more directories and hence commands are required). It is not documented how the content server distributes them over the sub-directories and there is no balancing job that relocates the content files in order to reduce the number of terminal directories by increasing the density of the left ones and removing the emptied ones. All this is quite sensible because, while it may matter to us, it is a non-issue to the repository.
Nevertheless, on the bright side, since a tree is by definition acyclic, rsync transfers won’t overlap and therefore can be parallelized without synchronization issues, even when intermediate “implied directories”‘s creations are requested simultaneously by 2 or more rsync commands (if rsync performs an operation equivalent to “mkdir -p”, possible errors due to race conditions during concurrent execution can be simply ignored since the operation is idempotent).

Empty terminal paths can be skipped without fear because they are not referenced in the docbase (only content files are) and hence their absence from the copy cannot introduce inconsistencies.

Of course, in the example above, those hypothetical 3900 rsync commands won’t be launched at once but in groups of some convenient value depending on the load that the machines can endure or the application’s response time degradation if the commands are executed during the normal work hours. Since we are dealing with files and possibly the network, the biggest bottleneck will be the I/Os and care should be exercised not to saturate them. When dedicated hardware such as high-speed networked NAS with SSD drives are used, this is less of a problem and more parallel rsync instances can be started, but such expensive resources are often shared across a company so that someone might still be impacted at one point. I for one remember one night as I was running one data-intensive DQL query in ten different repositories at once and users were suddenly complaining that they couldn’t work their Word documents any more because response time fell down to a crawl. How was that possible ? What was the relationship between DQL queries in several repositories and a desktop program ? Well, the repositories used Oracle databases whose datafiles were stored on a NAS also used as a host for networked drives mounted on desktop machines. Evidently, that configuration was somewhat sloppy but one never knows how things are configured at a large company, so be prepared for the worst and set up the transfer so that they can be easily suspended, interrupted and resumed. The good thing with rsync in archive mode is that the transfers can be resumed where they left off at a minimal cost just by relaunching the same commands with no need to compute a restarting point.

It goes without saying that setting up public key authentication or ssh connection sharing is mandatory when rsync-ing to a remote machine in order to suppress the thousands of authentication requests that will pop up during the batch execution.

But up to 128 * 64K rsync commands per filestore : isn’t that a bit too much ?

rsync performs mostly I/Os operations, reading and writing the filesystems, possibly across a network. The time spent in those slow operations (mostly waiting for them to complete, actually) by far outweighs the time spent launching the rsync processes and executing user or system code, especially if the terminal sub-directories are densely filled with large files. Moreover, if the DMClean jobs has been run ahead of time, this 128 * 64K figure is a maximum, it is only reached if all of the terminal sub-directories are not empty.
Still, rsync has some cleverness of its own in processing the files to copy so why not let it do its job ? Is it possible to reduce their number ? Of course, by just stopping one level before the terminal sub-directories, at their parents’ level. From there, up to 128 * 256 rsync commands are necessary, down from 128 * 64K commands. rsync would then explore itself the up to 64K terminal directories below, hopefully more efficiently than when explicitly told so. For sparse sub-directories or small docbases, this could be more efficient. If so, what would be the cut-off point ? This is a complex question depending on so many factors that there is no other way to answer it than to experiment with several situations. A few informal tests show that copying terminal sub-directories with up to 64K rsync commands is about 40% faster than copying their parent sub-directories. If optimality is defined as speed, then the “64k” variant is the best; if it is defined as compactness, then the “256” variant is the best. One explanation for this could be that the finer and simpler the tasks to perform, the quicker they terminate and free up processes to be reused. Or maybe rsync is overwhelmed by the up to 64K sub-directories to explore and is not so good at that and needs some help. The scripts in this article allow experimenting with the “256” variant.

To summarize up to this point, we will address the cost of navigating the special Documentum sub-tree layout by walking the location sub-trees up to the last directory level (or up to 2 levels above if so requested) and generate efficient rsync commands that can easily be parallelized. But before we start, how about asking the repository about its contents ? As it keeps track of it, wouldn’t this alternative be much easier and faster than navigating complex directory sub-trees ? Let’see.

Alternate solution: just ask the repository !

Since a repository obviously knows where its content files are stored on disks, it makes sense to get this information directly from it. In order to be sure to include all the possible renditions as well, we should query dmr_content instead of dm_document(all) (note that the DQL function MFILE_URL() returns those too, so that a “select MFILE_URL(”) from dm_document(all)” could also be used here). Also, unless the dmDMClean job is run beforehand, dmr_content includes orphan contents as well, so this point must be clarified ahead. Anyway, by querying dmr_content we are sure not to omit any content, orphans or not.
The short python/bash-like pseudo-code shows how we could do it:

for each filestore in the repository:
(
   for each content in the filestore:
      get its path, discard its filename;
      add the path to the set of paths to transfer;
   for each path in the set of paths:
      generate an rsync -avzR --stat command;
) &

Line 4 just gets the terminal sub-directories, while line 5 ensures that they are unique in order to avoid rsync-ing the same path multiple times. We use sets here to guarantee distinct terminal path values (set elements are unique in the set).
Line 7 outputs the rsync commands for the terminal sub-directories and the destination.
Even though all filestores are processed concurrently, there could be millions of contents in each filestore and such queries could take forever. However, if we run several such queries in parallel, each working on its own partition (i.e. a non-overlapping subset of the whole such that their union is equal to the whole), we could considerably speed it up. Constraints such “where r_object_id like ‘%0′”, “where r_object_id like ‘%1′”, “where r_object_id like ‘%2′”, .. “where r_object_id like ‘%f'” can slice up the whole set of documents into 16 more or less equally-sized subsets (since the r_object_id is essentially a sequence, its modulo 16 or 256 distribution is uniform), which can then be worked on independently and concurrently. Constraints like “where r_object_id like ‘%00′” .. “where r_object_id like ‘%ff'” can produce 256 slices, and so on.
Here is a short python 3 program that does all this:

#!/usr/bin/env python

# 12/2018, C. Cervini, dbi-services;
 
import os
import sys
import traceback
import getopt
from datetime import datetime
import DctmAPI
import multiprocessing
import multiprocessing.pool

def Usage():
   print("""Purpose:
Connects as dmadmin/xxxx to a given repository and generates rsync commands to transfer the given filestores' contents to the given destination;
Usage:
   ./walk_docbase.py -h|--help | -r|--repository  [-f|--filestores [{,}]|all] [-d|--dest ] [-l|--level ]
Example:
   ./walk_docbase.py --docbase dmtest
will list all the filestores in docbase dmtest and exit;
   ./walk_docbase.py --docbase dmtest --filestore all --dest dmadmin@remote-host:/documentum/data/cloned_dmtest
will transfer all the filestores' content to the remote destination in /documentum/data, e.g.:
   dm_location.path = /data/dctm/dmtest/filestore_01 --> /documentum/data/cloned_dmtest/filestore_01
if dest does not contain a file path, the same path as the source is used, e.g.:
   ./walk_docbase.py --docbase dmtest --filestore filestore_01 --dest dmadmin@remote-host
will transfer the filestore_01 filestore's content to the remote destination into the same directory, e.g.:
   dm_location.path = /data/dctm/dmtest/filestore_01 --> /documentum/dctm/dmtest/filestore_01
In any case, the destination root directory, if any is given, must exist as rsync does not create it (although it creates the implied directories);
Generated statements can be dry-tested by adding the option --dry-run, e.g.
rsync -avzR --dry-run /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0c dmadmin@dmtest:/home/dctm/dmtest
Commented out SQL statements to update the dm_location.file_system_path for each dm_filestore are output if the destination path differs from the source's;
level is the starting sub-directory level that will be copied by rsync;
Allowd values for level are 0 (the default, the terminal directories level), -1 and -2;
level -1 means the sub-directory level above the terminal directories, and so on;
Practically, use 0 for better granularity and parallelization;
""")

def print_error(error):
   print(error)

def collect_results(list_paths):
   global stats
   for s in list_paths:
      stats.paths = stats.paths.union(s)

def explore_fs_slice(stmt):
   unique_paths = set()
   lev = level
   try:
      for item in DctmAPI.select_cor(session, stmt):
         fullpath = DctmAPI.select2dict(session, f"execute get_path for '{item['r_object_id']}'")
         last_sep = fullpath[0]['result'].rfind(os.sep)
         fullpath = fullpath[0]['result'][last_sep - 8 : last_sep]
         for lev in range(level, 0):
            last_sep = fullpath.rfind(os.sep)
            fullpath = fullpath[ : last_sep]
         unique_paths.add(fullpath)
   except Exception as e:
      print(e)
      traceback.print_stack()
   DctmAPI.show(f"for stmt {stmt}, unique_paths={unique_paths}")
   return unique_paths

# --------------------------------------------------------
# main;
if __name__ == "__main__":
   DctmAPI.logLevel = 0
 
   # parse the command-line parameters;
   # old-style for more flexibility is not needed here;
   repository = None
   s_filestores = None
   dest = None
   user = ""
   dest_host = ""
   dest_path = ""
   level = 0
   try:
       (opts, args) = getopt.getopt(sys.argv[1:], "hr:f:d:l:", ["help", "docbase=", "filestore=", "destination=", "level="])
   except getopt.GetoptError:
      print("Illegal option")
      print("./graph-stats.py -h|--help | [-r|--repository ] [-f|--filestores [{,}]|all] [-d|--dest ][-l|--level ]")
      sys.exit(1)
   for opt, arg in opts:
      if opt in ("-h", "--help"):
         Usage()
         sys.exit()
      elif opt in ("-r", "--repository"):
         repository = arg
      elif opt in ("-f", "--filestores"):
         s_filestores = arg
      elif opt in ("-d", "--dest"):
         dest = arg
         p_at = arg.rfind("@")
         p_colon = arg.rfind(":")
         if -1 != p_at:
            user = arg[ : p_at]
            if -1 != p_colon:
               dest_host = arg[p_at + 1 : p_colon]
               dest_path = arg[p_colon + 1 : ]
            else:
               dest_path = arg[p_at + 1 : ]
         elif -1 != p_colon:
            dest_host = arg[ : p_colon]
            dest_path = arg[p_colon + 1 : ]
         else:
            dest_path = arg
      elif opt in ("-l", "--level"):
         try:
            level = int(arg)
            if -2 > level or level > 0:
               print("raising")
               raise Exception()
         except:
            print("level must be a non positive integer inside the interval (-2,  0)")
            sys.exit()
   if None == repository:
      print("the repository is mandatory")
      Usage()
      sys.exit()
   if None == dest or "" == dest:
      if None != s_filestores:
         print("the destination is mandatory")
         Usage()
         sys.exit()
   if None == s_filestores or 'all' == s_filestores:
      # all filestores requested;
      s_filestores = "all"
      filestores = None
   else:
      filestores = s_filestores.split(",")
 
   # global parameters;
   # we use a typical idiom to create a cheap namespace;
   class gp: pass
   gp.maxWorkers = 100
   class stats: pass

   # connect to the repository;
   DctmAPI.show(f"Will connect to docbase(s): {repository} and transfer filestores [{s_filestores}] to destination {dest if dest else 'None'}")

   status = DctmAPI.dmInit()
   session = DctmAPI.connect(docbase = repository, user_name = "dmadmin", password = "dmadmin")
   if session is None:
      print("no session opened, exiting ...")
      exit(1)

   # we need the docbase id in hex format;
   gp.docbase_id = "{:08x}".format(int(DctmAPI.dmAPIGet("get,c,docbaseconfig,r_docbase_id")))

   # get the requested filestores' dm_locations;
   stmt = 'select fs.r_object_id, fs.name, fs.root, l.r_object_id as "loc_id", l.file_system_path from dm_filestore fs, dm_location l where {:s}fs.root = l.object_name'.format(f"fs.name in ({str(filestores)[1:-1]}) and " if filestores else "")
   fs_dict = DctmAPI.select2dict(session, stmt)
   DctmAPI.show(fs_dict)
   if None == dest:
      print(f"filestores in repository {repository}")
      for s in fs_dict:
         print(s['name'])
      sys.exit()

   # filestores are processed sequentially but inside each filestore, the contents are queried concurrently;
   for storage_id in fs_dict:
      print(f"# rsync statements for filestore {storage_id['name']};")
      stats.paths = set()
      stmt = f"select r_object_id from dmr_content where storage_id = '{storage_id['r_object_id']}' and r_object_id like "
      a_stmts = []
      for slice in range(16):
         a_stmts.append(stmt + "'%0{:0x}'".format(slice))
      gp.path_pool = multiprocessing.pool.Pool(processes = 16)
      gp.path_pool.map_async(explore_fs_slice, a_stmts, len(a_stmts), callback = collect_results, error_callback = print_error)
      gp.path_pool.close()
      gp.path_pool.join()
      SQL_stmts = set()
      for p in stats.paths:
         last_sep = storage_id['file_system_path'].rfind(os.sep)
         if "" == dest_path:
            dp = storage_id['file_system_path'][ : last_sep] 
         else:
            dp = dest_path
         # note the dot in the source path: relative implied directories will be created from that position; 
         print(f"rsync -avzR {storage_id['file_system_path'][ : last_sep]}/.{storage_id['file_system_path'][last_sep : ]}/{str(gp.docbase_id)}/{p} {(user + '@') if user else ''}{dest_host}{':' if dest_host else ''}{dp}")
         if storage_id['file_system_path'][ : last_sep] != dest_path:
            # dm_location.file_system_path has changed, give the SQL statements to update them in clone's schema;
            SQL_stmts.add(f"UPDATE dm_location SET file_system_path = REPLACE(file_system_path, '{storage_id['file_system_path'][ : last_sep]}', '{dest_path}') WHERE r_object_id = '{storage_id['loc_id']}';")
      # commented out SQL statements to run before starting the repository clone;
      for stmt in SQL_stmts:
         print(f"# {stmt}")

   status = DctmAPI.disconnect(session)
   if not status:
      print("error while  disconnecting")

On line 10, the module DctmAPI is imported; such module was presented in a previous article (see here) but I include an updated version at the end of the present article.
Note the call to DctmAPI.select_cor() on line 51; this is a special version of DctmAPI.select2dict() where _cor stands for coroutine; actually, in python, it is called a generator but it looks very much like a coroutine from other programming languages. Its main interest is to separate navigating through the result set from consuming the returned data, for more clarity; also, since the the data are consumed one row at a time, there is no need to read them all into memory at once and pass them to the caller, which is especially efficient here where we can potentially have millions of documents. DctmAPI.select2dict() is still available and used when the expected result set is very limited, as for the list of dm_locations on line 154. By the way, DctmAPI.select2dict() invokes DctmAPI.select_cor() from within a list constructor, so they share that part of the code.
On line 171, function map_async is used to start 16 concurrent calls to explore_fs_slice on line 47 per filestore (the r_object_id % 16 expressed in DQL as r_object_id like ‘%0′ .. ‘%f’), each in its own process. That function repeatedly gets an object_id from the coroutine above and calls the administrative method get_path on it (we could query the dm_content.data_ticket and compute ourselves the file path but would it be any faster ?); the function returns a set of unique paths for its slice of ids. map_async then waits until all the processes terminate. Their result is collected by the callback collect_results starting on line 42; its parameter, list_paths, is a list of sets received from map_async (which received the sets from the terminating concurrent invocation of explore_fs_slice and put them in a list) that are further made unique by union-ing them into a global set. Starting on line 175, this set is iterated to generate the rsync commands.
Example of execution:

./walk-docbase.py -r dmtest -f all -d dmadmin@dmtest:/home/dctm/dmtest | tee clone-content
# rsync statements for filestore filestore_01;
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0c dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/02 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/06 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0b dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/04 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/09 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/01 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/03 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/07 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/00 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/05 dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0a dmadmin@dmtest:/home/dctm/dmtest
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/08 dmadmin@dmtest:/home/dctm/dmtest
# UPDATE dm_location SET file_system_path = REPLACE(file_system_path, '/home/dmadmin/documentum/data/dmtest', '/home/dctm/dmtest') WHERE r_object_id = '3a00c3508000013f';
# rsync statements for filestore thumbnail_store_01;
# rsync statements for filestore streaming_store_01;
# rsync statements for filestore replicate_temp_store;
# rsync statements for filestore replica_filestore_01;

This execution generated the rsync commands to copy all the dmtest repository’s filestores to the remote host dmtest’s new location /home/dctm/dmtest. As the filestores’ dm_location has changed, an SQL statement (to be taken as an example because it is for an Oracle RDBMS; the syntax may differ in another RDBMS) has been generated too to accommodate the new path. We do this in SQL because the docbase clone will still be down at this time and the change must be done at the database level.
The other default filestores in the test docbase are empty and so no rsync are necessary for them; normally, the placeholder docbase already has initialized their sub-tree.
Those rsync commands could be executed in parallel, say, 10 at a time, by launching them in the background with “wait” commands inserted in between, like this:

./walk-docbase.py -r dmtest -f all -d dmadmin@dmtest:/home/dctm/dmtest | gawk -v nb_rsync=10 'BEGIN {
print "\#!/bin/bash"
nb_dirs = 0
}
{
print $0 " &"
if (!$0 || match($0, /^#/)) next
if (0 == ++nb_dirs % nb_rsync)
print "wait"
}
END {
if (0 != nb_dirs % nb_rsync)
print "wait"
}' | tee migr.sh
#!/bin/bash
# rsync statements for filestore filestore_01; &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/08 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/02 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/04 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/03 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/06 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/01 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0a dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/07 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0c dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/05 dmadmin@dmtest:/home/dctm/dmtest &
wait
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/00 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/09 dmadmin@dmtest:/home/dctm/dmtest &
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00/0b dmadmin@dmtest:/home/dctm/dmtest &
# UPDATE dm_location SET file_system_path = REPLACE(file_system_path, '/home/dmadmin/documentum/data/dmtest', '/home/dctm/dmtest') WHERE r_object_id = '3a00c3508000013f';
# rsync statements for filestore thumbnail_store_01;
# rsync statements for filestore streaming_store_01;
# rsync statements for filestore replicate_temp_store;
# rsync statements for filestore replica_filestore_01;
wait

Even though there may be a count difference of up to 255 files between some rsync commands, they should complete roughly at the same time so that nb_rsync commands should be running at any time. If not, i.e. if the transfers frequently wait for a few long running rsync to complete (it could happen with huge files), it may be worth using a task manager that makes sure the requested parallelism degree is respected at any one time throughout the whole execution.
Let’s now make the generated script executable and launch it:

chmod +x migr.sh
time ./migr.sh

The parameter level lets one choose the levels of the sub-directories that rsync will copy, 0 (the default) for terminal sub-directories, -1 for the level right above them and -2 for the level above those. As discussed, the lesser the level, the lesser rsync commands are necessary, e.g. up to 128 * 64K for level = 0, up to 128 * 256 for level = -1 and up to 128 for level = -2.
Example of execution:

./walk-docbase.py -r dmtest -d dd --level -1
# rsync statements for filestore filestore_01;
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80/00 dmadmin@dmtest:/home/dctm/dmtest &
# UPDATE dm_location SET file_system_path = REPLACE(file_system_path, '/home/dmadmin/documentum/data/dmtest', 'dd') WHERE r_object_id = '3a00c3508000013f';
# rsync statements for filestore thumbnail_store_01;
# rsync statements for filestore streaming_store_01;
# rsync statements for filestore replicate_temp_store;
# rsync statements for filestore replica_filestore_01;

And also:

./walk-docbase.py -r dmtest -d dd --level -2
# rsync statements for filestore filestore_01;
rsync -avzR /home/dmadmin/documentum/data/dmtest/./content_storage_01/0000c350/80 dmadmin@dmtest:/home/dctm/dmtest &
# UPDATE dm_location SET file_system_path = REPLACE(file_system_path, '/home/dmadmin/documentum/data/dmtest', 'dd') WHERE r_object_id = '3a00c3508000013f';
# rsync statements for filestore thumbnail_store_01;
# rsync statements for filestore streaming_store_01;
# rsync statements for filestore replicate_temp_store;
# rsync statements for filestore replica_filestore_01;

So, this first solution looks quite simple eventhough it initially puts a little, yet tolerable stress on the docbase. The python script connects to the repository and generates the required rsync commands (with an user-selectable compactness level) and a gawk filter prepares an executable with those statements launched in parallel N (user-selectable) at a time.
Performance-wise, its not so good because all the contents must be queried for their full path, and that’s a lot of queries for a large repository.

All this being said, let’s see now if a direct and faster, out of the repository filesystem copy procedure can be devised. Please, follow the rest of this article in part II. The next paragraph just lists the latest version of the module DctmAPI.py.

DctmAPI.py revisited
"""
This module is a python - Documentum binding based on ctypes;
requires libdmcl40.so/libdmcl.so to be reachable through LD_LIBRARY_PATH;
C. Cervini - dbi-services.com - december 2018

The binding works as-is for both python2 amd python3; no recompilation required; that's the good thing with ctypes compared to e.g. distutils/SWIG;
Under a 32-bit O/S, it must use the libdmcl40.so, whereas under a 64-bit Linux it must use the java backed one, libdmcl.so;

For compatibility with python3 (where strings are now unicode ones and no longer arrays of bytes, ctypes strings parameters are always converted to unicode, either by prefixing them
with a b if litteral or by invoking their encode('ascii', 'ignore') method; to get back to text from bytes, b.decode() is used;these works in python2 as well as in python3 so the source is compatible with these two versions of the language;
"""

import os
import ctypes
import sys, traceback

# use foreign C library;
# use this library in eContent server = v6.x, 64-bit Linux;
dmlib = 'libdmcl.so'

dm = 0

class getOutOfHere(Exception):
   pass

def show(mesg, beg_sep = False, end_sep = False):
   "displays the message msg if allowed"
   if logLevel > 0:
      print(("\n" if beg_sep else "") + repr(mesg), ("\n" if end_sep else ""))

def dmInit():
   """
   initializes the Documentum part;
   returns True if successfull, False otherwise;
   dmAPI* are global aliases on their respective dm.dmAPI* for some syntaxic sugar;
   since they already have an implicit namespace through their dm prefix, dm.dmAPI* would be redundant so let's get rid of it;
   returns True if no error, False otherwise;
   """

   show("in dmInit()")
   global dm

   try:
      dm = ctypes.cdll.LoadLibrary(dmlib);  dm.restype = ctypes.c_char_p
      show("dm=" + str(dm) + " after loading library " + dmlib)
      dm.dmAPIInit.restype    = ctypes.c_int;
      dm.dmAPIDeInit.restype  = ctypes.c_int;
      dm.dmAPIGet.restype     = ctypes.c_char_p;      dm.dmAPIGet.argtypes  = [ctypes.c_char_p]
      dm.dmAPISet.restype     = ctypes.c_int;         dm.dmAPISet.argtypes  = [ctypes.c_char_p, ctypes.c_char_p]
      dm.dmAPIExec.restype    = ctypes.c_int;         dm.dmAPIExec.argtypes = [ctypes.c_char_p]
      status  = dm.dmAPIInit()
   except Exception as e:
      print("exception in dminit(): ")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show("exiting dmInit()")
      return True if 0 != status else False
   
def dmAPIDeInit():
   """
   releases the memory structures in documentum's library;
   returns True if no error, False otherwise;
   """
   status = dm.dmAPIDeInit()
   return True if 0 != status else False
   
def dmAPIGet(s):
   """
   passes the string s to dmAPIGet() method;
   returns a non-empty string if OK, None otherwise;
   """
   value = dm.dmAPIGet(s.encode('ascii', 'ignore'))
   return value.decode() if value is not None else None

def dmAPISet(s, value):
   """
   passes the string s to dmAPISet() method;
   returns TRUE if OK, False otherwise;
   """
   status = dm.dmAPISet(s.encode('ascii', 'ignore'), value.encode('ascii', 'ignore'))
   return True if 0 != status else False

def dmAPIExec(stmt):
   """
   passes the string stmt to dmAPIExec() method;
   returns TRUE if OK, False otherwise;
   """
   status = dm.dmAPIExec(stmt.encode('ascii', 'ignore'))
   return True if 0 != status else False

def connect(docbase, user_name, password):
   """
   connects to given docbase as user_name/password;
   returns a session id if OK, None otherwise
   """
   show("in connect(), docbase = " + docbase + ", user_name = " + user_name + ", password = " + password) 
   try:
      session = dmAPIGet("connect," + docbase + "," + user_name + "," + password)
      if session is None or not session:
         raise(getOutOfHere)
      else:
         show("successful session " + session)
         show(dmAPIGet("getmessage," + session).rstrip())
   except getOutOfHere:
      print("unsuccessful connection to docbase " + docbase + " as user " + user_name)
      session = None
   except Exception as e:
      print("Exception in connect():")
      print(e)
      traceback.print_stack()
      session = None
   finally:
      show("exiting connect()")
      return session

def execute(session, dql_stmt):
   """
   execute non-SELECT DQL statements;
   returns TRUE if OK, False otherwise;
   """
   show("in execute(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)
      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)
      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in execute():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show(dmAPIGet("getmessage," + session).rstrip())
      show("exiting execute()")
      return status

def select2dict(session, dql_stmt, attr_name = None):
   """
   execute the DQL SELECT statement passed in dql_stmt and returns an array of dictionaries (one per row) into result;
   attributes_names is the list of extracted attributes (the ones in SELECT ..., as interpreted by the server); if not None, attribute namea are appended to it, otherwise nothing is returned;
   """
   show("in select2dict(), dql_stmt=" + dql_stmt)
   return list(select_cor(session, dql_stmt, attr_name))

def select_cor(session, dql_stmt, attr_name = None):
   """
   execute the DQL SELECT statement passed in dql_stmt and return one row at a time;
   coroutine version;
   if the optional attributes_names is not None, it contains an appended list of attributes returned by the result set, otherwise no names are returned;
   return True if OK, False otherwise;
   """
   show("in select_cor(), dql_stmt=" + dql_stmt)

   status = False
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)

      # iterate through the result set;
      row_counter = 0
      if None == attr_name:
         attr_name = []
      width = {}
      while dmAPIExec("next," + session + "," + query_id):
         result = {}
         nb_attrs = dmAPIGet("count," + session + "," + query_id)
         if nb_attrs is None:
            show("Error retrieving the count of returned attributes: " + dmAPIGet("getmessage," + session))
            raise(getOutOfHere)
         nb_attrs = int(nb_attrs) 
         for i in range(nb_attrs):
            if 0 == row_counter:
               # get the attributes' names only once for the whole query;
               value = dmAPIGet("get," + session + "," + query_id + ",_names[" + str(i) + "]")
               if value is None:
                  show("error while getting the attribute name at position " + str(i) + ": " + dmAPIGet("getmessage," + session))
                  raise(getOutOfHere)
               attr_name.append(value)
               if value in width:
                  width[value] = max(width[attr_name[i]], len(value))
               else:
                  width[value] = len(value)

            is_repeating = dmAPIGet("repeating," + session + "," + query_id + "," + attr_name[i])
            if is_repeating is None:
               show("error while getting the arity of attribute " + attr_name[i] + ": " + dmAPIGet("getmessage," + session))
               raise(getOutOfHere)
            is_repeating = int(is_repeating)

            if 1 == is_repeating:
               # multi-valued attributes;
               result[attr_name[i]] = []
               count = dmAPIGet("values," + session + "," + query_id + "," + attr_name[i])
               if count is None:
                  show("error while getting the arity of attribute " + attr_name[i] + ": " + dmAPIGet("getmessage," + session))
                  raise(getOutOfHere)
               count = int(count)

               for j in range(count):
                  value = dmAPIGet("get," + session + "," + query_id + "," + attr_name[i] + "[" + str(j) + "]")
                  if value is None:
                     value = "null"
                  #result[row_counter] [attr_name[i]].append(value)
                  result[attr_name[i]].append(value)
            else:
               # mono-valued attributes;
               value = dmAPIGet("get," + session + "," + query_id + "," + attr_name[i])
               if value is None:
                  value = "null"
               width[attr_name[i]] = len(attr_name[i])
               result[attr_name[i]] = value
         if 0 == row_counter:
            show(attr_name.append)
         yield result
         row_counter += 1
      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         show("Error closing the query collection: " + dmAPIGet("getmessage," + session))
         raise(getOutOfHere)

      status = True

   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in select2dict():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      return status

def select(session, dql_stmt, attribute_names):
   """
   execute the DQL SELECT statement passed in dql_stmt and outputs the result to stdout;
   attributes_names is a list of attributes to extract from the result set;
   return True if OK, False otherwise;
   """
   show("in select(), dql_stmt=" + dql_stmt)
   try:
      query_id = dmAPIGet("query," + session + "," + dql_stmt)
      if query_id is None:
         raise(getOutOfHere)

      s = ""
      for attr in attribute_names:
         s += "[" + attr + "]\t"
      print(s)
      resp_cntr = 0
      while dmAPIExec("next," + session + "," + query_id):
         s = ""
         for attr in attribute_names:
            value = dmAPIGet("get," + session + "," + query_id + "," + attr)
            if "r_object_id" == attr and value is None:
               raise(getOutOfHere)
            s += "[" + (value if value else "None") + "]\t"
            show(str(resp_cntr) + ": " + s)
         resp_cntr += 1
      show(str(resp_cntr) + " rows iterated")

      err_flag = dmAPIExec("close," + session + "," + query_id)
      if not err_flag:
         raise(getOutOfHere)

      status = True
   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
      status = False
   except Exception as e:
      print("Exception in select():")
      print(e)
      traceback.print_stack()
      print(resp_cntr); print(attr); print(s); print("[" + value + "]")
      status = False
   finally:
      show("exiting select()")
      return status

def walk_group(session, root_group, level, result):
   """
   recursively walk a group hierarchy with root_group as top parent;
   """

   try:
      root_group_id = dmAPIGet("retrieve," + session + ",dm_group where group_name = '" + root_group + "'")
      if 0 == level:
         if root_group_id is None:
            show("Cannot retrieve group [" + root_group + "]:" + dmAPIGet("getmessage," + session))
            raise(getOutOfHere)
      result[root_group] = {}

      count = dmAPIGet("values," + session + "," + root_group_id + ",groups_names")
      if "" == count:
         show("error while getting the arity of attribute groups_names: " + dmAPIGet("getmessage," + session))
         raise(getOutOfHere)
      count = int(count)

      for j in range(count):
         value = dmAPIGet("get," + session + "," + root_group_id + ",groups_names[" + str(j) + "]")
         if value is not None:
            walk_group(session, value, level + 1, result[root_group])

   except getOutOfHere:
      show(dmAPIGet("getmessage," + session).rstrip())
   except Exception as e:
      print("Exception in walk_group():")
      print(e)
      traceback.print_stack()

def disconnect(session):
   """
   closes the given session;
   returns True if no error, False otherwise;
   """
   show("in disconnect()")
   try:
      status = dmAPIExec("disconnect," + session)
   except Exception as e:
      print("Exception in disconnect():")
      print(e)
      traceback.print_stack()
      status = False
   finally:
      show("exiting disconnect()")
      return status

Highlighted are the main changed lines that added a generator-based select() function yielding a dictionary for each row from the result set and changed select2dict() to use it.

Cet article Two techniques for cloning a repository filestore, part I est apparu en premier sur Blog dbi services.

Deploying SQL Server 2019 AGs on K8s with helm charts

Thu, 2019-01-17 07:00

This write-up follows my first article about helm chart with SQL Server. This time, I would like to cover the availability groups topic and how to deploy them with helm charts.

blog 151 - 0 - banner

In fact, to go through this feature for AGs was motivated to its usage in our Azure DevOps CI pipeline in order to deploy a configurable one on an AKS cluster with SQL Server 2019.

151 - 1 - DevOpsAzureHelm

If you look carefully at the release pipeline, Windocks is also another product we are using for our integration testing with SQL Server containers and I will probably explain more on this topic in a future blog post. But this time I would like to share some experiences with the construction of the AG helm chart.

First of all let’s precise I used the content provided by Microsoft on GitHub to deploy availability groups on K8s. This is a new functionality of SQL Server 2019 and we run actually with CTP 2.1 version. Chances are things will likely change over the time and I may bet Microsoft will release their own helm chart in the future. Anyway, for me it was an interesting opportunity to deep dive in helm charts feature.

First step I ran into was the parametrization (one big interest of Helm) of the existing template with input values including AG’s name, image container repository and tag used for deployment and different service settings like service type, service port and target service port.

Here one of my values.yaml file:

# General parameters
agname: ag1
acceptEula: true
# Container parameters
agentsContainerImage:
  repository: mcr.microsoft.com/mssql/ha
  tag: 2019-CTP2.1-ubuntu
  pullPolicy: IfNotPresent
sqlServerContainer:
  repository: mcr.microsoft.com/mssql/server
  tag: 2019-CTP2.1-ubuntu
  pullPolicy: IfNotPresent
# Service parameters
sqlservice:
  type: LoadBalancer
  port: 1433
agservice:
  type: LoadBalancer
  port: 1433

 

As a reminder, services on K8s are a way to expose pods to the outside world. I also introduced some additional labels for the purpose of querying the system. This is basically the same labels used in the stable template on GitHub.

labels:
    …
    app: {{ template "dbi_mssql_ag.name" . }}
    chart: {{ .Chart.Name }}-{{ .Chart.Version | replace "+" "_" }}
    release: {{ .Release.Name }}
    heritage: {{ .Release.Service }}

 

Here a sample of my ag-sqlserver-deployment.yaml file with parametrization stuff:

apiVersion: mssql.microsoft.com/v1
kind: SqlServer
metadata:
  labels:
    name: mssql1
    type: sqlservr
    app: {{ template "dbi_mssql_ag.name" . }}
    chart: {{ .Chart.Name }}-{{ .Chart.Version | replace "+" "_" }}
    release: {{ .Release.Name }}
    heritage: {{ .Release.Service }}
  name: mssql1
  namespace: {{ .Release.Namespace }}
spec:
  acceptEula: true
  agentsContainerImage: {{ .Values.agentsContainerImage.repository }}:{{ .Values.agentsContainerImage.tag }}
  availabilityGroups: [{{ .Values.agname }}]
  instanceRootVolumeClaimTemplate:
    accessModes: [ReadWriteOnce]
    resources:
      requests: {storage: 5Gi}
    storageClass: default
  saPassword:
    secretKeyRef: {key: sapassword, name: sql-secrets}
  masterKeyPassword:
    secretKeyRef: {key: masterkeypassword, name: sql-secrets} 
  sqlServerContainer: {image: '{{ .Values.sqlServerContainer.repository }}:{{ .Values.sqlServerContainer.tag }}'}

 

In addition, for a sake of clarity, I also took the opportunity to break the YAML files provided by Microsoft into different pieces including AG operator, AG RBAC configuration (security), AG instances and resources and AG services files. But you may wonder (like me) how to control the ordering of object’s creation? Well, it is worth noting that Helm collects all of the resources in a given Chart and it’s dependencies, groups them by resource type, and then installs them in a pre-defined order.

Let’ say I also removed the existing namespace’s creation from the existing YAML file because currently helm charts are not able to create one if it doesn’t exist. From a security perspective my concern was to keep the release deployment under control through helm charts so I preferred to precise the namespace directly in the –namespace parameter as well as creating the sql-secrets object which contains both sa and master key passwords manually according to the Microsoft documentation. In this way, you may segregate the permissions tiller has on a specific namespace and in my case, tiller has access to the ci namespace only.

Here comes likely the most interesting part. During the first deployment attempt, I had to face was the dependency that exists between the AG operator, SQL Server and AG resources as stated here:

The operator implements and registers the custom resource definition for SQL Server and the Availability Group resources.

The custom resource definition (CRD) is one of the first components with the deployment of the AG operator. You may retrieve an API service v1.mssql.microsoft.com as show below:

$ kubectl describe apiservice v1.mssql.microsoft.com
Name:         v1.mssql.microsoft.com
Namespace:
Labels:       kube-aggregator.kubernetes.io/automanaged=true
Annotations:  <none>
API Version:  apiregistration.k8s.io/v1
Kind:         APIService
Metadata:
  Creation Timestamp:  2019-01-17T01:45:19Z
  Resource Version:    283588
  Self Link:           /apis/apiregistration.k8s.io/v1/apiservices/v1.mssql.microsoft.com
  UID:                 8b90159d-19f9-11e9-96ba-ee2da997daf5
Spec:
  Group:                   mssql.microsoft.com
  Group Priority Minimum:  1000
  Service:                 <nil>
  Version:                 v1
  Version Priority:        100
Status:
  Conditions:
    Last Transition Time:  2019-01-17T01:45:19Z
    Message:               Local APIServices are always available
    Reason:                Local
    Status:                True
    Type:                  Available
Events:                    <none>

 

Then the API is referenced in the YAML file that contains the definition of SQL Server resource objects through the following elements:

apiVersion: mssql.microsoft.com/v1
kind: SqlServer

 

As you probably guessed, if this API is missing on your K8s cluster at the moment of installing the AG resources you’ll probably face the following error message:

Error: [unable to recognize “”: no matches for kind “SqlServer” in version “mssql.microsoft.com/v1″, unable to recognize “”: no matches for kind “SqlServer” in version “mssql.microsoft.com/v1″, unable to recognize “”: no matches for kind “SqlServer” in version “mssql.microsoft.com/v1″]

At this stage, referring to the Helm documentation, I decided to split my initial release deployment into 2 separate helm charts. Between the 2 suggested methods in the documentation I much prefer this one because updating / removing releases is little bit easier but at the cost of introducing an additional chart in the game. With the CRD hook method, the CRD is not attached to a specific chart deployment, so if we need to change something in the CRD, it doesn’t get updated in the cluster unless we tear down the chart and install it again. This also means that we can’t add a CRD to a chart that has already been deployed. Finally, I took a look at the charts dependency feature but it doesn’t fix my issue at all because chart validation seems to come before the completion of the custom API. This is at least what I noticed with the current version of my helm version (v2.12.1). Probably one area to investigate for Microsoft …

So let’s continue …  Here the structure of my two helm charts (respectively for my AG resources and my AG operator).

$ tree /f

───dbi_mssql_ag
│   │   .helmignore
│   │   Chart.yaml
│   │   values.yaml
│   │
│   ├───charts
│   └───templates
│       │   ag-services.yaml
│       │   ag-sqlserver-deployment.yaml
│       │   NOTES.txt
│       │   _helpers.tpl
│       │
│       └───tests
└───dbi_mssql_operator
    │   .helmignore
    │   Chart.yaml
    │   values.yaml
    │
    ├───charts
    └───templates
        │   ag-operator-deployment.yaml
        │   ag-security.yaml
        │   NOTES.txt
        │   _helpers.tpl
        │
        └───tests

 

The deployment consists in deploying the two charts in the correct order:

$ helm install --name ag-2019-o --namespace ci .\dbi_mssql_operator\
…
$ helm install --name ag-2019 --namespace ci .\dbi_mssql_ag\
…
$ helm ls
NAME            REVISION        UPDATED                         STATUS          CHART           APP VERSION     NAMESPACE
ag-2019         1               Wed Jan 16 23:48:33 2019        DEPLOYED        dbi-mssql-ag-1  2019.0.0        ci
ag-2019-o       1               Wed Jan 16 23:28:17 2019        DEPLOYED        dbi-mssql-ag-1  2019.0.0        ci
…
$ kubectl get all -n ci
NAME                                 READY     STATUS      RESTARTS   AGE
pod/mssql-initialize-mssql1-hb9xs    0/1       Completed   0          2m
pod/mssql-initialize-mssql2-47n99    0/1       Completed   0          2m
pod/mssql-initialize-mssql3-67lzn    0/1       Completed   0          2m
pod/mssql-operator-7bc948fdc-45qw5   1/1       Running     0          22m
pod/mssql1-0                         2/2       Running     0          2m
pod/mssql2-0                         2/2       Running     0          2m
pod/mssql3-0                         2/2       Running     0          2m

NAME                  TYPE           CLUSTER-IP     EXTERNAL-IP     PORT(S)             AGE
service/ag1           ClusterIP      None           <none>          1433/TCP,5022/TCP   2m
service/ag1-primary   LoadBalancer   10.0.62.75     xx.xx.xxx.xx   1433:32377/TCP      2m
service/mssql1        LoadBalancer   10.0.45.155    xx.xx.xxx.xxx   1433:31756/TCP      2m
service/mssql2        LoadBalancer   10.0.104.145   xx.xx.xxx.xxx       1433:31285/TCP      2m
service/mssql3        LoadBalancer   10.0.51.142    xx.xx.xxx.xxx       1433:31002/TCP      2m

NAME                             DESIRED   CURRENT   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/mssql-operator   1         1         1            1           22m

NAME                                       DESIRED   CURRENT   READY     AGE
replicaset.apps/mssql-operator-7bc948fdc   1         1         1         22m

NAME                      DESIRED   CURRENT   AGE
statefulset.apps/mssql1   1         1         2m
statefulset.apps/mssql2   1         1         2m
statefulset.apps/mssql3   1         1         2m

NAME                                DESIRED   SUCCESSFUL   AGE
job.batch/mssql-initialize-mssql1   1         1            2m
job.batch/mssql-initialize-mssql2   1         1            2m
job.batch/mssql-initialize-mssql3   1         1            2m

 

After that, my AG is deployed and ready!

Let me know if you want to try it and feel free to comment!. Apologize I don’t use a GitHub repository so far but things may be changing this year :)

Bear in mind  SQL Server 2019 is still in CTP version at the moment of this write-up. Things may change more or less until the first GA …

Happy AG deployment on K8s!

Cet article Deploying SQL Server 2019 AGs on K8s with helm charts est apparu en premier sur Blog dbi services.

Oracle OpenWorld Europe: London 2019

Thu, 2019-01-17 06:35

Oracle 19c, Oracle Cloud in numbers and pre-build environments on vagrant, docker and Oracle Linux Cloud Native environment, those were some of the topics at Open World Europe Conference in London. To see what’s behind with links to detailed sources, please read on.

The conference was organised by Oracle, most of the speakers were Oracle employees and introduced the audience at a high level into the Oracle ecosphere. To have an overview about what (huge portfolio) Oracle offers to the market and to get in touch with Oracle employees, open world conferences are the place to be. Many statements have already given at the bigger sister conference in San Francisco in October 2018, so European customers are the target audience for the conference in London. Most information on upcoming features given fall under save harbour statement, so one should be careful to take decisions based on these.

Oracle 19c

The main target in release 19 is stability, so fewer new features were added as in previous releases. Many new features are very well described on a post by our former dbi colleague Frank Pachot.

To get a deeper view into new features of every RDBMS release, a good source is to read the new features guides:

livesql.oracle.com is now running on Oracle 19, two demos on SQL functions introduced in 19c are available:

If you like to test Oracle 19c, you can participate in Oracle 19c beta program.

Oracle Cloud in numbers
  • 29’000’000+ active users
  • 25’000 customers
  • 1’075 PB storage
  • 83’000 VMs at 27 data centers
  • 1’600 operators

Reading these numbers, it’s obvious Oracle gains knowhow in Cloud environments and also understands better requirements building up private cloud environments at customers. It would be interesting to see what Oracle offers to small and medium sized companies.

Oracle Linux Cloud Native environment

Building up a stack with tools for DevOps teams can be very challenging for organizations:

  • huge effort
  • hard to find expert resources
  • no enterprise support
  • complex architectural bets

Thats why Oracle build up an stack on Oracle Linux that can be used both for dev, test and productive environments. Some features are:

The stack can be run in the cloud as well as on premises using Oracle Virtualbox. Since release 6, Virtualbox is able to move VMs to the Oracle Cloud.

Pre-build, ready-to-use environments on VirtualBox and Docker

It’s good practise to use Vagrant for fast Virtualbox provisioning. There are a couple of pre-build so called “Vagrant boxes” available by Oracle in their yum and githup repository.

If you want to test on pre-build oracle database environments (single instance, Real Application Cluster, Data Guard), Tim Hall provides Vagrant boxes for various releases.

If you are looking for pre-build docker containers, have a look at Oracle Container Registry.

Oracle strategy

To provide a pre-build stack follows a superior Oracle strategy: IT professionals should not deal with basic work (provisioning, patching, basic tuning), but concentrate on other, more important tasks. That why Oracle offers engineered systems and cloud services as a basis. What the more important subjects are was explained in a session about “the changing role of the DBA”.

Architecture Security
  • No insecure passwords
  • Concept work: who should have access to what and in which context?
  • Analyse privileges with the help of DBMS_PRIVILEGE_CAPTURE package
  • Data masking/redaction in test and dev environment
Availability Understand SQL

A personal highlight was the session from Chris R. Saxon which is as specialist for SQL. His presentation style is not only very entertaining, but also the content is interesting and helps you get the most out of Oracle Database engine. In Chris’ session, he explained why sql queries tend not to use indexes even there are present and do Full table scans instead. This is always bad and mainly based on clustering factor and data cardinality. You can follow his presentation on Youtube:

You can find more video content from Chris on his Youtube channel.

If you are interested to learn SQL, another great source is the Oracle SQL blog.

Cet article Oracle OpenWorld Europe: London 2019 est apparu en premier sur Blog dbi services.

Documentum CS 7.* – 777 permission on jobs log

Mon, 2019-01-14 14:32

A few weeks ago at a customer, our team was involved in a security control.
We tracked files with 777 permission and we detected that logs generated by Documentum jobs have 777 permissions.

Security before anything else, that’s why this topic was my top priority!

First of all, I checked the logs on some Content Servers, and I had the same issue everywhere.

[dmadmin@vmCS1 sysadmin]$ cd $DOCUMENTUM/dba/log/Repo1/sysadmin
[dmadmin@vmCS1 sysadmin]$ ls -rtl
total 192
-rwxrwxrwx. 1 dmadmin dmadmin   1561 Oct 25 10:12 DataDictionaryPublisherDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   5172 Oct 28 08:02 DMCleanDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   6701 Oct 28 08:17 DMFilescanDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin  14546 Nov  2 00:01 ConsistencyCheckerDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   2969 Nov  2 00:09 ContentWarningDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin    596 Nov  2 00:12 DBWarningDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin 102765 Nov  2 00:17 FileReportDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   3830 Nov  2 00:25 LogPurgeDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin    527 Nov  2 00:28 QueueMgtDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin  15932 Nov  2 00:31 StateOfDocbaseDoc.txt

I verified the umask at operating system level:

[dmadmin@vmCS1 ~]$ umask
0027

umask has the expected value!
For more information regarding the umask : https://en.wikipedia.org/wiki/Umask

Check if a different value of umask is set in the server.ini file ([SERVER_STARTUP] section):

[dmadmin@vmCS1 ~]$ cd $DOCUMENTUM/dba/config/Repo1
[dmadmin@vmCS1 ~]$ grep umask server.ini
[dmadmin@vmCS1 ~]$ 

No result
If it has been set, the umask setting in the server.ini overwrite the one set at operation system level.
This umask value is intended to control the permissions of files associated with documents stored in the repository, and their enclosing folders.
In my case, these files and folders have the correct permission.

Well, why only these logs have a different permission? I checked again some servers and I saw that not all jobs log have 777 permission, strange:

[dmadmin@vmCS2 sysadmin]$ ls -rtl
total 108
-rwxrwxrwx. 1 dmadmin dmadmin   601  Oct 18 07:12 DMFilescanDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   138  Oct 20 21:37 UpdateStatsDoc.txt
-rw-r-----. 1 dmadmin dmadmin   1832 Oct 24 13:45 FTCreateEventsDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   1251 Oct 25 11:55 DataDictionaryPublisherDoc.txt
-rwxrwxrwx. 1 dmadmin dmadmin   442  Oct 28 07:12 DMCleanDoc.txt

In fact, the common point between logs with 777 permission is that they are generated by dmbasic methods. These logs are not controlled by the umask set at the operating system level or server.ini.

The system umask value is overridden in the docbase start script, and set to 0. This value is then inherited by dmbasic methods!

[dmadmin@vmCS1 sysadmin]$ grep umask $DOCUMENTUM/dba/dm_start_Repo1
umask 0

I feel better now :D

So, to resolve this issue I had to:

  • Change the umask to 027 instead of 0 in the docbase start script
  • Stop the docbase
  • Change the permission of logs already generated
  • Start the docbase
  • Check the logs after a job execution

To make it easy and quickly, you can use below steps:
Commands below take in account High Availability case, don’t worry about that ;)

  1. To change on one docbase
    Define the docbase name

    		export DCTM_DOCBASE_NAME="DOCBASENAME"

    Check if it is a HA environment or not, and set the DCTM_DOCBASE_GLOBAL_NAME accordingly:

    		cd $DOCUMENTUM/dba
    		export DCTM_DOCBASE_SERVER_CONFIG=$(grep server_config_name config/${DCTM_DOCBASE_NAME}/server.ini | cut -d \  -f 3) ;
    		if [ ${DCTM_DOCBASE_SERVER_CONFIG} == ${DCTM_DOCBASE_NAME} ]
    		then
    			export DCTM_DOCBASE_GLOBAL_NAME=${DCTM_DOCBASE_NAME}
    		else
    			export DCTM_DOCBASE_SERVICE_NAME=$(grep 'service =' config/${DCTM_DOCBASE_NAME}/server.ini | cut -d \  -f 3) ;
    			export DCTM_DOCBASE_GLOBAL_NAME=${DCTM_DOCBASE_NAME}"_"${DCTM_DOCBASE_SERVICE_NAME}
    		fi

    Change the umask value in the start script

    		cp -p dm_start_${DCTM_DOCBASE_GLOBAL_NAME} dm_start_${DCTM_DOCBASE_GLOBAL_NAME}_bck_$(date +%Y%m%d-%H%M%S)
    		echo "Docbase ${DCTM_DOCBASE_NAME} : Start script has been saved"
    		sed -i 's,umask 0,umask 027,' dm_start_${DCTM_DOCBASE_GLOBAL_NAME}
    		echo "Docbase ${DCTM_DOCBASE_NAME} : Umask changed"

    Stop the docbases using the following command:

    		./dm_shutdown_${DCTM_DOCBASE_GLOBAL_NAME}

    Check if the docbase has been stopped:

    		ps -ef | grep ${DCTM_DOCBASE_NAME}

    Change the permission of existing files:

    		DCTM_DOCBASE_ID_DEC=$(grep docbase_id config/${DCTM_DOCBASE_NAME}/server.ini | cut -d \  -f 3)
    		DCTM_DOCBASE_ID_HEX=$(printf "%x\n" $DCTM_DOCBASE_ID_DEC)
    		chmod 640 log/*${DCTM_DOCBASE_ID_HEX}/sysadmin/*

    Start the docbase using the following command:

    		./dm_start_${DCTM_DOCBASE_GLOBAL_NAME}
  2. To change on all docbases
    Check if it is a HA environment or not (check done one docbase only), and set the DCTM_DOCBASE_GLOBAL_NAME accordingly, then change the umask value in the start script.

    		cd $DOCUMENTUM/dba
    		export FIRST_DOCBASE_NAME=$(ls config | head -1)
    		export DCTM_DOCBASE_SERVER_CONFIG=$(grep server_config_name config/${FIRST_DOCBASE_NAME}/server.ini | cut -d \  -f 3)
    		if [ ${FIRST_DOCBASE_NAME} == ${DCTM_DOCBASE_SERVER_CONFIG} ]
    		then
    			export HA_ENV="NO"
    		else
    			export HA_ENV="YES"
    		fi
    		
    		for i in `ls config`; do 
    			if [ ${HA_ENV} == "NO" ]
    			then
    				export DCTM_DOCBASE_GLOBAL_NAME=${i}
    			else
    				export DCTM_DOCBASE_SERVICE_NAME=$(grep 'service =' config/${i}/server.ini | cut -d \  -f 3)
    				export DCTM_DOCBASE_GLOBAL_NAME=${i}"_"${DCTM_DOCBASE_SERVICE_NAME}
    			fi
    			cp -p dm_start_${DCTM_DOCBASE_GLOBAL_NAME} dm_start_${DCTM_DOCBASE_GLOBAL_NAME}_bck_$(date +%Y%m%d-%H%M%S)
    			echo "Docbase ${i} : Start script has been saved"
    			sed -i 's,umask 0,umask 027,' dm_start_${DCTM_DOCBASE_GLOBAL_NAME}
    			echo "Docbase ${i} : Umask changed"
    		done

    Stop the docbases using the following command:

    		for i in `ls config`; do 
    			if [ ${HA_ENV} == "NO" ]
    			then
    				export DCTM_DOCBASE_GLOBAL_NAME=${i}
    			else
    				export DCTM_DOCBASE_SERVICE_NAME=$(grep 'service =' config/${i}/server.ini | cut -d \  -f 3)
    				export DCTM_DOCBASE_GLOBAL_NAME=${i}"_"${DCTM_DOCBASE_SERVICE_NAME}
    			fi
    			echo "Stopping docbase ${i}"
    			./dm_shutdown_${DCTM_DOCBASE_GLOBAL_NAME}
    			echo "The docbase ${i} has been stopped"
    		done

    Check that all docbases are stopped

    		ps -ef | grep dmadmin

    Change permission on log files

    chmod 640 log/*/sysadmin/*

    Start the docbases using the following commands:

    
    		for i in `ls config`; do 
    			if [ ${HA_ENV} == "NO" ]
    			then
    				export DCTM_DOCBASE_GLOBAL_NAME=${i}
    			else
    				export DCTM_DOCBASE_SERVICE_NAME=$(grep 'service =' config/${i}/server.ini | cut -d \  -f 3)
    				export DCTM_DOCBASE_GLOBAL_NAME=${i}"_"${DCTM_DOCBASE_SERVICE_NAME}
    			fi
    			echo "Starting docbase ${i}" 
    			./dm_start_${DCTM_DOCBASE_GLOBAL_NAME}
    			echo "The docbase ${i} has been started" 
    		done

    Check that all docbases are started

    		ps -ef | grep dmadmin

I was able to sleep peacefully this night ;) and you know now how to resolve this security issue.

Cet article Documentum CS 7.* – 777 permission on jobs log est apparu en premier sur Blog dbi services.

Copy or Migrate a SSISDB environment

Fri, 2019-01-11 10:42

Integration catalog is easy to work with and the environments feature is as well very convenient.

In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is only possible in another folder of your SSIS catalog. But how if you will to duplicate your environment or move it on another server.

With the below steps, you will be able to generate the script, to create your environment, on the same server changing the folder or/and the environment name or to run it on another server.

  1. Run the script below where you have initially created your SSIS catalog environment

 

DECLARE @FolderName nvarchar(128) = N'SSISDemo'
DECLARE @EnvName nvarchar(128)= 'Env_SSISDemo1'
SELECT 
'DECLARE @FolderName nvarchar(128) = N''' + @FolderName + '''
DECLARE @EnvName nvarchar(128)= ''' + @EnvName + '''
EXEC [SSISDB].[catalog].[create_environment] @folder_name=@FolderName, @environment_name=@EnvName, @environment_description=N''' + e.description + '''' 
as tsql_EnvCopy 
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id
WHERE f.name = @FolderName
AND e.name = @EnvName
UNION ALL 
SELECT 
'EXEC [SSISDB].[catalog].[create_environment_variable] 
@folder_name=@FolderName, 
@environment_name=@EnvName, 
@variable_name=N'''+ ev.name + ''', 
@data_type=N'''+ ev.type + ''', 
@sensitive='+ CONVERT(NCHAR,ev.sensitive) +', 
@value = ' + 
CASE ev.sensitive
WHEN 0 THEN 
CASE ev.type 
WHEN 'Date Time' THEN ''''+ CONVERT(NVARCHAR(max),ev.value) + '''' 
WHEN 'String' THEN 'N'''+ CONVERT(NVARCHAR(max),ev.value) + '''' 
ELSE CONVERT(NVARCHAR(max),ev.value)
END 
WHEN 1 THEN 
'##########'
END + ',
@description=N'''+ ev.description + ''''
as tsql_EnvVarcreate
FROM SSISDB.catalog.folders f
INNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id
INNER JOIN SSISDB.catalog.environment_variables ev on ev.environment_id = e.environment_id
WHERE f.name = @FolderName
AND e.name = @EnvName
  1. Copy the result of the script (column tsql_EnvCopy) in a copy it in a SSMS Query window connected to the server where your want to deploy your environment.
  2. Adapt the script if needed.
  • On the server where you want to create the new environment, be sure that:
    • The folder with the defined FolderName is existing.
    • FolderName/EnvName combination is not already existing on the server.
    • Change the values of the @FolderName or/and @EnvName if needed.
  • Change the values of the sensitive parameters:

As you can imagine the parameter values cannot be retrieved when they are set as sensitive. If you have such parameter replace the values ########## with the real ones before running the script.

  1. Run the script and you’re done

Cet article Copy or Migrate a SSISDB environment est apparu en premier sur Blog dbi services.

Compile additional packages for Oracle VM Server

Fri, 2019-01-11 03:13

I needed a special package on my OVM Server 3.4.6.
The package is called fio and is needed to do some I/O performance tests.
Unfortunately, OVM Server does not provide any package for compiling software and installing additional software to your OVM Server is also not supported.
But there is a solution:

Insatll a VM with Oracle VM Server 3.4.6 and added the official OVM SDK repositories:


rm -f /etc/yum.repos.d/*
echo '
[ovm34] name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleVM/OVM3/34_latest/x86_64/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
[ol6_latest] name=Oracle Linux $releasever Latest ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/latest/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
[ol6_addons] name=Oracle Linux $releasever Add ons ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/addons/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
[ol6_UEKR4] name=Latest Unbreakable Enterprise Kernel Release 4 for Oracle Linux $releasever ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL6/UEKR4/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1 ' > /etc/yum.repos.d/ovm-sdk.repo

Now install the necessary packages and compile your software:

On OVM 3.4 SDK VM
yum install -y gcc make zlib-devel libaio libaio-devel
wget https://codeload.github.com/axboe/fio/zip/master
unzip master
cd fio-master
./configure
make

Copy the compiled executable “fio” to your OVM Server or to an attached NFS share.
Run the program and do what you wanna do.

In my case I will run several different performance tests, but that is a story for an other blog post.

Reference: Oracle VM 3: How-to build an Oracle VM 3.3/3.4 SDK platform (Doc ID 2160955.1)

Cet article Compile additional packages for Oracle VM Server est apparu en premier sur Blog dbi services.

PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid

Fri, 2019-01-11 00:29

PostgreSQL 12 will give you more control on resetting statistics gathered by pg_stat_statements. When you check the documentation for PostgreSQL 11 (as linked in the previous sentence) you will see that the function has the following signature:

pg_stat_statements_reset() returns void

This means your only choice is to reset all the statistics. Today this commit landed and this will give you more control on which statistics to reset. The signature of the function now looks like this:

pg_stat_statements_reset(userid Oid, dbid Oid, queryid bigint) returns void

There are three new parameters for controlling what to reset: The user id, the database id and the id of a specific query. By default all of them are 0 meaning the the function will behave as in previous versions: Discarding all the statistics. Lets create two users, two databases and a table in each so we will have something in pg_stat_statements we can work with:

postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create user u1 with login password 'u1'" postgres
CREATE ROLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create user u2 with login password 'u2'" postgres
CREATE ROLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create database db1 with owner = u1" postgres
CREATE DATABASE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create database db2 with owner = u2" postgres
CREATE DATABASE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create table t1 (a int)" -U u1 db1
CREATE TABLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "create table t1 (a int)" -U u2 db2
CREATE TABLE
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "insert into t1 select * from generate_series(1,100)" -U u1 db1
INSERT 0 100
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "insert into t1 select * from generate_series(1,100)" -U u2 db2
INSERT 0 100
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "select count(*) from t1" -U u1 db1
 count 
-------
   100
(1 row)
postgres@pgbox:/u02/pgdata/DEV/ [PGDEV] psql -c "select count(*) from t1" -U u2 db2
 count 
-------
   100
(1 row)

We should be able to see the statements in pg_stat_statements but before doing that lets check the dbids:

postgres@pgbox:/home/postgres/ [PGDEV] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  16394            db1  pg_default
  16395            db2  pg_default
  13569       postgres  pg_default
  13568      template0  pg_default
      1      template1  pg_default

What do we see for our two databases?

postgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395);
 userid | dbid  |       queryid        | calls |                        query                        
--------+-------+----------------------+-------+-----------------------------------------------------
  16392 | 16394 |  7490503619681577402 |     3 | set client_encoding to 'unicode'
  16393 | 16395 |   843119317166481275 |     1 | insert into t1 select * from generate_series($1,$2)
  16392 | 16394 | -3672942776844552312 |     1 | insert into t1 select * from generate_series($1,$2)
  16393 | 16395 |  7490503619681577402 |     3 | set client_encoding to 'unicode'
  16392 | 16394 |  5583984467630386743 |     1 | select count(*) from t1
  16393 | 16395 |  4983979802666994390 |     1 | select count(*) from t1
  16393 | 16395 |  6842879890091936614 |     1 | create table t1 (a int)
  16392 | 16394 |  6842879890091936614 |     1 | create table t1 (a int)

We should be able to reset the statistics for a specific query:

postgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395) and queryid = 6842879890091936614;
 userid | dbid  |       queryid       | calls |          query          
--------+-------+---------------------+-------+-------------------------
  16393 | 16395 | 6842879890091936614 |     1 | create table t1 (a int)
  16392 | 16394 | 6842879890091936614 |     1 | create table t1 (a int)
(2 rows)
postgres=# select pg_stat_statements_reset(0, 0, 6842879890091936614);
 pg_stat_statements_reset 
--------------------------
 
(1 row)

postgres=# select userid,dbid,queryid,calls,query from pg_stat_statements where dbid in (16394,16395) and queryid = 6842879890091936614;
 userid | dbid | queryid | calls | query 
--------+------+---------+-------+-------
(0 rows)

Notice that this of course resets the statistics for both statements as they have the same queryid. You could specify the userid and/or dbid as well to reset just one of them. Nice new feature.

Cet article PostgreSQL 12, pg_stat_statements_reset for userid, queryid and dbid est apparu en premier sur Blog dbi services.

Documentum 7+ internal error during installation or upgrade DBTestResult7092863812136784595.tmp

Thu, 2019-01-10 02:35

This blog will go straight to the topic. When upgrading/installing your content server to 7+, you may experience an internal error with a popup telling you to look into a file called something like: DBTestResult7092863812136784595.tmp

In fact, the installation process failed to test the database connection. Even if it managed to find your schema previously. In the file you’ll find something like:

 Last SQL statement executed by DB was:

#0  0x00000033b440f33e in waitpid () from /lib64/libpthread.so.0
#1  0x00000000004835db in dmExceptionManager::WalkStack(dmException*, int, siginfo*, void*) ()
#2  0x0000000000483998 in dmExceptionHandlerProc ()
#3  <signal handler called>
#4  0x00007f3d8c0e7d85 in ber_flush2 () from /dctm/product/7.3/bin/liblber-2.4.so.2
#5  0x00007f3d8bebb00b in ldap_int_flush_request () from /dctm/product/7.3/bin/libldap-2.4.so.2
#6  0x00007f3d8bebb808 in ldap_send_server_request () from /dctm/product/7.3/bin/libldap-2.4.so.2
#7  0x00007f3d8bebbb30 in ldap_send_initial_request () from /dctm/product/7.3/bin/libldap-2.4.so.2
#8  0x00007f3d8beab828 in ldap_search () from /dctm/product/7.3/bin/libldap-2.4.so.2
#9  0x00007f3d8beab952 in ldap_search_st () from /dctm/product/7.3/bin/libldap-2.4.so.2
#10 0x00007f3d898f93b2 in nnflqbf () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#11 0x00007f3d898ef124 in nnflrne1 () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#12 0x00007f3d898fe5b6 in nnfln2a () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#13 0x00007f3d886cffc0 in nnfgrne () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#14 0x00007f3d887f4274 in nlolgobj () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#15 0x00007f3d886ce43f in nnfun2a () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#16 0x00007f3d886ce213 in nnfsn2a () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#17 0x00007f3d8875f7f1 in niqname () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#18 0x00007f3d88612d06 in kpplcSetServerType () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#19 0x00007f3d8861387b in kpuatch () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#20 0x00007f3d893e9dc1 in kpulon2 () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#21 0x00007f3d892e15f2 in OCILogon2 () from /opt/oracle/product/client_12.1.0.2//lib/libclntsh.so
#22 0x0000000000555232 in DBConnection::Connect(DBString const*, DBString const*, DBString const*) ()
#23 0x00000000005555e4 in DBConnection::DBConnection(DBString const&, DBString const&, DBString const&, DBString const&, DBStats*, dmListHead*, int, int volatile*) ()
#24 0x000000000055f6ff in DBDataBaseImp::DBDataBaseImp(DBString const&, DBString const&, DBString const&, DBString const&, DBStats*, DBDataBase*, dmListHead*, int, int volatile*) ()
#25 0x0000000000545aaf in DBDataBase::DBDataBase(DBStats*, DBString const&, DBString const&, DBString const&, DBString const&, dmListHead*, int, int volatile*) ()
#26 0x0000000000466bd8 in dmServer_Dbtest(int, char**) ()
#27 0x00000033b3c1ed1d in __libc_start_main () from /lib64/libc.so.6
#28 0x0000000000455209 in _start ()
Tue Jan  8 16:18:15 2019 Documentum Internal Error: Assertion failure at line: 1459 in file: dmexcept.cxx

Not so precise right?

In fact, it’s pretty simple. The installer failed to use your tnsnames.ora file because LDAP auth is set with a higher priority. For those who don’t know, the tnsnames.ora holds your database connection information. You won’t be able to connect documentum without it, as documentum will try to locate it.

Sometimes, depending on how the DBA installed the oracle client on the machine, LDAP identification may be set prior to the tnsnames identification. So you have two possibilities:

  • Edit sqlnet.ora to set TNSNAMES before LDAP.
  • Rename ldap.ora to something else so that the Oracle Client doesn’t find it and fall back to TNSNAMES. I recommend this way as if the DBA patches the Client, the sqlnet.ora may be set back to LDAP in priority.

For info, these files are located in $ORACLE_HOME/network/admin, by default they are installed under the Oracle user install owner. So to edit the files you must be root or ask the DBAs to do it for you.

Cet article Documentum 7+ internal error during installation or upgrade DBTestResult7092863812136784595.tmp est apparu en premier sur Blog dbi services.

How to build a dummy resource plan

Tue, 2019-01-08 01:54

Often switching off resource plan feature of Oracle database is desired.
Unfortunately setting parameter RESOURCE_MANAGER_PLAN to empty string does not help, because it does not disable switching to another resource plan, e.g. with dbms_scheduler.

Following procedure prevents this switching.

1. Creation of a dummy resource plan, e.g. TEST_PLAN:


BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan =>'TEST_PLAN',
comment => 'plan for TEST',
mgmt_mth => 'EMPHASIS',
active_sess_pool_mth => 'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth => 'PARALLEL_DEGREE_LIMIT_ABSOLUTE');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan =>'TEST_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => 'dummy directive'
);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

The only limitation is the number of available CPUs specified in parameter CPU_COUNT, resource manager enforces this limitation. The resource plan INTERNAL_PLAN is for internal use, so I would not use it.

2. Setting of parameter resource_manager_plan:


alter system set resource_manager_plan='FORCE:TEST_PLAN';

This setting prevents switching to another resource plan.

Caveat:

With this setting, deletion of specified resource plan (e.g. TEST_PLAN) causes database to crash:


alter system set resource_manager_plan='FORCE:TEST_PLAN';
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.DELETE_PLAN('TEST_PLAN');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
BEGIN
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 73473
Session ID: 24 Serial number: 17611

Cet article How to build a dummy resource plan est apparu en premier sur Blog dbi services.

Using Ansible to bring up a three node Patroni cluster in minutes

Mon, 2019-01-07 01:48

Automation is key today, nobody wants to do the same tasks over and over and again. Cloud without automation is not even possible. There are several tools around that help with automation and one of the most popular is Ansible. We already have several posts about Ansible on our blog platform but this one will be rather long. Setting up PostgreSQL high available architectures is our daily business and we as well try to automate as much as possible. We do not only automate to save time, even more important we automate to avoid human errors. What we will share with this post is how you could use Ansible to bring up a three node Patroni cluster from scratch.

Disclaimer: Please see what we show here as a kind of template. You might need to adjust several bits to fit into your environment, other bits for sure can be solved more elegant by using advanced features of Ansible. Anyway, using this template you should be able to bring up one PostgreSQL master instance, two replicas, Patroni and HAProxy in minutes on CentOS 7. This should work the same for Red Hat 7 but if you want to do the same on Debian based systems or SUSE you for sure need to adjust some of the Ansible tasks. This post does not explain how Ansible works nor does it explain what Patroni or HAProxy is.

The starting point is a CentOS minimal installation with just the postgres user and group created and sudo permissions for postgres. That’s it:

postgres@patroni1 ~]$ id -a
uid=1000(postgres) gid=1000(postgres) groups=1000(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[postgres@patroni1 ~]$ lsb_release -a
LSB Version:	:core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID:	CentOS
Description:	CentOS Linux release 7.5.1804 (Core) 
Release:	7.5.1804
Codename:	Core
[postgres@patroni1 ~]$ sudo cat /etc/sudoers | grep postgres
postgres	ALL=(ALL)	NOPASSWD: ALL
[postgres@patroni1 ~]$ 

This is the Ansible directory structure on my workstation:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ tree
.
├── _commands.sh
├── _init_dirs.sh
├── patroni
└── roles
    ├── common
    │   ├── files
    │   │   └── PostgreSQL-DMK-17-09.4.zip
    │   ├── handlers
    │   ├── meta
    │   ├── tasks
    │   │   └── main.yml
    │   ├── templates
    │   │   └── compile.sh.j2
    │   └── vars
    └── patroni
        ├── files
        │   ├── etcd.service
        │   └── patroni.service
        ├── handlers
        ├── meta
        ├── site.retry
        ├── site.yml
        ├── tasks
        │   └── main.yml
        ├── templates
        │   ├── etcd.conf.j2
        │   ├── haproxy.cfg.j2
        │   ├── hosts.j2
        │   ├── keepalived.conf.j2
        │   └── patroni.yml.j2
        └── vars

You can use the _init_dirs.sh script to create that but it is pretty much the Ansible default anyway:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat _init_dirs.sh 
#!/bin/bash
touch patroni
mkdir roles/
mkdir roles/common
mkdir roles/common/tasks
mkdir roles/common/handlers
mkdir roles/common/templates
mkdir roles/common/files
mkdir roles/common/vars
mkdir roles/common/meta
mkdir roles/patroni
mkdir roles/patroni/tasks
mkdir roles/patroni/handlers
mkdir roles/patroni/templates
mkdir roles/patroni/files
mkdir roles/patroni/vars
mkdir roles/patroni/meta

What you always need with Ansible is the inventory and in our case it looks like this:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat patroni 
[patroni-servers]
192.168.22.240 keepalived_role=MASTER keepalived_priority=102 ansible_hostname=patroni1 ansible_hostname_fqdn=patroni1.it.dbi-services.com
192.168.22.241 keepalived_role=SLAVE keepalived_priority=101 ansible_hostname=patroni2 ansible_hostname_fqdn=patroni2.it.dbi-services.com
192.168.22.242 keepalived_role=SLAVE keepalived_priority=100 ansible_hostname=patroni3 ansible_hostname_fqdn=patroni3.it.dbi-services.com

[patroni-servers:vars]
postgresql_version=11.1
postgresql_major_version=11
dmk_postgresql_version=11/db_1
etcd_vserion=3.3.10
postgres_user=postgres
postgres_group=postgres
dmk_version=17-09.4
cluster_name=PG1
blank=' '
virtual_ip=192.168.22.245

As you can see there are three machines and several variables defined. The *dmk* stuff if for our management kit, just ignore/delete that for your environment.

We have two roles, one common and one for Patroni. The common role is responsible for doing the common stuff and can be used for single instance PostgreSQL deployments as well so lets start with this one:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/common/tasks/main.yml 
- name: Install all dependencies for PostgreSQL 
  yum: name={{item}} state=present
  with_items:
   - gcc
   - openldap-devel
   - python-devel
   - readline-devel
   - redhat-lsb
   - bison
   - flex
   - perl-ExtUtils-Embed
   - zlib-devel
   - crypto-utils
   - openssl-devel
   - pam-devel
   - libxml2-devel
   - libxslt-devel
   - openssh-clients
   - bzip2
   - net-tools
   - wget
   - screen
   - unzip
   - sysstat
   - xorg-x11-xauth
   - systemd-devel
   - bash-completion

- name: Remove iwl packages
  yum: name={{item}} state=removed
  with_items:
   - iwl*

- name: upgrade all packages
  yum:
    name: '*'
    state: latest

- file:
    path: /u01/app/{{ postgres_user }}/local
    state: directory
    mode: 0700
    recurse: yes
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"

- file:
    path: /u01
    owner: "{{ postgres_user }}"
    group: "{{ postgres_user }}"
    mode: 0700

- file:
    path: /u01/app
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0700

- file:
    path: /u01/app/{{ postgres_user }}
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0700

- file:
    path: /u02/pgdata/
    state: directory
    mode: 0700
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"

- file:
    path: /u02/pgdata/{{ postgresql_major_version }}
    state: directory
    mode: 0700
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"

- file:
    path: /u02/pgdata/{{ postgresql_major_version }}/{{ cluster_name }}
    state: directory
    mode: 0700
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"

- file:
    path: /u99/pgdata/
    state: directory
    mode: 0700
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"

- file:
    path: /etc/pgtab
    state: touch
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0600

- name: check if PostgreSQL source code exists
  stat: 
    path: /home/{{ postgres_user }}/source.tar.bz2
  register: source_available

- name: Download the PostgreSQL source code if it is not already there
  get_url:
    url: https://ftp.postgresql.org/pub/source/v{{ postgresql_version }}/postgresql-{{ postgresql_version }}.tar.bz2
    dest: /home/{{ postgres_user }}/source.tar.bz2
    mode: 0775
  when: source_available.stat.exists == false

- name: Check if PostgreSQL is already installed
  stat:
    path: /u01/app/{{ postgres_user }}/product/{{ dmk_postgresql_version }}/bin/postgres
  register: postrgresql_is_installed

- name: extract the sources when PostgreSQL is not already installed
  shell: cd /home/{{ postgres_user }}; tar -axf source.tar.bz2
  become: yes
  become_user: "{{ postgres_user }}"
  when: postrgresql_is_installed.stat.exists == false

- template:
    src: compile.sh.j2
    dest: /home/{{ postgres_user }}/postgresql-{{ postgresql_version }}/compile.sh
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0700

- name: Install PostgreSQL from source code
  shell: cd /home/{{ postgres_user }}/postgresql-{{ postgresql_version }}; ./compile.sh
  become: yes
  become_user: "{{ postgres_user }}"
  when: postrgresql_is_installed.stat.exists == false

- name: check if DMK for PostgreSQL source code exists
  stat:
    path: /u01/app/{{ postgres_user }}/local/PostgreSQL-DMK-{{ dmk_version }}.zip
  register: dmk_source_available

- name: check if DMK for PostgreSQL is extracted
  stat:
    path: /u01/app/{{ postgres_user }}/local/dmk/bin/dmk.bash
  register: dmk_extracted

- name: Copy DMK source distribution
  copy:
    src: PostgreSQL-DMK-{{ dmk_version }}.zip
    dest: /u01/app/{{ postgres_user }}/local/PostgreSQL-DMK-{{ dmk_version }}.zip
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0700
  when: dmk_source_available.stat.exists == false

- name: extract DMK
  shell: cd /u01/app/{{ postgres_user }}/local; unzip PostgreSQL-DMK-{{ dmk_version }}.zip
  become: yes
  become_user: "{{ postgres_user }}"
  when: dmk_extracted.stat.exists == false

- name: check if DMK is installed
  stat:
    path: /home/{{ postgres_user }}/.DMK_HOME
  register: dmk_installed

- lineinfile:
    path: /etc/pgtab
    line: 'pg{{ postgresql_version }}:/u01/app/{{ postgres_user }}/product/{{ dmk_postgresql_version }}:dummy:9999:D'
    create: no
  when: dmk_installed.stat.exists == false

- name: Execute DMK for the first time
  shell: /u01/app/{{ postgres_user }}/local/dmk/bin/dmk.bash; cat /u01/app/{{ postgres_user }}/local/dmk/templates/profile/dmk.postgres.profile >> /home/{{ postgres_user }}/.bash_profile
  become: yes
  become_user: "{{ postgres_user }}"
  when: dmk_installed.stat.exists == false

This should be more or less self explaining so we will only summarize what it does:

  • Install required packages for compiling PostgreSQL from source
  • Remove the iwl* packages
  • Update all packages to the latest release
  • Create the directory structure
  • Download the PostgreSQL source code, compile and install
  • Install our DMK

As said, this role can be included in any other PostgreSQL setup as it only does basic stuff. There is one template used here, which is compile.sh.j2:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/common/templates/compile.sh.j2 
PGHOME=/u01/app/postgres/product/{{ dmk_postgresql_version }}
SEGSIZE=2
BLOCKSIZE=8

./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-pam \
            --with-ldap \
            --with-libxml \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
	    --with-systemd "
make -j 2 all
make install
cd contrib
make -j 2 install

This one is our standard way of bringing PostgreSQL onto the system and the only parameter is the PostgreSQL version we use for the directory name. No magic, simple stuff and that’s it for the common role.

Coming to the Patroni role. Here is it:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/tasks/main.yml 
---

- name: check if epel rpm already is there
  stat:
    path: /root/epel-release-latest-7.noarch.rpm
  register: epel_rpm_available

- name: Download the EPEL rpm
  get_url:
    url: http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
    dest: /root/epel-release-latest-7.noarch.rpm
    mode: 0440
  when: epel_rpm_available.stat.exists == false

- name: check if epel repository is already installed
  stat:
    path: /etc/yum.repos.d/epel.repo
  register: epel_installed


- name: Install the EPEL rpm
  shell: yum localinstall -y /root/epel-release-latest-7.noarch.rpm
  args: 
    warn: false
  when: epel_installed.stat.exists == false

- name: Install all dependencies for Patroni
  yum: name={{item}} state=present
  with_items:
   - python-pip
   - PyYAML
   - bind-utils
   - keepalived
   - haproxy

# create the hosts file
- template:
    src: hosts.j2
    dest: /etc/hosts
    owner: root
    group: root
    mode: 0644

- name: Create the file to load the watchdog module
  file:
    path: /etc/modules-load.d/softdog.conf
    state: touch

- name: Add the watchdog module
  shell: modprobe softdog

- name: Change ownershhip of the watchdog device
  shell: chown postgres /dev/watchdog
  args:
    warn: false

- name: check if etcd sources already exist
  stat:
    path: /home/{{ postgres_user }}/etcd-v{{ etcd_vserion }}-linux-amd64.tar.gz
  register: etcd_source_available

- name: Download etcd
  get_url:
    url: https://github.com/etcd-io/etcd/releases/download/v{{ etcd_vserion }}/etcd-v{{ etcd_vserion }}-linux-amd64.tar.gz
    dest: /home/{{ postgres_user }}/etcd-v{{ etcd_vserion }}-linux-amd64.tar.gz
    mode: 0755
  when: etcd_source_available.stat.exists == false

- name: check if etcd is available in DMK
  stat:
    path: /u01/app/{{ postgres_user }}/local/dmk/bin/etcd
  register: etcd_copied_to_dmk

- name: extract etcd
  shell: cd /home/{{ postgres_user }}/; tar -axf etcd-v{{ etcd_vserion }}-linux-amd64.tar.gz
  become: yes
  become_user: "{{ postgres_user }}"
  when: etcd_copied_to_dmk.stat.exists == false

- name: copy etcd to DMK
  shell: cp /home/{{ postgres_user }}/etcd-v{{ etcd_vserion }}-linux-amd64/etcd* /u01/app/{{ postgres_user }}/local/dmk/bin/
  become: yes
  become_user: "{{ postgres_user }}"
  when: etcd_copied_to_dmk.stat.exists == false

- template:
    src: etcd.conf.j2
    dest: /u01/app/{{ postgres_user }}/local/dmk/etc/etcd.conf
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0700

- name: Copy the etcd systemd service file
  copy:
    src: etcd.service
    dest: /etc/systemd/system/etcd.service
    owner: root
    group: root
    mode: 0755

- file:
    path: /u02/pgdata/etcd
    state: directory
    mode: 0700
    recurse: yes
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"

- name: force systemd to reread configs
  systemd:
    daemon_reload: yes

- name: Enable the systemd etcd service
  systemd:
    name: etcd
    enabled: yes

- name: Start the systemd etcd service
  shell: systemctl start etcd.service

- name: check if patroni is alraedy installed
  stat:
    path: /home/{{ postgres_user }}/.local/bin/patroni
  register: patroni_is_installed

- name: install and upgrade pip
  shell: pip install --upgrade pip
  when: patroni_is_installed.stat.exists == false
 
- name: install and upgrade setuptools
  become: yes
  become_user: "{{ postgres_user }}"
  shell: pip install --upgrade --user setuptools
  when: patroni_is_installed.stat.exists == false

- name: install psycopg2-binary
  become: yes
  become_user: "{{ postgres_user }}"
  shell: pip install --user psycopg2-binary
  when: patroni_is_installed.stat.exists == false

- name: install patroni
  become: yes
  become_user: "{{ postgres_user }}"
  shell: pip install --user patroni[etcd]
  when: patroni_is_installed.stat.exists == false

- file:
    src: /home/{{ postgres_user }}/.local/bin/patroni
    dest: /u01/app/{{ postgres_user }}/local/dmk/bin/patroni
    owner: "{{ postgres_user }}"
    group: "{{ postgres_user }}"
    state: link

- file:
    src: /home/{{ postgres_user }}/.local/bin/patronictl
    dest: /u01/app/{{ postgres_user }}/local/dmk/bin/patronictl
    owner: "{{ postgres_user }}"
    group: "{{ postgres_user }}"
    state: link

- template:
    src: patroni.yml.j2
    dest: /u01/app/{{ postgres_user }}/local/dmk/etc/patroni.yml
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0600

- name: Copy the patroni systemd service file
  copy:
    src: patroni.service
    dest: /etc/systemd/system/patroni.service
    owner: root
    group: root
    mode: 0755

- name: force systemd to reread configs 
  systemd:
    daemon_reload: yes

- name: Enable the systemd etcd service
  systemd:
    name: patroni
    enabled: yes

# add the instance to /etc/pgtab so DMK is aware of if
- lineinfile:
    path: /etc/pgtab
    line: '{{ cluster_name }}:/u01/app/{{ postgres_user }}/product/{{ dmk_postgresql_version }}:/u02/pgdata/{{ postgresql_major_version }}/{{ cluster_name }}:5432:N'

- template:
    src: haproxy.cfg.j2
    dest: /etc/haproxy/haproxy.cfg
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0600

- name: Enable the systemd haproxy service
  systemd:
    name: haproxy
    enabled: yes

# we need to set this so haproxy can be started
- name: Set selinux context for ha proxy
  shell: setsebool -P haproxy_connect_any=1

- template:
    src: keepalived.conf.j2
    dest: /etc/keepalived/keepalived.conf
    owner: "{{ postgres_user }}"
    group: "{{ postgres_group }}"
    mode: 0600
  with_items:
    - { role: "{{ hostvars[inventory_hostname].keepalived_role }}" , priority: "{{ hostvars[inventory_hostname].keepalived_priority }}" }

What it does:

  • Install the Extra Packages for Enterprise Linux (EPEL)
  • Install the dependencies for Patroni, HAProxy
  • Create the /etc/hosts file
  • Enable the watchdog service
  • Download and install etcd
  • Integrate etcd into systemd
  • Install Patroni, create the configuration files and integrate it into systemd
  • Install and configure HAProxy

This role uses several templates. The first one is used to create /etc/hosts:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/templates/hosts.j2 
#jinja2: trim_blocks:False
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

{%- for h in ansible_play_hosts %}
{{ hostvars[h]['ansible_enp0s8']['ipv4']['address'] }} {{ hostvars[h]['ansible_hostname'] }}
{% endfor %}

The second one is used to create the etcd configuration:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/templates/etcd.conf.j2 
name: {{ ansible_hostname }}
data-dir: /u02/pgdata/etcd
initial-advertise-peer-urls: http://{{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:2380
listen-peer-urls: http://{{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:2380
listen-client-urls: http://{{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:2379,http://localhost:2379
advertise-client-urls: http://{{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:2379
initial-cluster:{{ blank }} {%- for h in ansible_play_hosts %}
{{ hostvars[h]['ansible_hostname'] }}=http://{{ hostvars[h]['ansible_enp0s8']['ipv4']['address'] }}:2380{% if not loop.last %},{% endif %}
{% endfor %}

The third one creates the Patroni configuration:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/templates/patroni.yml.j2 
scope: {{ cluster_name }}
#namespace: /service/
name: {{ ansible_hostname }}

restapi:
  listen: {{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:8008
  connect_address: {{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:8008
#  certfile: /etc/ssl/certs/ssl-cert-snakeoil.pem
#  keyfile: /etc/ssl/private/ssl-cert-snakeoil.key
#  authentication:
#    username: username
#    password: password

# ctl:
#   insecure: false # Allow connections to SSL sites without certs
#   certfile: /etc/ssl/certs/ssl-cert-snakeoil.pem
#   cacert: /etc/ssl/certs/ssl-cacert-snakeoil.pem

etcd:
  host: 127.0.0.1:2379

bootstrap:
  # this section will be written into Etcd:///config after initializing new cluster
  # and all other cluster members will use it as a `global configuration`
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:
        wal_level: 'hot_standby'
        hot_standby: "on"
        wal_keep_segments: 8
        max_replication_slots: 10
        wal_log_hints: "on"
        listen_addresses: '*'
        port: 5432
        logging_collector: 'on'
        log_truncate_on_rotation: 'on'
        log_filename: 'postgresql-%a.log'
        log_rotation_age: '1440'
        log_line_prefix: '%m - %l - %p - %h - %u@%d - %x'
        log_directory: 'pg_log'
        log_min_messages: 'WARNING'
        log_autovacuum_min_duration: '60s'
        log_min_error_statement: 'NOTICE'
        log_min_duration_statement: '30s'
        log_checkpoints: 'on'
        log_statement: 'ddl'
        log_lock_waits: 'on'
        log_temp_files: '0'
        log_timezone: 'Europe/Zurich'
        log_connections: 'on'
        log_disconnections: 'on'
        log_duration: 'on'
        client_min_messages: 'WARNING'
        wal_level: 'replica'
        hot_standby_feedback: 'on'
        max_wal_senders: '10'
        shared_buffers: '128MB'
        work_mem: '8MB'
        effective_cache_size: '512MB'
        maintenance_work_mem: '64MB'
        wal_compression: 'off'
        max_wal_senders: '20'
        shared_preload_libraries: 'pg_stat_statements'
        autovacuum_max_workers: '6'
        autovacuum_vacuum_scale_factor: '0.1'
        autovacuum_vacuum_threshold: '50'
        archive_mode: 'on'
        archive_command: '/bin/true'
        wal_log_hints: 'on'
#      recovery_conf:
#        restore_command: cp ../wal_archive/%f %p

  # some desired options for 'initdb'
  initdb:  # Note: It needs to be a list (some options need values, others are switches)
  - encoding: UTF8
  - data-checksums

  pg_hba:  # Add following lines to pg_hba.conf after running 'initdb'
  - host replication replicator 192.168.22.0/24 md5
  - host all all 192.168.22.0/24 md5
#  - hostssl all all 0.0.0.0/0 md5

  # Additional script to be launched after initial cluster creation (will be passed the connection URL as parameter)
# post_init: /usr/local/bin/setup_cluster.sh

  # Some additional users users which needs to be created after initializing new cluster
  users:
    admin:
      password: admin
      options:
        - createrole
        - createdb
    replicator:
      password: postgres
      options:
        - superuser

postgresql:
  listen: {{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:5432
  connect_address: {{ hostvars[inventory_hostname]['ansible_enp0s8']['ipv4']['address'] }}:5432
  data_dir: /u02/pgdata/{{ postgresql_major_version }}/{{ cluster_name }}/
  bin_dir: /u01/app/{{ postgres_user }}/product/{{ dmk_postgresql_version }}/bin
#  config_dir:
  pgpass: /u01/app/{{ postgres_user }}/local/dmk/etc/pgpass0
  authentication:
    replication:
      username: replicator
      password: postgres
    superuser:
      username: postgres
      password: postgres
  parameters:
    unix_socket_directories: '/tmp'

watchdog:
  mode: automatic # Allowed values: off, automatic, required
  device: /dev/watchdog
  safety_margin: 5

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

Then we have the configuration template for HAProxy:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/templates/haproxy.cfg.j2 
#jinja2: trim_blocks:False
global
    maxconn 100

defaults
    log global
    mode tcp
    retries 2
    timeout client 30m
    timeout connect 4s
    timeout server 30m
    timeout check 5s

listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /
    # stats auth haproxy:haproxy
    # stats refresh 10s

listen {{ cluster_name }}
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    {%- for h in ansible_play_hosts %}
    server postgresql_{{ hostvars[h]['ansible_enp0s8']['ipv4']['address'] }}_5432 {{ hostvars[h]['ansible_enp0s8']['ipv4']['address'] }}:5432 maxconn 100 check port 8008
    {% endfor %}

Finally the template for keepalived:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/templates/keepalived.conf.j2 
vrrp_script chk_haproxy {
    script "killall -0 haproxy"
    interval 2
    weight 2
}

vrrp_instance VI_1 {
    interface enp0s8
    state {{ item.role }} 
    virtual_router_id 51
    priority {{ item.priority }}
    virtual_ipaddress {
      {{ virtual_ip }}
  }
  track_script {
    chk_haproxy
  }
}

What is left are the systemd service files. The one for etcd:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/files/etcd.service 
#
# systemd integration for etcd 
# Put this file under /etc/systemd/system/etcd.service
#     then: systemctl daemon-reload
#     then: systemctl list-unit-files | grep etcd
#     then: systemctl enable etcd.service
#

[Unit]
Description=dbi services etcd service
After=network.target

[Service]
User=postgres
Type=notify
ExecStart=/u01/app/postgres/local/dmk/bin/etcd --config-file /u01/app/postgres/local/dmk/etc/etcd.conf
Restart=always
RestartSec=10s
LimitNOFILE=40000

[Install]
WantedBy=multi-user.target

For Patroni:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/files/patroni.service 
#
# systemd integration for patroni 
# Put this file under /etc/systemd/system/patroni.service
#     then: systemctl daemon-reload
#     then: systemctl list-unit-files | grep patroni
#     then: systemctl enable patroni.service
#

[Unit]
Description=dbi services patroni service
After=etcd.service syslog.target network.target

[Service]
User=postgres
Group=postgres
Type=simple
ExecStartPre=-/usr/bin/sudo /sbin/modprobe softdog
ExecStartPre=-/usr/bin/sudo /bin/chown postgres /dev/watchdog
ExecStart=/u01/app/postgres/local/dmk/bin/patroni /u01/app/postgres/local/dmk/etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
Restart=no
TimeoutSec=30

[Install]
WantedBy=multi-user.target

The last bit is the site definition which combines all of the above.

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ cat roles/patroni/site.yml 
---
# This playbook deploys a three node patroni PostgreSQL cluster with HAProxy

- hosts: patroni-servers
  become: true
  become_user: root

  roles:
    - common
    - patroni

Once all of that is in place the palybook can be executed:

dwe@dwe:~/Documents/dbiProjects/dbi_dmk_postgres/dmk/ansible$ ansible-playbook -i ../patroni patroni/site.yml -u postgres

This runs for a couple of minutes as especially upgrading all the operating system packages and comling PostgreSQL will take some time. Once it completed you only need to reboot the systems and your cluster is ready:

postgres@patroni1:/home/postgres/ [pg11.1] patronictl list 
+---------+----------+----------------+--------+---------+-----------+
| Cluster |  Member  |      Host      |  Role  |  State  | Lag in MB |
+---------+----------+----------------+--------+---------+-----------+
|   PG1   | patroni1 | 192.168.22.240 | Leader | running |       0.0 |
|   PG1   | patroni2 | 192.168.22.241 |        | running |       0.0 |
|   PG1   | patroni3 | 192.168.22.242 |        | running |       0.0 |
+---------+----------+----------------+--------+---------+-----------+

HAProy is running as well on all three nodes and you can check that by pointing your browser to any of the hosts on port 7000:
Selection_062

Hope that helps.

Cet article Using Ansible to bring up a three node Patroni cluster in minutes est apparu en premier sur Blog dbi services.

ODA: The Patch version must be 12.2.1.4.0

Fri, 2019-01-04 09:43

When trying to patch your virtualized ODA with patch 12.1.2.12.0 and immediately after that with 12.2.1.4.0, you might consider to load the patches like this:


[root@XX1 Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p26433712_1212120_Linux-x86-64_1of2.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.
[root@XX1 Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p26433712_1212120_Linux-x86-64_2of2.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.
[root@XX1 Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p28216780_122140_Linux-x86-64_1of3.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.
[root@XX1 Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p28216780_122140_Linux-x86-64_2of3.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.
[root@XX1 Oracle_Soft_Repo]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p28216780_122140_Linux-x86-64_3of3.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.

After that, a verification attempt shows following strange error:


[root@XX1 tmp]# oakcli update -patch 12.1.2.12.0 --verify
ERROR : The Patch version must be 12.2.1.4.0

Reason is that only the newest patch version is saved in a flag file, so that less recent versions are not known:


[root@XX1 ~]# cat /opt/oracle/oak/pkgrepos/System/VERSION
version=12.2.1.4.0

To install patch 12.1.2.12.0, both patches have to be removed from repository first:


[root@XX1 ~]# oakcli manage cleanrepo --ver 12.2.1.4.0
Deleting the following files...
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OAK/12.2.1.4.0/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE2-24P/0018/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE2-24C/0018/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE3-24C/0306/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/SUN/T4-es-Storage/0342/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/HMP/2.4.1.0.11/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/IPMI/1.8.12.4/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/ASR/5.7.7/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/12.2.0.1.180417/Patches/27674384
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/12.1.0.2.180417/Patches/27338029
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.4.180417/Patches/27338049
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OEL/6.9/Patches/6.9.1
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OVS/12.2.1.4.0/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/GI/12.2.0.1.180417/Base

[root@XX1 ~]# oakcli manage cleanrepo --ver 12.1.2.12.0
Deleting the following files...
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OAK/12.1.2.12.0/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE2-24P/0018/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE2-24C/0018/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE3-24C/0291/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/SUN/T4-es-Storage/0342/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/HMP/2.3.5.2.8/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/IPMI/1.8.12.4/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/ASR/5.5.1/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/12.1.0.2.170814/Patches/26609783
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.4.170814/Patches/26609445
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.3.15/Patches/20760997
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.2.12/Patches/17082367
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OEL/6.8/Patches/6.8.4
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OVS/12.1.2.12.0/Base

After that, patch 12.1.2.1.12.0 has to be reloaded into repository:


[root@XX1 ~]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p26433712_1212120_Linux-x86-64_1of2.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.
[root@XX1 ~]# oakcli unpack -package /cloudfs/Oracle_Soft_Repo/p26433712_1212120_Linux-x86-64_2of2.zip
Unpacking will take some time, Please wait...
Successfully unpacked the files to repository.

Now repository is aware of patch 12.1.2.12.0:


[root@XX1 ~]# cat /opt/oracle/oak/pkgrepos/System/VERSION
version=12.1.2.12.0

[root@XX1 ~]# oakcli update -patch 12.1.2.12.0 --verify

INFO: 2018-11-29 14:14:34: Reading the metadata file now...

Component Name Installed Version Proposed Patch Version

--------------- ------------------ -----------------

Controller_INT 4.650.00-7176 Up-to-date

Controller_EXT 13.00.00.00 Up-to-date

Expander 0018 Up-to-date

SSD_SHARED {

[ c1d20,c1d21,c1d22, A29A Up-to-date

c1d23 ]

[ c1d16,c1d17,c1d18, A29A Up-to-date

c1d19 ]

}

HDD_LOCAL A7E0 Up-to-date

HDD_SHARED {

[ c1d0,c1d1,c1d2,c1d PAG1 Up-to-date

3,c1d4,c1d5,c1d6,c1d

8,c1d9,c1d10,c1d11,c

1d12,c1d13,c1d15 ]

[ c1d7,c1d14 ] PD51 PAG1

}

ILOM 3.2.9.23 r116695 Up-to-date

BIOS 30110000 Up-to-date

IPMI 1.8.12.4 Up-to-date

HMP 2.3.5.2.8 Up-to-date

OAK 12.1.2.11.0 12.1.2.12.0

OL 6.8 Up-to-date

OVM 3.4.3 Up-to-date

GI_HOME 12.1.0.2.170418(2517 12.1.0.2.170814(2660

1037,25942196) 9783,26609945)

DB_HOME 12.1.0.2.170418(2517 12.1.0.2.170814(2660

1037,25942196) 9783,26609945)

After installation of patch 12.1.2.12.0, patch 12.2.1.4.0 has to be loaded into repository and after that it can be installed.

Conclusion: It is only possible to load one patch into repository, before loading a newer patch, the former patch has to be installed.

Cet article ODA: The Patch version must be 12.2.1.4.0 est apparu en premier sur Blog dbi services.

Azure SQL Managed instances, Business Critical Tier and high availability

Fri, 2018-12-28 12:08

Last blog of this year 2018 with SQL databases and Cloud Azure and this is not trivial choice. <Speculation mode> I would say year 2019 could be important for all stakeholders. Cloud transformation has begun in many forms at customer shops and we heard about some big actors who are coming in Switzerland including Azure or Google. Data Platforms services will not be an exception in some degree of magnitude … <end of speculation mode>. I briefly introduced SQL Managed instances on a previous write-up and this service will likely not be available immediately when Azure datacenters will start next year in Switzerland. But I keep an eye on it because I believe it may address a lot of customer requests. So, I remembered to attend a session about SQL Server Managed Instances (SQL MI) a couple of months ago at SQL Saturday Madrid (October 2018) and a question raised about high-availability capabilities in such infrastructure especially if a SQL MI could be part of SQL Server availability groups. At this moment the answer was it is not supported because there is no need to use Always-On to achieve HA within the Azure region. Every instance is inherently highly available as that is one of the core PaaS capabilities in SQL Database including redundancy with Azure storage accounts as well as active and passive compute nodes. I admit I partially agreed because it means we restrict HA scenarios on fully PaaS architectures and I had in mind hybrid scenarios where we might include a SQL MI in AG as standby server for DR for instance. But anyway, at the moment of the session, 2 MI options were available including General Purpose and Business Critical in Preview only and on December 4th 2018 the latter was finally available in GA.

150 - 0 - banner

Let’s say Business Critical tier differs from General Purpose tiers in terms of HA because it provides built-in availability groups behind the scene and auto-failover groups for disaster recovery on a remote Azure region as well.

The interesting point is Business Critical tier enables offloading reporting queries on a hidden RO replica by default. Bear in mind replication is asynchronous between replicas meaning data loss and delay are possible on RO replicas.

As normal AG scenarios, if we specify application intent to read only in connection string transparent redirection takes place. I did a try on my SQL MI after upgrading on Business Critical Tier as shown below:

$ (az sql mi show -g sql-mi-rg -n sql-mi-dbi2 | ConvertFrom-Json).Sku.Tier
BusinessCritical

 

From SSMS, I just switched my connection from READ WRITE to READ ONLY intent and I was redirected transparently to the corresponding replica as follows:

150 - 1 - RO connection

Note that the AG configuration is hidden to users and sysadmins and there is no way to get information from usual system views as follows:

150 - 2 - AG config

However, we may get information of different states of underlying AG replication including 4 replicas as described in the Microsoft documentation.

SELECT 
	d.name as [db_name],
	drs.replica_id,
	drs.group_database_id,
	drs.is_local,
	drs.is_primary_replica,
	drs.synchronization_state_desc AS syncro_state,
	drs.synchronization_health_desc AS synchro_health,
	drs.database_state_desc AS db_state,
	drs.log_send_queue_size,
	drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states AS drs
join sys.databases AS d ON d.database_id = drs.database_id

 

150 - 3 - AG state

 

Auto failover groups is a complementary solution to address DR scenarios and to protect from a failure to of Azure region. Its implementation requires more work and more Azure resources … a challenge for next year!

Season’s greetings!!

 

 

Cet article Azure SQL Managed instances, Business Critical Tier and high availability est apparu en premier sur Blog dbi services.

EDB Failover Manager 3.3 With Virtual Ip Address

Mon, 2018-12-24 04:47

In a previous blog , I talked about installing and configuring Edb Failover Manager with PostgreSQL. The configuration of a Virtual Ip Address was not discussed in this previous article.In this blog I am going to show how we can implement a virtual IP with EFM.
We suppose that
-The standby is already built
-EFM is configured and running (see here for the configuration)
Below our configuration ( Iam using PostgreSQL 11.1 with Centos 7)

[root@dbi-pg-tools efm-3.3]# /usr/edb/efm-3.3/bin/efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Witness     192.168.22.100       UP     N/A
        Master      192.168.22.101       UP     UP
        Standby     192.168.22.102       UP     UP

Allowed node host list:
        192.168.22.101 192.168.22.102 192.168.22.100

Membership coordinator: 192.168.22.101

Standby priority host list:
        192.168.22.102

Promote Status:

        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.22.101       0/301BE38
        Standby     192.168.22.102       0/301BE38

        Standby database(s) in sync with master. It is safe to promote.
[root@dbi-pg-tools efm-3.3]#

To resume we have three servers
-dbi-pg-tools : the witness with IP 192.168.22.100
-dbi-pg-essentials : the master with IP 192.168.22.101
-dbi-pg-essentials_2 : the standby with IP 192.168.22.102
Let’s say that we want to configure a virtual IP 192.168.22.105.
The first step is to update the efm.properties on both nodes with following lines

virtualIp=192.168.22.105
virtualIp.interface=enp0s9   -- name of my interface
virtualIp.prefix=24
virtualIp.single=true

Then restart efm on both nodes.

[root@dbi-pg-essentials network-scripts]#  service efm-3.3 start

Running again the status command should show the VIP address. Restarting efm should be enough but if ever the virtual IP is not shown we can use following command to add the virtual IP.

[root@dbi-pg-essentials network-scripts]# /usr/edb/efm-3.3/bin/efm_address add4 enp0s9 192.168.22.105/24

Then the status should be

[root@dbi-pg-tools ~]# /usr/edb/efm-3.3/bin/efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Witness     192.168.22.100       UP     N/A      192.168.22.105
        Master      192.168.22.101       UP     UP       192.168.22.105*
        Standby     192.168.22.102       UP     UP       192.168.22.105

Allowed node host list:
        192.168.22.101 192.168.22.102 192.168.22.100

Membership coordinator: 192.168.22.101

Standby priority host list:
        192.168.22.102

Promote Status:

        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.22.101       0/301BEE0
        Standby     192.168.22.102       0/301BEE0

        Standby database(s) in sync with master. It is safe to promote.
[root@dbi-pg-tools ~]#

We can manually ping the virtual IP from both servers. There should not exist any loss packets

[root@dbi-pg-tools ~]# ping -q -c3 -w5 192.168.22.105
PING 192.168.22.105 (192.168.22.105) 56(84) bytes of data.

--- 192.168.22.105 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.278/0.366/0.528/0.116 ms
[root@dbi-pg-tools ~]#

And from the master we can see that the VIP address is assigned to enp0s9

[root@dbi-pg-essentials network-scripts]# ifconfig enp0s9                       
enp0s9: flags=4163  mtu 1500
        inet 192.168.22.105  netmask 255.255.255.0  broadcast 0.0.0.0
        ether 08:00:27:53:1c:ed  txqueuelen 1000  (Ethernet)
        RX packets 3394  bytes 397433 (388.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 3096  bytes 571448 (558.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@dbi-pg-essentials network-scripts]#

So we can use the virtual address to connect to the primary database

[postgres@dbi-pg-tools ~]$ psql -h 192.168.22.105
Password for user postgres:
psql (11.1)
Type "help" for help.

postgres=# \c sample
You are now connected to database "sample" as user "postgres".
sample=# insert into mytab values(1);
INSERT 0 1
sample=#

And now let’s do a switchover

[root@dbi-pg-tools ~]# /usr/edb/efm-3.3/bin/efm promote efm -switchover
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.
[root@dbi-pg-tools ~]#

A few moment after the switchover, we can see that the vip is now assigned to the new master

[root@dbi-pg-essentials_2 efm-3.3]# ifconfig enp0s9
enp0s9: flags=4163  mtu 1500
        inet 192.168.22.105  netmask 255.255.255.0  broadcast 0.0.0.0
        ether 08:00:27:4d:4d:4d  txqueuelen 1000  (Ethernet)
        RX packets 1235  bytes 149947 (146.4 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 837  bytes 525953 (513.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

That we can verify by running the status command

[root@dbi-pg-tools ~]# /usr/edb/efm-3.3/bin/efm cluster-status efm
Cluster Status: efm

        Agent Type  Address              Agent  DB       VIP
        -----------------------------------------------------------------------
        Witness     192.168.22.100       UP     N/A      192.168.22.105
        Standby     192.168.22.101       UP     UP       192.168.22.105
        Master      192.168.22.102       UP     UP       192.168.22.105*

Allowed node host list:
        192.168.22.101 192.168.22.102 192.168.22.100

Membership coordinator: 192.168.22.102

Standby priority host list:
        192.168.22.101

Promote Status:

        DB Type     Address              XLog Loc         Info
        --------------------------------------------------------------
        Master      192.168.22.102       0/301E260
        Standby     192.168.22.101       0/301E260

        Standby database(s) in sync with master. It is safe to promote.
[root@dbi-pg-tools ~]#
Conclusion :

In this blog we have seen how we can configure a virtual IP with EFM. Hope that will help

Cet article EDB Failover Manager 3.3 With Virtual Ip Address est apparu en premier sur Blog dbi services.

ODA : Free up space on local filesystems

Fri, 2018-12-21 10:02
Introduction

When you work on ODA you sometimes get struggled with local filesystem free space. ODA has terabytes of space on data disks, but local disks are still limited to a raid-1 array of 2x 480GB disks. And only few GB are dedicated to / and /u01 filesystems. You do not need hundreds of GB on these filesystems, but I think that you prefer to keep at least 20-30% of free space. And if you plan to patch your ODA, you surely need more space to pass all the steps without reaching dangerous level of filling. Here is how to grab free space on these filesystems.

Use additional purgeLogs script

PurgeLogs script is provided as an additional tool from Oracle. It should have been available with oakcli/odacli but it’s not. Download it from MOS note 2081655.1. As this tool is not part of the official ODA tool, please test it before using it on a production environment. It’s quite easy to use, put the zip in a folder, unzip it, and run it with root user. You can use this script with a single parameter that will clean up all the logfiles for all the Oracle products aged of a number of days:


df -h /
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 55G 29G 23G 56% /
df -h /u01/
Filesystem Size Used Avail Use% Mounted on
/dev/xvdb1 92G 43G 45G 50% /u01

cd /tmp/
unzip purgeLogs.zip
du -hs /opt/oracle/oak/log/*
11G /opt/oracle/oak/log/aprhodap02db0
4.0K /opt/oracle/oak/log/fishwrap
232K /opt/oracle/oak/log/test
./purgeLogs -days 1

--------------------------------------------------------
purgeLogs version: 1.43
Author: Ruggero Citton
RAC Pack, Cloud Innovation and Solution Engineering Team
Copyright Oracle, Inc.
--------------------------------------------------------

2018-12-20 09:20:06: I adrci GI purge started
2018-12-20 09:20:06: I adrci GI purging diagnostic destination diag/asm/+asm/+ASM1
2018-12-20 09:20:06: I ... purging ALERT older than 1 days

2018-12-20 09:20:47: S Purging completed succesfully!
du -hs /opt/oracle/oak/log/*
2.2G /opt/oracle/oak/log/aprhodap02db0
4.0K /opt/oracle/oak/log/fishwrap
28K /opt/oracle/oak/log/test


df -h /
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 55G 18G 34G 35% /
df -h /u01/
Filesystem Size Used Avail Use% Mounted on
/dev/xvdb1 92G 41G 47G 48% /u01

In this example, you just freed up about 13GB. If your ODA is composed of 2 nodes, don’t forget to use the same script on the other node.

Truncate hardware log traces

Hardware related traces are quietly filling up the filesystem if your ODA is running since a long time. This traces are located under /opt/oracle/oak/log/`hostname`/adapters. I don’t know if each model has this kind of behaviour but this was an example on an old X4-2 running for 3 years now.

cd /opt/oracle/oak/log/aprhodap02db0/adapters
ls -lrth
total 2.2G
-rw-r--r-- 1 root root 50M Dec 20 09:26 ServerAdapter.log
-rw-r--r-- 1 root root 102M Dec 20 09:27 ProcessorAdapter.log
-rw-r--r-- 1 root root 794M Dec 20 09:28 MemoryAdapter.log
-rw-r--r-- 1 root root 110M Dec 20 09:28 PowerSupplyAdapter.log
-rw-r--r-- 1 root root 318M Dec 20 09:30 NetworkAdapter.log
-rw-r--r-- 1 root root 794M Dec 20 09:30 CoolingAdapter.log
head -n 3 CoolingAdapter.log
[Mon Apr 27 18:02:28 CEST 2015] Action script '/opt/oracle/oak/adapters/CoolingAdapter.scr' for resource [CoolingType] called for action discovery
In CoolingAdapter.scr
[Mon Apr 27 18:07:28 CEST 2015] Action script '/opt/oracle/oak/adapters/CoolingAdapter.scr' for resource [CoolingType] called for action discovery
head -n 3 MemoryAdapter.log
[Mon Apr 27 18:02:26 CEST 2015] Action script '/opt/oracle/oak/adapters/MemoryAdapter.scr' for resource [MemoryType] called for action discovery
In MemoryAdapter.scr
[Mon Apr 27 18:07:25 CEST 2015] Action script '/opt/oracle/oak/adapters/MemoryAdapter.scr' for resource [MemoryType] called for action discovery

Let’s purge the oldest lines in these files:

for a in `ls *.log` ; do tail -n 200 $a > tmpfile ; cat tmpfile > $a ; rm -f tmpfile; done
ls -lrth
total 176K
-rw-r--r-- 1 root root 27K Dec 20 09:32 CoolingAdapter.log
-rw-r--r-- 1 root root 27K Dec 20 09:32 ProcessorAdapter.log
-rw-r--r-- 1 root root 30K Dec 20 09:32 PowerSupplyAdapter.log
-rw-r--r-- 1 root root 29K Dec 20 09:32 NetworkAdapter.log
-rw-r--r-- 1 root root 27K Dec 20 09:32 MemoryAdapter.log
-rw-r--r-- 1 root root 27K Dec 20 09:32 ServerAdapter.log

2GB of traces you’ll never use! Don’t forget the second node on a HA ODA.

Purge old patches in the repository: simply because they are useless

If you successfully patched your ODA at least 2 times, you can remove the oldest patch in the ODA repository. As you may know, patches are quite big in size because they include a lot of things. So it’s a good practise to remove the oldest patches when you have successfuly patched your ODA. To identify if old patches are still on your ODA, you can dig into folder /opt/oracle/oak/pkgrepos/orapkgs/. Purge of old patches is easy:

df -h / >> /tmp/dbi.txt
oakcli manage cleanrepo --ver 12.1.2.6.0
Deleting the following files...
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OAK/12.1.2.6.0/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/Seagate/ST95000N/SF04/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/Seagate/ST95001N/SA03/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/WDC/WD500BLHXSUN/5G08/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H101860SFSUN600G/A770/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/Seagate/ST360057SSUN600G/0B25/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HITACHI/H106060SDSUN600G/A4C0/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HITACHI/H109060SESUN600G/A720/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HITACHI/HUS1560SCSUN600G/A820/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/HSCAC2DA6SUN200G/A29A/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/HSCAC2DA4SUN400G/A29A/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/STEC/ZeusIOPs-es-G3/E12B/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/STEC/Z16IZF2EUSUN73G/9440/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE2-24P/0018/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE2-24C/0018/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/ORACLE/DE3-24C/0291/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Ilom/SUN/X4370-es-M2/3.0.16.22.f-es-r100119/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HITACHI/H109090SESUN900G/A720/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/STEC/Z16IZF4EUSUN200G/944A/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H7240AS60SUN4.0T/A2D2/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H7240B520SUN4.0T/M554/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Disk/HGST/H7280A520SUN8.0T/P554/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Expander/SUN/T4-es-Storage/0342/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Ilom/SUN/X4170-es-M3/3.2.4.26.b-es-r101722/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Ilom/SUN/X4-2/3.2.4.46.a-es-r101689/Base
Deleting the files under /opt/oracle/oak/pkgrepos/thirdpartypkgs/Firmware/Ilom/SUN/X5-2/3.2.4.52-es-r101649/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/HMP/2.3.4.0.1/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/IPMI/1.8.12.4/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/ASR/5.3.1/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/12.1.0.2.160119/Patches/21948354
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.4.160119/Patches/21948347
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.3.15/Patches/20760997
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/DB/11.2.0.2.12/Patches/17082367
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OEL/6.7/Patches/6.7.1
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/OVS/12.1.2.6.0/Base
Deleting the files under /opt/oracle/oak/pkgrepos/orapkgs/GI/12.1.0.2.160119/Base
df -h / >> /tmp/dbi.txt
cat /tmp/dbi.txt
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 55G 28G 24G 54% /
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 55G 21G 31G 41% /

Increase /u01 filesystem with remaining space

This only concern ODAs in bare metal. You may have noticed that not all the disk space is allocated to your ODA local filesystems. On modern ODAs, you have 2 M2 SSD of 480GB each in a RAID1 configuration for the system, and only half of the space is allocated. As the appliance is using LogicalVolumes, you can extend very easily the size of your /u01 filesystem.

This is an example on a X7-2M:


vgdisplay
--- Volume group ---
VG Name VolGroupSys
System ID
Format lvm2
Metadata Areas 1
Metadata Sequence No 7
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 6
Open LV 4
Max PV 0
Cur PV 1
Act PV 1
VG Size 446.00 GiB
PE Size 32.00 MiB
Total PE 14272
Alloc PE / Size 7488 / 234.00 GiB
Free PE / Size 6784 / 212.00 GiB
VG UUID wQk7E2-7M6l-HpyM-c503-WEtn-BVez-zdv9kM


lvdisplay
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolRoot
LV Name LogVolRoot
VG Name VolGroupSys
LV UUID icIuHv-x9tt-v2fN-b8qK-Cfch-YfDA-xR7y3W
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2018-03-20 13:40:00 +0100
LV Status available
# open 1
LV Size 30.00 GiB
Current LE 960
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:0
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolU01
LV Name LogVolU01
VG Name VolGroupSys
LV UUID ggYNkK-GfJ4-ShHm-d5eG-6cmu-VCdQ-hoYzL4
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2018-03-20 13:40:07 +0100
LV Status available
# open 1
LV Size 100.00 GiB
Current LE 3200
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:2
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolOpt
LV Name LogVolOpt
VG Name VolGroupSys
LV UUID m8GvKZ-zgFF-2gXa-NSCG-Oy9l-vTYd-ALi6R1
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2018-03-20 13:40:30 +0100
LV Status available
# open 1
LV Size 60.00 GiB
Current LE 1920
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:3
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolSwap
LV Name LogVolSwap
VG Name VolGroupSys
LV UUID 9KWiYw-Wwot-xCmQ-uzCW-mILq-rsPz-t2X2pr
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2018-03-20 13:40:44 +0100
LV Status available
# open 2
LV Size 24.00 GiB
Current LE 768
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:1
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolDATA
LV Name LogVolDATA
VG Name VolGroupSys
LV UUID oTUQsd-wpYe-0tiA-WBFk-719z-9Cgd-ZjTmei
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2018-03-20 13:55:25 +0100
LV Status available
# open 0
LV Size 10.00 GiB
Current LE 320
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:4
--- Logical volume ---
LV Path /dev/VolGroupSys/LogVolRECO
LV Name LogVolRECO
VG Name VolGroupSys
LV UUID mJ3yEO-g0mw-f6IH-6r01-r7Ic-t1Kt-1rf36j
LV Write Access read/write
LV Creation host, time localhost.localdomain, 2018-03-20 13:55:25 +0100
LV Status available
# open 0
LV Size 10.00 GiB
Current LE 320
Segments 1
Allocation inherit
Read ahead sectors auto
- currently set to 256
Block device 249:5

212GB are available. Let’s take 100GB for extending /u01:


lvextend -L +100G /dev/mapper/VolGroupSys-LogVolU01
Size of logical volume VolGroupSys/LogVolU01 changed from 100.00 GiB (3200 extents) to 200.00 GiB.
Logical volume LogVolU01 successfully resized.

Filesystem needs to be resized:

resize2fs /dev/mapper/VolGroupSys-LogVolU01
resize2fs 1.43-WIP (20-Jun-2013)
Filesystem at /dev/mapper/VolGroupSys-LogVolU01 is mounted on /u01; on-line resizing required
old_desc_blocks = 7, new_desc_blocks = 13
Performing an on-line resize of /dev/mapper/VolGroupSys-LogVolU01 to 52428800 (4k) blocks.
The filesystem on /dev/mapper/VolGroupSys-LogVolU01 is now 52428800 blocks long.

Now /u01 is bigger:

df -h /dev/mapper/VolGroupSys-LogVolU01
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroupSys-LogVolU01
197G 77G 111G 41% /u01

Conclusion

Don’t hesitate to clean up your ODA before having to deal with space pressure.

Cet article ODA : Free up space on local filesystems est apparu en premier sur Blog dbi services.

PAM Authentication With PostgreSQL

Fri, 2018-12-21 08:52

PostgreSQl supports many authentication methods. The PAM authentication method operates similarly to password except that it uses PAM (Pluggable Authentication Modules) as the authentication mechanism. The user must exist in the database before PAM can be used for authentication.
In this blog I will configure PAM authentication for a PostgreSQL cluster 11 running on a centos 7.

postgres=# select version();
                                                 version

--------------------------------------------------------------------------------
-------------------------
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (R
ed Hat 4.8.5-28), 64-bit
(1 row)

postgres=#

[root@dbi-pg-essentials ~]# cat /etc/centos-release
CentOS Linux release 7.3.1611 (Core)
[root@dbi-pg-essentials ~]# hostname
dbi-pg-essentials

We suppose that PostgreSQL is already installed with the PAM module. This should be the case if the installation was done with yum. If you decide to install using the sources, be sure to configure with the option –with-pam
With the installation this should exist a service named postgresql in the /etc/pam.d directory. If not you have to create a service for postgresql.

[postgres@dbi-pg-essentials pam.d]# pwd
/etc/pam.d
[root@dbi-pg-essentials pam.d]# ls -l postgresql
-rw-r--r--. 1 root root 71 Nov  7 12:37 postgresql
[root@dbi-pg-essentials pam.d]#

The first step is then to configure PostgreSQL to accept PAM authentication. Like other authentication methods, we have to add the corresponding entries in the pg_hba.conf file

[postgres@dbi-pg-essentials data]$ grep pamservice pg_hba.conf
host    all             all            192.168.22.0/24        pam pamservice=postgresql
[postgres@dbi-pg-essentials data]$

We can note the option pamservice=postgresql. Don’t forget to reload or to restart your cluster after modifying the pg_hba.conf file.
In my case I also had to change the permissions of /etc/shadow file to following

[postgres@dbi-pg-essentials data]$ ls -l /etc/shadow
-r--r--r-- 1 root root 988 Dec 21 11:20 /etc/shadow
[postgres@dbi-pg-essentials data]$

And the configuration is done. For the test let’s create a linux user named for example usrpam in the server

[root@dbi-pg-essentials ~]# useradd -m usrpam
[root@dbi-pg-essentials ~]# passwd usrpam
Changing password for user usrpam.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@dbi-pg-essentials ~]#

As specified earlier, the user should exist in the database before PAM authentication can be used. So let’s create the same user in PostgreSQL but without password

postgres=# create user usrpam with login;
CREATE ROLE
postgres=#

And now the usrpam should be able to connect from any client in the network 192.168.22.0.

[usr2@dbi-pg-essentials_2 ~]$  psql -h dbi-pg-essentials -U usrpam -d postgres  
Password for user usrpam:
psql (11.1)
Type "help" for help.

postgres=> select user;
  user
--------
 usrpam
(1 row)

postgres=>

Cet article PAM Authentication With PostgreSQL est apparu en premier sur Blog dbi services.

Deploying SQL Server on K8s with Helm charts

Tue, 2018-12-18 10:06

During the last DockerCon EU in Barcelona, I heard a lot about Helm with K8s architectures. It was also a good opportunity to write about it after attending to this conference.

blog 149 - 0 - banner

In a nutshell, Helm is a package manager for K8s and you may think of it like the other ones available on the Linux side with apt, yum or zypper to cite few of them. Helm charts are a key component of the Helm architecture and make deployments easy, standardized and reusable and this is definitely what I was looking for our current CI/CD pipeline implementation for DMK maintenance tool.

Helm matters for enterprise-scale deployments by addressing common challenges with the following (non-exhaustive) list of capabilities

  • Helm charts can be shared easily across the enterprise or with contributors over the world from GitHub repository.
  • Using helm charts allow to get quickly a specific environment for testing
  • Existing charts can be authored for specific deployments regarding the context
  • The easy deployment and deletion of applications make the Helm adoption easier
  • Production- ready packages are possible and eliminate deployment errors due to incorrect configuration files and reduce the complexity of maintaining application catalog

In my case, it’s been a while since I have in mind to simplify my first SQL Server container deployments on K8s with a complex YAML file including a lot of objects like services, pods, secrets and persistent volumes with Helm charts. One additional motivation was the capability to change in-flight some preconfigured settings in the deployment when I wanted to switch from my minikube environment to my AKS cluster on Azure.

In this first write-up I used a custom dbi services image for SQL Server (a production-ready docker image) and I decided to use this image as based of my custom Helm chart. First of all, let’s say I didn’t start from scratch and I used the mssql-linux stable chart available of GitHub but obviously I customized it for my own requirements:

  • The custom dbi services image for SQL Server includes the creation of the flexible architecture and I had to update the persistence volume and claims configuration with this new storage map.
  • The custom image leverages the deployment of our DMK maintenance tool (optional) that includes different SQL objects to perform maintenance of customer databases (basically update stats, rebuild index and backup tasks). So, I needed to add a parameter to enable or not the deployment of this tool inside the pod.
  • TSQL scripts are also executed during the container startup and they apply different server level configuration, configure tempdb database files placement and add some trace flags to meet our best practices. But no real impact on the helm chart here.
  • An “application” user may be created (optional) and will be part of the db_creator server role according to the least privilege principle. In most cases we consider an application doesn’t need sysadmin privileges even on a SQL Server pod and more generally speaking on microservice architectures. So as already done for the DMK parameter described previously, I had to add another one parameter for creating this user when the pod is spin up.

Let’s first begin with my helm chart hierarchy folder which includes important files including Chart.yaml, values.yaml and deployment.yaml.

[dab@DBI-LT-DAB:#]> tree /f
…
T:.
│   .helmignore
│   Chart.yaml
│   values.yaml
│
├───charts
└───templates
        deployment.yaml
        NOTES.txt
        pvc-app.yaml
        pvc-backup.yaml
        pvc-data.yaml
        pvc-tempdb.yaml
        pvc-tranlog.yaml
        secret.yaml
        service.yaml
        _helpers.tpl

 

Let’s focus on the deployment.yaml file and the customized part within the spec.containers.env section related to my docker image specifications:

  • MSSQL_USER, MSSQL_USER_PASSWORD are environment variables related to my “application” user
  • DMK environment variable enables deployment of the DMK maintenance tool

In addition, the environment variables related to the database file placement have been customized for master, tempdb and user databases according to my flexible architecture specifications with:

  • /u00 (for application files)
  • /u01 (for user data and system database files)
  • /u02 (for transaction log files)
  • /u03 (for tempdb database files)
  • /u98 (for backup files).

MountPaths and persistent volume claims section have also been updated accordingly as shown below:

apiVersion: apps/v1beta2
kind: Deployment
metadata:
  name: {{ template "mssql.fullname" . }}
  labels:
    app: {{ template "mssql.name" . }}
    chart: {{ .Chart.Name }}-{{ .Chart.Version | replace "+" "_" }}
    release: {{ .Release.Name }}
    heritage: {{ .Release.Service }}
{{- if .Values.deployment.annotations }}
  annotations:
{{ toYaml .Values.deployment.annotations | indent 4 }}
{{- end }}
spec:
  replicas: {{ .Values.replicaCount }}
  selector:
    matchLabels:
      app: {{ template "mssql.name" . }}
      release: {{ .Release.Name }}
  template:
    metadata:
      labels:
        app: {{ template "mssql.name" . }}
        release: {{ .Release.Name }}
    spec:
      containers:
        - name: {{ .Chart.Name }}
          image: "{{ .Values.image.repository }}:{{ .Values.image.tag }}"
          imagePullPolicy: {{ .Values.image.pullPolicy }}
          env:
            - name: ACCEPT_EULA
              value: "{{ .Values.acceptEula.value | upper }}"
            - name: MSSQL_PID
              value: "{{ .Values.edition.value }}"
            - name: MSSQL_SA_PASSWORD
              valueFrom:
               secretKeyRef:
                 name: {{ template "mssql.fullname" . }}-sa-secret
                 key: sapassword
            - name: MSSQL_USER
              value: "{{ .Values.usersql.value }}"
            - name: MSSQL_USER_PASSWORD
              valueFrom:
               secretKeyRef:
                 name: {{ template "mssql.fullname" . }}-user-secret
                 key: userpassword
            - name: DMK
              value: "{{ .Values.DMK.value }}"
            - name: MSSQL_MASTER_DATA_FILE
              value: /u01/sqlserverdata/mssqlserver/master.mdf
            - name: MSSQL_MASTER_LOG_FILE
              value: /u01/sqlserverdata/mssqlserver/mastlog.ldf
            - name: MSSQL_DATA_DIR
              value: /u01/sqlserverdata/mssqlserver
            - name: MSSQL_LOG_DIR
              value: /u02/sqlserverlog/mssqlserver
            - name: MSSQL_TEMPDBDATA_DIR
              value: /u03/sqlservertempdb/mssqlserver
            - name: MSSQL_TEMPDBLOG_DIR
              value: /u03/sqlservertempdb/mssqlserver
            - name: MSSQL_BACKUP_DIR
              value: /u98/sqlserver/backup/mssqlserver
            - name: MSSQL_ERROR_LOG
              value: /u00/app/sqlserver/admin/mssqlserver/log
            - name: MSSQL_DUMP_DIR
              value: /u00/app/sqlserver/admin/mssqlserver/dump
            - name: MSSQL_TCP_PORT
              value: "{{ .Values.service.port.value }}"
            - name: MSSQL_LCID
              value: "{{ .Values.lcid.value }}"
            - name: MSSQL_COLLATION
              value: "{{ .Values.collation.value }}"
            - name: MSSQL_ENABLE_HADR
              value: "{{ .Values.hadr.value }}"
            {{ if .Values.resources.limits.memory }}
            - name: MSSQL_MEMORY_LIMIT_MB
              valueFrom:
                resourceFieldRef:
                  resource: limits.memory
                  divisor: 1Mi
            {{ end }}
          ports:
            - name: mssql
              containerPort: {{ .Values.service.port.value }}
          volumeMounts:
            - name: data
              mountPath: /u01
            - name: transactionlog
              mountPath: /u02
            - name: tempdb
              mountPath: /u03
            - name: backup
              mountPath: /u98 
            - name: app
              mountPath: /u00
          livenessProbe:
            tcpSocket:
               port: mssql
            initialDelaySeconds: {{ .Values.livenessprobe.initialDelaySeconds }}
            periodSeconds: {{ .Values.livenessprobe.periodSeconds }}
          readinessProbe:
            tcpSocket:
               port: mssql
            initialDelaySeconds: {{ .Values.readinessprobe.initialDelaySeconds }}
            periodSeconds: {{ .Values.readinessprobe.periodSeconds }}
          resources:
{{ toYaml .Values.resources | indent 12 }}
    {{- if .Values.nodeSelector }}
      nodeSelector:
{{ toYaml .Values.nodeSelector | indent 8 }}
    {{- end }}
      volumes:
      - name: master
      {{- if .Values.persistence.enabled }}
        persistentVolumeClaim:
          {{- if .Values.persistence.existingMasterClaim }}
          claimName: {{ .Values.persistence.existingMasterClaim }}
          {{- else }}
          claimName: {{ template "mssql.fullname" . }}-master
          {{- end }}
      {{- else }}
        emptyDir: {}
      {{- end }}
      - name: data
      {{- if .Values.persistence.enabled }}
        persistentVolumeClaim:
          {{- if .Values.persistence.existingDataClaim }}
          claimName: {{ .Values.persistence.existingDataClaim }}
          {{- else }}
          claimName: {{ template "mssql.fullname" . }}-data
          {{- end -}}
      {{- else }}
        emptyDir: {}
      {{- end }}
      - name: transactionlog
      {{- if .Values.persistence.enabled }}
        persistentVolumeClaim:
          {{- if .Values.persistence.existingTransactionLogClaim }}
          claimName: {{ .Values.persistence.existingTransactionLogClaim }}
          {{- else }}
          claimName: {{ template "mssql.fullname" . }}-translog
          {{- end }}
      {{- else }}
        emptyDir: {}
      {{- end }}
      - name: tempdb
      {{- if .Values.persistence.enabled }}
        persistentVolumeClaim:
          {{- if .Values.persistence.existingTempdbClaim }}
          claimName: {{ .Values.persistence.existingTempdbClaim }}
          {{- else }}
          claimName: {{ template "mssql.fullname" . }}-tempdb
          {{- end }}
      {{- else }}
        emptyDir: {}
      {{- end }}
      - name: backup
      {{- if .Values.persistence.enabled }}
        persistentVolumeClaim:
          {{- if .Values.persistence.existingBackupClaim }}
          claimName: {{ .Values.persistence.existingBackupClaim }}
          {{- else }}
          claimName: {{ template "mssql.fullname" . }}-backup
          {{- end }}
      {{- else }}
        emptyDir: {}
      {{- end }}
      - name: app
      {{- if .Values.persistence.enabled }}
        persistentVolumeClaim:
          {{- if .Values.persistence.existingApppClaim }}
          claimName: {{ .Values.persistence.existingAppClaim }}
          {{- else }}
          claimName: {{ template "mssql.fullname" . }}-app
          {{- end }}
      {{- else }}
        emptyDir: {}
      {{- end }}

 

Referring to my flexible architecture, I added 2 YAML files that contain the new persistent volumes definition for respectively pvc-app for /u00 (app) and pvc-tempdb for /u03 (tempdb).

Here the content of my persistent volume claim for tempdb for instance:

{{- if and .Values.persistence.enabled (not .Values.persistence.existingTempdbClaim) }}
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: {{ template "mssql.fullname" . }}-tempdb
  labels:
    app: {{ template "mssql.fullname" . }}
    chart: "{{ .Chart.Name }}-{{ .Chart.Version }}"
    release: "{{ .Release.Name }}"
    heritage: "{{ .Release.Service }}"
{{- if .Values.persistence.annotations }}
  annotations:
{{ toYaml .Values.persistence.annotations | indent 4 }}
{{- end }}
spec:
  accessModes:
    - {{ .Values.persistence.tempdbAccessMode | quote }}
  resources:
    requests:
      storage: {{ .Values.persistence.tempdbSize | quote }}
{{- if .Values.persistence.storageClass }}
{{- if (eq "-" .Values.persistence.storageClass) }}
  storageClassName: ""
{{- else }}
  storageClassName: "{{ .Values.persistence.storageClass }}"
{{- end }}
{{- end }}
{{- end -}}

 

I added to the secret.yaml to include a section dedicated to my “application” user password

---
apiVersion: v1
kind: Secret
metadata:
  name: {{ template "mssql.fullname" . }}-user-secret
  labels:
    app: {{ template "mssql.name" . }}
    chart: {{ .Chart.Name }}-{{ .Chart.Version | replace "+" "_" }}
    release: {{ .Release.Name }}
    heritage: {{ .Release.Service }}
type: Opaque
data:
  {{ if .Values.userpassword }}
  userpassword:  {{ .Values.userpassword.value | b64enc | quote }}
  {{ else }}
  userpassword: {{ randAlphaNum 20 | b64enc | quote }}
{{ end }}

 

Note the helm chart allows you to define your own password or if empty it will generate an random password instead.

Finally, the values.yaml file contains predefined values for my release deployment

# General parameters
acceptEula: 
  value: "Y"
edition: 
  value: "Developer"
DMK: 
  value: "N"
collation: 
  value: SQL_Latin1_General_CP1_CI_AS
lcid: 
  value: 1033
hadr: 
    value: 0
# User parameters
sapassword: 
  value: Password1
usersql: 
  value: dbi_user
userpassword: 
  value: Password2
# Image parameters
image:
  repository: dbi/mssql-server-linux
  tag: 2017-CU12
  pullPolicy: IfNotPresent
# Service parameters
service:
  type: 
    value: LoadBalancer
  port: 
    value: 1433
  annotations: {}
deployment:
  annotations: {}
# Volumes & persistence parameters
persistence:
  enabled: true
  # existingDataClaim:
  # existingTransactionLogClaim:
  # existingBackupClaim:
  # existingMasterClaim:
  # existingAppClaim:
  # existingTempdbClaim:
  storageClass: ""
  dataAccessMode: ReadWriteOnce
  dataSize: 5Gi
  transactionLogAccessMode: ReadWriteOnce
  transactionLogSize: 5Gi
  tempdbAccessMode: ReadWriteOnce
  tempdbSize: 5Gi
  backupAccessMode: ReadWriteOnce
  backupSize: 5Gi
  masterAccessMode: ReadWriteOnce
  masterSize: 5Gi
  appAccessMode: ReadWriteOnce
  appSize: 5Gi
# Probe parameters
livenessprobe:
  initialDelaySeconds: 20
  periodSeconds: 15
readinessprobe:
  initialDelaySeconds: 20
  periodSeconds: 15
# Resourcep parameters
resources:
  limits:
  #  cpu: 100m
    memory: 5Gi
  # requests:
  #  cpu: 100m
  #  memory: 2Gi
nodeSelector: {}
  # kubernetes.io/hostname: minikube

Let’s install my environment release from the helm command below:

$ helm install --name sqlhelm . --set DMK.value=Y --set service.port.value=1451

 

Pretty simple right? Note also that I may change predefined parameter values according to my context very easily. For instance, the DMK maintenance tool is not installed by default when the container is spin up by default and I changed it by explicitly setup the DMK.value to Y. The same applies for the SQL Server port exposed through the service, by default 1433 changed to 1451 in my helm command.

The result is as follows:

LAST DEPLOYED: Mon Dec 17 23:23:26 2018
NAMESPACE: default
STATUS: DEPLOYED

RESOURCES:
==> v1/PersistentVolumeClaim
NAME                              STATUS  VOLUME                                    CAPACITY  ACCESS MODES  STORAGECLASS  AGE
sqlhelm-dbi-mssql-linux-app       Bound   pvc-5faffb52-024a-11e9-bd56-00155d0013d3  5Gi       RWO           hostpath      8m57s
sqlhelm-dbi-mssql-linux-backup    Bound   pvc-5fb0c43a-024a-11e9-bd56-00155d0013d3  5Gi       RWO           hostpath      8m57s
sqlhelm-dbi-mssql-linux-data      Bound   pvc-5fb32657-024a-11e9-bd56-00155d0013d3  5Gi       RWO           hostpath      8m57s
sqlhelm-dbi-mssql-linux-tempdb    Bound   pvc-5fb680fe-024a-11e9-bd56-00155d0013d3  5Gi       RWO           hostpath      8m57s
sqlhelm-dbi-mssql-linux-translog  Bound   pvc-5fbb9350-024a-11e9-bd56-00155d0013d3  5Gi       RWO           hostpath      8m57s

==> v1/Service
NAME                     TYPE          CLUSTER-IP   EXTERNAL-IP  PORT(S)         AGE
sqlhelm-dbi-mssql-linux  LoadBalancer  10.99.4.205  localhost    1451:32569/TCP  8m57s

==> v1beta2/Deployment
NAME                     DESIRED  CURRENT  UP-TO-DATE  AVAILABLE  AGE
sqlhelm-dbi-mssql-linux  1        1        1           1          8m57s

==> v1/Pod(related)
NAME                                      READY  STATUS   RESTARTS  AGE
sqlhelm-dbi-mssql-linux-67c4898dfb-qlfgr  1/1    Running  0         8m56s

==> v1/Secret
NAME                                 TYPE    DATA  AGE
sqlhelm-dbi-mssql-linux-user-secret  Opaque  1     8m57s
sqlhelm-dbi-mssql-linux-sa-secret    Opaque  1     8m57s

 

This command provides a picture of the deployed components and their different status including the persistent volume claims, my SQL Server pod, the service that exposes the SQL Server port and the K8s secrets for sa and my “application” user passwords. This picture is available at any moment by executing the following command:

$ helm status sqlhelm

 

We may also retrieve a list of existing releases from the following helm command:

$ helm ls sqlhelm
NAME    REVISION        UPDATED                         STATUS          CHART                   APP VERSION     NAMESPACE
sqlhelm 1               Mon Dec 17 23:23:26 2018        DEPLOYED        dbi-mssql-linux-1.0.0   1.0             default

 

It’s worth noting that each resource is identified by labels (a very powerful feature on K8s) and we may easily get components installed and related to my release by filtering by the corresponding label (app or release) as follows:

$ kubectl get all -l release=sqlhelm
NAME                                           READY     STATUS    RESTARTS   AGE
pod/sqlhelm-dbi-mssql-linux-67c4898dfb-qlfgr   1/1       Running   1          16h

NAME                              TYPE           CLUSTER-IP    EXTERNAL-IP   PORT(S)          AGE
service/sqlhelm-dbi-mssql-linux   LoadBalancer   10.99.4.205   localhost     1451:32569/TCP   16h

NAME                                      DESIRED   CURRENT   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/sqlhelm-dbi-mssql-linux   1         1         1            1           16h

NAME                                                 DESIRED   CURRENT   READY     AGE
replicaset.apps/sqlhelm-dbi-mssql-linux-67c4898dfb   1         1         1         16h

 

Let’s just take a look at my SQL Server pod log and let’s focus on the different custom steps applied during the startup of the corresponding pod. All the custom steps are well executed with input values from the values.yaml files.

kubectl logs sqlhelm-dbi-mssql-linux-67c4898dfb-qlfgr

======= 2018-12-17 22:29:44 Configuring tempdb database files placement OK =======
======= 2018-12-17 22:29:44 Configuring max server memory =======
2018-12-17 22:29:45.01 spid51      Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2018-12-17 22:29:45.03 spid51      Configuration option 'max server memory (MB)' changed from 2147483647 to 3840. Run the RECONFIGURE statement to install
…
======= 2018-12-17 22:29:45 Configuring max server memory OK =======
======= 2018-12-17 22:29:45 Creating login dbi_user =======
======= 2018-12-17 22:29:45 Creating login dbi_user OK =======
======= 2018-12-17 22:29:45 Installing DMK =======
Changed database context to 'master'.
Creating dbi_tools...
======= 2018-12-17 22:30:08 Installing DMK OK =======
======= MSSQL CONFIG COMPLETED =======

 

Finally let’s connect from mssql-cli utility to my SQL Server pod and let’s check if everything is ok from a configuration perspective:

master> select name as logical_name, physical_name
....... from sys.master_files;
+----------------+-------------------------------------------------+
| logical_name   | physical_name                                   |
|----------------+-------------------------------------------------|
| master         | /u01/sqlserverdata/mssqlserver/master.mdf       |
| mastlog        | /u01/sqlserverdata/mssqlserver/mastlog.ldf      |
| tempdev        | /u03/sqlservertempdb/mssqlserver/tempdb.mdf     |
| templog        | /u03/sqlservertempdb/mssqlserver/templog.ldf    |
| tempdbdev_2    | /u03/sqlservertempdb/mssqlserver/tempdb2.ndf    |
| tempdbdev_3    | /u03/sqlservertempdb/mssqlserver/tempdb3.ndf    |
| tempdbdev_4    | /u03/sqlservertempdb/mssqlserver/tempdb4.ndf    |
| modeldev       | /u01/sqlserverdata/mssqlserver/model.mdf        |
| modellog       | /u01/sqlserverdata/mssqlserver/modellog.ldf     |
| MSDBData       | /u01/sqlserverdata/mssqlserver/MSDBData.mdf     |
| MSDBLog        | /u01/sqlserverdata/mssqlserver/MSDBLog.ldf      |
| dbi_tools      | /u01/sqlserverdata/mssqlserver/dbi_tools.mdf    |
| dbi_tools_log  | /u02/sqlserverlog/mssqlserver/dbi_tools_log.ldf |
+----------------+-------------------------------------------------+

 

The database file placement meets my flexible architecture requirements. The DMK maintenance tool is also deployed correctly with the dbi_tools database as show below:

master> use dbi_tools;
Commands completed successfully.
Time: 0.253s
dbi_tools> select name as table_name
.......... from sys.tables;
+-----------------------------------+
| table_name                        |
|-----------------------------------|
| dbi_maintenance_task_logs         |
| dbi_maintenance_task_details_logs |
| dbi_maintenance_configuration     |
| __RefactorLog                     |
+-----------------------------------+

 

sa and dbi_user (name by default in my template) logins are available for sysadmin and classical user connections.

master> select name AS login_name
....... from sys.server_principals
....... where type = 'S' and name not like '##%##';
+--------------+
| login_name   |
|--------------|
| sa           |
| dbi_user     |
+--------------+

 

Deployment is done successfully! It was a brief overview of Helm capabilities with SQL Server and other write-ups will come soon!

Happy deployment!

 

Cet article Deploying SQL Server on K8s with Helm charts est apparu en premier sur Blog dbi services.

Red Hat Enterprise Linux 8 – Application Streams

Thu, 2018-12-13 09:05

You may have heard that : Red Hat Enterprise Linux 8 is available for downloading in beta version since few weeks…
You want to download it ? Click here.
RH_EnterpriseLinux8beta_stacked_RGB_BlueA significant change coming with this new version is the way the applications packages are provided. As you know, up to RHEL7 packages were downloaded via repositories listed in .repo files located by default under /etc/yum.repos.d/. This is still the same with RHEL8, but two new major repositories are available in the default redhat.repo files.

In order to get access to them we must of course register my system to a Red Hat Subscription…
[root@rhel8beta1 ~]# subscription-manager register --username xxx.yyy@zzz.com
Registering to: subscription.rhsm.redhat.com:443/subscription
Password:
The system has been registered with ID: e42829a5-8a8e-42d3-a69a-07a1499e9b0e
The registered system name is: rhel8beta1
[root@rhel8beta1 ~]#

…and attach it to a Pool (here the Pool will be chosen automatically) :
[root@rhel8beta1 ~]# subscription-manager attach --auto
Installed Product Current Status:
Product Name: Red Hat Enterprise Linux for x86_64 Beta
Status: Subscribed
[root@rhel8beta1 ~]#

As the /etc/yum.repos.d/redhat.repo is now available, let’s check which repository does it contain :
[root@rhel8beta1 ~]# grep -B1 name /etc/yum.repos.d/redhat.repo
[rhel-8-for-x86_64-rt-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Real Time Beta (Debug RPMs)
--
[rhel-8-for-x86_64-rt-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Real Time Beta (Source RPMs)
--
[rhel-8-for-x86_64-supplementary-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Supplementary Beta (RPMs)
--
[rhel-8-for-x86_64-resilientstorage-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Resilient Storage Beta (Debug RPMs)
--
[fast-datapath-beta-for-rhel-8-x86_64-source-rpms] name = Fast Datapath Beta for RHEL 8 x86_64 (Source RPMs)
--
[codeready-builder-beta-for-rhel-8-x86_64-debug-rpms] name = Red Hat CodeReady Linux Builder Beta for RHEL 8 x86_64 (Debug RPMs)
--
[codeready-builder-beta-for-rhel-8-x86_64-source-rpms] name = Red Hat CodeReady Linux Builder Beta for RHEL 8 x86_64 (Source RPMs)
--
[rhel-8-for-x86_64-appstream-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (Source RPMs)
--
[rhel-8-for-x86_64-nfv-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Real Time for NFV Beta (Source RPMs)
--
[rhel-8-for-x86_64-nfv-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Real Time for NFV Beta (RPMs)
--
[rhel-8-for-x86_64-resilientstorage-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Resilient Storage Beta (Source RPMs)
--
[codeready-builder-beta-for-rhel-8-x86_64-rpms] name = Red Hat CodeReady Linux Builder Beta for RHEL 8 x86_64 (RPMs)
--
[rhel-8-for-x86_64-supplementary-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Supplementary Beta (Debug RPMs)
--
[rhel-8-for-x86_64-highavailability-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - High Availability Beta (Source RPMs)
--
[rhel-8-for-x86_64-supplementary-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Supplementary Beta (Source RPMs)
--
[rhel-8-for-x86_64-appstream-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (RPMs)
--
[rhel-8-for-x86_64-rt-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Real Time Beta (RPMs)
--
[rhel-8-for-x86_64-appstream-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (Debug RPMs)
--
[fast-datapath-beta-for-rhel-8-x86_64-debug-rpms] name = Fast Datapath Beta for RHEL 8 x86_64 (Debug RPMs)
--
[rhel-8-for-x86_64-resilientstorage-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Resilient Storage Beta (RPMs)
--
[rhel-8-for-x86_64-baseos-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - BaseOS Beta (RPMs)
--
[rhel-8-for-x86_64-nfv-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - Real Time for NFV Beta (Debug RPMs)
--
[fast-datapath-beta-for-rhel-8-x86_64-rpms] name = Fast Datapath Beta for RHEL 8 x86_64 (RPMs)
--
[rhel-8-for-x86_64-baseos-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - BaseOS Beta (Debug RPMs)
--
[rhel-8-for-x86_64-highavailability-beta-debug-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - High Availability Beta (Debug RPMs)
--
[rhel-8-for-x86_64-highavailability-beta-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - High Availability Beta (RPMs)
--
[rhel-8-for-x86_64-baseos-beta-source-rpms] name = Red Hat Enterprise Linux 8 for x86_64 - BaseOS Beta (Source RPMs)
[root@rhel8beta1 ~]#

AppStream ? BaseOS ? What’s that… This is what we will discover in this blog.

First of all we can see that both are enabled by default :
[root@rhel8beta1 ~]# subscription-manager repos --list-enabled
+----------------------------------------------------------+
Available Repositories in /etc/yum.repos.d/redhat.repo
+----------------------------------------------------------+
Repo ID: rhel-8-for-x86_64-baseos-beta-rpms
Repo Name: Red Hat Enterprise Linux 8 for x86_64 - BaseOS Beta (RPMs)
Repo URL: https://cdn.redhat.com/content/beta/rhel8/8/x86_64/baseos/os
Enabled: 1


Repo ID: rhel-8-for-x86_64-appstream-beta-rpms
Repo Name: Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (RPMs)
Repo URL: https://cdn.redhat.com/content/beta/rhel8/8/x86_64/appstream/os
Enabled: 1
[root@rhel8beta1 ~]#

BaseOS

Content in BaseOS is intended to provide the core set of the underlying OS functionality that provides the foundation for all installations“.
This is how Red Hat define it. Over a thousand of packages are available from the BaseOS repository :
[root@rhel8beta1 ~]# yum --disablerepo "*" --enablerepo "rhel-8-for-x86_64-baseos-beta-rpms" list available | wc -l
1145
[root@rhel8beta1 ~]#

You can get the full list here.
Basically, those packages are system-related and are mainly used to manage and configure the OS and services (such as NetworkManager, Chrony, Dracut, aso…). In other words most of them are intended for use by system administrators. So nothing very new here except for the fact that they are all grouped in a unique dedicated repository.

AppStream

The second repository contains much more packages (full list here) :
[root@rhel8beta1 /]# yum --disablerepo "*" --enablerepo "rhel-8-for-x86_64-appstream-beta-rpms" list available | wc -l
4318
[root@rhel8beta1 /]#

Application Stream provides additional user space applications, runtime languages and databases. It replaces the “extra” Repos and the Software Collection. All the content in AppStream is available in two formats : the well known RPM format and a brand new one called “module” which an extension to the RPM format.
A module is a set of RPM packages that are linked together. For exemple, if you want to check which packages are concerned by the Postgresql module, you must use the new “yum module” command :
[root@rhel8beta1 /]# yum module list postgresql
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:47:42 ago on Mon Nov 26 15:13:03 2018.
Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (RPMs)
Name Stream Profiles Summary
postgresql 10 [d] client, default [d] postgresql module
postgresql 9.6 client, default [d] postgresql module


Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@rhel8beta1 /]#

By the way, YUM is no longer the default Packages Manager with RHEL8. The command is still available but it’s actually an alias of the DNF tool (coming from Fedora) :
[root@rhel8beta1 ~]# which yum
/usr/bin/yum
[root@rhel8beta1 ~]# ll /usr/bin/yum
lrwxrwxrwx. 1 root root 5 Oct 15 10:25 /usr/bin/yum -> dnf-3
[root@rhel8beta1 ~]#

If you want to have a look to the main usage differences between YUM and DNF, check that :
[root@rhel8beta1 /]# man yum2dnf
YUM2DNF(8)


NAME
yum2dnf - Changes in DNF compared to YUM
[...] [...]

Let’s go back to our modules. Here is how you can check the packages contained in a module :
[root@rhel8beta1 /]# yum module info postgresql
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (RPMs) 2.9 kB/s | 4.1 kB 00:01
Red Hat Enterprise Linux 8 for x86_64 - BaseOS Beta (RPMs) 2.9 kB/s | 4.1 kB 00:01
Name : postgresql
Stream : 10 [d] Version : 20180813131250
Context : 9edba152
Profiles : client, default [d] Default profiles : default
Repo : rhel-8-for-x86_64-appstream-beta-rpms
Summary : postgresql module
Description : This postgresql module has been generated.
Artifacts : postgresql-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-contrib-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-docs-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-plperl-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-plpython3-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-pltcl-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-server-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-server-devel-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-static-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-test-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-test-rpm-macros-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-upgrade-0:10.5-1.el8+1546+27ad5f8e.x86_64
: postgresql-upgrade-devel-0:10.5-1.el8+1546+27ad5f8e.x86_64


Name : postgresql
Stream : 9.6
Version : 20180813131400
Context : 9edba152
Profiles : client, default [d] Default profiles : default
Repo : rhel-8-for-x86_64-appstream-beta-rpms
Summary : postgresql module
Description : This postgresql module has been generated.
Artifacts : postgresql-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-contrib-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-docs-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-plperl-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-plpython3-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-pltcl-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-server-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-server-devel-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-static-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-test-0:9.6.10-1.el8+1547+210b7007.x86_64
: postgresql-test-rpm-macros-0:9.6.10-1.el8+1547+210b7007.x86_64


Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@rhel8beta1 /]#

The above output shows that a module can contain several streams. Each stream represent a different version of the application.
Moreover, a module can have couple of profiles. A profile is a set of certain RPM packages selected to be installed together for a particular use-case (server, client, development, minimal install, aso…).

To install an application from the default stream and with the default profile, add the ‘@’ character before the application name :
[root@rhel8beta1 /]# yum install @postgresql
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:25:55 ago on Mon Nov 26 16:27:13 2018.
Dependencies resolved.
=======================================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================================
Installing group/module packages:
postgresql-server x86_64 10.5-1.el8+1546+27ad5f8e rhel-8-for-x86_64-appstream-beta-rpms 5.1 M
Installing dependencies:
libpq x86_64 10.5-1.el8 rhel-8-for-x86_64-appstream-beta-rpms 188 k
postgresql x86_64 10.5-1.el8+1546+27ad5f8e rhel-8-for-x86_64-appstream-beta-rpms 1.5 M
Installing module profiles:
postgresql/default
Enabling module streams:
postgresql 10


Transaction Summary
=======================================================================================================================================================================================
Install 3 Packages


Total download size: 6.7 M
Installed size: 27 M
Is this ok [y/N]: y
[...] [...] [root@rhel8beta1 /]#

You can also use the “yum module install postgresql” command.

Quick check :
[root@rhel8beta1 ~]# which postgres
/usr/bin/postgres
[root@rhel8beta1 ~]# /usr/bin/postgres --version
postgres (PostgreSQL) 10.5
[root@rhel8beta1 ~]#

And if you want to install Postgres from an oldest stream and with another profile (here Postgres 9.6 client only) :
[root@rhel8beta1 /]# yum install @postgresql:9.6/client
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:33:45 ago on Mon Nov 26 16:27:13 2018.
Dependencies resolved.
=======================================================================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================================================================
Installing group/module packages:
postgresql x86_64 9.6.10-1.el8+1547+210b7007 rhel-8-for-x86_64-appstream-beta-rpms 1.4 M
Installing dependencies:
libpq x86_64 10.5-1.el8 rhel-8-for-x86_64-appstream-beta-rpms 188 k
Installing module profiles:
postgresql/client
Enabling module streams:
postgresql 9.6


Transaction Summary
=======================================================================================================================================================================================
Install 2 Packages


Total download size: 1.6 M
Installed size: 5.8 M
Is this ok [y/N]: y
[...] [...] [root@rhel8beta1 /]#

Check :
[root@rhel8beta1 ~]# which postgres
/usr/bin/postgres
[root@rhel8beta1 ~]# /usr/bin/postgres --version
postgres (PostgreSQL) 9.6.10
[root@rhel8beta1 ~]#


[root@rhel8beta1 ~]# yum module list --enabled
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:04:43 ago on Thu Dec 13 08:34:05 2018.
Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (RPMs)
Name Stream Profiles Summary
container-tools 1.0 [d][e] default [d] Common tools and dependencies for container runtimes
postgresql 9.6 [e] client [i], default [d] [i] postgresql module
satellite-5-client 1.0 [d][e] gui, default [d] Red Hat Satellite 5 client packages
virt rhel [d][e] default [d] Virtualization module


Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@rhel8beta1 ~]#

Hummm… Only the module 9.6 is enabled ? Let’s try to enable the version 10 :
[root@rhel8beta1 ~]# yum module enable postgresql:10
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:08:06 ago on Thu Dec 13 07:52:30 2018.
Dependencies resolved.
======================================================================================================================================
Package Arch Version Repository Size
======================================================================================================================================
Switching module streams:
postgresql 9.6 -> 10


Transaction Summary
======================================================================================================================================


Is this ok [y/N]: y
Complete!


Switching module streams does not alter installed packages (see 'module enable' in dnf(8) for details)
[root@rhel8beta1 ~]#

It’s better now :
[root@rhel8beta1 ~]# yum module list --enabled
Failed to set locale, defaulting to C
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:13:22 ago on Thu Dec 13 08:34:05 2018.
Red Hat Enterprise Linux 8 for x86_64 - AppStream Beta (RPMs)
Name Stream Profiles Summary
container-tools 1.0 [d][e] default [d] Common tools and dependencies for container runtimes
postgresql 10 [d][e] client [i], default [d] [i] postgresql module
satellite-5-client 1.0 [d][e] gui, default [d] Red Hat Satellite 5 client packages
virt rhel [d][e] default [d] Virtualization module


Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@rhel8beta1 ~]#

But…
[root@rhel8beta1 ~]# which postgres
/usr/bin/postgres
[root@rhel8beta1 ~]# /usr/bin/postgres --version
postgres (PostgreSQL) 9.6.10
[root@rhel8beta1 ~]#

…still using 9.6 :-(
After switching from one module to another, we must upgrade the corresponding packages :
[root@rhel8beta1 ~]# yum distro-sync
Updating Subscription Management repositories.
Updating Subscription Management repositories.
Last metadata expiration check: 0:18:40 ago on Thu Dec 13 08:34:05 2018.
Dependencies resolved.
=========================================================§=====================================================================================
Package Arch Version Repository Size
==============================================================================================================================================
Upgrading:
postgresql x86_64 10.5-1.el8+1546+27ad5f8e rhel-8-for-x86_64-appstream-beta-rpms 1.5 M
postgresql-server x86_64 10.5-1.el8+1546+27ad5f8e rhel-8-for-x86_64-appstream-beta-rpms 5.1 M


Transaction Summary
==============================================================================================================================================
Upgrade 2 Packages


Total download size: 6.5 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64.rpm 1.3 MB/s | 5.1 MB 00:03
(2/2): postgresql-10.5-1.el8+1546+27ad5f8e.x86_64.rpm 371 kB/s | 1.5 MB 00:04
--------------------------------------------------------------------------------------------------------------- --------------------------------
Total 1.6 MB/s | 6.5 MB 00:04
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: postgresql-10.5-1.el8+1546+27ad5f8e.x86_64 1/1
Upgrade: postgresql-10.5-1.el8+1546+27ad5f8e.x86_64
Upgrading : postgresql-10.5-1.el8+1546+27ad5f8e.x86_64 1/4
Upgrade: postgresql-10.5-1.el8+1546+27ad5f8e.x86_64
Upgrade: postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64
Running scriptlet: postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64 2/4
Upgrading : postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64 2/4
Running scriptlet: postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64 2/4
Upgrade: postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64
Upgraded: postgresql-server-9.6.10-1.el8+1547+210b7007.x86_64
Running scriptlet: postgresql-server-9.6.10-1.el8+1547+210b7007.x86_64 3/4
Cleanup : postgresql-server-9.6.10-1.el8+1547+210b7007.x86_64 3/4
Upgraded: postgresql-server-9.6.10-1.el8+1547+210b7007.x86_64
Running scriptlet: postgresql-server-9.6.10-1.el8+1547+210b7007.x86_64 3/4
Upgraded: postgresql-9.6.10-1.el8+1547+210b7007.x86_64
Cleanup : postgresql-9.6.10-1.el8+1547+210b7007.x86_64 4/4
Upgraded: postgresql-9.6.10-1.el8+1547+210b7007.x86_64
Running scriptlet: postgresql-9.6.10-1.el8+1547+210b7007.x86_64 4/4
Verifying : postgresql-10.5-1.el8+1546+27ad5f8e.x86_64 1/4
Verifying : postgresql-9.6.10-1.el8+1547+210b7007.x86_64 2/4
Verifying : postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64 3/4
Verifying : postgresql-server-9.6.10-1.el8+1547+210b7007.x86_64 4/4


Upgraded:
postgresql-10.5-1.el8+1546+27ad5f8e.x86_64 postgresql-server-10.5-1.el8+1546+27ad5f8e.x86_64


Complete!
[root@rhel8beta1 ~]#

And now it’s fine :
[root@rhel8beta1 ~]# which postgres
/usr/bin/postgres
[root@rhel8beta1 ~]# /usr/bin/postgres --version
postgres (PostgreSQL) 10.5
[root@rhel8beta1 ~]#

So what ?

That was only a first quick try with the AppStream fonctionality. What we can remember here is that with this new way to manage packages we can benefit from parallel availability of multiple versions of software. This is due to the disassociation from the kernel space (BaseOS) – which is still managed in a traditional way, and the user space (AppStream) – which is now deployed in the form of “containerized” applications.
Up to now, when we wanted to upgrade an application to a given version, we had to think about the inter-dependency between this application and the other one that we didn’t want to update. With RHEL8, we can now upgrade one while keeping the other in its current version.

Cet article Red Hat Enterprise Linux 8 – Application Streams est apparu en premier sur Blog dbi services.

Understand Oracle Text at a glance

Thu, 2018-12-13 00:19

What is Oracle Text?

Oracle Text provides indexing, word and theme searching, and viewing capabilities for text in query applications and document classification applications.

Oracle text activation for a user

create user ORATXT identified by oratxt ;
grant ctxapp to ORATXT ;
grant execute on ctxsys.ctx_cls to ORATXT ;
grant execute on ctxsys.ctx_ddl to ORATXT ;
grant execute on ctxsys.ctx_doc to ORATXT ;
grant execute on ctxsys.ctx_output to ORATXT ;
grant execute on ctxsys.ctx_query to ORATXT ;
grant execute on ctxsys.ctx_report to ORATXT ;
grant execute on ctxsys.ctx_thes to ORATXT ;
grant execute on ctxsys.ctx_ulexer to ORATXT ;

Oracle Text configuration and usage

To design an Oracle Text application, first determine the type of queries you expect to run. This enables you to choose the most suitable index for the task. There are 4 use cases with Oracle Text:

  1. Document Collection Applications
    • The collection is typically static with no significant change in content after the initial indexing run. Documents can be of any size and of different formats, such as HTML, PDF, or Microsoft Word. These documents are stored in a document table. Searching is enabled by first indexing the document collection.
    • Queries usually consist of words or phrases. Application users can specify logical combinations of words and phrases using operators such as OR and AND. Other query operations can be used to improve the search results, such as stemming, proximity searching, and wildcarding.
    • An important factor for this type of application is retrieving documents relevant to a query while retrieving as few non-relevant documents as possible. The most relevant documents must be ranked high in the result list.
    • The queries for this type of application are best served with a CONTEXT index on your document table. To query this index, the application uses the SQL CONTAINS operator in the WHERE clause of a SELECT statement.
    • Example of searching
    • SQL> select score(1), doc_id, html_content from docs where contains(html_content, 'dbi', 1) > 0;
       
      SCORE(1) ID HTML_CONTENT
      ---------- ---------- -----------------------------------------------------------
      4 1 <HTML>dbi services provide various IT services</HTML>
      4 9 <HTML>You can become expert with dbi services</HTML>
      4 3 <HTML>The compaany dbi services is in Switzerland.</HTML>

  2. Catalog Information Applications
    • The stored catalog information consists of text information, such as book titles, and related structured information, such as price. The information is usually updated regularly to keep the online catalog up to date with the inventory.
    • Queries are usually a combination of a text component and a structured component. Results are almost always sorted by a structured component, such as date or price. Good response time is always an important factor with this type of query application.
    • Catalog applications are best served by a CTXCAT index. Query this index with the CATSEARCH operator in the WHERE clause of a SELECT statement.
    • Example of searching
    • SQL> select product, price from auction where catsearch(title, 'IT', 'order by price')> 0;
       
      PRODUCT PRICE
      ----------------------------------- ----------
      IT Advice 1 hour 499
      Course IT management 3999
      License IT monitoring 199
      IT desk 810

  3. Document Classification Applications
    • In a document classification application, an incoming stream or a set of documents is compared to a pre-defined set of rules. When a document matches one or more rules, the application performs some action. For example, assume there is an incoming stream of news articles. You can define a rule to represent the category of Finance. The rule is essentially one or more queries that select document about the subject of Finance. The rule might have the form ‘stocks or bonds or earnings’.
    • When a document arrives about a Wall Street earnings forecast and satisfies the rules for this category, the application takes an action, such as tagging the document as Finance or e-mailing one or more users.
    • To create a document classification application, create a table of rules and then create a CTXRULE index. To classify an incoming stream of text, use the MATCHES operator in the WHERE clause of a SELECT statement. See Figure 1-5 for the general flow of a classification application.
    • SQL> select category_id, category_name
      from categories
      where matches(blog_string, 'Dbi services add value to your IT infrastructure by providing experts in different technologies to cover all your needs.');
       
      QUERY_ID QUERY_STRING
      ---------- -----------------------------------
      9 Expertise
      2 Advertisement
      6 IT Services

  4. XML Search Applications
    • An XML search application performs searches over XML documents. A regular document search usually searches across a set of documents to return documents that satisfy a text predicate; an XML search often uses the structure of the XML document to restrict the search.
      Typically, only that part of the document that satisfies the search is returned. For example, instead of finding all purchase orders that contain the word electric, the user might need only purchase orders in which the comment field contains electric.

In conclusion, there is various uses cases for which Oracle Text will help you with text indexation. Before implementing, verify which will best suit your need. Also, it may be interesting to compare with an external text indexer like Solr which is also able to index your database via a JDBC driver.

I hope it may help and please do not hesitate to contact us if you have any questions or require further information.

Cet article Understand Oracle Text at a glance est apparu en premier sur Blog dbi services.

Transparent Data Encryption – Certificate in master database: is it really a good practice?

Tue, 2018-12-11 02:00

As encryption solution in SQL Server, Transparent Data Encryption (TDE) is simple and quick to set up. That’s why this is a common encryption mechanism.

TDE encrypts data with a certificate at the page level, before SQL Server writes on the disk. It is supposed to protect your environment from some scenarios, where SQL Server files (backups or data) are stolen.
By default the certificate used for encryption is stored in the master database. But is it really a good practice?

Let’s see with a common scenario:
– One MSSQL instance where TDE is enabled for one database
– One MSSQL instance without any certificate

Configuring TDE for a database

Create a master key:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@$$w0rd'
GO

Create a certificate to use for TDE:

USE master
GO
CREATE CERTIFICATE CertinMaster
   WITH SUBJECT = 'Self-Signed Certificate in Master',   
   EXPIRY_DATE = '20241231';  
GO  

Certificate

Create a database encryption key in the database you want to encrypt, protected by the certificate:

USE dummy
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertinMaster
GO

Enable encryption on the database:

USE master
GO
ALTER DATABASE dummy SET ENCRYPTION ON
GO

tde

At this step:
– TDE is configured for dummy database
– Certificate used for encryption is located in the master database

Restoring encrypted database to an another instance – first try

After backing up dummy database and copying the backup to another MSSQL instance, an error is displayed when trying to restore this database with required certificate:

Restore_tde_witout_certificate_impossible

This example shows how TDE protects data from a scenario where someone has robbed your backup file.
A similar error appears if an attach file procedure is used.

Restoring encrypted database to an another instance – second try

After backing up master database, let’s try to restore it on the other MSSQL environment.
Restoring a master database is a bit more complicated than a user database, but not impossible.

First it is mandatory to set the instance in Single User mode, by adding Trace Flag ‘-m’ at startup parameters.
PowerShell code executed on target server:

$Path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.ARCTURUS\MSSQLServer\Parameters\";
New-ItemProperty -Path $Path -PropertyType String -Name SQLArg3 -Value "-m"

Note: depending on how many startup parameters you have (default is 3), adjust accordingly the item property to SQLArgX.

A restart of the instance is necessary to apply the Trace Flag. Do not start the agent service, otherwise it will connect to the instance in single user mode.

As only one connection to the instance is authorized in this mode, PowerShell is more suited to do the job for the restore:

$Query= "RESTORE DATABASE master FROM DISK = 'C:\Mountpoints\Backup\master.bak' WITH REPLACE";
Invoke-Sqlcmd -ServerInstance KERRIGAN\ARCTURUS -Query $Query"

The SQL Server is automatically stopped.

First remove the Single user mode:

$Path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.ARCTURUS\MSSQLServer\Parameters\";
Remove-ItemProperty -Path $Path -Name SQLArg3"

After disabling Single User mode, SQL Server services (Engine + Agent) can start again.

After query the new master, it is effectively holding the certificate:
Certificate_Copied

Finally after trying to restore the dummy database on this instance, it works without any trouble: data are freely accessible.

Conclusion

For a complete protection, I do not recommend to use a simple form of Transparent Data Encryption. As a best practice, I do recommend to store the encryption key on a third-party system using Extensible Key Management (EKM).

 

Cet article Transparent Data Encryption – Certificate in master database: is it really a good practice? est apparu en premier sur Blog dbi services.

Pages