Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

PostgreSQL Installation and Usage: Ubuntu 22.04 Database Guide

🚀 What Will You Learn in This Guide?​

This guide teaches you step by step how to install PostgreSQL (Postgres) database and basic administration operations on Ubuntu 22.04.
After installation, you will learn how to create a new user (role) and database, add tables, query, update and delete data.
As a result, you will have a reliable SQL infrastructure for your projects.

🧠 Technical Summary​

Subject: Installation of PostgreSQL relational database management system on Ubuntu 22.04 and basic SQL operations.
Problem it solves: Provides a reliable, scalable and open source database infrastructure for web applications.
Steps:

  1. Installing PostgreSQL
  2. Creating roles (users)
  3. Creating a database
  4. Creating a table
  5. Add, query, update and delete data

⚙️ Step 1 – PostgreSQL Installation​

Ubuntu's default APT repositories contain the stable version of PostgreSQL.

Update Package Index​
sudo apt update

➡️ Updates package indexes on the server.

Install PostgreSQL Packages​
sudo apt install postgresql postgresql-contrib -y

➡️ Installs PostgreSQL server and additional tools.

Check Service​
sudo systemctl status postgresql

➡️ Checks whether the service is active (running).

👤 Step 2 – Roles and Authentication​

PostgreSQL uses the concept of role in the user system. A role can function as both a user and a group. With the installation, a role named postgres and a Linux user with the same name are created.

Switch to Postgres User​
sudo -i -u postgres

➡️ postgres switches to Linux user.

Start PostgreSQL Client​
psql

➡️ Opens an interactive PostgreSQL shell.

To log out:​
\q
Alternative Connection Method​
sudo -u postgres psql

➡️ Connects to the PostgreSQL shell without leaving your normal account.

👥 Step 3 – Creating New Role (User) and Database​

In PostgreSQL, new users (roles) are generally created on an application basis.

Create New Role​
sudo -u postgres createuser --interactive

➡️ Creates a new PostgreSQL user.

The script will ask you for the role name:

Enter name of role to add: genixnode_admin
Shall the new role be a superuser? (y/n) y
Create New Database​

By default each role accesses the same database as its name:

sudo -u postgres createdb genixnode_admin

➡️ Creates a database for the user named genixnode_admin.

🔑 Step 4 – Connecting to the Database with New Role​

As per the peer authentication system, a Linux user with the same name must be created.

Add Linux User​
sudo adduser genixnode_admin

➡️ Creates a system user that matches the database role.

Connect to Database​
sudo -u genixnode_admin psql

➡️ Connects to the PostgreSQL database with the new user.

To see connection information:​
\conninfo

🗄️ Step 5 – Creating a Table​

Once you connect to your database, you can create a table. For example, a table listing parking equipment:

CREATE TABLE park_ekipman (
    ekip_no serial PRIMARY KEY,
    tip varchar (50) NOT NULL,
    renk varchar (25) NOT NULL,
    yerlesim varchar(25) CHECK (yerlesim IN ('kuzey','guney','bati','dogu')),
    kurulum_tarihi date
);

➡️ Creates a table with auto-incrementing primary key and constraints.

To list tables:

\dt

🧩 Step 6 – Add, Query, Update and Delete Data​

Adding Data​
INSERT INTO park_ekipman (tip, renk, yerlesim, kurulum_tarihi)
VALUES ('kaydirak', 'mavi', 'guney', '2023-09-01');
Data Query​
SELECT * FROM park_ekipman;
Data Update​
UPDATE park_ekipman SET renk = 'kirmizi' WHERE tip = 'salincak';

➡️ Updates the data of a specific row.

Deleting Data​
DELETE FROM park_ekipman WHERE tip = 'kaydirak';

➡️ Deletes the row that meets the specified condition.

🧱 Step 7 – Update the Table (Add/Delete Columns)​

Adding a New Column​
ALTER TABLE park_ekipman ADD son_bakim date;

➡️ Adds a last maintenance date column to the table.

Delete a Column​
ALTER TABLE park_ekipman DROP son_bakim;

➡️ Removes the column and its contents.

💬 Frequently Asked Questions (FAQ)​

  1. How to set PostgreSQL password?

If you want password-based access instead of peer authentication:

ALTER USER genixnode_admin WITH PASSWORD 'yeni_sifre';
  1. How do I enable remote connections?

Activate the listen_addresses='*' line in the /etc/postgresql/14/main/postgresql.conf file. Also edit client authentication in the pg_hba.conf file.

  1. Which port does PostgreSQL use?

The default port is 5432. Open this port on your firewall:

sudo ufw allow 5432/tcp
  1. How to take a database backup?
sudo -u postgres pg_dump veritabani_adi > yedek.sql
  1. How do I restart the PostgreSQL service?
sudo systemctl restart postgresql

🎯 Result​

In this guide, you learned how to install PostgreSQL on Ubuntu 22.04, create users and databases, and table and data operations. Now you can use a powerful, secure and open source database environment in your projects.