Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

PostgreSQL Installation: Using PostgreSQL on Ubuntu

In this guide, you will learn how to install PostgreSQL on Ubuntu servers, basic administration steps and important settings for the production environment.
The goal is to create a secure and sustainable database infrastructure.

🧠 Technical Summary​

This guide explains the installation and use of PostgreSQL on Ubuntu.
The goal is to teach user (role) management, database creation and basic SQL operations.

Prerequisites​

  • Ubuntu 20.04 or above server
  • A user with authority sudo
  • Basic Linux terminal knowledge

1. PostgreSQL Installation​

First update the package list:

sudo apt update
  • This command refreshes the package information in the system.

Install PostgreSQL and additional tools:​


sudo apt install postgresql postgresql-contrib
  • This command installs the PostgreSQL server and auxiliary modules.

Verify installation:


psql --version
  • This command shows the PostgreSQL version.

Start the service:


sudo systemctl start postgresql
  • This process ensures the operation of the database service.

2. PostgreSQL Role Logic​

  1. PostgreSQL manages access control with the concept of role. Role; Acts as a user or user group.
  • With the installation, an administrator role named postgres is created.

Switch to Postgres user:


sudo -i -u postgres
  • This command allows you to perform operations with administrative privileges.
  1. Open the PostgreSQL shell:

psql
  • This command starts the interactive PostgreSQL terminal.

To exit:


\q

3. Creating a New Role (User)​

  1. Interactive role creation:

createuser --interactive
  • This command defines a new PostgreSQL user.
  1. Alternatively:

sudo -u postgres createuser --interactive
  • This method operates without changing the user.

4. Creating a New Database​

  1. To create a database for the user:

createdb proje_db
  • This command creates a database with the specified name.

Or:


sudo -u postgres createdb proje_db

5. Creating Roles and Databases with SQL (Optional)​

  1. Enter the PostgreSQL terminal:

sudo -u postgres psql
  • Create user with password:

CREATE ROLE uygulama_user WITH LOGIN PASSWORD 'guclu_sifre';
  • This command defines a user who can log in.
  • Create database and assign owner:

CREATE DATABASE uygulama_db OWNER uygulama_user;
  1. Authorize:

GRANT ALL PRIVILEGES ON DATABASE uygulama_db TO uygulama_user;
  • This action provides database access to the user.

6. Connecting to PostgreSQL with User​

  1. Create Linux user:

sudo adduser uygulama_user
  • This step is required for peer authentication.

Connect:


sudo -u uygulama_user psql
  • This command connects to PostgreSQL with the relevant user.

View connection information:


\conninfo

7. Creating and Deleting Tables​

Sample table creation:


CREATE TABLE envanter (
  id serial PRIMARY KEY,
  urun_tipi varchar(50),
  renk varchar(30),
  eklenme_tarihi date
);
  • This command defines a new table.

Listing tables:


\dt

8. Adding, Querying and Deleting Data​

  1. Adding data:

INSERT INTO envanter (urun_tipi, renk, eklenme_tarihi)
VALUES ('masa', 'beyaz', '2024-01-10');
  • This command adds a record to the table.

Listing data:


SELECT * FROM envanter;

Data deletion:


DELETE FROM envanter WHERE urun_tipi = 'masa';

9. Adding and Removing Columns​

  1. Adding new column:

ALTER TABLE envanter ADD son_bakim date;
  1. Delete column:

ALTER TABLE envanter DROP son_bakim;

10. Performance and Configuration Fundamentals​

Configuration files:

1. postgresql.conf: Performance settings

2. pg_hba.conf: Authentication rules

Reload after change:


sudo systemctl reload postgresql
  • This command applies the settings without interruption.

11. Backup and Restore​

  1. Taking a single database backup:

pg_dump -U postgres -F c -f uygulama_db.dump uygulama_db
  • This command creates a compressed backup.

Restore:


pg_restore -U postgres -d postgres --create uygulama_db.dump

Frequently Asked Questions​

1. Why is PostgreSQL preferred? It offers high data integrity and ACID compliance.

2. Is the default version sufficient for Ubuntu? It is sufficient for most applications.

3. Can sync-like operations be performed in the production environment? No, migration should be preferred.

4. Where should backups be stored? In a storage area other than the same server.

Result​

With this guide, you learned PostgreSQL installation and basic management on Ubuntu. You can establish a secure database infrastructure with correct configuration and regular backup.

You can immediately test this structure on the GenixNode infrastructure to use it with high availability and performance.