Rizqi Mulki

Backend Developer

How to Install and Optimize PostgreSQL on Linux for Development

—

by

Rizqi Mulki
in ,

PostgreSQL is a powerful, open-source relational database management system with over 30 years of active development. It’s known for reliability, feature robustness, and performance. This guide will help you install and optimize PostgreSQL on Linux specifically for development purposes.

Table of Contents

  1. Prerequisites
  2. Installation
  3. Basic Configuration
  4. User and Database Setup
  5. Performance Optimization for Development
  6. Useful Development Tools
  7. Common Troubleshooting
  8. Development Best Practices

Prerequisites

Before installing PostgreSQL, ensure your system meets these requirements:

  • Linux distribution (Ubuntu, Debian, CentOS, Fedora, etc.)
  • Sudo/root privileges
  • At least 512MB RAM (1GB+ recommended for development)
  • 1GB+ free disk space
  • Internet connection for package downloads

Installation

Ubuntu/Debian

  1. Update your package lists: sudo apt update
  2. Install PostgreSQL and its common extensions: sudo apt install postgresql postgresql-contrib
  3. Verify installation: sudo systemctl status postgresql
  4. Enable automatic startup: sudo systemctl enable postgresql

CentOS/RHEL/Fedora

  1. Install the PostgreSQL repository: # For CentOS/RHEL 8 sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm # For Fedora 35+ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/F-35-x86_64/pgdg-fedora-repo-latest.noarch.rpm
  2. Install PostgreSQL: sudo dnf -qy module disable postgresql sudo dnf install -y postgresql14-server postgresql14-contrib
  3. Initialize the database: sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
  4. Start and enable the service: sudo systemctl start postgresql-14 sudo systemctl enable postgresql-14

Arch Linux

  1. Install PostgreSQL: sudo pacman -S postgresql
  2. Initialize the database cluster: sudo -iu postgres initdb -D /var/lib/postgres/data
  3. Start and enable the service: sudo systemctl start postgresql sudo systemctl enable postgresql

Basic Configuration

The main configuration files for PostgreSQL are:

  • postgresql.conf: Main configuration file
  • pg_hba.conf: Client authentication configuration
  • pg_ident.conf: User name mapping

These are typically located in /etc/postgresql/[version]/main/ for Debian-based systems or /var/lib/pgsql/[version]/data/ for RHEL-based systems.

Accessing PostgreSQL Server

  1. Switch to the postgres user: sudo -i -u postgres
  2. Access the PostgreSQL prompt: psql
  3. Basic commands: -- Display connection information \conninfo -- List databases \l -- Exit psql \q

User and Database Setup

  1. Create a new database user: CREATE USER devuser WITH PASSWORD 'password';
  2. Create a new database: CREATE DATABASE devdb;
  3. Grant privileges: GRANT ALL PRIVILEGES ON DATABASE devdb TO devuser;
  4. For development, you might want to make your user a superuser: ALTER USER devuser WITH SUPERUSER;

Performance Optimization for Development

For development environments, optimizing PostgreSQL focuses on convenience and quick iteration rather than maximum throughput or security.

Edit your postgresql.conf file to include these development-friendly settings:

# Memory Configuration
shared_buffers = 256MB         # 25% of system RAM for development machines
work_mem = 24MB                # Higher for development to speed up complex queries
maintenance_work_mem = 64MB    # For faster vacuum, create index, etc.

# Query Planner
random_page_cost = 1.1         # Assuming development uses SSD storage
effective_cache_size = 768MB   # 50-75% of total RAM for development

# Write Ahead Log (WAL)
wal_buffers = 16MB             # Helps with bursts of activity
synchronous_commit = off       # Dangerous in production, but speeds up development

# Background Writer
bgwriter_delay = 200ms         # Less aggressive for development

# Logging for Development
log_min_duration_statement = 100  # Log queries that take more than 100ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

After making changes, restart PostgreSQL:

sudo systemctl restart postgresql

Useful Development Tools

pgAdmin 4

pgAdmin is a comprehensive GUI management tool:

# On Ubuntu/Debian
sudo apt install pgadmin4

# On RHEL/CentOS
sudo yum install pgadmin4

psql Tips

Enable extended display for better readability:

\x auto

View command execution time:

\timing on

Database Backup and Restore

For quick backup/restore during development:

# Backup a database
pg_dump -U devuser devdb > devdb_backup.sql

# Restore from backup
psql -U devuser -d devdb -f devdb_backup.sql

Extensions for Development

Install useful extensions:

-- Full-text search
CREATE EXTENSION pg_trgm;

-- JSON operations
CREATE EXTENSION jsonb_plperlu;

-- Performance analysis
CREATE EXTENSION pg_stat_statements;

Common Troubleshooting

Connection Issues

If you can’t connect to PostgreSQL:

  1. Check if PostgreSQL is running: sudo systemctl status postgresql
  2. Verify pg_hba.conf configuration (located in the data directory): # For local development, you might want to add: # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
  3. Restart PostgreSQL after changes: sudo systemctl restart postgresql

Memory-Related Errors

If your queries are failing due to memory issues, adjust these settings:

work_mem = 32MB            # Increase for complex queries
maintenance_work_mem = 64MB

Slow Query Performance

Enable the query execution plan analyzer:

EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;

Database Reset

For development, sometimes you need to quickly reset:

# Drop and recreate database
dropdb -U postgres devdb
createdb -U postgres -O devuser devdb

Development Best Practices

Use Transaction Blocks

Always wrap development code in transactions:

BEGIN;
-- Your operations here
COMMIT;
-- or ROLLBACK; if something goes wrong

Use Prepared Statements

For repetitive queries, use prepared statements:

PREPARE user_select(int) AS
SELECT * FROM users WHERE user_id = $1;

EXECUTE user_select(123);

Set Up .psqlrc

Create a ~/.psqlrc file to customize your psql environment:

\set QUIET 1
\pset null '(null)'
\set PROMPT1 '%[%033[1;33m%]%n@%m:%>%[%033[0m%] %[%033[1;31m%]%/%[%033[0m%]%R%# '
\set PROMPT2 '%[%033[1;33m%]%n@%m:%>%[%033[0m%] %[%033[1;31m%]%/%[%033[0m%]%R%# '
\timing
\x auto
\set VERBOSITY verbose
\set HISTCONTROL ignoredups
\set COMP_KEYWORD_CASE upper
\unset QUIET

Development Schema Management

Use schema separation for development:

-- Create schemas for different parts of your application
CREATE SCHEMA api;
CREATE SCHEMA data;

-- Set search path
SET search_path TO api, data, public;

Optimize Indexes for Development

Create indexes on columns you frequently query:

CREATE INDEX idx_users_email ON users(email);

For development environments, consider using unlogged tables for temporary data:

CREATE UNLOGGED TABLE temp_data (id serial, data jsonb);

By following these installation and optimization steps, you’ll have a PostgreSQL database environment well-suited for development work. Remember that development configurations prioritize convenience and speed over security and data integrity, so these settings should be revisited before moving to production.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA ImageChange Image