Part III. Server Administration

This part covers topics that are of interest to a Postgres Pro 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 Postgres Pro 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 Postgres Pro database system. Readers are encouraged to look at Part I and Part II for additional information.

Table of Contents

16. Binary Installation
16.1. Installing Postgres Pro Standard on Linux
16.2. Installing Postgres Pro on Windows
16.3. Installing Additional Supplied Modules
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
18.2. Building libpq with Visual C++ or Borland C++
19. Server Setup and Operation
19.1. The Postgres Pro User Account
19.2. Creating a Database Cluster
19.3. Starting the Database Server
19.4. Managing Kernel Resources
19.5. Shutting Down the Server
19.6. Upgrading a Postgres Pro Cluster
19.7. Preventing Server Spoofing
19.8. Encryption Options
19.9. Secure TCP/IP Connections with SSL
19.10. Secure TCP/IP Connections with SSH Tunnels
19.11. Registering Event Log on Windows
20. Server Configuration
20.1. Setting Parameters
20.2. File Locations
20.3. Connections and Authentication
20.4. Resource Consumption
20.5. Write Ahead Log
20.6. Replication
20.7. Query Planning
20.8. Error Reporting and Logging
20.9. Run-time Statistics
20.10. Automatic Vacuuming
20.11. Client Connection Defaults
20.12. Lock Management
20.13. Version and Platform Compatibility
20.14. Error Handling
20.15. Preset Options
20.16. Customized Options
20.17. Developer Options
20.18. Short Options
21. Client Authentication
21.1. The pg_hba.conf File
21.2. User Name Maps
21.3. Authentication Methods
21.4. Authentication Problems
22. Database Roles
22.1. Database Roles
22.2. Role Attributes
22.3. Role Membership
22.4. Dropping Roles
22.5. Default Roles
22.6. Function Security
23. Managing Databases
23.1. Overview
23.2. Creating a Database
23.3. Template Databases
23.4. Database Configuration
23.5. Destroying a Database
23.6. Tablespaces
24. Localization
24.1. Locale Support
24.2. Collation Support
24.3. Character Set Support
25. Routine Database Maintenance Tasks
25.1. Routine Vacuuming
25.2. Routine Reindexing
25.3. Log File Maintenance
26. Backup and Restore
26.1. SQL Dump
26.2. File System Level Backup
26.3. Continuous Archiving and Point-in-Time Recovery (PITR)
27. High Availability, Load Balancing, and Replication
27.1. Comparison of Different Solutions
27.2. Log-Shipping Standby Servers
27.3. Failover
27.4. Alternative Method for Log Shipping
27.5. Hot Standby
28. Recovery Configuration
28.1. Archive Recovery Settings
28.2. Recovery Target Settings
28.3. Standby Server Settings
29. Monitoring Database Activity
29.1. Standard Unix Tools
29.2. The Statistics Collector
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. Write-Ahead Logging (WAL)
31.3. Asynchronous Commit
31.4. WAL Configuration
31.5. WAL Internals
32. Regression Tests
32.1. Running the Tests
32.2. Test Evaluation
32.3. Variant Comparison Files
32.4. TAP Tests
32.5. Test Coverage Examination