Skip to main content

Search

Welcome to Tibero GTS!

OpenSQL (PostgreSQL) Installation Guide

Document Type | Technical Information

Category | Installation

Applicable Product Version | OpenSQL (PostgreSQL) 14.2

Document Number | OINTI001

 

Overview

This guide summarizes the installation procedure for the OpenSQL (PostgreSQL) DBMS based on a test environment.

It includes pre-installation preparations and step-by-step installation tasks confirmed through the actual installation process, and is intended for reference when building OpenSQL for the first time in a test environment.

 

Test Environment

  • DB: OpenSQL (PostgreSQL) 14.2
  • OS: CentOS 7.7

Configuration Information 

Primary Server192.168.41.220
Standby Server192.168.41.221
Postgresql Port5432
Pcp Port9898
Watchdog Port9000
Watchdogheartbeat Port9694

 

 

Method

Installation Test

  1. Pre-installation Checklist

1) Minimum Installation Requirements

• CPU core: 1 Core

• Memory: 300MB

• Disk: 750MB

 

2) Required Packages

• GNU Make 3.80 or higher

• Compiler: gcc recommended

• Compression package: tar

• Compression library (pg_dump, pg_restore): zlib

• Command-line SQL interpreter: readline

• Non-English message display: gettext

• Extension modules: libxslt, libicu

• Git / Libperl 5.8.3 or higher

• OpenSQL team guide package:

# yum install -y make gcc gcc-c++ tar zlib zlib-devel readline readline-devel gettext gettext-devel git libxslt libicu

# yum install -y perl perl-libs tcl python python-devel openssl openssl-devel

 

3) Optional Packages

• libperl 5.8.3 or higher

• PL/tcl: tcl 8.4 or higher

• PL/Python: python 2.7 or higher, python 3 supported

• Encrypted client connection: openssl, openssl-devel

• Authentication related: Kerberos, OpenLDAP, PAM

※ If you need to install extensions such as gis / pgaudit, the libperl package must be installed

 

4) Disable Firewall and Set SELinux to 0

• For smooth test installation, disable the firewall: systemctl stop firewalld / systemctl disable firewalld

• SELINUX DISABLED: Edit SELINUX=disabled in vi /etc/sysconfig/selinux

 

2. OpenSQL (Postgresql) Installation

1) Install the Provided RPM (※ The installation RPM can be provided by the OpenSQL team.)

• After uploading the installation file to the test environment VM, perform the following installation steps as the root account.

• Installation version: Postgresql-14.2

• When installing the RPM, you must install in the following order.

▶ [Account to execute: root]

# pwd

/binary/PostgreSQL_v1_package_rhel7/postgresql-14

# unzip PostgreSQL_v1_package_rhel7.zip

# cd /sql/PostgreSQL_v1_package_rhel7/postgresql-14

# rpm -ivh postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql14-14.2-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm

# rpm -ivh postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm

Alternatively, you can automatically install without a specific order using yum install –y *.rpm,

and you can check the installed information with yum list installed | grep postgresql.

 

※ Package Description

postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpmShared library required by all postgresql clients
postgresql14-14.2-1PGDG.rhel7.x86_64.rpmpostgresql client programs and libraries
postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpmPrograms required to create and run a postgresql server
postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpmAdditional feature distribution sources and binaries distributed with postgresql

 

2) Create User and Directory

• User: Create opensql (create user as requested by customer)

• Required directories must be created manually

▶ [Account to execute: root]

# groupadd opensql -g 1008

# useradd opensql -u 1008 -g opensql -c 'OpenSQL Database' -d /opensql

# passwd opensql (set password as user prefers)

# echo “d /run/opensql 0755 opensql opensql -” > /usr/lib/tmpfiles.d/opensql.conf

This command prevents the directory under /var/run from being deleted as it is volatile

 

▶ [Account to execute: opensql]

# mkdir -p /opensql/pg/14/data

# mkdir -p /opensql/pg/14/log/pg_log

# mkdir -p /opensql/pg/14/pg_wal

# mkdir -p /opensql/pg/14/archive

# mkdir -p /var/run/opensql

# chown -R opensql:opensql/opensql

# chown -R opensql:opensql/var/run/opensql

 

