Install Postgresql from source on CentOS 7

In this article, let us review how to install postgreSQL 9.2.24 database on Centos 7 from source code.


Step 1: Download postgreSQL source code

Download From the postgreSQL download site.

# wget https://ftp.postgresql.org/pub/source/v9.2.24/postgresql-9.2.24.tar.gz

Step 2: Install postgreSQL

# tar xvfz postgresql-9.2.24.tar.gz # cd postgresql-9.2.24 # ./configure # make # make install

PostgreSQL Installation Issue:

You may encounter the following error message while performing ./configure during postgreSQL installation.

# ./configure checking for -lreadline... no checking for -ledit... no configure: error: readline library not found If you have readline already installed, see config.log for details on the failure.  It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support.

PostgreSQL Installation Solution:

Install the readline-devel and libtermcap-devel to solve the above issue.

# yum install libtermcap-devel readline-devel

Step 3: Verify the postgreSQL directory structure

After the installation, make sure bin, doc, include, lib, man and share directories are created under the default /usr/local/pgsql directory as shown below.

# ls -l /usr/local/pgsql/ total 14 drwxr-xr-x 2 root root 4096 Jan 22 10:24 bin lrwxrwxrwx 1 root root 4096 Jan 22 14:17 data drwxr-xr-x 6 root root 4096 Jan 22 10:24 include drwxr-xr-x 3 root root 4096 Jan 22 10:24 lib drwxr-xr-x 6 root root 4096 Jan 22 13:28 share

Step 4: Create postgreSQL user account

# adduser postgres # passwd postgres Changing password for user postgres. New UNIX password: Retype new UNIX password: passwd: all authentication tokens updated successfully.

Step 5: Create postgreSQL data directory

Create the postgres data directory and make postgres user as the owner.

# mkdir /usr/local/pgsql/data # chown postgres:postgres /usr/local/pgsql/data # ls -ld /usr/local/pgsql/data drwxr-xr-x 2 postgres postgres 19 Jan 22 14:17 /home/postgres/data

Step 6: Initialize postgreSQL data directory

Before you can start creating any postgreSQL database, the empty data directory created in the above step should be initialized using the initdb command as shown below.

# su - postgres # /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

Step 7: Validate the postgreSQL data directory

Make sure all postgres DB configuration files (For example, postgresql.conf) are created under the data directory as shown below.

$ ls -l /usr/local/pgsql/data total 60 drwx------ 7 postgres postgres    67 Jan 22 14:23 base drwx------ 2 postgres postgres  4096 Jan 22 14:25 global drwx------ 2 postgres postgres    18 Jan 22 14:23 pg_clog -rw------- 1 postgres postgres  4302 Jan 22 14:23 pg_hba.conf -rw------- 1 postgres postgres  4476 Jan 22 14:23 pg_hba.conf-default -rw------- 1 postgres postgres  1636 Jan 22 14:23 pg_ident.conf drwx------ 4 postgres postgres    36 Jan 22 14:23 pg_multixact drwx------ 2 postgres postgres    18 Jan 22 14:24 pg_notify drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_serial drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_snapshots drwx------ 2 postgres postgres    25 Jan 22 16:09 pg_stat_tmp drwx------ 2 postgres postgres    18 Jan 22 14:23 pg_subtrans drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_tblspc drwx------ 2 postgres postgres     6 Jan 22 14:23 pg_twophase -rw------- 1 postgres postgres     4 Jan 22 14:23 PG_VERSION drwx------ 3 postgres postgres    60 Jan 22 14:23 pg_xlog -rw------- 1 postgres postgres 19691 Jan 22 14:23 postgresql.conf -rw------- 1 postgres postgres    59 Jan 22 14:24 postmaster.opts -rw------- 1 postgres postgres    70 Jan 22 14:24 postmaster.pid -rw------- 1 postgres postgres   940 Jan 22 14:23 startup.log

Step 8: Start postgreSQL database

Use the postgres postmaster command to start the postgreSQL server in the background as shown below.

$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 & [1] 2222 $ cat logfile LOG:  database system was shut down at 2020-01-22 11:10:10 WIB LOG:  database system is ready to accept connections LOG:  autovacuum launcher started

Step 9: Create postgreSQL DB and test the installation

Create a test database and connect to it to make sure the installation was successful as shown below

$ /usr/local/pgsql/bin/createdb test $ /usr/local/pgsql/bin/psql test Welcome to psql 9.2.24, the PostgreSQL interactive terminal. Type:  \copyright for distribution terms        \h for help with SQL commands        \? for help with psql commands        \g or terminate with semicolon to execute query        \q to quit test=#

Step 10:Enable remote access to PostgreSQL server

By default, the PostgreSQL server listens only on the local interface 127.0.0.1.
To enable remote access to your PostgreSQL server open the configuration file postgresql.conf and add listen_addresses = '*'
in the CONNECTIONS AND AUTHENTICATION section.

$ sudo vim /usr/local/pgsql/data/postgresql.conf

Edit like below

#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*'     # what IP address(es) to listen on; port = 5432

$ sudo vim /usr/local/pgsql/data/pg_hba.conf

Edit like below

# TYPE  DATABASE        USER            ADDRESS                 METHOD # "local" is for Unix domain socket connections only local   all             all                                     md5 # IPv4 local connections: host    all             all             127.0.0.1/32            ident # IPv6 local connections: host    all             all             ::1/128                 ident # Allow replication connections from localhost, by a user with the # replication privilege. local   replication     all                                     peer host    replication     all             127.0.0.1/32            ident host    replication     all             ::1/128                 ident host    all             all             0.0.0.0/0               md5

save the file and restart the PostgreSQL service with:

$ sudo systemctl restart postgresql

Verify the changes with the ss utility or netstat

$ ss -nlt | grep 5432 LISTEN   0         128                 0.0.0.0:5432             0.0.0.0:* LISTEN   0         128                    [::]:5432                [::]:*

or you can use netstat

# netstat -pltnu | grep 5432 tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      5360/postmaster

As you can see from the output above the PostgreSQL server is listening on all interfaces (0.0.0.0).

and the last open port 5432

firewall-cmd --zone=public --add-port=5422/tcp firewall-cmd --zone=public --add-port=5422/tcp --permanent systemctl restart firewalld