You are previewing PostgreSQL: Up and Running.

PostgreSQL: Up and Running

Cover of PostgreSQL: Up and Running by Regina Obe... Published by O'Reilly Media, Inc.
  1. PostgreSQL: Up and Running
  2. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  3. Preface
    1. What Makes PostgreSQL Special and Why Use It?
    2. Why Not PostgreSQL?
    3. For More Information on PostgreSQL
    4. Conventions Used in This Book
    5. Using Code Examples
    6. Safari® Books Online
    7. How to Contact Us
  4. 1. The Basics
    1. Where to Get PostgreSQL
      1. Notable PostgreSQL Forks
    2. Administration Tools
    3. What’s New in Latest Versions of PostgreSQL?
      1. Why Upgrade?
      2. What to Look for in PostgreSQL 9.2
      3. PostgreSQL 9.1 Improvements
    4. Database Drivers
    5. Server and Database Objects
    6. Where to Get Help
  5. 2. Database Administration
    1. Configuration Files
      1. The postgresql.conf File
      2. The pg_hba.conf File
      3. Reload the Configuration Files
    2. Setting Up Groups and Login Roles (Users)
      1. Creating an Account That Can Log In
      2. Creating Group Roles
      3. Roles Inheriting Rights
    3. Databases and Management
      1. Creating and Using a Template Database
      2. Organizing Your Database Using Schemas
      3. Permissions
    4. Extensions and Contribs
      1. Installing Extensions
      2. Common Extensions
    5. Backup
      1. Selective Backup Using pg_dump
      2. Systemwide Backup Using pg_dumpall
    6. Restore
      1. Terminating Connections
      2. Using psql to Restore Plain Text SQL backups
      3. Using pg_restore
    7. Managing Disk Space with Tablespaces
      1. Creating Tablespaces
      2. Moving Objects Between Tablespaces
    8. Verboten
      1. Delete PostgreSQL Core System Files and Binaries
      2. Giving Full Administrative Rights to the Postgres System (Daemon) Account
      3. Setting shared_buffers Too High
      4. Trying to Start PostgreSQL on a Port Already in Use
  6. 3. psql
    1. Interactive psql
    2. Non-Interactive psql
    3. Session Configurations
      1. Changing Prompts
      2. Timing Details
      4. Shortcuts
      5. Retrieving Prior Commands
    4. psql Gems
      1. Executing Shell Commands
      2. Lists and Structures
    5. Importing and Exporting Data
    6. Basic Reporting
  7. 4. Using pgAdmin
    1. Getting Started
      1. Overview of Features
      2. Connecting to a PostgreSQL server
      3. Navigating pgAdmin
    2. pgAdmin Features
      1. Accessing psql from pgAdmin
      2. Editing postgresql.conf and pg_hba.conf from pgAdmin
      3. Creating Databases and Setting Permissions
      4. Backup and Restore
    3. pgScript
    4. Graphical Explain
    5. Job Scheduling with pgAgent
      1. Installing pgAgent
      2. Scheduling Jobs
      3. Helpful Queries
  8. 5. Data Types
    1. Numeric Data Types
      1. Serial
      2. Generate Series Function
    2. Arrays
      1. Array Constructors
      2. Referencing Elements in An Array
      3. Array Slicing and Splicing
    3. Character Types
      1. String Functions
      2. Splitting Strings into Arrays, Tables, or Substrings
      3. Regular Expressions and Pattern Matching
    4. Temporal Data Types
      1. Time Zones: What It Is and What It Isn’t
      2. Operators and Functions for Date and Time Data Types
    5. XML
      1. Loading XML Data
      2. Querying XML Data
    6. Custom and Composite Data Types
      1. All Tables Are Custom
      2. Building Your Own Custom Type
  9. 6. Of Tables, Constraints, and Indexes
    1. Tables
      1. Table Creation
      2. Multi-Row Insert
      3. An Elaborate Insert
    2. Constraints
      1. Foreign Key Constraints
      2. Unique Constraints
      3. Check Constraints
      4. Exclusion Constraints
    3. Indexes
      1. PostgreSQL Stock Indexes
      2. Operator Class
      3. Functional Indexes
      4. Partial Indexes
      5. Multicolumn Indexes
  10. 7. SQL: The PostgreSQL Way
    1. SQL Views
    2. Window Functions
      1. Partition By
      2. Order By
    3. Common Table Expressions
      1. Standard CTE
      2. Writeable CTEs
      3. Recursive CTE
    4. Constructions Unique to PostgreSQL
      1. DISTINCT ON
      2. LIMIT and OFFSET
      3. Shorthand Casting
      4. ILIKE for Case Insensitive Search
      5. Set Returning Functions in SELECT
      6. Selective DELETE, UPDATE, and SELECT from Inherited Tables
      7. RETURNING Changed Records
      8. Composite Types in Queries
  11. 8. Writing Functions
    1. Anatomy of PostgreSQL Functions
      1. Function Basics
      2. Trusted and Untrusted Languages
    2. Writing Functions with SQL
    3. Writing PL/pgSQL Functions
    4. Writing PL/Python Functions
      1. Basic Python Function
    5. Trigger Functions
    6. Aggregates
  12. 9. Query Performance Tuning
    2. Writing Better Queries
      1. Overusing Subqueries in SELECT
      2. Avoid SELECT *
      3. Make Good Use of CASE
    3. Guiding the Query Planner
      1. Strategy Settings
      2. How Useful Is Your Index?
      3. Table Stats
      4. Random Page Cost and Quality of Drives
    4. Caching
  13. 10. Replication and External Data
    1. Replication Overview
      1. Replication Lingo
      2. PostgreSQL Built-in Replication Advancements
      3. Third-Party Replication Options
    2. Setting Up Replication
      1. Configuring the Master
      2. Configuring the Slaves
      3. Initiate the Replication Process
    3. Foreign Data Wrappers (FDW)
      1. Querying Simple Flat File Data Sources
      2. Querying More Complex Data Sources
  14. A. Install, Hosting, and Command-Line Guides
    1. Installation Guides and Distributions
      1. Windows, Mac OS X, Linux Desktops
      2. Other Linux, Unix, Mac Distributions
    2. Where to Host PostgreSQL
      1. Virtual Private Server (VPS)/Virtual Dedicated Server
      2. Cloud Server Hosters
      3. PostgreSQL Database as a Service
    3. PostgreSQL Packaged Command-Line Tools
      1. Database Backup: pg_dump
      2. Server Backup: pg_dumpall
      3. Database Backup: pg_restore
      4. psql: Interactive and Scriptable
  15. About the Authors
  16. SPECIAL OFFER: Upgrade this ebook with O’Reilly
  17. Copyright

