How to install PostgreSQL on CentOS 7.3 Server

Welcome to CentOS Tutorial. This tutorial is going to show you how to install PostgreSQL on CentOS 7.3 Server. As you know PostgreSQL is an open source object relational management system (RDBMS). It’s a popular database system together with MySQL/MariaDB. PostgreSQL is available in CentOS repository, so we can easily install this database server. We can install PostgreSQL on local system or remote cloud system. For example, if you have an Amazon EC2 machine, you can install PostgreSQL system there. When this post is written, PostgreSQL 9.2.23 is available through the CentOS repository. This tutorial is presented by Manjaro.site

Steps to install PostgreSQL on CentOS 7.3 

Step 1. Login to the CentOS 7.3

Connect and login to CentOS 7.3 via SSH. You can use Putty (Windows) or simply use Terminal console and use ssh command to connect. 

ssh root@192.168.1.9

Step 2. Install PostgreSQL

Now execute this command to install PostgreSQL from CentOS repository

yum install postgresql-server postgresql-contrib

Output example

[root@localhost ~]# yum install postgresql-server postgresql-contrib
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: sumberterbuka.beritagar.id
 * extras: centos.merahciptamedia.co.id
 * updates: centos.usonyx.net
Resolving Dependencies
--> Running transaction check
---> Package postgresql-contrib.x86_64 0:9.2.23-1.el7_4 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.23-1.el7_4 for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: postgresql(x86-64) = 9.2.23-1.el7_4 for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.22)(64bit) for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18)(64bit) for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11)(64bit) for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: libxslt.so.1()(64bit) for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-9.2.23-1.el7_4.x86_64
---> Package postgresql-server.x86_64 0:9.2.23-1.el7_4 will be installed
--> Running transaction check
---> Package libxslt.x86_64 0:1.1.28-5.el7 will be installed
---> Package postgresql.x86_64 0:9.2.23-1.el7_4 will be installed
---> Package postgresql-libs.x86_64 0:9.2.23-1.el7_4 will be installed
---> Package uuid.x86_64 0:1.6.2-26.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================================
 Package                                Arch                       Version                              Repository                   Size
==========================================================================================================================================
Installing:
 postgresql-contrib                     x86_64                     9.2.23-1.el7_4                       updates                     552 k
 postgresql-server                      x86_64                     9.2.23-1.el7_4                       updates                     3.8 M
Installing for dependencies:
 libxslt                                x86_64                     1.1.28-5.el7                         base                        242 k
 postgresql                             x86_64                     9.2.23-1.el7_4                       updates                     3.0 M
 postgresql-libs                        x86_64                     9.2.23-1.el7_4                       updates                     233 k
 uuid                                   x86_64                     1.6.2-26.el7                         base                         55 k

Transaction Summary
==========================================================================================================================================
Install  2 Packages (+4 Dependent packages)

Total download size: 7.9 M
Installed size: 35 M
Is this ok [y/d/N]:

Wait until the installation completed. 

Step 3. Configure PostgreSQL

Initialize PostgreSQL

postgresql-setup initdb

Start and Enable PostgreSQL

systemctl start postgresql
systemctl enable postgresql

Check status

[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2017-09-19 22:04:59 EDT; 1min 1s ago
 Main PID: 1942 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─1942 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─1943 postgres: logger process   
           ├─1945 postgres: checkpointer process   
           ├─1946 postgres: writer process   
           ├─1947 postgres: wal writer process   
           ├─1948 postgres: autovacuum launcher process   
           └─1949 postgres: stats collector process   

Sep 19 22:04:58 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
Sep 19 22:04:59 localhost.localdomain systemd[1]: Started PostgreSQL database server.

Step 4. Configure User Access

By default, PostgreSQL create a new user called postgres to access the database. We need to change the password for this user for security reason. 

Change user postgres Linux password

passwd postgres

Change user postgres database user. Please change the ‘12345678’ with your actual password. Please note that this is the user that have administrative access to PostgreSQL. 

su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD '12345678';"

Step 5. Access the PostgreSQL 

Now we will try to access the PostgreSQL server via Terminal using psql command. First we need to login using postgres user. 

su - postgres

Now enter PostgreSQL console with this command

psql postgres

Here you can do many things to manage your database such as create new database, etc. Thanks for reading this How to install PostgreSQL on CentOS 7.3. Cheers and Have a nice day

1 Trackback / Pingback

  1. How to Enable Remote Access on PostgreSQL Server

Leave a Reply

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.