PostgreSQL Installation and Management on Ubuntu

💡 What You Will Learn in This Guide

In this comprehensive guide, you will learn how to install PostgreSQL, an open source relational database management system, on Ubuntu. After installation, we will cover topics such as role-based authentication, using psql, basic SQL commands, performance configuration, and backup strategies.

---

⚙️ Step 1 – PostgreSQL Installation

Requirements:

• Ubuntu 20.04 or above
• sudo authorized user
• Internet connection

Update the package directory:

sudo apt update

Install PostgreSQL and additional tools:

sudo apt install postgresql postgresql-contrib

Verify that the installation works:

sudo systemctl status postgresql

If you see “active (running)” output, the service is active.

---

👤 Step 2 – PostgreSQL Roles and Authentication

PostgreSQL handles user management through roles. By default, the superuser role named postgres is created with the installation.

Switch to this user:

sudo -i -u postgres

Open the console:

psql

For exit:

\q

PostgreSQL uses “peer authentication” by default on local connections; If there is a PostgreSQL role with the same name as your system username, you will be logged in directly.

---

🧩 Step 3 – Creating New Role and Database

Create an interactive role:

sudo -u postgres createuser --interactive

The command will ask for your username and super authority.

Create a new database:

sudo -u postgres createdb genix_user

Or create it manually with SQL:

CREATE ROLE genix_user WITH LOGIN PASSWORD 'guclu_parola';
CREATE DATABASE genixnode_db OWNER genix_user;
GRANT ALL PRIVILEGES ON DATABASE genixnode_db TO genix_user;

---

🗃️ Step 4 – Table Creation and SQL Operations

Connect to PostgreSQL:

sudo -u genix_user psql -d genixnode_db

Create sample table:

CREATE TABLE envanter (
  id SERIAL PRIMARY KEY,
  urun_adi VARCHAR(50),
  miktar INT,
  ekleme_tarihi DATE
);

Add data:

INSERT INTO envanter (urun_adi, miktar, ekleme_tarihi)
VALUES ('Laptop', 5, CURRENT_DATE);

View data:

SELECT * FROM envanter;

Update data:

UPDATE envanter SET miktar=10 WHERE urun_adi='Laptop';

Delete data:

DELETE FROM envanter WHERE urun_adi='Laptop';

---

⚙️ Step 5 – Performance and Configuration

You can improve system performance by editing the postgresql.conf file:

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

Parameter	Description	Suggestion
shared_buffers	Memory cache	25% of RAM
work_mem	Memory per query	16MB
maintenance_work_mem	For maintenance tasks	256MB

To apply changes:

sudo systemctl reload postgresql

Additionally:

• Run VACUUM ANALYZE after large tables.
• Use PgBouncer to reduce connection load.

---

🛡️ Step 6 – Backup and Restore

Single Database Backup:

pg_dump -U postgres -F c -f proje_db.dump proje_db

Backup Restore:

pg_restore -U postgres -d postgres --create --clean -j 4 proje_db.dump

Backup All Databases:

pg_dumpall -U postgres > tum_veriler.sql

Restore All:

psql -U postgres -f tum_veriler.sql

It is recommended to define regular cron job for backup operations.

---

❓ Frequently Asked Questions (FAQ)

1. In which Ubuntu versions is PostgreSQL supported?

Compatible with versions 20.04, 22.04, 24.04 and 25.04.

2. How do I back up my database?

You can create a compressed backup with the pg_dump command.

3. Why should PostgreSQL be preferred?

It is an enterprise-level solution with JSONB, ACID compliance, PostGIS, parallel query and open source support.

4. How can I improve performance?

Optimize memory settings, remove unnecessary indexes, run regular maintenance commands.

5. What does psql command not found error mean?

Your PATH variable may not contain the PostgreSQL bin directory. Use path /usr/bin/psql directly.

---

🏁 Result

You now have full control over PostgreSQL installation, user management and SQL operations on Ubuntu 🎉 Integrate the commands you have learned into your system to make your database safe, performant and organized.

💡 For a higher performance infrastructure, you can try these steps on GenixNode PostgreSQL Servers!