DEV Community

Cover image for Installing PostgreSQL on Arch Linux | Practical Setup Guide
Ishan Jarwal
Ishan Jarwal

Posted on

Installing PostgreSQL on Arch Linux | Practical Setup Guide

#postgressql #archlinux #tutorial

Installing postgres on windows/mac is pretty simple, you just follow the steps on the installation wizard and you get it working. You will also find lots of video tutorials and blogs regarding installation in windows/mac. 

But when it comes to linux (specifically arch), things get interesting. Since linux gives you more control, setting up anything on it can seem tricky at first glance but as you go through, you understand each and every step and its purpose. 

Before getting into it, understand this : Postgres is not like MongoDB which you initialize once through the CLI by providing the --dbpath flag, it is a system service. Meaning it will run on the system 24x7 (if we configure it that way) and different system users will be able to use it according to the access they have been granted.

First things first : Installation

Always update the packages first :

sudo pacman -Syu
Enter fullscreen mode Exit fullscreen mode

Install PostgreSQL:

sudo pacman -S postgresql
Enter fullscreen mode Exit fullscreen mode

 

Lets initialize a Database Cluster

PostgreSQL requires a data directory initialization. This is the directory which holds the actual data (one-time step unlike MongoDB) :

sudo -u postgres initdb -D /var/lib/postgres/data
Enter fullscreen mode Exit fullscreen mode

Flags explained

-u postgres → run command as postgres system user initdb → creates system tables & internal structure -D → data directory location

 

Start & Enable Service

Start server:

sudo systemctl start postgresql
Enter fullscreen mode Exit fullscreen mode

Enable on every boot :

sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

Check status:

systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

 

Enter psql Shell

What is psql ? It is a command line interface to interact with out postgres service, databases, relations etc.

Lets enter the psql shell with the user “postgres”. 

On installing postgres, a new user named “postgres” is generated with default privileges.

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Why no password?

  • Default auth configuration is the “peer” auth. Peer auth uses our OS user to login
  • OS user identity is trusted

 

Create Role for a Project

It is always a good practice to create a separate role for every major project. This way you follow modular principles.

Inside psql:

CREATE ROLE myapp WITH
  LOGIN
  PASSWORD 'devpass123'
  CREATEDB;
Enter fullscreen mode Exit fullscreen mode

Meaning:

  • LOGIN → allows authentication
  • PASSWORD → required for apps/ORMs
  • CREATEDB → grant access to create databases

 

Create a Database 

CREATE DATABASE myapp_db OWNER myapp;
Enter fullscreen mode Exit fullscreen mode

Ownership = full control over that DB.

 

Some frequently used and useful psql Commands

List Databases

\l
Enter fullscreen mode Exit fullscreen mode

 

List roles (users)

\du
Enter fullscreen mode Exit fullscreen mode

 

List tables/relations

\d
Enter fullscreen mode Exit fullscreen mode

 

Show current connection details

\conninfo
Enter fullscreen mode Exit fullscreen mode

 

Show the active role

SELECT current_user;
Enter fullscreen mode Exit fullscreen mode

 

Connection URL Format

To connect to the database, you will need a url (Yes this is the one you add in your environment variables)

General structure : postgresql://USER:PASSWORD@HOST:PORT/DATABASE

Example : postgresql://myapp:devpass123@localhost:5432/myapp_db

This url can be used by:

  • Prisma
  • ORMs
  • Drivers
  • CLI tools

 

Enable Password Authentication (Critical for Prisma)

Edit config:

sudo nano /var/lib/postgres/data/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Change these lines according to your needs :

local   all   all                 md5
host    all   all   127.0.0.1/32  md5
host    all   all   ::1/128       md5
Enter fullscreen mode Exit fullscreen mode

Replace md5 with one of the following :

  • trust → no password
  • peer → OS identity
  • md5 / scram-sha-256 → password-based login

 

Restart PostgreSQL

To apply the config changes :

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

 

Testing our Connection 

psql "postgresql://myapp:devpass123@localhost:5432/myapp_db"
Enter fullscreen mode Exit fullscreen mode

OR

psql -U myapp -d myapp_db -h localhost -W
Enter fullscreen mode Exit fullscreen mode

Flags explained

-U → database role -d → database name -h → host (forces TCP) -W → force password prompt

 

Some Common Errors & Causes you might face

  • password authentication failed → wrong password
  • database does not exist → DB missing
  • role does not exist → user missing
  • could not connect to server → service stopped / wrong port

 

Again, remember

PostgreSQL:

  • Always-running service
  • Config-driven behavior
  • URL = connection info only

No manual server start like MongoDB.

 

Happy building 🚀

Top comments (0)