Red Hat Database

Administrator and User's Guide

ISBN: N/A
Table of Contents
1. Server Runtime Environment
The Postgres User Account
Creating a Database Cluster
Starting the Database Server
Server Start-up Failures
Client Connection Problems
Runtime Configuration
Planner and Optimizer Tuning
Logging and Debugging
General Operation
Write-Ahead Logging (WAL)
Short Options
Managing Kernel Resources
Shared Memory and Semaphores
Resource Limits
Shutting Down the Server
Secure TCP/IP Connections with SSL
Secure TCP/IP Connections with SSH Tunnels
2. Client Authentication
The pg_hba.conf File
Authentication Methods
Trust Authentication
Password Authentication
Kerberos Authentication
Ident-Based Authentication
Authentication Problems
3. Localization
Locale Support
Overview
Benefits
Troubleshooting
Multibyte Support
Enabling Multibyte Support
Setting the Encoding
Automatic Encoding Translation Between Server and Client
About Unicode
When Translation Is Not Possible
Single-byte Character Set Recoding
4. Managing Databases
Creating a Database
Template Databases
Alternative Locations
Accessing a Database
Destroying a Database
5. Database User and Permission Management
Database Users
User Attributes
Groups
Privileges
Functions and Triggers
6. Procedural Languages
Installing Procedural Languages
7. Backup and Restore
SQL Dump
Using pg_dumpall
Restoring the Dump
Large Databases
Caveats
File System Level Backup
8. Routine Database Maintenance Tasks
General Discussion
Routine Vacuuming
Recovering Disk Space
Updating Planner Statistics
Preventing Transaction ID Wraparound Failures
Log File Maintenance
9. psql
Connecting To A Database
Entering Queries
psql Meta-Commands
Command-line Options
Advanced Features
Variables
SQL Interpolation
Prompting
Miscellaneous
GNU Readline
Examples
Important Issues
10. Overview of PostgreSQL Architecture and Internals
PostgreSQL Architectural Concepts
PostgreSQL Backend Internals Overview
The Path of a Query
How Connections are Established
The Parser Stage
Parser
Transformation Process
The PostgreSQL Rule System
The Rewrite System
Planner/Optimizer
Generating Possible Plans
Executor
11. Performance Tips
Using EXPLAIN
Statistics Used by the Planner
Controlling the Planner with Explicit JOINs
Populating a Database
Disable Autocommit
Use COPY FROM
Remove Indexes
ANALYZE Afterwards
12. Genetic Query Optimization (GEQO)
Query Handling as a Complex Optimization Problem
Genetic Algorithms (GA)
Genetic Query Optimization (GEQO) in PostgreSQL
13. Monitoring Database Activity
Standard Linux Tools
Statistics Collector
Statistics Collection Configuration
Viewing Collected Statistics
14. The Rule System
Query Trees
Views and the Rule System
Implementation of Views in PostgreSQL
How SELECT Rules Work
The Power of Views in PostgreSQL
View Rules in Non-SELECT Statements
Updating Views
Rules on INSERT, UPDATE, and DELETE
How Non-Select Rules Work
Cooperation with Views
Rules and Permissions
Rules versus Triggers
15. Interfacing Extensions To Indexes
About the Red Hat Database System Catalogs
Modifying the Catalogs to Extend Indexes
Access Method Support Routines
Operator Classes
Creating the Operators and Support Routines
Index Cost Estimation Functions
16. Transactions and Locks
Multi-Version Concurrency Control (MVCC)
Transaction Isolation
Read-Committed Isolation Level
Serializable Isolation Level
Data Consistency Checks at the Application Level
Locking and Tables
Table-Level Locks
Row-Level Locks
Deadlocks
Data Consistency Checks at the Application Level
Locking and Indexes
Special Columns
17. Write-Ahead Logging (WAL)
General Description
Benefits of WAL
Future Benefits
Implementation
Database Recovery with WAL
WAL Configuration
18. Frontend/Backend Protocol
Overview
Protocol
Start-up
Query
Function Call
Notification Responses
Canceling Requests in Progress
Termination
SSL Session Encryption
Message Data Types
Message Formats
19. BKI Backend Interface
BKI File Format
BKI Commands
Example
20. System Catalogs
Overview
pg_aggregate
pg_attrdef
pg_attribute
pg_class
pg_database
pg_description
pg_group
pg_index
pg_inherits
pg_language
pg_largeobject
pg_listener
pg_operator
pg_proc
pg_relcheck
pg_rewrite
pg_shadow
pg_statistic
pg_trigger
pg_type
21. Tutorial 1: Getting Started
Architectural Fundamentals
Creating a Database
Accessing a Database
22. Tutorial 2: The SQL Language
Concepts
Creating a New Table
Populating a Table With Rows
Querying a Table
Joins Between Tables
Aggregate Functions
Updates
Deletions
23. Tutorial 3: Advanced Features
Views
Foreign Keys
Transactions
Inheritance
Conclusion
SQL Reference Books
I. Utilities Reference
createdb -- Create a new PostgreSQL database
createlang -- Add a new programming language to a PostgreSQL database
createuser -- Create a new PostgreSQL user
dropdb -- Remove an existing PostgreSQL database
droplang -- Remove a programming language from a PostgreSQL database
dropuser -- Drops (removes) a PostgreSQL user
ecpg --  Embedded SQL C preprocessor
initdb -- Create a new PostgreSQL database cluster
initlocation -- Create a secondary PostgreSQL database storage area
ipcclean -- Clean up shared memory and semaphores from an aborted PostgreSQL server backend.
pgaccess -- PostgreSQL graphical client
pg_config -- Provides information about the installed version of PostgreSQL
pg_ctl -- Starts, stops, or restarts a PostgreSQL server
pg_dump --  Extract a PostgreSQL database into a script file or other archive file
pg_dumpall -- Extract all databases into a script file
pg_passwd -- Manipulate a secondary PostgreSQL password file.
pg_restore --  Restore a PostgreSQL database from an archive file
psql --  PostgreSQL interactive terminal
pgtclsh --  PostgreSQL Tcl shell client
pgtksh --  PostgreSQL Tcl/Tk shell client
postgres -- Run a PostgreSQL single-user backend
postmaster -- PostgreSQL multi-user database server
vacuumdb -- Clean and analyze a PostgreSQL database
Index