Setup PostgreSQL on Linux (Arch-based Systems)

This guide provides a step-by-step process to install, configure, and manage PostgreSQL on an Arch-based Linux system using systemctl. It includes best practices, security recommendations, and additional configurations for a robust setup.

---

1. Install PostgreSQL

Install the Latest Version

sudo pacman -S postgresql

• Note: Ensure your package manager is up-to-date with sudo pacman -Syu before installation to avoid dependency issues.
• Suggestion: If you need client tools only (e.g., psql), install postgresql-libs instead.

Install a Specific Version (Optional)

If you need a specific PostgreSQL version:

1. Check available versions:

   pacman -Ss postgresql

2. Install a specific version (if available in the repository):

   sudo pacman -S postgresql-<version>

3. For versions not in the repository, build from source:
   • Download the source from PostgreSQL's official site.
   • Extract and compile:

     tar -xvf postgresql-<version>.tar.gz
     cd postgresql-<version>
     ./configure
     make
     sudo make install

   • Note: Building from source requires development tools (base-devel) and dependencies like libxml2, openssl, etc.

---

2. Verify Installation

Check PostgreSQL Version

postgres --version

• Expected Output: postgres (PostgreSQL) <version>.
• Troubleshooting: If the command is not found, ensure /usr/bin is in your PATH or that PostgreSQL is installed correctly.

---

3. Initialize PostgreSQL

Confirm PostgreSQL is Not Running

sudo systemctl status postgresql

• Expected Output: The service should be inactive or dead.
• Troubleshooting: If it’s running, stop it with:

  sudo systemctl stop postgresql

Switch to the postgres User

PostgreSQL uses a dedicated postgres user for administrative tasks:

sudo su - postgres

• Note: Always use the postgres user for admin tasks to avoid permission issues.
• Alternative: Use sudo -i -u postgres for an interactive login.

Initialize the Data Directory

The default data directory is /var/lib/postgres/data. Initialize it with:

initdb --locale en_US.UTF-8 -D /var/lib/postgres/data

• Options Explained:
  • --locale en_US.UTF-8: Sets the database encoding to UTF-8, suitable for most applications.
  • -D /var/lib/postgres/data: Specifies the data directory.
• Suggestion: Use a custom data directory for better organization (e.g., /var/lib/postgresql/<version>/data).
• Troubleshooting: If you encounter a "data directory already exists" error, ensure the directory is empty or specify a new one.

Logout of postgres User

exit

---

4. Start and Verify PostgreSQL Service

Start PostgreSQL

sudo systemctl start postgresql

Enable PostgreSQL to Start on Boot (Optional)

To ensure PostgreSQL starts automatically on system boot:

sudo systemctl enable postgresql

Confirm PostgreSQL is Running

sudo systemctl status postgresql

• Expected Output: The service should be active (running) with a recent timestamp.
• Troubleshooting: If the service fails to start, check logs with:

  journalctl -u postgresql

---

5. Create a User and Database

Log into the postgres User

sudo su - postgres

Create a New User

Create a PostgreSQL user that matches your Linux username for convenience:

createuser --interactive

• Prompts:
  • Enter name of role to add: <your_linux_username> (e.g., john).
  • Shall the new role be a superuser?: y (for simplicity, but see security note below).
• Security Note: Avoid making the user a superuser in production. Instead, grant specific privileges:

  createuser --interactive --pwprompt

  • This prompts for a password and creates a non-superuser role.

Create a Database

Create a database owned by the new user:

createdb <your_linux_username>

• Suggestion: Name the database meaningfully (e.g., myapp_db) instead of using the username.

  createdb myapp_db -O <your_linux_username>

Logout of postgres User

exit

---

6. Configure Authentication

Set a Password for a User (Optional)

If your application or security policy requires a password:

psql -c "ALTER USER <your_linux_username> WITH PASSWORD 'newpassword';"

• Note: Replace newpassword with a secure password.
• Suggestion: Store passwords securely (e.g., in a password manager or .pgpass file).

