Part III. Server Administration

This part covers topics that are of interest to a Postgres Pro administrator. This includes installation, configuration of the server, management of users and databases, and maintenance tasks. Anyone running Postgres Pro server, even for personal use, but especially in production, should be familiar with these topics.

The information attempts to be in the order in which a new user should read it. The chapters are self-contained and can be read individually as desired. The information is presented in a narrative form in topical units. Readers looking for a complete description of a command are encouraged to review the 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. 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 review the Part I and Part II parts for additional information.

Table of Contents

16. Binary Installation
16.1. Installing Postgres Pro Standard
16.2. Installing Additional Supplied Modules
16.3. Migrating to Postgres Pro
17. Server Setup and Operation
17.1. The Postgres Pro User Account
17.2. Creating a Database Cluster
17.3. Starting the Database Server
17.4. Managing Kernel Resources
17.5. Shutting Down the Server
17.6. Upgrading a Postgres Pro Cluster
17.7. Preventing Server Spoofing
17.8. Encryption Options
17.9. Secure TCP/IP Connections with SSL
17.10. Secure TCP/IP Connections with GSSAPI Encryption
17.11. Secure TCP/IP Connections with SSH Tunnels
17.12. Registering Event Log on Windows
18. Server Configuration
18.1. Setting Parameters
18.2. File Locations
18.3. Connections and Authentication
18.4. Resource Consumption
18.5. Write Ahead Log
18.6. Replication
18.7. Query Planning
18.8. Error Reporting and Logging
18.9. Run-time Statistics
18.10. Automatic Vacuuming
18.11. Client Connection Defaults
18.12. Lock Management
18.13. Version and Platform Compatibility
18.14. Memory Purge
18.15. Error Handling
18.16. Preset Options
18.17. Customized Options
18.18. Developer Options
18.19. Short Options
19. Client Authentication
19.1. The pg_hba.conf File
19.2. User Name Maps
19.3. Authentication Methods
19.4. Trust Authentication
19.5. Password Authentication
19.6. GSSAPI Authentication
19.7. SSPI Authentication
19.8. Ident Authentication
19.9. Peer Authentication
19.10. LDAP Authentication
19.11. RADIUS Authentication
19.12. Certificate Authentication
19.13. PAM Authentication
19.14. BSD Authentication
19.15. Authentication Problems
20. Database Roles
20.1. Database Roles
20.2. Role Attributes
20.3. Role Membership
20.4. Dropping Roles
20.5. Predefined Roles
20.6. Function Security
21. Managing Databases
21.1. Overview
21.2. Creating a Database
21.3. Template Databases
21.4. Database Configuration
21.5. Destroying a Database
21.6. Tablespaces
22. Localization
22.1. Locale Support
22.2. Collation Support
22.3. Character Set Support
23. Routine Database Maintenance Tasks
23.1. Routine Vacuuming
23.2. Routine Reindexing
23.3. Log File Maintenance
24. Backup and Restore
24.1. SQL Dump
24.2. File System Level Backup
24.3. Continuous Archiving and Point-in-Time Recovery (PITR)
25. High Availability, Load Balancing, and Replication
25.1. Comparison of Different Solutions
25.2. Log-Shipping Standby Servers
25.3. Failover
25.4. Hot Standby
26. Monitoring Database Activity
26.1. Standard Unix Tools
26.2. The Cumulative Statistics System
26.3. Viewing Locks
26.4. Progress Reporting
26.5. Monitoring Disk Usage
27. Reliability and the Write-Ahead Log
27.1. Reliability
27.2. Data Checksums
27.3. Write-Ahead Logging (WAL)
27.4. Asynchronous Commit
27.5. WAL Configuration
27.6. WAL Internals
28. Logical Replication
28.1. Publication
28.2. Subscription
28.3. Logical Replication Failover
28.4. Row Filters
28.5. Column Lists
28.6. Conflicts
28.7. Restrictions
28.8. Architecture
28.9. Monitoring
28.10. Security
28.11. Configuration Settings
28.12. Quick Setup
29. Just-in-Time Compilation (JIT)
29.1. What Is JIT compilation?
29.2. When to JIT?
29.3. Configuration
29.4. Extensibility
30. Enhanced Security
30.1. Memory Purge
30.2. Integrity Checks
30.3. Separation of Duties between Privileged DBMS Users
31. Troubleshooting