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 see Part VI.

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 Part I and Part II for additional information.

Table of Contents

16. Installation from Binaries
17. Installation from Source Code
17.1. Short Version
17.2. Requirements
17.3. Getting the Source
17.4. Installation Procedure
17.5. Post-Installation Setup
17.6. Supported Platforms
17.7. Platform-Specific Notes
18. Installation from Source Code on Windows
18.1. Building with Visual C++ or the Microsoft Windows SDK
19. Binary Installation
19.1. Installing PostgreSQL for 1C on Linux
19.2. Installing PostgreSQL for 1C on Windows
19.3. Installing Additional Supplied Modules
19.4. Migrating to PostgreSQL
20. Server Setup and Operation
20.1. The PostgreSQL User Account
20.2. Creating a Database Cluster
20.3. Starting the Database Server
20.4. Managing Kernel Resources
20.5. Shutting Down the Server
20.6. Upgrading a PostgreSQL Cluster
20.7. Preventing Server Spoofing
20.8. Encryption Options
20.9. Secure TCP/IP Connections with SSL
20.10. Secure TCP/IP Connections with GSSAPI Encryption
20.11. Secure TCP/IP Connections with SSH Tunnels
20.12. Registering Event Log on Windows
21. Server Configuration
21.1. Setting Parameters
21.2. File Locations
21.3. Connections and Authentication
21.4. Resource Consumption
21.5. Write Ahead Log
21.6. Replication
21.7. Query Planning
21.8. Error Reporting and Logging
21.9. Run-time Statistics
21.10. Automatic Vacuuming
21.11. Client Connection Defaults
21.12. Lock Management
21.13. Version and Platform Compatibility
21.14. Error Handling
21.15. Preset Options
21.16. Customized Options
21.17. Developer Options
21.18. Short Options
22. Client Authentication
22.1. The pg_hba.conf File
22.2. User Name Maps
22.3. Authentication Methods
22.4. Trust Authentication
22.5. Password Authentication
22.6. GSSAPI Authentication
22.7. SSPI Authentication
22.8. Ident Authentication
22.9. Peer Authentication
22.10. LDAP Authentication
22.11. RADIUS Authentication
22.12. Certificate Authentication
22.13. PAM Authentication
22.14. BSD Authentication
22.15. Authentication Problems
23. Database Roles
23.1. Database Roles
23.2. Role Attributes
23.3. Role Membership
23.4. Dropping Roles
23.5. Predefined Roles
23.6. Function Security
24. Managing Databases
24.1. Overview
24.2. Creating a Database
24.3. Template Databases
24.4. Database Configuration
24.5. Destroying a Database
24.6. Tablespaces
25. Localization
25.1. Locale Support
25.2. Collation Support
25.3. Character Set Support
26. Routine Database Maintenance Tasks
26.1. Routine Vacuuming
26.2. Routine Reindexing
26.3. Log File Maintenance
27. Backup and Restore
27.1. SQL Dump
27.2. File System Level Backup
27.3. Continuous Archiving and Point-in-Time Recovery (PITR)
28. High Availability, Load Balancing, and Replication
28.1. Comparison of Different Solutions
28.2. Log-Shipping Standby Servers
28.3. Failover
28.4. Hot Standby
29. Monitoring Database Activity
29.1. Standard Unix Tools
29.2. The Cumulative Statistics System
29.3. Viewing Locks
29.4. Progress Reporting
29.5. Dynamic Tracing
30. Monitoring Disk Usage
30.1. Determining Disk Usage
30.2. Disk Full Failure
31. Reliability and the Write-Ahead Log
31.1. Reliability
31.2. Data Checksums
31.3. Write-Ahead Logging (WAL)
31.4. Asynchronous Commit
31.5. WAL Configuration
31.6. WAL Internals
32. Logical Replication
32.1. Publication
32.2. Subscription
32.3. Row Filters
32.4. Column Lists
32.5. Conflicts
32.6. Restrictions
32.7. Architecture
32.8. Monitoring
32.9. Security
32.10. Configuration Settings
32.11. Quick Setup
33. Just-in-Time Compilation (JIT)
33.1. What Is JIT compilation?
33.2. When to JIT?
33.3. Configuration
33.4. Extensibility
34. Regression Tests
34.1. Running the Tests
34.2. Test Evaluation
34.3. Variant Comparison Files
34.4. TAP Tests
34.5. Test Coverage Examination