This part covers topics that are of interest to aPostgreSQL 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 aPostgreSQL 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 seePart 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 thePostgreSQL database system. Readers are encouraged to look atPart I andPart II for additional information.

Table of Contents

15.Installation from Source Code

15.1.Short Version

15.2.Requirements

15.3.Getting The Source

15.4.Installation Procedure

15.5.Post-Installation Setup

15.6.Supported Platforms

15.7.Platform-specific Notes

16.Installation from Source Code onWindows

16.1.Building withVisual C++ or theMicrosoft Windows SDK

16.2.Buildinglibpq withVisual C++ orBorland C++

17.Server Setup and Operation

17.1.ThePostgreSQL 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 aPostgreSQL Cluster

17.7.Preventing Server Spoofing

17.8.Encryption Options

17.9.Secure TCP/IP Connections with SSL

17.10.Secure TCP/IP Connections withSSH Tunnels

17.11.RegisteringEvent Log onWindows

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.Error Handling

18.15.Preset Options

18.16.Customized Options

18.17.Developer Options

18.18.Short Options

19.Client Authentication

19.1.Thepg_hba.conf File

19.2.User Name Maps

19.3.Authentication Methods

19.4.Authentication Problems

20.Database Roles

20.1.Database Roles

20.2.Role Attributes

20.3.Role Membership

20.4.Function and Trigger 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.Alternative Method for Log Shipping

25.5.Hot Standby

26.Recovery Configuration

26.1.Archive Recovery Settings

26.2.Recovery Target Settings

26.3.Standby Server Settings

27.Monitoring Database Activity

27.1.Standard Unix Tools

27.2.The Statistics Collector

27.3.Viewing Locks

27.4.Dynamic Tracing

28.Monitoring Disk Usage

28.1.Determining Disk Usage

28.2.Disk Full Failure

29.Reliability and the Write-Ahead Log

29.1.Reliability

29.2.Write-Ahead Logging (WAL)

29.3.Asynchronous Commit

29.4.WAL Configuration

29.5.WAL Internals

30.Regression Tests

30.1.Running the Tests

30.2.Test Evaluation

30.3.Variant Comparison Files

30.4.TAP Tests

30.5.Test Coverage Examination