How To Set up PostgreSQL on Centos 7

In this article, we want to teach you How To Set up PostgreSQL on Centos 7.

PostgreSQL is an enterprise-class open source database management system.

It supports both SQL and JSON for relational and non-relational queries for extensibility and SQL compliance.

PostgreSQL supports advanced data types and performance optimization features, which are only available in expensive commercial databases, like Oracle and SQL Server.

Also, it is known as Postgres.

what you read in this post?

Set up PostgreSQL on Centos 7

Set up PostgreSQL on Centos 7

Before you start to install PostgreSQL on Centos 7, you need to log in to your server as a non-root user with sudo privileges. To do this, you can follow our article the Initial Server Setup with Centos 7.

Now you can follow the steps below to install PostgreSQL on Centos 7.

Install PostgreSQL on Centos 7

Here you will install PostgreSQL from the default Centos repository. First, update your local package index with the command below:

sudo yum update

Then, you can install PostgreSQL and its dependencies on Centos 7 with the command below:

sudo yum install postgresql-server postgresql-contrib

When your installation is completed, you can initialize your database with the following command:

sudo postgresql-setup initdb

Now you can use the following commands to start and enable PostgreSQL on boot:

sudo systemctl start postgresql

sudo systemctl enable postgresql

Verify that your PostgreSQL is active and running on your Centos 7 with the command below:

sudo systemctl status postgresql

In your output you will see:

Output
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-12-21 03:26:09 EST; 57s ago
Main PID: 3430 (postgres)
CGroup: /system.slice/postgresql.service
├─3430 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
├─3431 postgres: logger process
├─3433 postgres: checkpointer process
├─3434 postgres: writer process
├─3435 postgres: wal writer process
├─3436 postgres: autovacuum launcher process
└─3437 postgres: stats collector process

At this point, you have PostgreSQL active and running on your Centos 7. Let’s see some basic usage of PostgreSQL.

How to Use PostgreSQL on Centos 7

During the PostgreSQL installation, a user named Postgres will be created. You can change the Postgres user password with the command below:

sudo passwd postgres

You will be asked to enter the password and in your output, you will see:

Output
passwd: all authentication tokens updated successfully.

Now you can switch to the PostgreSQL shell on Centos 7 with the following command:

su - postgres

Note: If you receive an error, you can set a valid shell on the user with the following command:

su --shell /bin/bash postgres

Then, you can run the same command:

su - postgres

You can change the PostgreSQL Postgres user password with the command below:

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

Remember to change the phrase ‘NewPassword’ with your own.

Here you can switch to the PostgreSQL user shell on Centos 7 with the following command:

psql postgres

In the Postgres shell, you can type \h to list the available commands:

postgres=# \h
Output
Available help:
ABORT DECLARE
ALTER AGGREGATE DELETE
ALTER COLLATION DISCARD
ALTER CONVERSION DO
ALTER DATABASE DROP AGGREGATE
ALTER DEFAULT PRIVILEGES DROP CAST
...

To exit the environment you can type \q.

Also, you can create new databases. With the following command, we create a database named testDB with the Postgres Linux user:

createdb testDB

Next, you can create a new role with the Postgres Linux user on Centos 7 by using the following command, here we named it samplerole:

createuser -P samplerole

You will be asked to set a password for your new role.

Now you can connect to the new database that you have created with the following command:

psql testDB

You can use \l or \list commands to show all the databases. To know the current database you’re using, you can use \c. In case you want more information about connections such as the socket, port, etc. then you can use \conninfo.

To delete a database you can use the following command: