Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

PostgreSQL Installation: Quick Start on Ubuntu

In this guide, you will learn how to install PostgreSQL on Ubuntu servers in short and clear steps.
The goal is to get a working database live quickly.

🧠 Technical Summary​

This guide explains the installation and basic use of PostgreSQL on Ubuntu.
Installation, role management, database creation, and remote access are covered.

Prerequisites​

  • Ubuntu 20.04 or above
  • User with authority sudo
  • Basic terminal knowledge

1. PostgreSQL Installation​

Update package list:

sudo apt update
  • This command refreshes the package resources.
  1. Install PostgreSQL:

sudo apt install postgresql postgresql-contrib
  • This command installs the PostgreSQL server.
  1. Start the service:

sudo systemctl start postgresql
  • This command runs the database.
  1. Check the version:

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

2. Using PostgreSQL Roles​

  1. PostgreSQL handles user management with a role system.
  • The default authentication method is peer authentication.
  1. Enter the PostgreSQL console:

sudo -u postgres psql

  • This command logs in with administrator privileges.

  • To exit:


\q

3. Creating a New Role (User)​

  1. Create new user:

sudo -u postgres createuser --interactive
  • This command creates an interactive role.

4. Creating a New Database​

  1. Create a database for the user:

sudo -u postgres createdb proje_db
  • This command creates an empty database.

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

  1. Enter the PostgreSQL console:

sudo -u postgres psql
  • Create passworded user:

CREATE ROLE uygulama_user WITH LOGIN PASSWORD 'GucluSifre123' CREATEDB;
  • This command defines an entry authorized role.
  • Create database:

CREATE DATABASE uygulama_db OWNER uygulama_user;
  • This command determines the owner of the database.

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 provides automatic login.

Check connection info:


\conninfo

7. Opening Remote Access Securely​

  1. Edit the postgresql.conf file:

sudo nano /etc/postgresql/*/main/postgresql.conf
  • This file contains connection settings.

Update the following line:


listen_addresses = '*'

Edit pg_hba.conf:


sudo nano /etc/postgresql/*/main/pg_hba.conf
  • Add the following line:

host    uygulama_db   uygulama_user   203.0.113.10/32   scram-sha-256
  • This line defines IP based access.

Firewall and Service Renewal​


sudo ufw allow 5432/tcp
sudo systemctl restart postgresql
  • These commands activate the connection.

8. Backup and Restore​

  1. Back up the database:

pg_dump -U uygulama_user -F c uygulama_db > yedek.dump
  • This command takes a compressed backup.

Restore:


pg_restore -U uygulama_user -d postgres --create yedek.dump
  • This command restores the database.

Frequently Asked Questions​

1. What causes the Peer authentication failed error? It occurs if the Linux username and PostgreSQL role do not match.

2. What is the PostgreSQL default port? The default port is 5432.

3. Is remote access secure? Secure with IP restriction and password.

4. Where should backups be kept? They should be stored outside the same server.

Result​

With this guide, you quickly installed PostgreSQL on Ubuntu. You now have a ready database infrastructure for your applications.

You can run this structure with high performance on the GenixNode infrastructure.