※ Directory Description:

/opensql/pg/14/datapostgresql database cluster area
/opensql/pg/14/log/pg_logpostgresql log path
/opensql/pg/14/pg_walPath where postgresql wal files are stored
/opensql/pg/14/archivePath where postgresql archive files are stored
/var/run/opensqlPath where unix domain sockets required for postgresql communication are stored

 

3) Set .bash_profile

▶ [Account to execute: opensql]

# vi ~/.bash_profile

###OpenSQL-PostgreSQL ENV

PS1="[\u@\h:\W]$ "

export PGVERSION=14

if [ -f "/usr/pgsql-$PGVERSION/bin/pg_ctl" ]; then

export PGINST=/usr/pgsql-$PGVERSION

export MANPATH=/usr/pgsql-$PGVERSION/share/man:$MANPATH

export LD_LIBRARY_PATH=/usr/pgsql-$PGVERSION/lib:$LD_LIBRARY_PATH

export PATH=/usr/pgsql-$PGVERSION/bin:$PATH

export PGLIB=/usr/pgsql-$PGVERSION/lib

fi

export PGHOME=/opensql/pg

export PGDATA=/opensql/pg/$PGVERSION/data

export PGHOST=/var/run/opensql

export PGUSER=postgres

 

###OpenSQL-PostgreSQL ALIAS

alias pginst="cd $PGINST"

alias pglib="cd $PGLIB"

alias pghome="cd $PGHOME"

alias pgdata="cd $PGDATA"

alias pglog="cd $PGHOME/$PGVERSION/log/pg_log"

alias pgwal="cd $PGHOME/$PGVERSION/pg_wal"

alias pgconf="vi $PGDATA/postgresql.conf" alias pghba="vi $PGDATA/pg_hba.conf"

 

4) Create postgresql database cluster

• Use the initdb command to configure postgresql environment and generate basic files.

▶ [Account to execute: opensql]

-- Syntax: initdb [options...][-U] superuser name [-D] directory [-X] wal file location

# initdb -U postgres -D $PGDATA -X $PGHOME/$PGVERSION/pg_wal

 

※ Option Description

-D, --pgdata=]DATADIRDirectory to create a new database cluster
-U, --username=NAMEDatabase superuser name
-X, --waldir=WALDIRTransaction log directory location
--wal-segsize=SIZEWAL segment file size, in MB
--encodingSpecify database encoding (default is OS)

 

5) Add postgresql.conf Settings

▶ [Account to execute: opensql]

# vi $PGDATA/postgresql.conf - Add at the end

listen_addresses = '*'

port = 5432

unix_socket_directories = '/var/run/opensql'

logging_collector = on

log_directory = '/opensql/pg/14/log/pg_log'

log_filename = 'postgresql-%Y-%m-%d-%H%M%S.log'

log_rotation_age = 0

log_rotation_size = 100MB

wal_log_hints = on

archive_mode = on

archive_command = 'test ! -f /opensql/pg/14/archive/%f && cp %p /opensql/pg/14/archive/%f'

 

6) pg_hba.conf Settings (Set to allow all access for redundancy and access testing)

▶ [Account to execute: opensql]

# vi $PGDATA/pg_hba.conf -- Add at the end

# TYPE DATABASE USER ADDRESS METHOD

host all all 0.0.0.0/0 trust

 

※ Field Value Description:

typeSpecify authentication type (local, host, etc.)
databaseDatabase name to apply the rule
userUser name to apply the rule
addressClient's IP address or domain address to access
methodSpecify authentication method (trust, md5, password, etc.)

 

7) Start / Stop

• pg_ctl command ;

- start: Start database server

- stop: Stop database server

- restart: Restart database server

- reload: Apply modified configuration file to server

- status: Display server status

[User to execute: opensql]

# pg_ctl start -- Start

# pg_ctl stop -- Stop

-- How to change master account password: alter role postgres password ’opensql’;

 

Note

  • OpenSQL (PostgreSQL) DBMS currently supports up to PostgreSQL version 15.
  • The installation test included in this document is based on a record of past version installation tests installed at customer sites.
  • The installation binaries can be provided separately through the OpenSQL team.

 

 

 

Related to