Install Postgres Advanced Server on Linux(RHEL/CentOS)


In this post, I had explained how we can install PostgreSQL on a Linux server using the source code. I also had shown ho we can automate the installation & create multiple instances. This time I am going to demonstrate how we can install Postgres Plus Advanced Server on a Linux server(RHEL/CentOS). Postgres Plus Advanced Server is an advanced version of PostgreSQL that offers many features as compared to the open source variant. You can read about it here.

Lets start with this tutorial. Please note that you will need root privileges to perform the installation.

Download the installer. First, download the installer from its official site. Make sure that you choose the installer according to your CPU architecture. You will need to create your account on that site to download it. This is a binary installer.

Unpack the tarball. Issue below commands to unpack the installer tarball. You can see a Java installer here 😉 Postgres Plus Advanced Server or PPA needs Java for some functionalities like Postgre Client (a web-based UI). So, Java has to be installed otherwise PPA will disable Java based components. I installed Java 1.8 (OpenJDK) & it installed a few dependencies as well. And when I executed the installer, it threw a “segmentation fault” error. So, I uninstalled Java & all its dependencies(that got installed alongwith Java) and then installed Java 1.7(the pre-compiled one, not using YUM since that would have installed dependencies again) & then I was able to run installer. If you face the same issue, follow what I did 😉

[root@server shashank]# ll
total 335956
-rw-rw-r-- 1 shashank shashank 160872482 Dec 9 10:22 jdk-8u25-linux-x64.tar.gz
-rw-r--r-- 1 root root 0 Dec 9 10:26 postgresql.txt
-rw-rw-r-- 1 shashank shashank 183143348 Dec 8 16:23 ppasmeta-9.4.1.3-linux-x64.tar.gz
[root@server shashank]# tar -xf ppasmeta-9.4.1.3-linux-x64.tar.gz
[root@server shashank]# ll
total 335960
-rw-rw-r-- 1 shashank shashank 160872482 Dec 9 10:22 jdk-8u25-linux-x64.tar.gz
-rw-r--r-- 1 root root 0 Dec 9 10:26 postgresql.txt
drwxrwxr-x 2 500 500 4096 Feb 10 2015 ppasmeta-9.4.1.3-linux-x64
-rw-rw-r-- 1 shashank shashank 183143348 Dec 8 16:23 ppasmeta-9.4.1.3-linux-x64.tar.gz
[root@server shashank]# cd ppasmeta-9.4.1.3-linux-x64
[root@server ppasmeta-9.4.1.3-linux-x64]# ll
total 180960
-rwxr-xr-x 1 500 500 185293474 Feb 10 2015 ppasmeta-9.4.1.3-linux-x64.run
-rw-rw-r-- 1 500 500 7836 Feb 10 2015 README_FIRST_Linux64.txt

Run the installer.

[root@server ppasmeta-9.4.1.3-linux-x64]# ./ppasmeta-9.4.1.3-linux-x64.run
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : ^C

You can see I interrupted the installation 😀 Why? Because its better if we add a user called postgres without assigning any password before installing. PostgreSQL uses “postgres” user for its operation. Only “postgres” user can start/stop/restart database. So, add this is advance.

[root@psltddpdb01 ppasmeta-9.4.1.3-linux-x64]# useradd postgres
[root@psltddpdb01 ppasmeta-9.4.1.3-linux-x64]# ./ppasmeta-9.4.1.3-linux-x64.run
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.

----------------------------------------------------------------------------
Please read the following License Agreement. You must accept the terms of this
agreement before continuing with the installation.

Press [Enter] to continue:
Limited Use Software License Agreement
Version 2.9

IMPORTANT - READ CAREFULLY

Press [Enter] to continue:

Do you accept this license? [y/n]: y

----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be
installed.

Installation Directory [/opt/PostgresPlus]: /opt/app/PostgresPlus

----------------------------------------------------------------------------
Select the components you want to install.

Database Server [Y/n] :y

Connectors [Y/n] :y

