You are currently viewing How to install PostgreSQL on Centos

How to install PostgreSQL on Centos

PostgreSQL also known as Postgres is an open-source object-relational database used in various applications. Its strong flexibility, scalability, and robustness make it popular among other database engines. In this article, we cover how to install PostgreSQL on a centos machine. Note that these steps apply to all other Linux distributions.

How to install PostgreSQL


  1. A Linux server running on Centos (preferably version 7 and higher)
  2. Server access with the root privileges

Follow the following steps to install PostgreSQL on centos

  1. System update:- Access the server on SSH and update the system package repository
sudo yum update
  1. Install PostgreSQL
sudo yum install postgresql-server postgresql-contrib
  1. Initialize the database:- Once PostgreSQL is installed, start it, and enable it to start on boot/reboot.
sudo postgresql-setup initdb      #initialize database
sudo systemctl start postgresql   #start database
sudo systemctl enable postgresql  #set database to start on boot or reboot
  1. Set up a password for PostgreSQL default User

Note that the default user is used to initiate the creation of other database users and other databases. To set a password for the default database user, run the following:

sudo -u postgres psql      #this opens the postgresql shell. Run the following

ALTER USER postgres WITH PASSWORD 'set-password';    #set-password will be the default user's password.

\q      #exits the database shell to ssh shell
  1. Configure the database access Control

By default, PostgreSQL is set to allow connections from localhost only. This default setting can be modified to allow remote connection. Open the pg_hba.conf file as below:

sudo vi /var/lib/pgsql/data/pg_hba.conf

Add the following line of code or modify the existing one.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all                  md5

Besides the above, also confirm and update the settings on postgresql.conf. The file has some settings to block remote connection. Run the following to open the file.

sudo vi /var/lib/pgsql/data/postgresql.conf  

#locate the line with listen_addresses. By defult this line is set to the following

listen_addresses = 'localhost'

#To enable remote connection, udpate the line to

listen_addresses = '*'

#Note for local connections only; and you encounter some about being unable to connect to server; update localhost to the localhost IP. see below

listen_addresses = '' 

How to create a database user on Postgres

Once the database engine is installed and started, we can now create a new database user. On SSH shell, run the following

sudo -u postgres psql -c "CREATE USER user-name WITH PASSWORD 'user-password';"
#use your prefered values for user-name and user-password

How to create a database on Postgres

To create a database, run the following command on SSH shell. Here, we create a database by the name database-name and grant the user created in the steps above all the privileges on this database.

sudo -u postgres psql -c "CREATE DATABASE database-name;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE database-name TO user-name;"

How to connect to Database

After creating the database and the database user, it’s now time to access the database. To connect to the database locally from the server; run

psql -h -U user-name -d database-name   #run this from the ssh commandline

To connect to the database remotely, run the following. Ensure that you have installed Postgres engine on your local machine.

psql -h server-IP -U user-name -d database-name

PostgreSQL commands you must know

psql -h <hostname> -U <username> -d <database_name>   #to conect to PostgreSQL server

\l    #Lists all available database

\c <database_name>   #connects to the specified database

\dt   #List the current database tables

\d <table_name>     #show the tables structure

SELECT * FROM table_name;   #select data from table

\q   #To exit from postgresql shell

CREATE TABLE table_name (column1 datatype1 constraints,column2 datatype2 constraints);   #creates a table

INSERT INTO table_name (column1, column2) VALUES (value1, value2); #insert data into the database

Make a donation to support us

Web hosting and email hosting packages

For web development services, SEO services, Digital marketing strategies, website set up services, web hosting and domain registration; contact Dynamic Technologies.

Related articles: