Part III. 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 look into Part VI, “Reference”.

The first few chapters are written so that they can be understood without prerequisite knowledge, so that 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 Part I, “Tutorial” and Part II, “The SQL Language” for additional information.

Table of Contents

14. Installation Instructions
14.1. Short Version
14.2. Requirements
14.3. Getting The Source
14.4. If You Are Upgrading
14.5. Installation Procedure
14.6. Post-Installation Setup
14.6.1. Shared Libraries
14.6.2. Environment Variables
14.7. Supported Platforms
15. Client-Only Installation on Windows
16. Operating System Environment
16.1. The PostgreSQL User Account
16.2. Creating a Database Cluster
16.3. Starting the Database Server
16.3.1. Server Start-up Failures
16.3.2. Client Connection Problems
16.4. Managing Kernel Resources
16.4.1. Shared Memory and Semaphores
16.4.2. Resource Limits
16.4.3. Linux Memory Overcommit
16.5. Shutting Down the Server
16.6. Encryption Options
16.7. Secure TCP/IP Connections with SSL
16.8. Secure TCP/IP Connections with SSH Tunnels
17. Server Configuration
17.1. Setting Parameters
17.2. File Locations
17.3. Connections and Authentication
17.3.1. Connection Settings
17.3.2. Security and Authentication
17.4. Resource Consumption
17.4.1. Memory
17.4.2. Free Space Map
17.4.3. Kernel Resource Usage
17.4.4. Cost-Based Vacuum Delay
17.4.5. Background Writer
17.5. Write Ahead Log
17.5.1. Settings
17.5.2. Checkpoints
17.5.3. Archiving
17.6. Query Planning
17.6.1. Planner Method Configuration
17.6.2. Planner Cost Constants
17.6.3. Genetic Query Optimizer
17.6.4. Other Planner Options
17.7. Error Reporting and Logging
17.7.1. Where To Log
17.7.2. When To Log
17.7.3. What To Log
17.8. Run-Time Statistics
17.8.1. Query and Index Statistics Collector
17.8.2. Statistics Monitoring
17.9. Automatic Vacuuming
17.10. Client Connection Defaults
17.10.1. Statement Behavior
17.10.2. Locale and Formatting
17.10.3. Other Defaults
17.11. Lock Management
17.12. Version and Platform Compatibility
17.12.1. Previous PostgreSQL Versions
17.12.2. Platform and Client Compatibility
17.13. Preset Options
17.14. Customized Options
17.15. Developer Options
17.16. Short Options
18. Database Roles and Privileges
18.1. Database Roles
18.2. Role Attributes
18.3. Privileges
18.4. Role Membership
18.5. Functions and Triggers
19. Managing Databases
19.1. Overview
19.2. Creating a Database
19.3. Template Databases
19.4. Database Configuration
19.5. Destroying a Database
19.6. Tablespaces
20. Client Authentication
20.1. The pg_hba.conf file
20.2. Authentication methods
20.2.1. Trust authentication
20.2.2. Password authentication
20.2.3. Kerberos authentication
20.2.4. Ident-based authentication
20.2.5. LDAP authentication
20.2.6. PAM authentication
20.3. Authentication problems
21. Localization
21.1. Locale Support
21.1.1. Overview
21.1.2. Behavior
21.1.3. Problems
21.2. Character Set Support
21.2.1. Supported Character Sets
21.2.2. Setting the Character Set
21.2.3. Automatic Character Set Conversion Between Server and Client
21.2.4. Further Reading
22. Routine Database Maintenance Tasks
22.1. Routine Vacuuming
22.1.1. Recovering disk space
22.1.2. Updating planner statistics
22.1.3. Preventing transaction ID wraparound failures
22.1.4. The auto-vacuum daemon
22.2. Routine Reindexing
22.3. Log File Maintenance
23. Backup and Restore
23.1. SQL Dump
23.1.1. Restoring the dump
23.1.2. Using pg_dumpall
23.1.3. Handling large databases
23.2. File System Level Backup
23.3. Continuous Archiving and Point-In-Time Recovery (PITR)
23.3.1. Setting up WAL archiving
23.3.2. Making a Base Backup
23.3.3. Recovering using a Continuous Archive Backup
23.3.4. Timelines
23.3.5. Caveats
23.4. Warm Standby Servers for High Availability
23.4.1. Planning
23.4.2. Implementation
23.4.3. Failover
23.4.4. Record-based Log Shipping
23.4.5. Incrementally Updated Backups
23.5. Migration Between Releases
24. High Availability and Load Balancing
25. Monitoring Database Activity
25.1. Standard Unix Tools
25.2. The Statistics Collector
25.2.1. Statistics Collection Configuration
25.2.2. Viewing Collected Statistics
25.3. Viewing Locks
25.4. Dynamic Tracing
25.4.1. Compiling for Dynamic Tracing
25.4.2. Built-in Trace Points
25.4.3. Using Trace Points
25.4.4. Defining Trace Points
26. Monitoring Disk Usage
26.1. Determining Disk Usage
26.2. Disk Full Failure
27. Reliability and the Write-Ahead Log
27.1. Reliability
27.2. Write-Ahead Logging (WAL)
27.3. WAL Configuration
27.4. WAL Internals
28. Regression Tests
28.1. Running the Tests
28.2. Test Evaluation
28.2.1. Error message differences
28.2.2. Locale differences
28.2.3. Date and time differences
28.2.4. Floating-point differences
28.2.5. Row ordering differences
28.2.6. Insufficient stack depth
28.2.7. The “random” test
28.3. Variant Comparison Files