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

Chapter 10. Replication and External Data

PostgreSQL has a number of options for sharing data with external servers or data sources. The first option is PostgreSQL’s own built-in replication, which allows you to have a readied copy of your server on another PostgreSQL server. The second option, unveiled in 9.1, is the Foreign Data Wrapper, which allows you to query and copy data from many kinds of external data resources utilizing the SQL/Management of External Datasource (MED) standard. The third option is to use third-party add-ons, many of which are freely available and time-tested.

Replication Overview

You can probably enumerate countless reasons for the need to replicate, but they all boil down to two: availability and scalability. If your main server goes down you want another to immediately assume its role. For small databases, you could just make sure you have another physical server ready and restore the database onto it, but for large databases (say, in the terabytes), the restore itself could take many hours. To avoid the downtime, you’ll need to replicate. The other main reason is for scalability. You set up a database to handle your collection of fancy elephant beetles. After a few years of unbridled breeding, you now have millions of fancy elephant beetles. People all over the world now come to your site to check out the beetles. You’re overwhelmed by the traffic. Replication comes to your aid; you set up a read-only slave server to replicate with your main server. People ...

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