Infinite Cache [Y/n] :y

Migration Toolkit [Y/n] :y

Postgres Enterprise Manager Client [Y/n] :y

pgpool-II [Y/n] :y

pgpool-II Extensions [Y/n] :y

EDB*Plus [Y/n] :y

Slony Replication [Y/n] :y

PgBouncer [Y/n] :y

Is the selection above correct? [Y/n]: y

----------------------------------------------------------------------------
Additional Directories

Please select a directory under which to store your data.

Data Directory [/opt/app/PostgresPlus/9.4AS/data]:

Please select a directory under which to store your Write-Ahead Logs.

Write-Ahead Log (WAL) Directory [/opt/app/PostgresPlus/9.4AS/data/pg_xlog]:

----------------------------------------------------------------------------
Configuration Mode

Postgres Plus Advanced Server always installs with Oracle(R) compatibility features and maintains full PostgreSQL compliance. Select your style preference for installation defaults and samples.

The Oracle configuration will cause the use of certain objects (e.g. DATE data types, string operations, etc.) to produce Oracle compatible results, create the same Oracle sample tables, and have the database match Oracle examples used in the documentation.

Configuration Mode

[1] Oracle Compatible
[2] PostgreSQL Compatible
Please choose an option [1] : 2

----------------------------------------------------------------------------
Please provide a password for the database superuser (postgres). A locked Unix
user account (postgres) will be created if not present.

Password :
Retype Password :
----------------------------------------------------------------------------
Additional Configuration

Please select the port number the server should listen on.

Port [5432]:

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]

Please choose an option [1] :

Install sample tables and procedures. [Y/n]:
----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Server Utilization

Please select the type of server to determine the amount of system resources
that may be utilized:

