PostgreSQL Features and Setup

Introduction to PostgreSQL

PostgreSQL (often shortened to "Postgres") is a powerful, open-source object-relational database system with over 30 years of active development. It has earned a strong reputation for reliability, feature robustness, and performance.

Key Features of PostgreSQL

• ACID Compliance: Ensures reliability and data integrity through Atomicity, Consistency, Isolation, and Durability
• Advanced Data Types: Beyond basic types, supports arrays, JSON, hstore (key-value pairs), geometric types, and more
• Extensibility: Create custom data types, functions, operators, and extensions like PostGIS for geospatial data
• Concurrency and Performance: Multi-Version Concurrency Control (MVCC) allows high concurrency without read locks
• Robust Security: Granular access controls, column and row-level security, and SSL support
• Full-Text Search: Built-in capabilities for text search with ranking and stemming
• Cross-Platform: Runs on virtually all major operating systems
• Active Community: Large community of users and developers with regular updates and extensive documentation

PostgreSQL vs. Other Database Systems

When to Choose PostgreSQL

• Data Integrity is Critical: Banking, financial systems, or medical applications
• Complex Data Requirements: Need for advanced data types, custom functions
• Scalability: Applications expected to grow significantly
• Geographic Data: When using spatial data with PostGIS
• Complex Querying: Applications requiring sophisticated business logic in the database
• Future-Proofing: When you want a database that can adapt to evolving requirements

Installing PostgreSQL

Platform-Specific Installation

# Install Homebrew if you don't have it
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install PostgreSQL
brew install postgresql

# Start PostgreSQL service
brew services start postgresql

# Create a database with your username (recommended)
createdb `whoami`
            

# Update package index
sudo apt update

# Install PostgreSQL and related tools
sudo apt install postgresql postgresql-contrib

# Start PostgreSQL service
sudo systemctl start postgresql

# Enable PostgreSQL to start on boot
sudo systemctl enable postgresql

# Switch to postgres user and create your own user
sudo -u postgres psql
postgres=# CREATE USER yourusername WITH SUPERUSER PASSWORD 'yourpassword';
postgres=# CREATE DATABASE yourusername;
postgres=# \q

# Testing connection
psql -U yourusername -d yourusername -h localhost
            

PostgreSQL Configuration Basics

Key Configuration Files

PostgreSQL's configuration system consists of several important files:

• postgresql.conf: Main configuration file for server parameters
• pg_hba.conf: Client authentication configuration file (controls who can connect)
• pg_ident.conf: User name mapping configuration (optional)

The location of these files varies depending on your installation method and platform:

C:\Program Files\PostgreSQL\14\data\

/usr/local/var/postgres/

/opt/homebrew/var/postgres/

/etc/postgresql/14/main/

# Connect to PostgreSQL
psql

# Show the location of postgresql.conf
SHOW config_file;

# Show the location of pg_hba.conf
SHOW hba_file;

# Exit psql
\q
          

Important Configuration Parameters

Here are some key parameters you might want to adjust in postgresql.conf:

# Connection Settings
listen_addresses = 'localhost'     # Listen only on localhost for security
port = 5432                        # Default port

# Memory Settings
shared_buffers = 512MB             # Adjust based on your system memory
work_mem = 16MB                    # Memory for each sort/hash operation
maintenance_work_mem = 128MB       # For vacuum, index creation, etc.
effective_cache_size = 4GB         # Estimate of available system cache

# Write Ahead Log
wal_level = replica                # For replication capability
max_wal_size = 1GB                 # Maximum WAL size before checkpoint

# Query Tuning
random_page_cost = 1.1             # Lower for SSDs (default 4.0 is for HDDs)
effective_io_concurrency = 200     # Higher for SSDs

# Logging
log_destination = 'stderr'         # Log to standard error
logging_collector = on             # Enable log collection
log_directory = 'log'              # Directory to store logs
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  # Log file naming pattern
log_min_duration_statement = 1000  # Log queries taking over 1 second
          

Client Authentication (pg_hba.conf)

The pg_hba.conf file controls which users can connect to which databases from which locations.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow a specific user from a specific network:
host    mydb            myuser          192.168.1.0/24          md5

# Allow any user from a specific host to connect to their same-named database:
host    sameuser        all             10.0.0.5/32             md5
          

Using PostgreSQL Command Line Tools

Essential PostgreSQL CLI Tools

PostgreSQL comes with several command-line tools that are essential for developers and administrators:

• psql: Interactive terminal for PostgreSQL
• createdb: Create a new database
• dropdb: Remove a database
• createuser: Create a new database user
• dropuser: Remove a database user
• pg_dump: Extract a database into a script file or archive file
• pg_restore: Restore a database from an archive file created by pg_dump
• pg_basebackup: Create a base backup of a PostgreSQL cluster

Working with psql

The psql utility is a powerful interactive terminal for working with PostgreSQL. Here's how to get started:

# Connect to a specific database
psql -U username -d database_name -h hostname -p port

