Linux PostgreSQL Guide - ryzendew/Linux-Tips-and-Tricks GitHub Wiki

Linux PostgreSQL Guide

Complete beginner-friendly guide to PostgreSQL on Linux, covering Arch Linux, CachyOS, and other distributions including installation, configuration, and database management.

Table of Contents

  1. PostgreSQL Installation
  2. PostgreSQL Configuration
  3. Database Management
  4. Troubleshooting

PostgreSQL Installation

Install PostgreSQL

Arch/CachyOS:

# Install PostgreSQL
sudo pacman -S postgresql

# Initialize database
sudo -u postgres initdb -D /var/lib/postgres/data

# Enable service
sudo systemctl enable --now postgresql

Debian/Ubuntu:

sudo apt install postgresql postgresql-contrib

Fedora:

sudo dnf install postgresql-server postgresql
sudo postgresql-setup --initdb

Verify Installation

Check PostgreSQL:

# Check service
systemctl status postgresql

# Connect to database
sudo -u postgres psql

PostgreSQL Configuration

Create User

Create database user:

-- In psql
CREATE USER myuser WITH PASSWORD 'mypassword';
CREATE DATABASE mydb OWNER myuser;
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Configure Access

Edit pg_hba.conf:

# Edit config
sudo vim /var/lib/postgres/data/pg_hba.conf

Database Management

Connect to Database

Connect:

# As postgres user
sudo -u postgres psql

# As specific user
psql -U myuser -d mydb

Basic SQL

SQL commands:

-- Create table
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100));

-- Insert data
INSERT INTO users (name) VALUES ('John');

-- Query
SELECT * FROM users;

Troubleshooting

PostgreSQL Not Starting

Check logs:

# Check service
systemctl status postgresql

# Check logs
journalctl -u postgresql

# Check data directory
ls -la /var/lib/postgres/data

Summary

This guide covered PostgreSQL installation, configuration, and database management for Arch Linux, CachyOS, and other distributions.

Next Steps

This guide covers Arch Linux, CachyOS, and other Linux distributions. For distribution-specific details, refer to your distribution's documentation.