How To Install PostgreSQL 11 / 10 on CentOS 7

How To Install PostgreSQL 11 / 10 on CentOS 7

PostgreSQL is an object-relational database management system (ORDBMS) available for many platforms including Linux, FreeBSD, Solaris, Microsoft Windows, and Mac OS X. It is an open source DBMS system with an emphasis on extensibility and standards compliance.It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

Prerequisites

1. Server with root login (CentOS 7)

Setup PostgreSQL repository

PostgreSQL publishes rpm packages for all Linux platforms, their packages are fresher than those available in the OS repositories already exists on the server.

Now, we need to add the repository by installing PostgreSQL repo rpm.

PostgreSQL 11

yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

PostgreSQL 10

yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

Install PostgreSQL 11 / 10 on CentOS 7

Install PostgreSQL 11 / 10 using, yum command.

PostgreSQL 11

yum install -y postgresql11-server postgresql11

PostgreSQL 10

yum install -y postgresql10-server postgresql10

 

Initialize PostgreSQL Server

After completing the installation of PostgreSQL, need to initialize if we are using it for the first time.

PostgreSQL 11

/usr/pgsql-11/bin/postgresql-11-setup initdb

PostgreSQL 10

/usr/pgsql-10/bin/postgresql-10-setup initdb

PostgreSQL’s data is found at /var/lib/pgsql/<version>/data/ directory in default.

Controlling PostgreSQL Service

To start PostgreSQL service

PostgreSQL 11

systemctl start postgresql-11

PostgreSQL 10

systemctl start postgresql-10

To enable PostgreSQL on system startup

PostgreSQL 11

systemctl enable postgresql-11

PostgreSQL 10

systemctl enable postgresql-10

To check the status of PostgreSQL service

PostgreSQL 11

systemctl status postgresql-11

PostgreSQL 10

systemctl status postgresql-10

Sample output(PostgreSQL 11)

[root@webhostingchennai ~]# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-04-12 13:06:40 EEST; 2 days ago
     Docs: https://www.postgresql.org/docs/11/static/
  Process: 23726 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 23731 (postmaster)
   CGroup: /system.slice/postgresql-11.service
           ├─23731 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
           ├─23733 postgres: logger
           ├─23735 postgres: checkpointer
           ├─23736 postgres: background writer
           ├─23737 postgres: walwriter
           ├─23738 postgres: autovacuum launcher
           ├─23739 postgres: stats collector
           └─23740 postgres: logical replication launcher

Apr 15 13:41:01 server.webhostingchennai.co.in systemd[1]: Starting PostgreSQL 11 database server...
Apr 15 13:41:01 server.webhostingchennai.co.in postmaster[1408]: 2019-02-15 13:41:01.202 EST [1408] LOG:  listening on...432
Apr 15 13:41:01 server.webhostingchennai.co.in postmaster[1408]: 2019-02-15 13:41:01.202 EST [1408] LOG:  listening on...432
Apr 15 13:41:01 server.webhostingchennai.co.in postmaster[1408]: 2019-02-15 13:41:01.207 EST [1408] LOG:  listening on...32"
Apr 15 13:41:01 server.webhostingchennai.co.in postmaster[1408]: 2019-02-15 13:41:01.213 EST [1408] LOG:  listening on...32"
Apr 15 13:41:01 server.webhostingchennai.co.in postmaster[1408]: 2019-02-15 13:41:01.226 EST [1408] LOG:  redirecting ...ess
Apr 15 13:41:01 server.webhostingchennai.co.in postmaster[1408]: 2019-02-15 13:41:01.226 EST [1408] HINT:  Future log ...g".
Apr 15 13:41:01 server.webhostingchennai.co.in systemd[1]: Started PostgreSQL 11 database server.
[root@webhostingchennai ~]#

Configuring PostgreSQL Server

In default, PostgreSQL listens on localhost that means you can access the database from the server only and won’t be able to connect to the database remotely (outside network).

To enable the database service access for external network, edit the configuration file.

PostgreSQL 11

nano /var/lib/pgsql/11/data/postgresql.conf

PostgreSQL 10

nano /var/lib/pgsql/10/data/postgresql.conf

Set the listen_addresses to *.

listen_addresses = '*'

Restart PostgreSQL service.

PostgreSQL 11

systemctl restart postgresql-11

PostgreSQL 10

systemctl restart postgresql-10

Confirm the PostgreSQL listening on port 5432 using netstat command.

[root@webhostingchennai ~]# netstat -aon | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp6       0      0 :::5432                 :::*                    LISTEN      off (0.00/0/0)
unix  2      [ ACC ]     STREAM     LISTENING     348965   /var/run/postgresql/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     348967   /tmp/.s.PGSQL.5432
[root@webhostingchennai ~]#

Else you need to open port 5432 in your server by using the below commands.

firewall-cmd --zone=public --permanent --add-port=5432/tcp
firewall-cmd --reload

Access PostgreSQL server

To create a database, log in as postgres (Linux user). Login from the root user or reset the password of postgres user for login.

su -l postgres

Access the database using the psql command. It is an interactive front-end terminal for PostgreSQL database.

$ psql

Output:

-bash-4.2$ psql
psql (11.2)
Type "help" for help.

postgres=#

Set password for postgres (Database administrator) user.

postgres=# \password

Now, we have successfully installed the PostgreSQL 11 / 10 on CentOS 7.

To install pgAdmin 4 on centOS 7 : Click here

Kindly share your valuable comments to improve us.