# Examples:
psql -U postgres -d postgres         # Connect as postgres to postgres database
psql -d mydb                         # Connect to mydb as the current OS user
psql -h localhost -U webuser -d appdb # Connect to appdb on localhost as webuser
          

# Get help on psql commands
\h             # SQL command help
\?             # psql command help

# Connection information
\conninfo      # Display connection information

# List database objects
\l             # List all databases
\c dbname      # Connect to a different database
\dt            # List tables in current database
\d tablename   # Describe a table
\du            # List all users/roles
\dn            # List all schemas
\df            # List all functions
\dx            # List installed extensions

# Query execution
\timing on     # Turn on query execution time display
\e             # Edit a query in your preferred editor
\g             # Execute the last command again
\s             # Show command history
\i filename    # Execute commands from a file

# Output formatting
\x             # Toggle expanded display
\H             # Toggle HTML output
\a             # Toggle between aligned and unaligned output

# Miscellaneous
\password      # Change your password
\q             # Quit psql
          

-- Create a new database
CREATE DATABASE mydatabase;

-- Create a new user
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

-- Create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) VALUES ('johndoe', 'john@example.com');

-- Query data
SELECT * FROM users;

-- Update data
UPDATE users SET email = 'johndoe@example.com' WHERE username = 'johndoe';

-- Delete data
DELETE FROM users WHERE username = 'johndoe';
          

Database Backup and Restore

# Create a SQL backup (plain text)
pg_dump -U username -d database_name > backup.sql

# Create a compressed backup
pg_dump -U username -d database_name | gzip > backup.sql.gz

# Create a custom-format backup (compressed and allows selective restore)
pg_dump -U username -d database_name -F c -f backup.dump

# Backup only specific tables
pg_dump -U username -d database_name -t table1 -t table2 > backup_tables.sql

# Backup only the schema (no data)
pg_dump -U username -d database_name --schema-only > schema.sql

# Backup only the data (no schema)
pg_dump -U username -d database_name --data-only > data.sql
          

# Restore a SQL backup
psql -U username -d database_name < backup.sql

# Restore a compressed backup
gunzip -c backup.sql.gz | psql -U username -d database_name

# Restore a custom-format backup
pg_restore -U username -d database_name backup.dump

# Restore only specific tables from a custom backup
pg_restore -U username -d database_name -t table1 backup.dump

# Create a new database and restore into it
createdb -U username new_database
pg_restore -U username -d new_database backup.dump
          

PostgreSQL Data Types

One of PostgreSQL's strengths is its extensive set of built-in data types. Here's an overview of the most important ones:

Special PostgreSQL Data Types

PostgreSQL offers several specialized data types that make it stand out from other database systems:

-- Create a table with an array column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    tags TEXT[],  -- An array of text
    dimensions INTEGER[]  -- An array of integers
);

-- Insert data with arrays
INSERT INTO products (name, tags, dimensions) 
VALUES ('Widget', '{"red", "small", "discount"}', '{10, 20, 15}');

-- Query array elements
SELECT name, tags[1] FROM products;  -- Get first tag (arrays are 1-indexed)

-- Search within arrays
SELECT * FROM products WHERE 'red' = ANY(tags);

-- Update an array
UPDATE products SET tags = array_append(tags, 'new') WHERE id = 1;
          

-- Create a table with JSON columns
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    properties JSON,          -- Standard JSON
    metadata JSONB            -- Binary JSON (more efficient)
);

-- Insert JSON data
INSERT INTO events (name, properties, metadata)
VALUES (
    'Page View',
    '{"page": "/home", "user_agent": "Mozilla/5.0"}',
    '{"user_id": 123, "session_id": "abc123"}'
);

-- Query JSON data
SELECT name, properties->>'page' AS page FROM events;  -- Extract as text
SELECT name, metadata->'user_id' AS user_id FROM events;  -- Extract as JSON

-- Filter by JSON values
SELECT * FROM events WHERE metadata->>'user_id' = '123';

-- Update JSON data
UPDATE events 
SET metadata = metadata || '{"logged_in": true}'::jsonb  -- Merge JSON objects
WHERE id = 1;
          

-- Create a table with range types
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    during TSRANGE NOT NULL  -- Timestamp range
);

-- Insert range data
INSERT INTO reservations (room_id, during)
VALUES (
    123,
    tsrange('2023-01-10 12:00:00', '2023-01-10 14:00:00')
);

-- Check for overlap
SELECT * FROM reservations 
WHERE during && tsrange('2023-01-10 13:00:00', '2023-01-10 15:00:00');

-- Check if a value is within range
SELECT * FROM reservations 
WHERE during @> '2023-01-10 13:30:00'::timestamp;
          

Setting Up PostgreSQL for Python Development

Creating a Development Database and User

-- Connect as postgres user
psql -U postgres

-- Create a new user for the application
CREATE USER pythonapp WITH PASSWORD 'secure_password';

