Biblioteca de cunoștințe

PostgreSQL is an advanced, standards-compliant open-source relational database known for reliability, strong JSON support, and suitability for complex applications built with Django, Rails, Laravel, or Node.js.

Prerequisites

• Ubuntu 22.04/24.04 or Debian 11/12 VPS
• Root or sudo access

Step 1 — Update the System

sudo apt update
sudo apt upgrade -y

Step 2 — Install PostgreSQL

sudo apt install postgresql postgresql-contrib -y

Step 3 — Enable and Start PostgreSQL

sudo systemctl enable postgresql
sudo systemctl start postgresql
sudo systemctl status postgresql

Step 4 — Log In as the postgres System User

sudo -i -u postgres
psql

Step 5 — Set a Password for the postgres Role

ALTER USER postgres WITH PASSWORD 'CHANGE_ME_STRONG_PASSWORD';

Step 6 — Create a Database and Application User

CREATE DATABASE myapp;
CREATE USER appuser WITH ENCRYPTED PASSWORD 'CHANGE_ME_STRONG_PASSWORD';
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
\q
exit

Step 7 — Verify the Version

psql --version

Connecting Remotely (Only If Required)

By default, PostgreSQL only accepts local connections. To allow remote access, edit both:

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

listen_addresses = '*'

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

host    myapp    appuser    YOUR_TRUSTED_IP/32    scram-sha-256

sudo systemctl restart postgresql
sudo ufw allow from YOUR_TRUSTED_IP to any port 5432

Never open PostgreSQL to the entire internet — always restrict to specific trusted IPs.

Common Errors

"psql: FATAL: password authentication failed" — verify the password and that pg_hba.conf uses an appropriate authentication method (scram-sha-256 recommended).

PostgreSQL won't start:

sudo journalctl -u postgresql

Best Practices

• Never use the postgres superuser for application connections
• Restrict remote access to specific IPs, never 0.0.0.0/0
• Enable regular backups — see How to Back Up and Restore PostgreSQL Databases

FAQ

Why would I choose PostgreSQL over MySQL/MariaDB?
PostgreSQL is often preferred for complex queries, strong JSON/JSONB support, and strict standards compliance; MySQL/MariaDB are frequently chosen for their simplicity and widespread CMS/framework compatibility (like WordPress).

Related Articles

• How to Back Up and Restore PostgreSQL Databases
• How to Install and Secure MySQL 8 on Ubuntu & Debian
• How to Run MySQL, PostgreSQL & Redis in Docker Containers