Start  trial

    Start trial

      Version:
      Platform:
      OS:

      There are no articles for this combination of options currently.

      There are no articles for this combination of options currently.

      There are no articles for this combination of options currently.

      Fujitsu Enterprise Postgres 13 SP1 is not available on IBM LinuxONE.

      Fujitsu Enterprise Postgres 13 is not available on IBM Power®.

      Fujitsu Enterprise Postgres 13 SP1 is not available on IBM Power®.

      Fujitsu Enterprise Postgres 15 SP1 is not available on IBM Power®.

      Fujitsu Enterprise Postgres 15 SP2 is not available on IBM Power®.

      How to install Fujitsu Enterprise PostgresQSG01
      Product Fujitsu Enterprise Postgres
      Operating system RHEL7.2 or later minor version, RHEL8.1 or later minor version RHEL7.4 or later minor version, RHEL8.2 or later minor version RHEL7.4 or later minor version, RHEL8.2 or later minor version, RHEL9.0 or later minor version SLES 12 SP5 SLES 12 SP5, SLES 15 SP3 SLES 12 SP5, SLES 15 SP3 or later minor version RHEL 8.2 or later minor version RHEL 8.2 or later minor version RHEL 8.2 or later minor version, RHEL 9.0 or later minor version SLES 15 SP3 or later minor version RHEL 8.4 or later minor version RHEL 8.4 or later minor version, RHEL 9.0 or later minor version SLES 15 SP3 or later minor version
      Last update January 2024

      Prepare for server installation

      Install OS packages

      To start with the setup, switch to the OS superuser 'root' and install the required OS packages.

      Note: When you connect to the server for your Fujitsu Enterprise Postgres installation, you can see the following OS prompt. In this cheat sheet as an example, the following will be an output in case of 'train' as user name but it can be any other user based on your setup.

      To install package, use yum install command:

      [train@hostname ~]$ sudo su –
      [root@hostname ~]# yum install –y package name

      Following is an example to install 'alsa-lib' package:

      [root@hostname ~]# yum install –y alsa-lib

      To install package, use zypper install command:

      [train@hostname ~]$ sudo su –
      [root@hostname ~]# zypper install package name

      Following is an example to install 'glibc' package:

      [root@hostname ~]# zypper install glibc

      There are multiple packages to be installed and the list varies for each version of the OS. Following are the lists of packages for the respective versions. (reference: Installation and Setup Guide for Server > Chapter 2 > 2.1 Required Operating System)

      Required packages

      This OS is not supported in the product version and platform.
      alsa-lib
      audit-libs
      bzip2-libs Required when using pgBackRest.
      cyrus-sasl-lib
      dstat Required when using parallel scan.
      gdb Required to run FJQSS.Required when using pgx_fjqssinf command.
      glibc
      glibc.i686
      iputils Required for Mirroring Controller.
      java-1.8.0-openjdk Required when using the database multiplexing and WebAdmin. Use build 1.8.0.352.b08 or later for s390x architecture.Required when using the database multiplexing and WebAdmin. Use build 1.8.0.242.b08 or later for s390x architecture.Required when using the database multiplexing and WebAdmin. Use build 1.8.0.322.b06 or later for ppc64le architecture.Required when using the database multiplexing and WebAdmin. Use build 1.8.0.312.b07 or later for ppc64le architecture.Required when using the database multiplexing and WebAdmin. Use build 1.8.0_222-b10 or later for s390x architecture.
      libgcc
      libicu Provides collation support. Install 67.x.Provides collation support. Install 60.x.Provides collation support. Install 50.x.
      libmemcached Required when using Pgpool-II.
      libmemcached-awesome Required when using Pgpool-II.
      libnsl
      libnsl2
      libselinux Required for sepgsql.
      libstdc++
      libtool-ltdl Required when using ODBC drivers.
      libzstd
      llvm Version 13 of llvm is required to run SQL using run-time compilation. Install the package that contains libLLVM-13.so. For example, version 13.0.x of "lvm-libs" published with Application Streams includes libLLVM-13.so. Fujitsu Enterprise Postgres is configured to use runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. If you turn off the jit parameter, you do not need to install llvm. Failure to install llvm without turning off the jit parameter can result in errors during SQL execution. For more information about runtime compilation, see "Just-in-Time Compilation (JIT) "in the PostgreSQL Documentation. Versions 13.0.x, 12.0.x, 11.0.x, 10.0.x, or 9.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-13.so, libLLVM-12.so, libLLVM-11.so. libLLVM-10.so, or libLLVM-9.so. For example, version 13.0.x of "llvm-libs" published with Application Streams includes libLLVM-13.so. By default, version 13.0.x is used. If you use a version other than 13.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn12 when using version 12.0.x. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL.For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation". Version 10.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-10.so. For example, the "llvm-toolset-10.0-llvm-libs" published in Red Hat Developer Tools and Red Hat Software Collections includes libLLVM10.so. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL.For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation". Version 11.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-11.so. For example, version 11.0.x of "llvm-libs" published with Application Streams includes libLLVM-11.so. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf.You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in the PostgreSQL Documentation. Note: If JIT compilation using LLVM is performed on RHEL8, a verbose server message is logged one or more times on first query execution, for each associated database connection. ORC error: No callback manager available for s390x-ibm-linux This error message results from a known deficiency in one of the features of the LLVM library for IBM Z, but this feature is not used by the server and the error does not otherwise affect LLVM operation. To prevent this message from being logged, JIT compilation may be disabled, by setting the value "off" to the configuration variable "jit". Versions 13.0.x or 12.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-13.so, or libLLVM-12.so. For example, version 13.0.x of "llvm-libs" published with Application Streams includes libLLVM-13.so. By default, version 13.0.x is used. If you use a version other than 13.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn12 when using version 12.0.x. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in the PostgreSQL Documentation. Versions 12.0.x, 11.0.x, 10.0.x, or 9.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-12.so, libLLVM-11.so, libLLVM-10.so, or libLLVM-9.so. For example, version 12.0.x of "llvm-libs" published with Application Streams includes libLLVM-12.so. By default, version 12.0.x is used. If you use a version other than 12.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn11 when using version 11.0.x. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation". Versions 11.0.x, 10.0.x, or 9.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-11.so, libLLVM-10.so, or libLLVM-9.so. For example, version 11.0.x of "llvm-libs" published with Application Streams includes libLLVM-11.so. By default, version 11.0.x is used. If you use a version other than 11.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn10 when using version 10.0.x. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in the PostgreSQL Documentation. Note: If JIT compilation using LLVM is performed on RHEL8, a verbose server message is logged one or more times on first query execution, for each associated database connection. ORC error: No callback manager available for s390x-ibm-linux This error message results from a known deficiency in one of the features of the LLVM library for IBM Z, but this feature is not used by the server and the error does not otherwise affect LLVM operation. To prevent this message from being logged, JIT compilation may be disabled, by setting the value "off" to the configuration variable "jit". Versions 12.0.x or 11.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-12.so, or libLLVM-11.so. For example, version 12.0.x of "llvm-libs" published with Application Streams includes libLLVM-12.so. By default, version 12.0.x is used. If you use a version other than 12.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn11 when using version 11.0.x. FUJITSU Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in the PostgreSQL Documentation. Note that if JIT compilation using LLVM11 is performed on RHEL8, a verbose server message is logged one or more times on first query execution, for each associated database connection. ORC error: No callback manager available for powerpc64leunknown-linux-gnu This error message results from a known deficiency in one of the features of the LLVM library for POWER, but this feature is not used by the server and the error does not otherwise affect LLVM operation. To prevent this message from being logged, JIT compilation may be disabled, by setting the value "off" to the configuration variable "jit". Versions 10.0.x, 9.0.x, 8.0.x, or 7.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-10.so, libLLVM-9.so, libLLVM-8.so, or libLLVM-7.so. For example, version 10.0.x of "llvm-libs" published with Application Streams includes libLLVM-10.so. By default, version 10.0.x is used. If you use a version other than 10.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn9 when using version 9.0.x. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation". Versions 10.0.x, 9.0.x, or 8.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM-10.so, libLLVM-9.so, or libLLVM-8.so. For example, version 10.0.x of "llvm-libs" published with Application Streams includes libLLVM-10.so. By default, version 10.0.x is used. If you use a version other than 10.0.x, specify the version you want to use in the jit_provider parameter in postgresql.conf. For example, use llvmjit-vsn9 when using version 9.0.x. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in the PostgreSQL Documentation. Note: If JIT compilation using LLVM is performed on RHEL8, a verbose server message is logged one or more times on first query execution, for each associated database connection. ORC error: No callback manager available for s390x-ibm-linux. This error message results from a known deficiency in one of the features of the LLVM library for IBM Z, but this feature is not used by the server and the error does not otherwise affect LLVM operation. To prevent this message from being logged, JIT compilation may be disabled, by setting the value "off" to the configuration variable "jit".
      lz4
      lz4-libs
      ncurses-libs
      net-tools
      nss-softokn-freebl
      opencryptoki Required if transparent data encryption is used in conjunction with a hardware security module. Install 3.12.1 or later into default directory.
      pam Required when using PAM authentication.
      pcp-system-tools Required when using parallel scan.
      perl-libs Required when using PL/Perl. Install 5.32.Required when using PL/Perl. Install 5.26.Required when using PL/Perl. Install 5.16.
      protobuf-c Required if using the Transparent Data Encryption feature when using a key management system as a keystore. Install 1.3.3.Required if using the Transparent Data Encryption feature when using a key management system as a keystore. Install 1.3.0.Required if using the Transparent Data Encryption feature when using a key management system as a keystore. Install 1.0.2.
      python3 Required when using PL/Python based on Python 3. Install 3.8.x.Required when using PL/Python based on Python 3. Install 3.9.x.Required when using PL/Python based on Python 3. Install 3.6.x.
      redhat-lsb
      rsync Required when using Pgpool-II.
      sudo
      sysstat Required when using FJQSS. Set up the sar command after installation.Required when using pgx_fjqssinf command. Set up the sar command after installation.
      tcl Required when using PL/Tcl. Install 8.6.Required when using PL/Tcl. Install 8.5.
      unzip
      xz-libs
      zlib
      alsa-lib
      audit-libs
      dstat Required when using parallel scan.
      gdb Required to run FJQSS.Required when using pgx_fjqssinf command.
      glibc
      glibc-32bit
      iputils Required for Mirroring Controller.
      JRE 8 Required when using the installer, database multiplexing, and WebAdmin. The following JREs are available: Oracle JRE. Use update 31 or later of the 64-bit version, and OpenJDK Past the Java TCK (Technology Compatibility Kit)
      libasound2
      libaudit1
      libbz2-1 Required when using pgBackRest.
      libfreebl3
      libgcc
      libgcc_s1
      libicu Provides collation support. Install 65.Provides collation support. Install 52.
      libicu-suse65_1 Provides collation support. Install 65.
      libltdl7 Required when using ODBC drivers.
      liblz4-1
      liblz4-1_7
      liblzma5
      libmemcached Required when using Pgpool-II.
      libncurses5
      libncurses6
      libstdc++
      libstdc++6
      libtool-ltdl Required when using ODBC drivers.
      libz1
      libzstd1
      llvm Version 7.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM.so.7. For example, the "libLLVM7" published in SLE-Module-Basesystem15-SP3-Updates includes libLLVM.so.7. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf.You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL.For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation". Version 7.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM.so.7. For example, the "libLLVM7" published in SLES12-SP5-Updates includes libLLVM.so.7. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation". Install version 7.0.x of llvm to run SQL with runtime compilation (just-in-time compilation) and add the directory where the shared library libLLVM-*.so is located to the environment variable LD_LIBRARY_PATH. Fujitsu Enterprise Postgres 15 uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in the PostgreSQL Documentation. Version 7.0.x of llvm is required to run SQL with runtime compilation (just-in-time compilation). Install the package that contains libLLVM.so.7. For example, the "libLLVM7" published in SLE-Module-Basesystem15-SP3-Updates includes libLLVM.so.7. Fujitsu Enterprise Postgres uses runtime compilation by default. If you do not want to use runtime compilation, turn off the jit parameter in postgresql.conf. You do not need to install llvm if you turn off the jit parameter. Failure to install llvm without turning off the jit parameter may result in an error when executing SQL. For more information about runtime compilation, see "Just-in-Time Compilation (JIT)" in "PostgreSQL Documentation".
      LLVM-libs Install version 5.0.2 or later.
      libLLVM7 Install version 7.0.x.
      ncurses-libs
      net-tools
      nss-softokn-freebl
      opencryptoki Required if transparent data encryption is used in conjunction with a hardware security module. Install 3.10 or later into default directory.
      Oracle JRE 8 Required when using the installer or database multiplexing. Use update 31 or later of the 64-bit version.
      pam Required when using PAM authentication.
      perl Required when using PL/Perl. Install 5.26.
      perl-libs Required when using PL/Perl. Install 5.18.
      protobuf-c Required if using the Transparent Data Encryption feature when using a key management system as a keystore. Install 1.3.2.
      python3 Required when using PL/Python based on Python 3. Install 3.6.x.Required when using PL/Python based on Python 3. Install 3.4.x.Required when using PL/Python based on Python 3. Install 3.7.x.
      rsync Required when using Pgpool-II.
      sudo
      sysstat Set up the sar command after installation.Required when using FJQSS. Set up the sar command after installation.Required when using pgx_fjqssinf command. Set up the sar command after installation.
      tcl Required when using PL/Tcl. Install 8.6.
      unzip
      xz-libs
      zlib
      java-1_8_0-openjdk Required when using the database multiplexing and WebAdmin. Use build 1.8.0.302 or later for s390x architecture.Required when using the database multiplexing and WebAdmin. Use build 1.8.0.312 or later for ppc64le architecture.

      Set up Java

      Ensure that JRE 8 is installed. Setup JAVA_HOME by the following command. Please check your exact installed directory path because it might be different than the one below:

      [root@hostname ~]# export JAVA_HOME==/usr/lib64/jvm/java-1.8.0-openjdk-1.8.0/jre/lib/amd64/

      Set up Java

      Ensure that JRE 8 is installed. Setup JAVA_HOME by the following command. Please check your exact installed directory path because it might be different than the one below:

      [root@hostname ~]# export JAVA_HOME==/usr/lib64/jvm/java-1.8.0-openjdk-1.8.0/jre/lib/amd64/

      Create necessary directories

      The following directories are required to install Fujitsu Enterprise Postgres.

      • /media/dvd is the mount point of Fujitsu Enterprise Postgres ISO.
      • /database/inst1 is the data storage directory which is used to store the database data.
      • /backup is the backup data storage directory which is used to store the backup data.
      • /pg_tblspc is the directory for tablespace which is used to store table files and index files in a separate area from the database cluster.

      Create directories as shown below:

      [root@hostname ~]# mkdir -p /media/dvd
      [root@hostname ~]# mkdir -p /database/inst1
      [root@hostname ~]# mkdir /backup
      [root@hostname ~]# mkdir /pg_tblspc

      Create a database user

      Create a database user 'fepuser' who will be assigned the instance administrator role, using useradd (or adduser) command. For this exercise, set the password to 'fepuser':

      [train@hostname ~]$ sudo su
      [root@hostname ~]# useradd -m fepuser
      [root@hostname ~]# passwd fepuser
      [root@hostname ~]# Password:

      When you complete, try logging to 'fepuser' to confirm it was created correctly:

      [root@hostname ~]# su – fepuser
      [root@hostname ~]# Password:
      [fepuser@hostname ~]$ exit

      Change the ownership of the directories

      Give the ownership of those new directories to 'fepuser':

      [root@hostname ~]# chown -R fepuser /database/inst1
      [root@hostname ~]# chown -R fepuser /backup
      [root@hostname ~]# chown -R fepuser /pg_tblspc

      Mount the ISO file

      Mount the ISO images of the Fujitsu Enterprise Postgres (Advanced Edition) server to the '/media/dvd' directory. Please check your exact ISO file path and file name, as they vary by the product version or your platform.

      [root@hostname ~]# mount -t iso9660 -r -o loop /home/train/FUJITSU_Enterprise_Postgres_version_FullVersion.iso /media/dvd
      

      Verify the content in the '/media/dvd' directory:

      [root@hostname ~]# ls -l /media/dvd

      Install the server

      Installation of Fujitsu Enterprise Postgres on x86 can be performed using either interactive mode or silent mode.

      Installation in interactive mode

      Perform installation in interactive mode and follow the instructions to complete installation of Fujitsu Enterprise Postgres database server:

      [root@hostname ~]# cd /media/dvd
      [root@hostname ~]# ./install.sh

      You can select the product for installation and check the settings as follows:

      • The product to be installed: For this example, choose 'all'. You can also select only the products you need to install.
      • Installation information: For this example, choose 'y'. The default value is also 'y'.

      Installation in silent mode

      Perform installation in silent mode by passing the installation parameters CSV file to complete installation of Fujitsu Enterprise Postgres.

      The installation parameters CSV file is used to specify which components to be installed along with the installation path. Copy the sample.csv file and modify as necessary.

      • You can choose to install only specific component(s) instead of all. Specify 'Y' against '*InstallExecute' parameter that you want to install. If you specify 'N', the component will not be installed.
      • You can also specify the desired path to install each component, by specifying the path against '*InstallPath' parameter.

      For this example, install Server, Client, WebAdmin, and pgPool-II components to the specific path.

      [root@hostname ~]# cp /media/dvd/sample/sample.csv /tmp/silent_response.csv
      [root@hostname ~]# vi /tmp/silent_response.csv
      installInfo,Name,Fujitsu Enterprise Postgres
      parameters,ServerInstallExecute,Y
      parameters,ServerInstallPath,/opt/fsepvversionserver64
      parameters,Client64InstallExecute,Y
      parameters,Client64InstallPath,/opt/fsepvversionclient64
      parameters,WebAdminInstallExecute,Y
      parameters,WebAdminInstallPath,/opt/fsepvversionwebadmin
      parameters,WebSetupExecute,Y
      parameters,WebPortNumber1,27515
      parameters,WebPortNumber2,27516
      parameters,Pgpool2InstallExecute,Y
      parameters,Pgpool2InstallPath,/opt/fsepvversionpgpool-II

      Run the installation by executing the installer specifying with the installation parameters CSV file.

      [root@hostname ~]# cd /media/dvd/
      [root@hostname dvd]# ./silent.sh /tmp/silent_response.csv

      Check the installed components

      The directories for the Fujitsu Enterprise Postgres binaries will be created under '/opt' by the installation process. All database binaries will be stored under this folder. After the installation is completed, check the installed components using ls command or tree command. For this example, we use ls command:

      [root@hostname ~]# ls -l /opt

      The installation package (rpm) of SERVER is located as below. (* is the version, OS, etc.)

      • Path for the SERVER package required on RHEL8: SERVER/Linux/packages/r80s390x/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on RHEL8: SERVER/Linux/packages/r80s390x/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on RHEL9: SERVER/Linux/packages/r90s390x/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on SLES15: SERVER/Linux/packages/SUSE15s390x/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on RHEL8: SERVER/Linux/packages/r80ppc64le/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on RHEL8: SERVER/Linux/packages/r80ppc64le/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on RHEL9: SERVER/Linux/packages/r90ppc64le/FJSVfsep-SV-*.rpm
      • Path for the SERVER package required on SLES15: SERVER/Linux/packages/SUSE15ppc64le/FJSVfsep-SV-*.rpm

      Run the installation of Fujitsu Enterprise Postgres database server with rpm command. The example below is for RHEL 8 operating system, and ** depends on your platform. Please make sure the rpm package name in your directory:

      [root@hostname ~]# cd /media/dvd
      [root@hostname dvd]# cd SERVER/Linux/packages/r80**
      [root@hostname r80**]# rpm -ivh FJSVfsep-SV-*.rpm

      Run the installation of Fujitsu Enterprise Postgres database server with rpm command. The example below is for SLES 15 operating system, and ** depends on your platform. Please make sure the rpm package name in your directory:

      [root@hostname ~]# cd /media/dvd
      [root@hostname dvd]# cd SERVER/Linux/packages/SUSE15**
      [root@hostname SUSE15**]# rpm -ivh FJSVfsep-SV-*.rpm

      When features other than SERVER are also required, select and install the appropriate packages which you need.

      The directories for the Fujitsu Enterprise Postgres binaries will be created under '/opt' by the installation process. All database binaries will be stored under this folder. After the installation is completed, check the installed components using ls command or tree command. For this example, we use ls command:

      [root@hostname ~]# ls -l /opt

      Create and connect a database instance

      Set environment variables in profile

      Switch to the OS user account 'fepuser' that you want as the databases instance administrator. You cannot use the OS superuser 'root':

      [root@hostname ~]# su - fepuser
      [fepuser@hostname ~]$ cd ~
      [fepuser@hostname ~]$ pwd
      [fepuser@hostname ~]$ vi .bash_profile

      Edit the '.bash_profile' file of 'fepuser' and set some standard path environment variables as follows:

      PATH=/opt/fsepvversionserver64/bin:$HOME/.local/bin:$HOME/bin:$PATH
      MANPATH=/opt/fsepvversionserver64/share/man:$MANPATH
      LD_LIBRARY_PATH=/opt/fsepvversionserver64/lib:$LD_LIBRARY_PATH
      export PATH
      export MANPATH
      export LD_LIBRARY_PATH

      Load the file so that you do not have to type the full path in each time:

      [fepuser@hostname ~]$ . ./.bash_profile

      Create an instance

      Create the database cluster with the initdb command, specifying the storage destination directory '/database/inst1'. Also specify the locale setting option as required:

      [fepuser@hostname ~]$ initdb -D /database/inst1 --lc-collate="C" --lc-ctype="C" --encoding=UTF8

      Connect to the database server

      Start the database server and connect to the database:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start -l logfile
      [fepuser@hostname ~]$ psql -d postgres
      postgres=# \l+

      You can also start or stop the database instance by running the following command as 'fepuser':

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 stop

      Uninstall Fujitsu Enterprise Postgres

      Prepare for uninstallation.

      Before starting the uninstallation, stop the following:

      • Applications that use the product
      • Connection Manager
      • Database instance

        Execute the pg_ctl command in stop mode:

        [fepuser@hostname ~]$ pg_ctl stop -D /database/inst1
      • Web server feature of WebAdmin

        Note that if platform is IBM LinuxONE or IBM Power, you also need to remove WebAdmin setup.

      • Mirroring Controller
      • pgBadger
      • Pgpool-II

      Uninstallation

      Start the Uninstall (middleware) as 'root' user and follow the instructions to select the product to be uninstalled:

      [root@hostname ~]# /opt/FJSVcir/cimanager.sh -c

      Run the uninstallation of SERVER feature as 'root' user:

      [root@hostname ~]# rpm -e FJSVfsep-SV-version

      When features other than SERVER need to be uninstalled, uninstall it by specifying the appropriate package name.

      After the uninstallation is completed, the installation directories may remain. If they are not required, delete them.

      How to configure Transparent Data EncryptionQSG02

      Fujitsu Enterprise Postgres is not available on Version 13 on IBM Power®.
      The article below is invalid.

      Product Fujitsu Enterprise Postgres
      Operating system RHEL, SLES
      Last update January 2024

      Transparent Data Encryption (TDE) is a way to protect data that is stored in an OS file system from attackers who bypass the database server's authentication and access controls. Tablespace data is encrypted when it is stored to disk and decrypted when it is read from disk, and this operation is handled transparently to users.

      Prerequisite

      To proceed with the following steps, it is assumed that Fujitsu Enterprise Postgres is already installed, the database cluster has been already created with the data directory 'database/inst1', and the database instance can be started and stopped as 'fepuser' user through the following commands:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 stop
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 restart

      If not, then install Fujitsu Enterprise Postgres database server following the steps provided in How to install Fujitsu Enterprise Postgres.

      Prepare for configuration

      Create required directories and permissions for the tablespace and keystore:

      [root@hostname ~]# mkdir -p /pg_tblspc/enc_tblspc
      [root@hostname ~]# mkdir -p /database/tde/keystore
      [root@hostname ~]# chown fepuser:fepuser /pg_tblspc/enc_tblspc /database/tde/keystore
      [root@hostname ~]# chmod 700 /pg_tblspc/enc_tblspc /database/tde/keystore

      Create the master encryption key

      Add below parameters to postgresql.conf:

      [fepuser@hostname ~]$ vi /database/inst1/postgresql.conf
      keystore_location = '/database/tde/keystore'
      tablespace_encryption_algorithm = 'AES256'

      Restart the instance:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 restart

      Create the master encryption key:

      postgres=# SELECT pgx_set_master_key('pass-phrase');

      Open the keystore

      To create encrypted tablespaces and access to the encrypted data, the keystore must be opened. Open the keystore using one of the following methods.

      Option 1. Open the keystore using pgx_open_keystore function:

      postgres=# SELECT pgx_open_keystore('pass-phrase');

      Option 2. Open the keystore using pg_ctl command:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 --keystore-passphrase restart
      Enter passphrase: <pass-phrase>

      Option 3. Enable auto-open of the keystore:

      [fepuser@hostname ~]$ pgx_keystore -a -P 'pass-phrase' /database/tde/keystore/keystore.ks

      Create an encrypted tablespace

      Create an encrypted tablespace:

      postgres=# CREATE TABLESPACE enc_tblspc LOCATION '/pg_tblspc/enc_tblspc' with (tablespace_encryption_algorithm='AES256');

      Verify if the created tablespace is encrypted:

      postgres=# SELECT spcname, spcencalgo FROM pg_tablespace ts, pgx_tablespaces tsx WHERE ts.oid = tsx.spctablespace;

      Create a table inside the encrypted tablespace:

      postgres=# CREATE TABLE emp (name TEXT) TABLESPACE enc_tblspc;
      postgres=# INSERT INTO emp VALUES('ABC');
      How to take a logical backup, and how to restore it using Postgres utilitiesQSG03

      Fujitsu Enterprise Postgres is not available on Version 13 on IBM Power®.
      The article below is invalid.

      This article is not available for your selection of product version, platform, and OS.
      The article below is invalid.

      This article is not available for your selection of product version, platform, and OS.
      The article below is invalid.

      Product Fujitsu Enterprise Postgres
      Operating system RHEL, SLES
      Last update January 2024

      One of the backup utilities of Fujitsu Enterprise Postgres is pg_dump and pg_restore. Use these utilities to take logical backup and restore the database.

      Prerequisite

      To proceed with the following steps, it is assumed that Fujitsu Enterprise Postgres is already installed, the database cluster has been already created with the data directory '/database/inst1', and the database instance can be started and stopped as 'fepuser' user through the following commands:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 stop
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 restart

      If not, then install Fujitsu Enterprise Postgres database server following the steps provided in the Quick Start Guide "How to install Fujitsu Enterprise Postgres".

      Create and populate a test database

      Create a test database using pgbench, and verify the tables generated:

      [fepuser@hostname ~]$ createdb testdb01;
      [fepuser@hostname ~]$ pgbench -i testdb01;

      Log in to the database and verify the tables generated:

      postgres=# \l
      postgres=# \c testdb01
      You are now connected to database "testdb01" as user "fepuser".
      testdb01=# \d
                    List of relations
      Schema |       Name       | Type  |  Owner
      --------+------------------+-------+----------
      public | pgbench_accounts | table | fepuser
      public | pgbench_branches | table | fepuser
      public | pgbench_history  | table | fepuser
      public | pgbench_tellers  | table | fepuser
      (4 rows)

      Create the backups

      Create a directory to store the backups:

      [fepuser@hostname ~]$ mkdir -p /backup/inst1/

      Take a backup using pg_dump command:

      [fepuser@hostname ~]$pg_dump -Fc -U fepuser -w testdb01 > /backup/inst1/mybackup

      Check the backups

      List the table of contents (TOC) of the backups. It would dump the TOC and related information:

      [fepuser@hostname ~]$ pg_restore --list /backup/inst1/mybackup

      Restore the database

      Drop the original database:

      [fepuser@hostname ~]$ dropdb testdb01

      Then verify if it is deleted. The following psql command should not show 'testdb01' in the list:

      postgres=# \l

      Restore the database from the backup using pg_restore command:

      [fepuser@hostname ~]$ pg_restore -C -d postgres /backup/inst1/mybackup

      Verify that the database is restored. The following psql command should show 'testdb01' in the list:

      postgres=# \l

      Log in to the database and verify if it has been restored correctly:

      postgres=# \c testdb01
      You are now connected to database "testdb01" as user "fepuser".
      testdb01=# \d
                   List of relations
      Schema |       Name       | Type  |  Owner
      --------+------------------+-------+----------
      public | pgbench_accounts | table | fepuser
      public | pgbench_branches | table | fepuser
      public | pgbench_history  | table | fepuser
      public | pgbench_tellers  | table | fepuser
      (4 rows)

      The above example illustrates a very basic usage of pg_dump and pg_restore utilities to create backup and restore. However, various options and switches provide much more fine grained control over the backup and its restore.

      How to configure Data MaskingQSG04

      Fujitsu Enterprise Postgres is not available on Version 13 on IBM Power®.
      The article below is invalid.

      Product Fujitsu Enterprise Postgres
      Operating system RHEL, SLES
      Last update January 2024

      Data Masking enables sensitive data that stored in the database to be extracted and redacted before sending it to any application.

      Prerequisite

      To proceed with the following steps, it is assumed that Fujitsu Enterprise Postgres is already installed, the database cluster has been already created with the data directory '/database/inst1', and the database instance can be started and stopped as 'fepuser' user through the following commands:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 stop
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 restart

      If not, then install Fujitsu Enterprise Postgres database server following the steps provided in How to install Fujitsu Enterprise Postgres.

      Configure Fujitsu Enterprise Postgres for Data Masking

      To configure Data Masking, you must update postgresql.conf file and set 'shared_preloaded_libraries' parameter to the value 'pgx_datamasking':

      [fepuser@hostname ~]$ vi /database/inst1/postgresql.conf
      shared_preload_libraries = 'pgx_datamasking'

      Save the changes and restart the server to apply the configuration changes:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1/ restart

      Create the Data Masking extension

      Create the extension in the database where Data Masking will be used:

      [fepuser@hostname ~]$ psql -d postgres
      postgres=# CREATE EXTENSION pgx_datamasking;

      Create a table to test Data Masking

      Create a new table named employee:

      postgres=# CREATE TABLE employee(id INT PRIMARY KEY NOT NULL,name VARCHAR(20) NOT NULL,
      postgres-#                       age INT NOT NULL,salary REAL,credit_card TEXT);

      Insert some data into it for testing purpose:

      postgres=# INSERT INTO employee VALUES (1, 'EMP1', 42, 175269, '1234-2345-3456-4567');
      postgres=# INSERT INTO employee VALUES (2, 'EMP2', 36, 119275, '8686-7575-6464-5353');
      postgres=# INSERT INTO employee VALUES (3, 'EMP3', 42, 123216, '3453-3454-5343-3433');
      postgres=# INSERT INTO employee VALUES (4, 'EMP4', 50, 159967, '2581-4703-6925-8147');

      View the values before applying any masking policy to them:

      postgres=# SELECT * FROM employee;

      Create the Data Masking policy

      Full masking

      This will change the data in specified column with replacement value.

      Create a full masking policy on 'salary' column:

      postgres=# SELECT pgx_create_confidential_policy (
      postgres-#         table_name := 'employee',
      postgres-#        policy_name := 'employee_policy',
      postgres-#        enable      := 't',
      postgres-#        expression  := '1=1',
      postgres-#        column_name := 'salary');
      pgx_create_confidential_policy
      --------------------------------
      t
      (1 row)

      Verify the values after application of the masking policy:

      postgres=# SELECT * FROM employee;

      Partial masking

      This will partially changes the data in specified column with replacement values.

      Alter the masking policy:

      postgres=# SELECT pgx_alter_confidential_policy (
      postgres-#        table_name         := 'employee',
      postgres-#        policy_name        := 'employee_policy',
      postgres-#        action             :='ADD_COLUMN',
      postgres-#        column_name        :='credit_card',
      postgres-#        function_type      := 'PARTIAL',
      postgres-#        function_parameters:= 'VVVVFVVVVFVVVVFVVVV, VVVV-VVVV-VVVV-VVVV, *, 0, 12');
      pgx_alter_confidential_policy
      --------------------------------
      t
      (1 row)

      Verify the values after application of the masking policy:

      postgres=# SELECT * FROM employee;

      Regular expression masking

      This will applies a regular expression to the column's value.

      Alter the masking policy:

      postgres=# SELECT pgx_alter_confidential_policy (
      postgres-#        table_name         := 'employee',
      postgres-#        policy_name        := 'employee_policy',
      postgres-#        action             := 'add_column',
      postgres-#        column_name        := 'name',
      postgres-#        function_type      := 'regexp',
      postgres-#        regexp_pattern     := 'EMP',
      postgres-#        regexp_replacement := '*',
      postgres-#        regexp_flags       := 'g');
      pgx_alter_confidential_policy
      --------------------------------
      t
      (1 row)

      Verify the values after application of the masking policy:

      postgres=# SELECT * FROM employee;

      Drop the Data Masking policy

      Delete existing masking policy:

      postgres=# SELECT pgx_drop_confidential_policy (
      postgres-#        table_name  := 'employee',
      postgres-#        policy_name := 'employee_policy');
      pgx_drop_confidential_policy
      --------------------------------
      t
      (1 row)

      Verify that unmasked data is visible after dropping the policy:

      postgres=# SELECT * FROM employee;

      Create the Data Masking policy for specific user only

      Here, we have create the new database user called 'test_masking' and assign the appropriate grants to select the employee table as below.

      postgres=# Create user test_masking password 'fep123' login;
      postgres=# Grant ALL ON employee TO test_masking ;

      Create a full masking policy on 'salary' column:

      postgres=# SELECT pgx_create_confidential_policy (
      postgres-#        table_name  := 'employee',
      postgres-#        policy_name := 'employee_policy',
      postgres-#        enable      := 't',
      postgres-#        column_name := 'salary',
      postgres-#        expression  := 'current_user = ''test_masking''');

      Verify differences in the values of 'employee' table:

      postgres=# SELECT * FROM employee;
      postgres=# \c postgres test_masking
      postgres=> SELECT * FROM employee;
      How to configure Vertical Clustered IndexQSG05

      Fujitsu Enterprise Postgres is not available on Version 13 on IBM Power®.
      The article below is invalid.

      Product Fujitsu Enterprise Postgres
      Operating system RHEL, SLES
      Last update January 2024

      Vertical Clustered Index (VCI) enables fast aggregation by using its memory-resident and disk-resident functions.

      Prerequisite

      To proceed with the following steps, it is assumed that Fujitsu Enterprise Postgres is already installed, the database cluster has been already created with the data directory '/database/inst1', and the database instance can be started and stopped as 'fepuser' user through the following commands:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 stop
      [fepuser@hostname ~]$ pg_ctl -D /database/inst1 restart

      If not, then install Fujitsu Enterprise Postgres database server following the steps provided in How to install Fujitsu Enterprise Postgres.

      Configure Fujitsu Enterprise Postgres for Vertical Clustered Index

      Note: For VCI, minimal 2 CPUs are required to show parallel processing and implementation of max_parallel_workers. Hence, please minimal 2 CPUs machine should be used.

      To configure VCI, you must update postgresql.conf file and set 'shared_preloaded_libraries' and 'session_preload_libraries' parameters to the value 'vci, pg_prewarm' and a few other related parameters as below:

      [fepuser@hostname ~]$ vi /database/inst1/postgresql.conf 
      shared_preload_libraries = 'vci, pg_prewarm'
      session_preload_libraries = 'vci, pg_prewarm'
      shared_buffers = 100MB
      reserve_buffer_ratio = 30
      vci.control_max_workers = 8
      vci.max_parallel_degree = 4
      max_worker_processes = 18
      full_page_writes = on
      jit = off
      max_wal_size = 2GB

      Save the changes and restart the server to apply the configuration changes:

      [fepuser@hostname ~]$ pg_ctl -D /database/inst1/ restart

      Create the Vertical Clustered Index and pg_prewarm extensions

      Create extensions in the database where VCI will be used:

      [fepuser@hostname ~]$ psql postgres 
      postgres=# CREATE EXTENSION vci;
      CREATE EXTENSION
      postgres=# CREATE EXTENSION pg_prewarm;
      CREATE EXTENSION

      Create a table to test Vertical Clustered Index

      Create a new table with lots of random numbers:

      postgres=# CREATE TABLE random_numbers AS SELECT numbers, md5(random()::text) FROM generate_series(1,10000000) numbers;
      SELECT 10000000

      Now, check the performance of sum() aggregate function before creating the VCI. Notice the 'Execution time':

      postgres=# EXPLAIN ANALYZE SELECT SUM(numbers) FROM random_numbers;

      Create the Vertical Clustered Index

      Create VCI on the target columns:

      postgres=# CREATE INDEX random_numbers_vci ON random_numbers USING VCI (numbers) WITH (stable_buffer=true);
      CREATE INDEX

      Now, check the performance of sum() aggregate function after creating the VCI. Notice the difference in 'Execution Time' shown in the Query Plan:

      postgres=# EXPLAIN ANALYZE SELECT SUM(numbers) FROM random_numbers;

      Delete the Vertical Clustered Index

      Delete the created VCI:

      postgres=# DROP INDEX random_numbers_vci;
      How to configure the Dedicated Audit LogQSG0^

      Fujitsu Enterprise Postgres is not available on Version 13 on IBM Power®.
      The article below is invalid.

      Product Fujitsu Enterprise Postgres
      Operating system RHEL, SLES
      Last update May 2024

      The Dedicated Audit log of Fujitsu Enterprise Postgres enables the automated retrieval of details of database access as an audit log. These audit logs can be output to a dedicated log file or to the server log file. The details in the log file can be used for efficient monitoring.

      There are two types of auditing in Fujitsu Enterprise Postgres:

      • Session auditing
      • Object auditing

      This guide provides the steps for setting up session audit log only.

      Prerequisite

      To proceed with the following steps, the following is assumed:

      • Fujitsu Enterprise Postgres is already installed
      • The database cluster has been already created with the data directory '/database/inst1'
      • The database instance can be started and stopped as 'fepuser' user through the following commands:
        [fepuser@hostname ~]$ pg_ctl -D /database/inst1 start
        [fepuser@hostname ~]$ pg_ctl -D /database/inst1 stop
        [fepuser@hostname ~]$ pg_ctl -D /database/inst1 restart

      If the requirements above are not met, then install Fujitsu Enterprise Postgres database server following the steps provided in How to install Fujitsu Enterprise Postgres.

      Copy the pgaudit installation files

      As a superuser copy the pgaudit installation files:

      [fepuser@hostname ~]$ su –
      Password: ********
      [root@hostname ~]# cp -r /opt/fsepv15server64/OSS/pgaudit/* /opt/fsepv15server64/
      [root@hostname ~]# exit

      Create and set up the pgaudit configuration file

      Create the pgaudit configuration file in the data directory and set up the logger parameters as below:

      [fepuser@hostname ~]$ export PGDATA=/database/inst1
      [fepuser@hostname ~]$ vi ${PGDATA}/pgaudit.conf
      
      [output]
      logger = 'auditlog'	# Dedicated Audit Log file. Either auditlog or serverlog
      log_filename= 'pgaudit-%Y-%m-%d.log'
      log_file_mode = '0600'  # Permissions for audit logs

      Save the changes and set the permissions on the configuration file as below and then stop the instance:

      [fepuser@hostname ~]$ chmod 0600 ${PGDATA}/pgaudit.conf
      [fepuser@hostname ~]$ pg_ctl -D $PGDATA stop

      Configure postgresql.config for session audit logging

      In the postgresql.conf file, set up the following parameters:

      [fepuser@hostname ~]$ vi ${PGDATA}/postgresql.conf
      
      shared_preload_libraries = 'pgaudit' # (change requires restart)
      pgaudit.config_file = 'pgaudit.conf'
      log_replication_commands = on

      Save the changes and restart the server to apply the configuration changes:

      [fepuser@hostname ~]$ pg_ctl -D ${PGDATA} start

      Create the required extension

      Create the pgaudit extension in the database where it is supposed to be used:

      postgres=# CREATE EXTENSION pgaudit;
      CREATE EXTENSION 

      Configure the session audit logging for a user

      Session audit logging can be used to capture all the activity of a user in a Fujitsu Enterprise Postgres instance.

      Edit 'pgaudit.config' to add rule section to configure session audit logging for user 'user01' (as an example) as below:

      [output]
      logger = 'auditlog' # Dedicated Audit Log file. Either auditlog or serverlog
      log_filename= 'pgaudit-%Y-%m-%d.log'
      log_file_mode = '0600' # Permissions for audit logs
      
      [rule]
      audit_role='user01' # Roles to audit

      Save the changes and restart the server to apply the configuration changes:

      [fepuser@hostname ~]$ pg_ctl -D ${PGDATA} restart

      Verify the session audit logging for the user

      Create a 'user01' (as superuser ) in the Postgres instance and then connect to the instance as user 'user01' and execute SQL statements:

      [fepuser@hostname ~]$ psql –p 27500 -d postgres -U user01 ;
      postgres=# select now();
      postgres=# CREATE TABLE tab1001 (name text);
      CREATE TABLE
      postgres=# CREATE TABLE tab1002 (empno int);
      CREATE TABLE 
      postgres=# \q
      [fepuser@hostname ~]$ psql -d postgres -U user01 ;
      postgres=# select now();
      postgres=# INSERT INTO tab1001 VALUES ('mynameisAAA');
      INSERT 0 1
      postgres=# INSERT INTO tab1001 VALUES ('mynameisBBB');
      INSERT 0 1
      postgres=# INSERT INTO tab1002 VALUES (9001);
      INSERT 0 1
      postgres=# INSERT INTO tab1002 VALUES (9002);
      INSERT 0 1
      postgres=# \q
      [fepuser@hostname ~]$ psql -d postgres -U user01 ;
      postgres=# select now();
      postgres=# SELECT * FROM tab1001;
      postgres=# SELECT * FROM tab1002;
      postgres=# \q
      [fepuser@hostname ~]$

      Verify that the above SQL activity for 'user01' was captured in the dedicated audit log:

      [fepuser@hostname ~]$ cat ${PGDATA}/pgaudit_log/pgaudit- 2024-05-10.log
      AUDIT: SESSION,CONNECT,2024-05-10 08:39:52 UTC,[local],22896,[unknown],user01,postgres,3/9,,,,00000,,,connection authorized: user=user01 database=postgres application_name=psql,,
      AUDIT: SESSION,READ,2024-05-10 08:41:31 UTC,[local],22896,psql,user01,postgres,3/10,1,1,SELECT,,,,,select now();,<not logged>
      AUDIT: SESSION,DDL,2024-05-10 08:42:43 UTC,[local],22896,psql,user01,postgres,3/11,2,1,CREATE TABLE,,TABLE,public.tab1001,,CREATE TABLE tab1001 (name text);,<not logged>
      AUDIT: SESSION,DDL,2024-05-10 08:43:19 UTC,[local],22896,psql,user01,postgres,3/12,3,1,CREATE TABLE,,TABLE,public.tab1002,,CREATE TABLE tab1002 (empno int);,<not logged>
      AUDIT: SESSION,READ,2024-05-10 08:44:41 UTC,[local],22896,psql,user01,postgres,3/15,6,1,SELECT,,,,,select now();,<not logged>
      AUDIT: SESSION,CONNECT,2024-05-10 08:55:27 UTC,[local],23116,[unknown],user01,postgres,3/34,,,,00000,,,connection authorized: user=user01 database=postgres application_name=psql,,
      AUDIT: SESSION,READ,2024-05-10 08:55:40 UTC,[local],23116,psql,user01,postgres,3/35,1,1,SELECT,,,,,select now();,<not logged>
      AUDIT: SESSION,WRITE,2024-05-10 08:57:38 UTC,[local],23116,psql,user01,postgres,3/36,2,1,INSERT,,TABLE,public.tab1001,,INSERT INTO tab1001 VALUES ('mynameisAAA');,<not logged>
      AUDIT: SESSION,WRITE,2024-05-10 08:58:02 UTC,[local],23116,psql,user01,postgres,3/37,3,1,INSERT,,TABLE,public.tab1001,,INSERT INTO tab1001 VALUES ('mynameisBBB');,<not logged>
      AUDIT: SESSION,WRITE,2024-05-10 08:58:57 UTC,[local],23116,psql,user01,postgres,3/39,5,1,INSERT,,TABLE,public.tab1002,,INSERT INTO tab1002 VALUES (9001);,<not logged>
      AUDIT: SESSION,WRITE,2024-05-10 08:59:05 UTC,[local],23116,psql,user01,postgres,3/40,6,1,INSERT,,TABLE,public.tab1002,,INSERT INTO tab1002 VALUES (9002);,<not logged>
      [fepuser@hostname ~]$
      AUDIT: SESSION,CONNECT,2024-05-10 10:42:55 UTC,[local],24617,[unknown],user01,postgres,3/580,,,,00000,,,connection authorized: user=user01 database=postgres application_name=psql,,
      AUDIT: SESSION,READ,2024-05-10 10:34:15 UTC,[local],24480,psql,user01,postgres,3/575,1,1,SELECT,,,,,select now();,<not logged>
      AUDIT: SESSION,READ,2024-05-10 10:34:33 UTC,[local],24480,psql,user01,postgres,3/576,2,1,SELECT,,TABLE,public.tab1001,,SELECT * FROM tab1001;,<not logged>
      AUDIT: SESSION,READ,2024-05-10 10:34:40 UTC,[local],24480,psql,user01,postgres,3/577,3,1,SELECT,,TABLE,public.tab1002,,SELECT * FROM tab1002;,<not logged>
      [fepuser@hostname ~]$

      Configure the object audit logging for a user

      Object audit logging can be used to capture all the activity of user for which he has been granted permissions or privileges.

      Edit 'pgaudit.config' to add 'option' section to configure object audit logging for user 'user51' (as an example) as below:

      [output]
      logger = 'auditlog' # Dedicated Audit Log file. Either auditlog or serverlog
      log_filename= 'pgaudit-%Y-%m-%d.log'
      log_file_mode = '0600' # Permissions for audit logs
      
      [option]
      role='hrman01' # Roles to audit

      Save the changes and restart the server to apply the configuration changes:

      [fepuser@hostname ~]$ pg_ctl -D ${PGDATA} restart

      Verify the object audit logging for the user

      Connect as fepuser and create emp table (just as example) and populate with some data as shown below. Then create a 'hrman01' user (as non-superuser) in the postgres instance and then grant privileges as below:

      [fepuser@hostname ~]$ psql –p 27500 -d postgres;
      CREATE TABLE emp (id int, name text, address text, salary real);
      INSERT INTO emp VALUES ( 1001 , 'empName101', 'emp_address101', 105000 );
      INSERT INTO emp VALUES ( 1002 , 'empName102', 'emp_address102', 108000 );
      INSERT INTO emp VALUES ( 1012 , 'empName112', 'emp_address112', 103000 );
      INSERT INTO emp VALUES ( 1013 , 'empName113', 'emp_address113', 119000 );
      INSERT INTO emp VALUES ( 1015 , 'empName115', 'emp_address115', 115000 );
      
      CREATE USER hrman01 nosuperuser login;
      GRANT SELECT ON emp TO hrman01;
      GRANT UPDATE (salary) ON emp TO hrman01;
      GRANT UPDATE (address) ON emp TO hrman01;

      Connect to the instance as user 'hrman01' user and execute following SQL statements to observe object audit logging:

      [fepuser@hostname ~]$ psql –p 27500 -d postgres -U hrman01 ;
      postgres=> SELECT id from emp;
        id
      ------
       1001
       1002
       1012
       1013
       1015
      (5 rows)
      
      postgres=> SELECT name from emp;
          name
      ------------
       empName101
       empName102
       empName112
       empName113
       empName115
      (5 rows)
      
      postgres=> UPDATE emp SET name = 'empName11200' WHERE id = 1012;
      2024-05-10 13:11:56.652 UTC [26852] ERROR:  permission denied for table emp
      2024-05-10 13:11:56.652 UTC [26852] STATEMENT:  UPDATE emp SET name = 'empName11200' WHERE id = 1012;
      ERROR:  permission denied for table emp
      postgres=> UPDATE emp SET salary = 120000 WHERE id = 1012;
      UPDATE 1
      postgres=> UPDATE emp SET address = 'emaddress-1000013131' WHERE id = 1013;
      UPDATE 1
      postgres=> 
      postgres=> \q
      [fepuser@hostname ~]$

      Verify that the above SQL activity for 'hrman01' was captured in the dedicated audit log as below. Observe that there was no record for the errored out statement (ERROR: permission denied for table emp) for which there was no privilege granted to the user.

      [fepuser@hostname ~]$ cat ${PGDATA}/pgaudit_log/pgaudit- 2024-05-10.log
      AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.emp,SELECT id from emp;,<not logged>
      AUDIT: OBJECT,2,1,READ,SELECT,TABLE,public.emp,SELECT name from emp;,<not logged>
      AUDIT: OBJECT,3,1,WRITE,UPDATE,TABLE,public.emp,UPDATE emp SET salary = 120000 WHERE id = 1012;,<not logged>
      AUDIT: OBJECT,4,1,WRITE,UPDATE,TABLE,public.emp,UPDATE emp SET address = 'emaddress-1000013131' WHERE id = 1013;,<not logged>
      [fepuser@hostname ~]$

      Configuration to view audit log via SQL

      Optionally, we can also enable viewing audit logs from SQL. It requires configuration changes as suggested below.

      Add 'file_fdw' in 'shared_preload_libraries' parameter.

      [fepuser@hostname ~]$ vi ${PGDATA}/postgresql.conf
      
      shared_preload_libraries = 'pgaudit,file_fdw'

      Restart the instance.

      [fepuser@hostname ~]$ pg_ctl -D ${PGDATA} restart

      Add extension 'file_fdw'.

      postgres=# create extension file_fdw;
      CREATE EXTENSION

      Create an external server.

      postgres=# create server auditlog foreign data wrapper file_fdw;
      CREATE SERVER

      Create foreign table to store audit logs.

      postgres=#postgres=# CREATE FOREIGN TABLE auditlog (header text, class text, 
      postgres-#     sql_start_time timestamp with time zone, remote_host_name text, backend_process_id text,
      postgres-#     application_name text, session_user_name text, database_name text, 
      postgres-#     virtual_transaction_id text, statement_id text, substatement_id text, command_tag text,
      postgres-#     sqlstate text, object_type text, object_name text, error_message text, sql text, 
      postgres-#     parameter text) 
      postgres-#   SERVER auditlog 
      postgres-#   OPTIONS (filename '/database/inst1/pgaudit_log/pgaudit-2024-05-10.log', format 'csv');
      CREATE FOREIGN TABLE
      

      View the audit log table using SQL

      Now you can view the audit log using SQL. In the below example, only one record of the audit logs will be returned.

      postgres=# SELECT * FROM auditlog LIMIT 1;

      Transforming the audit log into SQL tables

      Optionally, audit logs can be saved to the permanent tables.

      postgres=# CREATE TABLE auditlog_2024_05_10 AS SELECT * FROM auditlog
      postgres-# WHERE cast(sql_start_time::timestamp(0) AS DATE) = cast(now()::timestamp(0) AS DATE);
      SELECT 29
      postgres=# SELECT * FROM auditlog_2024_05_10 LIMIT 1 ;

      Disabling the audit logging

      To drop the extension, execute following command.

      postgres=# DROP EXTENSION pgaudit;

      Remove 'pg_audit' from shared_preload_libraries parameter in postgresql.conf file.

      [fepuser@hostname ~]$ vi ${PGDATA}/postgresql.conf
      
      shared_preload_libraries = '' 

      Remove the pgaudit files which were copied earlier in section 3.1.1.

      [root@hostname ~]# find /opt/fsepv15server64/OSS/pgaudit -type f |sed s'#/opt/fsepv15server64/OSS/pgaudit#rm /opt/fsepv15server64#' > /tmp/List.sh
      [root@hostname ~]# sh -x /tmp/List.sh

      Restart the instance.

      [fepuser@hostname ~]$ pg_ctl –D $PGDATA start
      Click to navigate Click to navigate Click to navigate Click to navigate Click to navigate
      Quick Start Guides provide standard steps to deploy and use Fujitsu Enterprise Postgres. To discuss any technical requirements or questions specific to your environment, contact us.

      Read our latest blogs

      Read our most recent articles regarding all aspects of PostgreSQL and FUJITSU Enterprise Postgres.