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. Server Run-time 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. Run-time Configuration
16.4.1. File Locations
16.4.2. Connections and Authentication
16.4.3. Resource Consumption
16.4.4. Write Ahead Log
16.4.5. Query Planning
16.4.6. Error Reporting and Logging
16.4.7. Runtime Statistics
16.4.8. Client Connection Defaults
16.4.9. Lock Management
16.4.10. Version and Platform Compatibility
16.4.11. Preset Options
16.4.12. Customized Options
16.4.13. Developer Options
16.4.14. Short Options
16.5. Managing Kernel Resources
16.5.1. Shared Memory and Semaphores
16.5.2. Resource Limits
16.5.3. Linux Memory Overcommit
16.6. Shutting Down the Server
16.7. Secure TCP/IP Connections with SSL
16.8. Secure TCP/IP Connections with SSH Tunnels
17. Database Users and Privileges
17.1. Database Users
17.2. User Attributes
17.3. Groups
17.4. Privileges
17.5. Functions and Triggers
18. Managing Databases
18.1. Overview
18.2. Creating a Database
18.3. Template Databases
18.4. Database Configuration
18.5. Destroying a Database
18.6. Tablespaces
19. Client Authentication
19.1. The pg_hba.conf file
19.2. Authentication methods
19.2.1. Trust authentication
19.2.2. Password authentication
19.2.3. Kerberos authentication
19.2.4. Ident-based authentication
19.2.5. PAM authentication
19.3. Authentication problems
20. Localization
20.1. Locale Support
20.1.1. Overview
20.1.2. Behavior
20.1.3. Problems
20.2. Character Set Support
20.2.1. Supported Character Sets
20.2.2. Setting the Character Set
20.2.3. Automatic Character Set Conversion Between Server and Client
20.2.4. Further Reading
21. Routine Database Maintenance Tasks
21.1. Routine Vacuuming
21.1.1. Recovering disk space
21.1.2. Updating planner statistics
21.1.3. Preventing transaction ID wraparound failures
21.2. Routine Reindexing
21.3. Log File Maintenance
22. Backup and Restore
22.1. SQL Dump
22.1.1. Restoring the dump
22.1.2. Using pg_dumpall
22.1.3. Handling large databases
22.1.4. Caveats
22.2. File system level backup
22.3. On-line backup and point-in-time recovery (PITR)
22.3.1. Setting up WAL archiving
22.3.2. Making a Base Backup
22.3.3. Recovering with an On-line Backup
22.3.4. Timelines
22.3.5. Caveats
22.4. Migration Between Releases
23. Monitoring Database Activity
23.1. Standard Unix Tools
23.2. The Statistics Collector
23.2.1. Statistics Collection Configuration
23.2.2. Viewing Collected Statistics
23.3. Viewing Locks
24. Monitoring Disk Usage
24.1. Determining Disk Usage
24.2. Disk Full Failure
25. Write-Ahead Logging (WAL)
25.1. Benefits of WAL
25.2. WAL Configuration
25.3. Internals
26. Regression Tests
26.1. Running the Tests
26.2. Test Evaluation
26.2.1. Error message differences
26.2.2. Locale differences
26.2.3. Date and time differences
26.2.4. Floating-point differences
26.2.5. Row ordering differences
26.2.6. The random test
26.3. Platform-specific comparison files