Running multiple PostgreSQL instances on the same machine

Sam

Code for this tutorial can be found on GitHub

Why Run Multiple PostgreSQL Instances?

Running multiple PostgreSQL instances on a single server can be valuable for:

  • Development and testing environments
  • Isolating different applications
  • Testing different PostgreSQL versions
  • Maintaining separate resource allocations

Prerequisites

Before starting, ensure you have:

  • PostgreSQL installed on your system
  • Sufficient disk space
  • Access to pg_ctl and initdb commands
  • Appropriate user permissions

Step-by-Step Setup Process

1. Create Data Directories

First, create separate data directories for each instance:

mkdir -p /data/postgres/instance1
mkdir -p /data/postgres/instance2

2. Initialize Database Clusters

Use initdb to create new database clusters for each instance:

initdb -D /data/postgres/instance1
initdb -D /data/postgres/instance2

3. Configure Different Ports

Edit postgresql.conf in each data directory to set different ports:

For instance1 (/data/postgres/instance1/postgresql.conf):

port = 5432
unix_socket_directories = '/tmp'

For instance2 (/data/postgres/instance2/postgresql.conf):

port = 5433
unix_socket_directories = '/tmp'

4. Starting the Instances

Use pg_ctl to manage each instance:

# Start first instance
pg_ctl -D /data/postgres/instance1 -l /data/postgres/instance1/logfile start

# Start second instance
pg_ctl -D /data/postgres/instance2 -l /data/postgres/instance2/logfile start

5. Verify Running Instances

Check if both instances are running:

# Check first instance
psql -p 5432 -c "SELECT current_database(), current_setting('port');"

# Check second instance
psql -p 5433 -c "SELECT current_database(), current_setting('port');"

Managing Your Instances

Starting Instances

pg_ctl -D /data/postgres/instance1 start
pg_ctl -D /data/postgres/instance2 start

Stopping Instances

pg_ctl -D /data/postgres/instance1 stop
pg_ctl -D /data/postgres/instance2 stop

Checking Status

pg_ctl -D /data/postgres/instance1 status
pg_ctl -D /data/postgres/instance2 status

Best Practices

  1. Resource Management

    • Set different memory parameters for each instance
    • Monitor resource usage carefully
    • Adjust shared_buffers per instance

  2. Port Management

    • Document port assignments
    • Use consistent port numbering schemes
    • Verify port availability before starting

  3. Backup Considerations

    • Back up each instance separately
    • Label backups clearly with instance information
    • Test restore procedures for each instance

Troubleshooting Common Issues

  • Port Already in Use: Verify no other services are using your chosen ports
  • Permission Denied: Ensure proper ownership of data directories
  • Insufficient Resources: Check system resources and PostgreSQL parameters

Next Steps

After setting up multiple instances, consider:

  • Implementing monitoring solutions
  • Setting up backup strategies
  • Configuring instance-specific optimizations

Getting Help

Remember to specify the port number when connecting or troubleshooting:

# Connect to specific instance
psql -p 5432 -d postgres  # First instance
psql -p 5433 -d postgres  # Second instance

Table of Contents