5. Server Administration

This part covers topics that are of interest to a PostgreSQL database administrator. This includes installation of the software, set up and configuration of the server, management of users and databases, and maintenance tasks. Anyone who runs a PostgreSQL server, even for personal use, but especially in production, should be familiar with the topics covered in this part.

The information in this part is arranged approximately in the order in which a new user should read it. But the chapters are self-contained and can be read individually as desired. The information in this part is presented in a narrative fashion in topical units. Readers looking for a complete description of a particular command should see reference.

The first few chapters are written so they can be understood without prerequisite knowledge, so new users who need to set up their own server can begin their exploration with this part. The rest of this part is about tuning and management; that material assumes that the reader is familiar with the general use of the PostgreSQL database system. Readers are encouraged to look at tutorial and sql for additional information.

• 5.1. Installation from Source Code
  • 5.1.1. Short Version
  • 5.1.2. Requirements
  • 5.1.3. Getting the Source
  • 5.1.4. Installation Procedure
    • 5.1.4.1. Client-only installation:
    • 5.1.4.2. Uninstallation:
    • 5.1.4.3. Cleaning:
  • 5.1.5. Post-Installation Setup
  • 5.1.6. Supported Platforms
  • 5.1.7. Platform-Specific Notes
• 5.2. Server Setup and Operation
  • 5.2.1. The PostgreSQL User Account
  • 5.2.2. Creating a Database Cluster
    • 5.2.2.1. Use of Secondary File Systems
    • 5.2.2.2. File Systems
  • 5.2.3. Starting the Database Server
    • 5.2.3.1. Server Start-up Failures
    • 5.2.3.2. Client Connection Problems
  • 5.2.4. Managing Kernel Resources
    • 5.2.4.1. Shared Memory and Semaphores
    • 5.2.4.2. systemd RemoveIPC
    • 5.2.4.3. Resource Limits
    • 5.2.4.4. Linux Memory Overcommit
    • 5.2.4.5. Linux Huge Pages
  • 5.2.5. Shutting Down the Server
  • 5.2.6. Upgrading a PostgreSQL Cluster
    • 5.2.6.1. Upgrading Data via pg_dumpall
    • 5.2.6.2. Upgrading Data via pg_upgrade
    • 5.2.6.3. Upgrading Data via Replication
  • 5.2.7. Preventing Server Spoofing
  • 5.2.8. Encryption Options
  • 5.2.9. Secure TCP/IP Connections with SSL
    • 5.2.9.1. Basic Setup
    • 5.2.9.2. OpenSSL Configuration
    • 5.2.9.3. Using Client Certificates
    • 5.2.9.4. SSL Server File Usage
    • 5.2.9.5. Creating Certificates
  • 5.2.10. Secure TCP/IP Connections with GSSAPI Encryption
    • 5.2.10.1. Basic Setup
  • 5.2.11. Secure TCP/IP Connections with SSH Tunnels
  • 5.2.12. Registering Event Log on Windows
• 5.3. Server Configuration
  • 5.3.1. Setting Parameters
    • 5.3.1.1. Parameter Names and Values
    • 5.3.1.2. Parameter Interaction via the Configuration File
    • 5.3.1.3. Parameter Interaction via SQL
    • 5.3.1.4. Parameter Interaction via the Shell
    • 5.3.1.5. Managing Configuration File Contents
  • 5.3.2. File Locations
  • 5.3.3. Connections and Authentication
    • 5.3.3.1. Connection Settings
    • 5.3.3.2. Authentication
    • 5.3.3.3. SSL
  • 5.3.4. Resource Consumption
    • 5.3.4.1. Memory
    • 5.3.4.2. Disk
    • 5.3.4.3. Kernel Resource Usage
    • 5.3.4.4. Cost-based Vacuum Delay
    • 5.3.4.5. Background Writer
    • 5.3.4.6. Asynchronous Behavior
  • 5.3.5. Write Ahead Log
    • 5.3.5.1. Settings
    • 5.3.5.2. Checkpoints
    • 5.3.5.3. Archiving
    • 5.3.5.4. Recovery
    • 5.3.5.5. Archive Recovery
    • 5.3.5.6. Recovery Target
  • 5.3.6. Replication
    • 5.3.6.1. Sending Servers
    • 5.3.6.2. Primary Server
    • 5.3.6.3. Standby Servers
    • 5.3.6.4. Subscribers
  • 5.3.7. Query Planning
    • 5.3.7.1. Planner Method Configuration
    • 5.3.7.2. Planner Cost Constants
    • 5.3.7.3. Genetic Query Optimizer
    • 5.3.7.4. Other Planner Options
  • 5.3.8. Error Reporting and Logging
    • 5.3.8.1. Where to Log
    • 5.3.8.2. When to Log
    • 5.3.8.3. What to Log
    • 5.3.8.4. Using CSV-Format Log Output
    • 5.3.8.5. Using JSON-Format Log Output
    • 5.3.8.6. Process Title
  • 5.3.9. Run-time Statistics
    • 5.3.9.1. Cumulative Query and Index Statistics
    • 5.3.9.2. Statistics Monitoring
  • 5.3.10. Automatic Vacuuming
  • 5.3.11. Client Connection Defaults
    • 5.3.11.1. Statement Behavior
    • 5.3.11.2. Locale and Formatting
    • 5.3.11.3. Shared Library Preloading
    • 5.3.11.4. Other Defaults
  • 5.3.12. Lock Management
  • 5.3.13. Version and Platform Compatibility
    • 5.3.13.1. Previous PostgreSQL Versions
    • 5.3.13.2. Platform and Client Compatibility
  • 5.3.14. Error Handling
  • 5.3.15. Preset Options
  • 5.3.16. Customized Options
  • 5.3.17. Developer Options
  • 5.3.18. Short Options