Configure pg_hba.conf for Authentication

Edit the pg_hba.conf file to control authentication methods:

sudo nano /var/lib/postgres/data/pg_hba.conf

• Example Configuration (for PostgreSQL 13 or later):

  # TYPE  DATABASE  USER  ADDRESS         METHOD
  local   all       all                   scram-sha-256
  host    all       all   127.0.0.1/32    scram-sha-256
  host    all       all   ::1/128         scram-sha-256
  

• Explanation:
  • local: Allows local connections (e.g., via psql).
  • host: Allows TCP/IP connections (e.g., from localhost).
  • scram-sha-256: A secure authentication method (preferred over md5).
• Suggestion: Use scram-sha-256 for enhanced security. Avoid trust in production.
• Note: The path to pg_hba.conf may vary (e.g., /etc/postgresql/<version>/main/pg_hba.conf on some systems).

Reload Configuration

Apply changes without restarting PostgreSQL:

sudo systemctl reload postgresql

---

7. Test the Setup

Log into PostgreSQL

Test the new user and database:

psql -U <your_linux_username> -d <your_linux_username>

• Expected Output: You should enter the psql shell.
• Troubleshooting: If login fails, verify:
  • The user and database exist (\du for users, \l for databases in psql).
  • The pg_hba.conf settings are correct.
  • The password (if set) is correct.

Verify Database

In the psql shell, list databases:

\l

List tables (should be empty for a new database):

\dt

---

8. Additional Suggestions

Backup Configuration

Set up regular backups using pg_dump or pg_basebackup:

pg_dump <your_linux_username> > backup.sql

• Suggestion: Schedule backups with cron:

  crontab -e

  Add:

  0 2 * * * pg_dump -U <your_linux_username> <your_linux_username> > /backups/db_backup_$(date +\%F).sql
  

Performance Tuning

Edit postgresql.conf to optimize performance:

sudo nano /var/lib/postgres/data/postgresql.conf

• Suggestions:
  • Adjust max_connections based on your application needs.
  • Tune work_mem and shared_buffers for better performance.
  • Enable logging for debugging:

    log_connections = on
    log_disconnections = on
    

Firewall Configuration

If allowing remote connections, configure your firewall:

sudo ufw allow 5432/tcp

• Security Note: Restrict access to specific IPs in pg_hba.conf for remote connections.

Monitoring

Use tools like pg_stat_activity or external monitoring tools (e.g., pgAdmin, Prometheus) to monitor database performance.

---

9. Complete Command Summary

Here’s the complete sequence of commands for reference:

# Install PostgreSQL
sudo pacman -Syu
sudo pacman -S postgresql

# Check version
postgres --version

# Confirm PostgreSQL is not running
sudo systemctl status postgresql

# Switch to postgres user
sudo su - postgres

# Initialize data directory
initdb --locale en_US.UTF-8 -D /var/lib/postgres/data

# Logout
exit

# Start PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Confirm PostgreSQL is running
sudo systemctl status postgresql

# Create user and database
sudo su - postgres
createuser --interactive
createdb <your_linux_username>

# Logout
exit

# Set password (optional)
psql -c "ALTER USER <your_linux_username> WITH PASSWORD 'newpassword';"

# Configure pg_hba.conf
sudo nano /var/lib/postgres/data/pg_hba.conf
# Edit as needed, then reload
sudo systemctl reload postgresql

# Test login
psql -U <your_linux_username> -d <your_linux_username>

---

10. Troubleshooting Tips

• Service won’t start: Check logs (journalctl -u postgresql) for errors like permission issues or missing data directories.
• Authentication errors: Verify pg_hba.conf settings and ensure the correct authentication method is used.
• Permission denied: Ensure the postgres user has ownership of the data directory:

  sudo chown -R postgres:postgres /var/lib/postgres/data

• Forgot password: Reset it using:

  psql -U postgres -c "ALTER USER <your_linux_username> WITH PASSWORD 'newpassword';"