Denham Coder

The idea of this post is to document my journey to building PostgreSQL on a Red Hat Linux server in Microsoft Azure. The post assumes the reader has experience using Microsoft Azure so those steps will not be detailed. A lot of the information came from this Microsoft link.

Creating a Linux Virtual Machine

From Azure I selected Create a resource and picked the Red Hat offering below.

Following the on-screen creation options and pick a reasonable server size based on your subscription. I picked a B1ms sized VM which is perfect for my testing needs.

In addition I created a username and password knowing I would configure SSH after the build completed. Lastly I configured a DNS name since the IP address can change during restarts.

Connecting using Putty

I am running windows 10 and therefore my telnet tool of choice is Putty.

I carry out a quick connection test using the DNS name I configured in Azure and my username and password. Since I don’t want to be entering my password every time, I configured SSH. Rather than describe the steps here, you can refer to a previous blog I created. Accessing a Azure Linux VM using SSH on Windows

Installing and Building PostgreSQL

Using putty connect to your VM.
The following steps need to be run as root. Switch to root as follows.

sudo su -
whoami

The prompt will show you running as root however; you can also run whoami.
At the time of writing this blog I am installing PostgreSQL 11.5 and the following OS dependencies were added. This will produce a lot of output; just ensure there are no errors.

yum install readline-devel gcc make zlib-devel openssl openssl-devel libxml2-devel pam-devel pam libxslt-devel tcl-devel python-devel -y

Next we download the PostgreSQL 11.5 source code and extract the tar file.

cd /root
wget https://ftp.postgresql.org/pub/source/v11.5/postgresql-11.5.tar.bz2 -P /root/
tar jxvf  postgresql-11.5.tar.bz2

Details on building the source code can be found in the postgresql-11.5\INSTALL file. The steps below are the steps I used.
Now we need to perform a build configuration to prepare our installation. It will carry out various checks on our target OS. Here we also set our target directory to /opt/postgresql-11.5.

cd postgresql-11.5
./configure --prefix=/opt/postgresql-11.5

Once the checks are complete we can build the Code. We have choices on what to include and not include. I decided to include everything.

gmake install-world

Configuring PostgreSQL

At this point we have PostgreSQL installed in /opt/postgresql-11.5. Now we need to carry out the following configuration steps in preparation to starting the PostgreSQL cluster.

A good practise is to create a symbolic link called pgsql. That way we can make reference to it in other scripts and configurations and it will allow us to switch to a different version just be changing the symbolic link. We also need a directory pgsql_data which is away from the PostgreSQL code and will hold our database files, configurations and logs. Additional locations for database files can be added using the PostgreSQL tablespace feature.

ln -s /opt/postgresql-11.5 /opt/pgsql
mkdir -p /opt/pgsql_data

We now create a non-root user called postgres which will own the pgsql_data folder and the running PostgreSQL processes.

useradd postgres
passwd postgres
chown -R postgres.postgres /opt/pgsql_data

Next we switch to the new postgres user and setup a bash profile using the following:

sudo su - postgres

cat >> ~/.bash_profile <<EOF
export PGPORT=1999
export PGDATA=/opt/pgsql_data
export LANG=en_US.utf8
export PGHOME=/opt/pgsql
export PATH=\$PATH:\$PGHOME/bin
export MANPATH=\$MANPATH:\$PGHOME/share/man
export DATA=`date +"%Y%m%d%H%M"`
export PGUSER=postgres
alias rm='rm -i'
alias ll='ls -lh'
EOF

To pick up the profile changes either exit and then switch user again or run source .bash_profile.

Try running psql –V to verify the paths are correct and the binary is found. It will also confirm the PostgreSQL version. Any issues then run env and check the PATH variable.

Initialising the Database Cluster

We have reached the point where we can now create the PostgreSQL database cluster. It is important we run this using the postgres account.

Creating a database cluster consists of:

• Creating the directories where the database data will reside.
• Generating the shared catalog tables that belong to the whole cluster rather than to any particular database.
• Creating the template1 and postgres databases.

sudo su - postgres
initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W

We specify the target directory using our environment variable, UTF8 database encoding and a standard US English locale. We pass in postgres as the PostgreSQL superuser and the -W switch will prompt us to supply a password for it. By default the installation allows us to connect locally without specifying the password. This is obviously not something you would allow outside of a demo installation.

