Fujitsu Enterprise Postgres Quick Start Guides

Install OS packages

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

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

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

[root@hostname ~]# zypper install glibc

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

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

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

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

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

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

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

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

Set environment variables in profile

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

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

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

Create an instance

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

Connect to the database server

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

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

Prepare for uninstallation.

Uninstallation

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

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

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

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');

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:

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');

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

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

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.

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 extension in the database where Data Masking will be used:

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

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;

Full masking

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)

postgres=# SELECT * FROM employee;

Partial masking

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)

postgres=# SELECT * FROM employee;

Regular expression masking

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)

postgres=# SELECT * FROM employee;

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;

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;

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 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 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 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 created VCI:

postgres=# DROP INDEX random_numbers_vci;