-- Create a new database
CREATE DATABASE python_app_db;

-- Grant privileges to the user
GRANT ALL PRIVILEGES ON DATABASE python_app_db TO pythonapp;

-- Connect to the new database
\c python_app_db

-- Create a schema (optional but recommended)
CREATE SCHEMA app;

-- Grant privileges on the schema
GRANT ALL ON SCHEMA app TO pythonapp;

-- Set the default search path
ALTER USER pythonapp SET search_path TO app, public;

-- Create a sample table
CREATE TABLE app.users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Grant privileges on the table
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO pythonapp;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO pythonapp;

-- Test the connection
\q
psql -U pythonapp -d python_app_db
          

Installing Python PostgreSQL Drivers

To connect to PostgreSQL from Python, you'll need a database driver. The two most popular options are:

• psycopg2: The most popular PostgreSQL adapter for Python
• psycopg2-binary: Pre-compiled binary version of psycopg2 (easier to install)

# Install psycopg2 binary (recommended for development)
pip install psycopg2-binary

# Alternatively, install from source (requires development headers)
pip install psycopg2
          

import psycopg2

# Connection parameters
params = {
    'dbname': 'python_app_db',
    'user': 'pythonapp',
    'password': 'secure_password',
    'host': 'localhost',
    'port': 5432
}

# Establish a connection
try:
    conn = psycopg2.connect(**params)
    
    # Create a cursor
    cur = conn.cursor()
    
    # Execute a simple test query
    cur.execute("SELECT version();")
    
    # Fetch the result
    version = cur.fetchone()
    print(f"Connected to PostgreSQL: {version[0]}")
    
    # Close cursor and connection
    cur.close()
    conn.close()
    print("Connection closed.")
    
except Exception as e:
    print(f"Error: {e}")
          

Other Python Libraries for PostgreSQL

Beyond the basic psycopg2 driver, several higher-level libraries can make working with PostgreSQL easier:

PostgreSQL Graphical Tools

pgAdmin

pgAdmin is the most popular and feature-rich open-source administration and development platform for PostgreSQL.

• Complete management interface for PostgreSQL
• Support for all PostgreSQL objects and features
• SQL query tool with syntax highlighting and execution plans
• Data import and export capabilities
• Server monitoring dashboard
• Available as a desktop application or web application

pgAdmin is typically installed alongside PostgreSQL, but you can also download it separately from pgadmin.org.

Other GUI Tools

• DBeaver: Free, multi-platform database tool supporting PostgreSQL and many other databases
• DataGrip: JetBrains IDE for databases (commercial, with free options for students and open-source developers)
• Navicat for PostgreSQL: Powerful commercial database administration tool
• Postico: Simple, native PostgreSQL client for macOS
• TablePlus: Modern, native tool for multiple databases (freemium)
• Beekeeper Studio: Open-source SQL editor and database manager
• OmniDB: Web-based database management tool with focus on PostgreSQL

Practical Activities

Activity 1: PostgreSQL Setup and Exploration

1. Install PostgreSQL on your development machine
2. Connect to the PostgreSQL server using the psql command-line tool
3. Create a new database for your development work
4. Create a dedicated database user with appropriate permissions
5. Install and configure pgAdmin or another GUI tool
6. Explore the PostgreSQL system catalogs to list all databases, tables, and users

Activity 2: Creating a Database Schema

Design and implement a database schema for a simple blogging platform with the following requirements:

1. Users with username, email, password, and registration date
2. Blog posts with title, content, publication date, and author (user)
3. Categories for blog posts (many-to-many relationship)
4. Comments with content, author, and timestamp
5. Tags for blog posts (many-to-many relationship)
6. Ensure proper relationships between entities
7. Implement appropriate constraints and indexes

Activity 3: Python Connection Test

1. Set up a Python virtual environment for your development
2. Install psycopg2 or psycopg2-binary
3. Write a Python script that:
   • Connects to your PostgreSQL database
   • Creates a simple table with a few columns
   • Inserts several rows of data
   • Queries the data and displays the results
   • Updates some of the data
   • Deletes a row
   • Properly handles any errors and closes the connection

Key Takeaways

• PostgreSQL is a powerful, open-source object-relational database system with a strong focus on extensibility and standards compliance
• It offers advanced features like custom data types, JSON support, array columns, and full-text search
• Installation is straightforward on Windows, macOS, and Linux platforms
• Configuration is managed through postgresql.conf (server settings) and pg_hba.conf (authentication)
• The psql command-line tool provides a powerful interface for PostgreSQL administration and querying
• PostgreSQL offers a rich set of data types beyond standard SQL, including arrays, JSON, and ranges
• For Python development, psycopg2 is the most popular PostgreSQL adapter
• pgAdmin and other GUI tools provide user-friendly interfaces for database management

Next Steps

In our next lecture, we'll dive deeper into Python and PostgreSQL integration, exploring how to connect to databases, execute queries, and process results in Python applications.