How to Install and Use PostgreSQL Database on Linux

How to Install and Use PostgreSQL Database on Linux

Introduction
PostgreSQL, also known as Postgres, is a powerful, open-source relational database management system (RDBMS) known for its reliability, robustness, and flexibility. Whether you are a developer, database administrator, or simply someone interested in databases, installing PostgreSQL on Linux is straightforward. In this guide, we’ll walk you through the process of installing PostgreSQL, setting it up, and running basic commands to manage your databases.

What is PostgreSQL?
PostgreSQL is a versatile RDBMS that supports both SQL (relational) and JSON (non-relational) querying. It is ideal for complex and high-volume data applications, making it popular for web development, data analysis, and application backends. PostgreSQL is known for its advanced features such as foreign keys, joins, views, triggers, and stored procedures, which help in managing structured and semi-structured data efficiently.

Benefits of Using PostgreSQL on Linux

• Open Source: Free to use and constantly updated by the community.
• Cross-Platform: Works seamlessly across Linux, Windows, and macOS.
• Advanced Features: ACID compliance, full-text search, indexing, and more.
• Scalability: Handles high volumes of transactions efficiently.
• Security: Offers encryption and robust access controls.

Step 1: Installing PostgreSQL on Linux
To get started, let’s go over how to install PostgreSQL on your Linux system. We’ll cover installation on popular distributions like Ubuntu and CentOS.

Installing PostgreSQL on Ubuntu
Update the Package List: Before installing, update the package list to ensure you get the latest version of PostgreSQL.

sudo apt update

• Install PostgreSQL: You can install PostgreSQL and the associated packages with a single command.

sudo apt install postgresql postgresql-contrib

• Verify Installation: After installation, you can check the PostgreSQL service status to ensure it is running.

sudo systemctl status postgresql

If the service is active (running), PostgreSQL is installed successfully.

Installing PostgreSQL on CentOS/RHEL
Add PostgreSQL Yum Repository:

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable the Built-In PostgreSQL Module:

sudo dnf -qy module disable postgresql

Install PostgreSQL:

sudo dnf install -y postgresql15-server

Initialize Database:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

Start and Enable PostgreSQL:

sudo systemctl enable –now postgresql-15

Step 2: Configuring PostgreSQL
Once PostgreSQL is installed, you might want to perform some basic configurations.

Accessing the PostgreSQL Command Line
To access the PostgreSQL command line interface (psql), use the following command:

sudo -u postgres psql

Here, postgres is the default user created during installation. Once inside the psql shell, you can begin executing commands.

Creating a New User and Database
Create a New Role/User:

CREATE USER myuser WITH PASSWORD ‘mypassword’;

Create a New Database:

CREATE DATABASE mydatabase;

Grant Privileges:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

These commands allow you to set up a new user with access to a specific database, which is useful for managing permissions.

Configuring Remote Access (Optional)
By default, PostgreSQL only listens to localhost. To allow remote connections, you need to make a few changes.

Edit postgresql.conf:

sudo nano /etc/postgresql/15/main/postgresql.conf

Change:

listen_addresses = ‘localhost’

To:

listen_addresses = ‘*’

Edit pg_hba.conf:

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add the following line:

host all all 0.0.0.0/0 md5

Restart PostgreSQL:

sudo systemctl restart postgresql

Step 3: Using PostgreSQL Commands
Now that you have PostgreSQL installed and configured, let’s go over some basic commands to help you get started.

Creating a Table

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
position VARCHAR(50),
salary INT
);
Inserting Data

INSERT INTO employees (name, position, salary) VALUES
(‘John Doe’, ‘Software Engineer’, 70000),
(‘Jane Smith’, ‘Data Analyst’, 65000);
Querying Data

SELECT * FROM employees;

Updating Data

UPDATE employees SET salary = 75000 WHERE name = ‘John Doe’;

Deleting Data

DELETE FROM employees WHERE name = ‘Jane Smith’;

Exiting psql Shell
To exit the PostgreSQL command line, use:

\q

Step 4: Managing PostgreSQL Service
Knowing how to control the PostgreSQL service is essential.

Starting PostgreSQL

sudo systemctl start postgresql

Stopping PostgreSQL

sudo systemctl stop postgresql

Restarting PostgreSQL

sudo systemctl restart postgresql

Enabling PostgreSQL to Start on Boot

sudo systemctl enable postgresql