Once this completes the prompt will suggest starting the PostgreSQL cluster. Before we do that let’s we need to do a couple more steps.

Configuring the Start and Stop Script

We need to configure the script that allows us to start and stop the PostgreSQL cluster. The PostgreSQL build provides a template we can use. As root, let’s copy this template to its target location.

sudo su -
cp /root/postgresql-11.5/contrib/start-scripts/linux /etc/init.d/postgresql
chmod +x /etc/init.d/postgresql

Now edit /etc/init.d/postgresql and change the two variables as shown.

We are now ready to start the PostgreSQL cluster using this script. As well as starting the cluster with this script, we can stop it, check its status, and tell it to restart or reload its config files. If you run it from root you will not be prompted for the postgres user password every time.

/etc/init.d/postgresql start
/etc/init.d/postgresql stop
/etc/init.d/postgresql restart
/etc/init.d/postgresql reload
/etc/init.d/postgresql status

You can view start-up messages in the log file /opt/pgsql_data/serverlog.

Connect to PostgreSQL using PSQL

We are now ready to test connecting to PostgreSQL. Just run from postgres user psql with no parameters.

sudo su - postgres
psql

If all goes well we should set the postgres prompt. Enter \conninfo and hit enter should give us the output below.

So at this point we have everything running except we can only connect to PostgreSQL if we have telnetted to the server locally. In the next section I will show you how to configure the server to allow remote connections.

Configuring Linux and PostgreSQL to Allow Remote Connections

Linux Firewall
The first thing we need to open up is the Linux firewall. Our PostgreSQL process is listening on TCP port 1999. We can control and check whether the firewall is disabled, enabled or running using the following commands.

systemctl status firewalld
systemctl enable firewalld
systemctl disable firewalld
service firewalld stop
service firewalld start
firewall-cmd --state

In order to allow a client IP address through to PostgreSQL we can add a firewall rule just for our client IP address. Just type whatsmyip in google and it will give your external IP address. Change n.n.n.n to your client IP address.

sudo firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="n.n.n.n/32" port protocol="tcp" port="1999" accept'
sudo firewall-cmd --reload 

PostgreSQL Config File
By default PostgreSQL listens on localhost only. We can see that by running the netstat command below.

Edit the file /opt/pgsql_data/postgresql.conf and change the following setting.

listen_addresses = '*'

Restart PostgreSQL using /etc/init.d/PostgreSQL restart and then rerun the netstat command.

We can now see the PostgreSQL process listening for any IPv4 and IPv6 addresses.

PostgreSQL HBA Config File
The last file to edit is the client authentication configuration file. I am not going to explain the full contents of this file as that is a post all on its own. In this file we can configure what IP addresses are allowed to connect and authentication method to use. For the purposes of testing I am going to allow my client IP only with a level called TRUST. This means I can connect without having to specify a password.

Edit the file /opt/pgsql_data/pg_hba.conf and add the line below under the heading # IPv4 local connections:. Change n.n.n.n to your client IP address.

Host    all    all    n.n.n.n/32    trust

Restart PostgreSQL using /etc/init.d/PostgreSQL restart

PostgreSQL will now be able to accept connections from your client IP address. Before we test it continue below and install pgAdmin.

Installing pgAdmin on your Client

The command line tool psql is fine for administering PostgreSQL however; UI’s provide a much better experience for the user. There is a free open source product called pgAdmin which is an excellent browser based UI for PostgreSQL. At the time of writing pgAdmin 4 is released and is available in the download section on the website. In addition there is also a pgAgent which is a job scheduler that can be downloaded separately and managed through pgAdmin.

Go ahead and select the version for your OS and install it. I am using the version for Windows 10.

To launch pgAdmin you run C:\Program Files (x86)\pgAdmin 4\v4\runtime\pgAdmin4.exe. This executes a background process that runs continuously in the system tray. From here you can right click the blue elephant icon and select New pgAdmin 4 Window. This will launch your default browser and open the UI. Acknowledge any prompts.

To add your server, right click Servers in the top left hand corner and select Create -> Server. This will open the Create Server window.

On the General tab enter a name for your connection.

On the Connection tab enter your Linux host name and change the port to 1999.

On the SSL tab change the SSL Mode to Disable.

Click the Save button and if all is well your connection will succeed.

I hope this was useful and I should be adding more PostgreSQL posts in the future.