• 5.4. Client Authentication
  • 5.4.1. The pg_hba.conf File
    • 5.4.1.1. Example pg_hba.conf Entries
  • 5.4.2. User Name Maps
    • 5.4.2.1. An Example pg_ident.conf File
  • 5.4.3. Authentication Methods
  • 5.4.4. Trust Authentication
  • 5.4.5. Password Authentication
  • 5.4.6. GSSAPI Authentication
  • 5.4.7. SSPI Authentication
  • 5.4.8. Ident Authentication
  • 5.4.9. Peer Authentication
  • 5.4.10. LDAP Authentication
  • 5.4.11. RADIUS Authentication
  • 5.4.12. Certificate Authentication
  • 5.4.13. PAM Authentication
  • 5.4.14. BSD Authentication
  • 5.4.15. Authentication Problems
• 5.5. Database Roles
  • 5.5.1. Database Roles
  • 5.5.2. Role Attributes
  • 5.5.3. Role Membership
  • 5.5.4. Dropping Roles
  • 5.5.5. Predefined Roles
  • 5.5.6. Function Security
• 5.6. Managing Databases
  • 5.6.1. Overview
  • 5.6.2. Creating a Database
  • 5.6.3. Template Databases
  • 5.6.4. Database Configuration
  • 5.6.5. Destroying a Database
  • 5.6.6. Tablespaces
• 5.7. Localization
  • 5.7.1. Locale Support
    • 5.7.1.1. Overview
    • 5.7.1.2. Behavior
    • 5.7.1.3. Selecting Locales
    • 5.7.1.4. Locale Providers
    • 5.7.1.5. Problems
  • 5.7.2. Collation Support
    • 5.7.2.1. Concepts
    • 5.7.2.2. Managing Collations
  • 5.7.3. Character Set Support
    • 5.7.3.1. Supported Character Sets
    • 5.7.3.2. Setting the Character Set
    • 5.7.3.3. Automatic Character Set Conversion Between Server and Client
    • 5.7.3.4. Available Character Set Conversions
    • 5.7.3.5. Further Reading
• 5.8. Routine Database Maintenance Tasks
  • 5.8.1. Routine Vacuuming
    • 5.8.1.1. Vacuuming Basics
    • 5.8.1.2. Recovering Disk Space
    • 5.8.1.3. Updating Planner Statistics
    • 5.8.1.4. Updating the Visibility Map
    • 5.8.1.5. Preventing Transaction ID Wraparound Failures
    • 5.8.1.6. The Autovacuum Daemon
  • 5.8.2. Routine Reindexing
  • 5.8.3. Log File Maintenance
• 5.9. Backup and Restore
  • 5.9.1. SQL Dump
    • 5.9.1.1. Restoring the Dump
    • 5.9.1.2. Using pg_dumpall
    • 5.9.1.3. Handling Large Databases
  • 5.9.2. File System Level Backup
  • 5.9.3. Continuous Archiving and Point-in-Time Recovery (PITR)
    • 5.9.3.1. Setting Up WAL Archiving
    • 5.9.3.2. Making a Base Backup
    • 5.9.3.3. Making a Base Backup Using the Low Level API
    • 5.9.3.4. Recovering Using a Continuous Archive Backup
    • 5.9.3.5. Timelines
    • 5.9.3.6. Tips and Examples
    • 5.9.3.7. Caveats
• 5.10. High Availability, Load Balancing, and Replication
  • 5.10.1. Comparison of Different Solutions
  • 5.10.2. Log-Shipping Standby Servers
    • 5.10.2.1. Planning
    • 5.10.2.2. Standby Server Operation
    • 5.10.2.3. Preparing the Primary for Standby Servers
    • 5.10.2.4. Setting Up a Standby Server
    • 5.10.2.5. Streaming Replication
    • 5.10.2.6. Replication Slots
    • 5.10.2.7. Cascading Replication
    • 5.10.2.8. Synchronous Replication
    • 5.10.2.9. Continuous Archiving in Standby
  • 5.10.3. Failover
  • 5.10.4. Hot Standby
    • 5.10.4.1. User’s Overview
    • 5.10.4.2. Handling Query Conflicts
    • 5.10.4.3. Administrator’s Overview
    • 5.10.4.4. Hot Standby Parameter Reference
    • 5.10.4.5. Caveats