[1] Development (e.g. a developer's laptop)
[2] General Purpose (e.g. a web or application server)
[3] Dedicated (a server running only Postgres Plus)
Please choose an option [2] : 3

----------------------------------------------------------------------------
Dynatune Dynamic Tuning:
Workload Profile

Please select the type of workload this server will be used for:

[1] Transaction Processing (OLTP systems)
[2] General Purpose (OLTP and reporting workloads)
[3] Reporting (Complex queries or OLAP workloads)
Please choose an option [1] : 1

----------------------------------------------------------------------------
Advanced Configuration

----------------------------------------------------------------------------
PgBouncer Listening Port [6432]:

----------------------------------------------------------------------------
Service Configuration

Autostart PgBouncer Service [Y/n]: y
Autostart pgAgent Service [Y/n]: y
Update Notification Service [Y/n]: y
The Update Notification Service informs, downloads and installs whenever
security patches and other updates are available for your Postgres Plus Advanced
Server installation.

----------------------------------------------------------------------------
Pre Installation Summary

Following settings will be used for installation:

Installation Directory: /opt/app/PostgresPlus
Data Directory: /opt/app/PostgresPlus/9.4AS/data
WAL Directory: /opt/app/PostgresPlus/9.4AS/data/pg_xlog
Database Port: 5432
Database Superuser: postgres
Operating System Account: postgres
Database Service: ppas-9.4
PgBouncer Listening Port: 6432

Press [Enter] to continue:

----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your
computer.

Do you want to continue? [Y/n]: y

----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.

Installing Postgres Plus Advanced Server
0% ______________ 50% ______________ 100%
########################################
Installing Database Server ...
Installing pgAgent ...
Installing Connectors ...
Installing Migration Toolkit ...
Installing EDB*Plus ...
Installing Infinite Cache ...
Installing Postgres Enterprise Manager Client ...
Installing Slony Replication ...
Installing pgpool-II ...
Installing pgpool-II Extensions ...
Installing PgBouncer ...
Installing StackBuilder Plus ...
#

----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.

Change ownership to “postgres” user on data directory.

[root@server ppasmeta-9.4.1.3-linux-x64]# chown postgres.postgres /opt/app/PostgresPlus/9.4AS/data

Switch to postgres user & login to database.

[root@server ppasmeta-9.4.1.3-linux-x64]# su - postgres
[postgres@psltddpdb01 ~]$ /opt/app/PostgresPlus/9.4AS/bin/psql -p5432
psql.bin (9.4.1.3)
Type "help" for help.

postgres=# \q

That’s all from this post 🙂 I hope this was informative & fun to read this 🙂 Thanks for visiting my blog 🙂

Advertisements

Automate PostgreSQL Database Instance Creation


At times there are needs to setup multiple PostgreSQL instances. I have explained you how to install PostgreSQL & how to automate its installation on Linux. This time I will show you how to automate PostgreSQL instances creation using a shell-script. Instances are PostgreSQL installations to different directories running on different ports.

Below is the script that I wrote to automate this task 🙂 It will prompt you to enter the location & port.

#set -x
#PostgreSQL instance creation script version 1.0
#Author : - Shashank Srivastava
echo "Welcome to PostgreSQL instance creation script. Below instances are already created. Use a unique path."
echo ""
ls -lf /opt | grep -i postgre | sort
echo ""
echo -n "Enter the absolute path for this instance. "
read ans
echo ""
echo "You chose $ans. Below are the Postgre ports currently in use. Please choose a unique port when prompted."
echo ""
netstat -tulnp | grep postgre
echo ""
echo -n "Enter port for this new instance to run on. "
read PORTNUM
echo ""
mkdir -p $ans
sleep 2s
mkdir -p $ans/data
sleep 2s
chown -R postgres $ans/data
cd /home/shashank/postgresql-9.3.5
./configure -prefix=$ans
make
make install
su - postgres -c "$ans/bin/initdb -D $ans/data"
su - postgres -c "cp -p $ans/data/postgresql.conf $ans/data/postgresql.conf.bkp"
sed -i "s/#port = 5432/port = $PORTNUM/g" $ans/data/postgresql.conf
su - postgres -c "$ans/bin/pg_ctl -D $ans/data -l logfile start"
echo ""
echo "Checking PostgreSQL verison..."
echo ""
$ans/bin/psql --version
echo ""
echo "Postgre instance is running on port $PORTNUM"
netstat -tulnp | grep $PORTNUM
echo ""
echo "Installation Successful."

Here’s the script in action 😉

[root@server shashank]# sh postgre_instance_setup.sh
Welcome to PostgreSQL instance creation script. Below instances are already created. Use a unique path.

PostgreSQL
PostgreSQL2
PostgreSQL3
PostgreSQL4
PostgreSQL5
PostgreSQL6
PostgreSQL7

Enter the absolute path for this instance. abc

You chose abc. Below are the Postgre ports currently in use. Please choose a unique port when prompted.

tcp        0      0 127.0.0.1:5437              0.0.0.0:*                   LISTEN      785/postgres
tcp        0      0 127.0.0.1:5438              0.0.0.0:*                   LISTEN      8863/postgres
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      6723/postgres
tcp        0      0 0.0.0.0:5433                0.0.0.0:*                   LISTEN      51663/postgres
tcp        0      0 0.0.0.0:5434                0.0.0.0:*                   LISTEN      35999/postgres
tcp        0      0 127.0.0.1:5435              0.0.0.0:*                   LISTEN      48190/postgres
tcp        0      0 127.0.0.1:5436              0.0.0.0:*                   LISTEN      57141/postgres
tcp        0      0 ::1:5437                    :::*                        LISTEN      785/postgres
tcp        0      0 ::1:5438                    :::*                        LISTEN      8863/postgres
tcp        0      0 :::5432                     :::*                        LISTEN      6723/postgres
tcp        0      0 :::5433                     :::*                        LISTEN      51663/postgres
tcp        0      0 :::5434                     :::*                        LISTEN      35999/postgres
tcp        0      0 ::1:5435                    :::*                        LISTEN      48190/postgres
tcp        0      0 ::1:5436                    :::*                        LISTEN      57141/postgres

Enter port for this new instance to run on. 

Automating PostgreSQl Installation Using Shell Script


This post taught you how to install PostgreSQL Database on Linux using source code. That approach is good when number of servers is less. But what if it is to be installed on a large number of servers? 😉  Such as in a clustered environment? Here’s how to achieve it using a shell script. 🙂

Better enable password-less SSH from your master server/jumpbox to all destination servers to avoid typing passwords. Then copy the installer tarball from jumpbox to all servers using below command.

for hst in `cat /home/shashank/hosts.txt`; do scp $hst:/home/shashank; done

Now that installer has been copied to destination servers, its time to run the script from jumpbox to install it on all destination servers in one go 🙂 Issue below command to execute automation script & install PostgreSQL on all our target servers.

for hst in `cat /home/shashank/hosts.txt`; do ssh -t $hst 'bash -s' < /home/shashank/postgre_installer.sh; done

Below is the installer script.

#PostgreSQL Automation Installer Script version 1
#Author - Shashank Srivastava
#set -x
echo "Checking availability of pre-required packages"
sleep 1s
sudo rpm -q readline-devel.x86_64
if [ $? != 0 ]
then
echo "Readline package not found. Installing it...."
sudo yum install readline-devel.x86_64 -y
fi
sudo rpm -q zlib-devel-1.2.3-29.el6.x86_64
if [ $? != 0 ]
then
echo "zlib package not found. Installing it...."
sudo yum install zlib-devel.x86_64 -y
fi
echo "Unpacking PostgreSQL tarball..."
sleep 2s
sudo tar -zxf /home/shashank/postgresql-9.3.5.tar.gz
echo ""
echo "Tarball unpacked. Creating destination directory for Postgre...."
sudo mkdir -p /opt/PostgreSQL/9.3
cd /home/shashank/postgresql-9.3.5/
echo ""
echo "Destination directory created. Building installer from source coude...."
sleep 1s
sudo ./configure --prefix=/opt/PostgreSQL/9.3
sudo make
sleep 2s
echo ""
echo "Installer built. Installing it now...."
sudo make install
sleep 1s
id -a postgres
if [ $? != 0 ]
then
echo "postgres user not found. Adding it...."
sudo useradd postgres
else
echo "postgres user already exists. Proceeding to next step...."
fi
echo ""
echo "Configuring PostgreSQL...."
sudo mkdir -p /opt/PostgreSQL/9.3/data
sudo chown postgres /opt/PostgreSQL/9.3/data
echo ""
echo "Defining data directory for Postgre & starting it...."
sudo su - postgres -c "/opt/PostgreSQL/9.3/bin/initdb /opt/PostgreSQL/9.3/data/; sleep 2s; /opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data/ -l logfile start"
echo ""
echo "Checking version...."
echo "-------------------------------------------"
/opt/PostgreSQL/9.3/bin/psql --version
/opt/PostgreSQL/9.3/bin/postgres --version
echo ""
echo "Script executed successfully!"

How To Check PostgreSQL Version in RHEL


In the previous post, I wrote about how to install PostgreSQL. In this post, I will describe how we can check PostgreSQL version. Issue the below commands for client (psql) & server (postgres) versions.

[root@server shashank]# /opt/PostgreSQL/9.3/bin/psql --version
psql (PostgreSQL) 9.3.5
[root@server shashank]# /opt/PostgreSQL/9.3/bin/postgres --version
postgres (PostgreSQL) 9.3.5

Install PostgreSQL from Source on RHEL/CentOS


PostgreSQL, as we know is an Open Source RDBMS. Since I am not that much into Database, I don’t know how it compares with MySQL 😦 My focus on this post is on how to install it on a Linux server. Lets start with it 🙂

Download source-code. First step will be to download the source-code from its official site. Choose the version that you want to install. I chose 9.3.5 since this was my requirement. I downloaded the zipped tar file. Choose any one that suits you. Its around 21MB in size.

Unpack the tar file. Now we need to untar the downloaded file & configure it to prepare installation. Issue below command to untar it.

tar -xvf postgresql-9.3.5.tar.gz

Do a listing of the pwd (present working directory) & see if a new directory is created or not. Now move to this directory. By default, PostgreSQL is installed under /usr/local/pgsql directory. If you want to install it here, just run the configuration script, that is :

./configure

If you want it to be installed somewhere else, then issue :

./configure -prefix=/opt/PostgreSQL/9.3.

Prefix means the  directory where you want to install it. Choose accordingly. This step will take some time, So, be patient 😉

You may run into a few errors like : –

configure: error: readline library not found
configure: error: zlib library not found.

To fix it, install these packages.

readline-devel.x86_64 & zlib-devel.x86_64

Make sure to use packages for your architecture.

Build installer & run installer. Now that we have configured the source, we can now make the installer & install PostgreSQL. Just issue

make

It will produce the installer. Its better to execute this command & then go have a cup of coffee 😉 It will take some more time & after that issue command :

make install

It will install PostgreSQL on your Linux machine 🙂

make[1]: Leaving directory `/opt/postgresql-9.3.5/config'
PostgreSQL installation complete.

Add a user postgres. Issue

useradd postgres

& create its password.

passwd postgres

Create data directory. Make a directory inside PostgreSQL installation directory /usr/local/pgsql or anywhere it is installed.

mkdir -p /usr/local/pgsql

or

 mkdir -p /opt/PostgreSQL/9.3/data

Change ownership of this directory to postgres.

chown postgres /usr/local/pgsql

Initiate the database. Switch to postgres ID & issue this command

/opt/PostgreSQL/bin/initdb -D /opt/PostgreSQL/9.3/data

This will dump a few lines of text on screen.

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /opt/PostgreSQL/9.3/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /opt/PostgreSQL/9.3/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/data
or
/opt/PostgreSQL/9.3/bin/pg_ctl -D /opt/PostgreSQL/9.3/data -l logfile start

PostgreSQL is now installed on your Linux machine 🙂 Hope, this post was able to explain it in an easy manner 🙂 Looking forward to hearing from you 🙂

Troubleshooting basic PostgreSQL Problems In Linux (RHEL)


Encountered 2 PostgreSQL problems today. One was about the wrong permission to opt/PostgreSQL/9.3/data directory.

[root@server pg_log]# /etc/rc.d/init.d/postgresql-9.3 restart
Restarting PostgreSQL 9.3:
waiting for server to shut down.... done
server stopped
waiting for server to start........ stopped waiting
pg_ctl: could not start server
Examine the log output.
PostgreSQL 9.3 did not start in a timely fashion, please see /opt/PostgreSQL/9.3/data/pg_log/startup.log for details

Examining the log file pointed to below.

FATAL:  data directory "/opt/PostgreSQL/9.3/data" has group or world access

DETAIL:  Permissions should be u=rwx (0700)

So, the trick was to chmod 0700 to opt/PostgreSQL/9.3/data directory 😉 And it solved it 🙂

[root@server pg_log]# /etc/rc.d/init.d/postgresql-9.3 restart
Restarting PostgreSQL 9.3:
pg_ctl: PID file "/opt/PostgreSQL/9.3/data/postmaster.pid" does not exist
Is server running?
starting server anyway
waiting for server to start.... done
server started
PostgreSQL 9.3 restarted successfully

Other problem was with a normal user trying to restart PostgreSQL server. So I made below changes to the /etc/sudoers file. 

shashank ALL=(ALL) NOPASSWD: /etc/rc.d/init.d/postgresql-9.3 restart
 shashank ALL=(ALL) NOPASSWD: /etc/rc.d/init.d/postgresql-9.3 start
 shashank ALL=(ALL) NOPASSWD: /etc/rc.d/init.d/postgresql-9.3 stop

prefixing sudo to /etc/rc.d/init.d/postgresql-9.3 restart did the trick 😉