Appendix A. Install, Hosting, and Command-Line Guides

Installation Guides and Distributions

Windows, Mac OS X, Linux Desktops

EnterpriseDB, a company devoted to popularizing PostgreSQL technology, builds installers for Windows, Mac OS X, and desktop versions of Linux. For Windows users, this is the preferred installer to use. Mac OS X and Linux opinions vary depending on what you are doing. For example, the EnterpriseDb PostGIS installers for Mac OS X and Linux aren’t always kept up to date with the latest releases of PostGIS, so PostGIS Mac OS X and Linux users, tend to prefer other distributions. EnterpriseDb also distribute binaries for beta versions of coming PostgreSQL versions. The installers are super easy to use. They come packaged with PgAdmin GUI Administration tool and a stack builder that allows you to install additional add-ons like JDBC, .NET drivers, Ruby, PostGIS, phpPgAdmin, pgAgent, WaveMaker, and others.

EnterpriseDB has two offerings: the official, open source PostgreSQL, which EnterpriseDB calls the Community Edition, and their proprietary edition called Advanced Plus. The proprietary fork offers Oracle compatibility and enhanced management features. Don’t get confused between the two when you download. In this book, we will focus on the official PostgreSQL, not Advanced Plus; however, much of the material apply more or less equally to Advanced Plus.


If you want to try out different versions of PostgreSQL on the same machine or want to run it from a USB device. ...

The best content for your career. Discover unlimited learning on demand for around $1/day.