• 5.11. Monitoring Database Activity
  • 5.11.1. Standard Unix Tools
  • 5.11.2. The Cumulative Statistics System
    • 5.11.2.1. Statistics Collection Configuration
    • 5.11.2.2. Viewing Statistics
    • 5.11.2.3. pg_stat_activity
    • 5.11.2.4. pg_stat_replication
    • 5.11.2.5. pg_stat_replication_slots
    • 5.11.2.6. pg_stat_wal_receiver
    • 5.11.2.7. pg_stat_recovery_prefetch
    • 5.11.2.8. pg_stat_subscription
    • 5.11.2.9. pg_stat_subscription_stats
    • 5.11.2.10. pg_stat_ssl
    • 5.11.2.11. pg_stat_gssapi
    • 5.11.2.12. pg_stat_archiver
    • 5.11.2.13. pg_stat_bgwriter
    • 5.11.2.14. pg_stat_wal
    • 5.11.2.15. pg_stat_database
    • 5.11.2.16. pg_stat_database_conflicts
    • 5.11.2.17. pg_stat_all_tables
    • 5.11.2.18. pg_stat_all_indexes
    • 5.11.2.19. pg_statio_all_tables
    • 5.11.2.20. pg_statio_all_indexes
    • 5.11.2.21. pg_statio_all_sequences
    • 5.11.2.22. pg_stat_user_functions
    • 5.11.2.23. pg_stat_slru
    • 5.11.2.24. Statistics Functions
  • 5.11.3. Viewing Locks
  • 5.11.4. Progress Reporting
    • 5.11.4.1. ANALYZE Progress Reporting
    • 5.11.4.2. CREATE INDEX Progress Reporting
    • 5.11.4.3. VACUUM Progress Reporting
    • 5.11.4.4. CLUSTER Progress Reporting
    • 5.11.4.5. Base Backup Progress Reporting
    • 5.11.4.6. COPY Progress Reporting
  • 5.11.5. Dynamic Tracing
    • 5.11.5.1. Compiling for Dynamic Tracing
    • 5.11.5.2. Built-in Probes
    • 5.11.5.3. Using Probes
    • 5.11.5.4. Defining New Probes
• 5.12. Monitoring Disk Usage
  • 5.12.1. Determining Disk Usage
  • 5.12.2. Disk Full Failure
• 5.13. Reliability and the Write-Ahead Log
  • 5.13.1. Reliability
  • 5.13.2. Data Checksums
    • 5.13.2.1. Off-line Enabling of Checksums
  • 5.13.3. Write-Ahead Logging (WAL)
  • 5.13.4. Asynchronous Commit
  • 5.13.5. WAL Configuration
  • 5.13.6. WAL Internals
• 5.14. Logical Replication
  • 5.14.1. Publication
  • 5.14.2. Subscription
    • 5.14.2.1. Replication Slot Management
    • 5.14.2.2. Examples
  • 5.14.3. Row Filters
    • 5.14.3.1. Row Filter Rules
    • 5.14.3.2. Expression Restrictions
    • 5.14.3.3. UPDATE Transformations
    • 5.14.3.4. Partitioned Tables
    • 5.14.3.5. Initial Data Synchronization
    • 5.14.3.6. Combining Multiple Row Filters
    • 5.14.3.7. Examples
  • 5.14.4. Column Lists
    • 5.14.4.1. Combining Multiple Column Lists
    • 5.14.4.2. Examples
  • 5.14.5. Conflicts
  • 5.14.6. Restrictions
  • 5.14.7. Architecture
    • 5.14.7.1. Initial Snapshot
  • 5.14.8. Monitoring
  • 5.14.9. Security
  • 5.14.10. Configuration Settings
  • 5.14.11. Quick Setup
• 5.15. Just-in-Time Compilation (JIT)
  • 5.15.1. What Is JIT compilation?
    • 5.15.1.1. JIT Accelerated Operations
    • 5.15.1.2. Inlining
    • 5.15.1.3. Optimization
  • 5.15.2. When to JIT?
  • 5.15.3. Configuration
  • 5.15.4. Extensibility
    • 5.15.4.1. Inlining Support for Extensions
    • 5.15.4.2. Pluggable JIT Providers
• 5.16. Regression Tests
  • 5.16.1. Running the Tests
    • 5.16.1.1. Running the Tests Against a Temporary Installation
    • 5.16.1.2. Running the Tests Against an Existing Installation
    • 5.16.1.3. Additional Test Suites
    • 5.16.1.4. Locale and Encoding
    • 5.16.1.5. Custom Server Settings
    • 5.16.1.6. Extra Tests
  • 5.16.2. Test Evaluation
    • 5.16.2.1. Error Message Differences
    • 5.16.2.2. Locale Differences
    • 5.16.2.3. Date and Time Differences
    • 5.16.2.4. Floating-Point Differences
    • 5.16.2.5. Row Ordering Differences
    • 5.16.2.6. Insufficient Stack Depth
    • 5.16.2.7. The random Test
    • 5.16.2.8. Configuration Parameters
  • 5.16.3. Variant Comparison Files
  • 5.16.4. TAP Tests
  • 5.16.5. Test Coverage Examination