Red Hat Database

Administrator and User's Guide


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
Run-time 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
Password Authentication
Kerberos Authentication
Ident-Based Authentication
3. Localization
Locale Support
Overview
Benefits
Troubleshooting
Multibyte Support
Enabling Multibyte Support
Setting the Encoding
Automatic Encoding Translation Between Backend and Frontend
About Unicode
When Translation Is Not Possible
Single-byte Character Set Recoding
4. Managing Databases
Creating a Database
Alternative Locations
Accessing a Database
Destroying a Database
5. Procedural Languages
Installing Procedural Languages
6. Database User and Permission Management
Database Users
User Attributes
Groups
Privileges
Functions and Triggers
7. Backup and Restore
SQL Dump
Using pg_dumpall
Restoring the Dump
Large Databases
Caveats
File System Level Backup
8. psql
Connecting To A Database
Entering Queries
psql Meta-Commands
Advanced Features
Variables
SQL Interpolation
Prompting
Miscellaneous
GNU Readline
Examples
Important Issues
9. 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
10. Performance Tips
Using EXPLAIN
Controlling the Planner with Explicit JOINs
Populating a Database
Disable Auto-commit
Use COPY FROM
Remove Indexes
11. Genetic Query Optimization (GEQO)
Query Handling as a Complex Optimization Problem
Genetic Algorithms (GA)
Genetic Query Optimization (GEQO) in PostgreSQL
12. The Rule System
Query Trees
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
13. Interfacing Extensions To Indices
About the Red Hat Database System Catalogs
Modifying the Catalogs to Extend Indices
Index Cost Estimation Functions
14. 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
Locking and Indexes
Special Columns
15. Write-Ahead Logging (WAL)
General Description
Benefits of WAL
Future Benefits
Implementation
Database Recovery with WAL
WAL Configuration
16. Frontend/Backend Protocol
Overview
Protocol
Start-up
Query
Function Call
Notification Responses
Canceling Requests in Progress
Termination
Message Data Types
Message Formats
17. BKI Backend Interface
BKI File Format
BKI Commands
Example
18. System Catalogs
Overview
pg_aggregate
pg_attrdef
pg_attribute
pg_class
pg_database
pg_description
pg_group
pg_index
pg_inherits
pg_language
pg_operator
pg_proc
pg_relcheck
pg_shadow
pg_type
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 aborted backends
pgaccess -- PostgreSQL graphical client
pg_config -- Provides information about the installed version of PostgreSQL
pg_ctl -- Starts, stops, or restarts the postmaster
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 text password file
pg_restore --  Restore a PostgreSQL database from an archive file created by pg_dump
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
Administrator and User's Guide