PostgreSQL: Up and Running. A Practical Guide to the Advanced Open Source Database. 3rd Edition (e-book)

PostgreSQL: Up and Running. A Practical Guide to the Advanced Open Source Database. 3rd Edition (e-book) - Opis

Thinking of migrating to PostgreSQL? This clear, fast-paced introduction helps you understand and use this open source database system. Not only will you learn about the enterprise class features in versions 9.5 to 10, you...ll also discover that PostgeSQL is more than a database system-it...s an impressive application platform as well.With examples throughout, this book shows you how to achieve tasks that are difficult or impossible in other databases. This third edition covers new features, such as ANSI-SQL constructs found only in proprietary databases until now: foreign data wrapper (FDW) enhancements; new full text functions and operator syntax introduced in version 9.6; XML constructs new in version 10; query parallelization features introduced in 9.6 and enhanced in 10; built-in logical replication introduced in Version 10.e.If you...re a current PostgreSQL user, you...ll pick up gems you may have missed before.Learn basic administration tasks such as role management, database creation, backup, and restoreApply the psql command-line utility and the pgAdmin graphical administration toolExplore PostgreSQL tables, constraints, and indexesLearn powerful SQL constructs not generally found in other databasesUse several different languages to write database functionsTune your queries to run as fast as your hardware will allowQuery external and variegated data sources with foreign data wrappersLearn how to use built-in replication to replicate data Spis treści:
Preface
Audience
For More Information on PostgreSQL
Code and Output Formatting
Conventions (...) więcej Used in This Book
Using Code Examples
OReilly Safari
How to Contact Us
1. The Basics
Why PostgreSQL?
Why Not PostgreSQL?
Where to Get PostgreSQL
Administration Tools
psql
pgAdmin
phpPgAdmin
Adminer
PostgreSQL Database Objects
Whats New in Latest Versions of PostgreSQL?
Why Upgrade?
Features Introduced in PostgreSQL 10
Features Introduced in PostgreSQL 9.6
Features Introduced in PostgreSQL 9.5
Features Introduced in PostgreSQL 9.4
Database Drivers
Where to Get Help
Notable PostgreSQL Forks
2. Database Administration
Configuration Files
Making Configurations Take Effect
Reloading
Restarting
The postgresql.conf File
Checking postgresql.conf settings
Changing the postgresql.conf settings
I edited my postgresql.conf and now my server wont
start.
The pg_hba.conf File
I edited my pg_hba.conf and now my server is broken.
Authentication methods
Managing Connections
Check for Queries Being Blocked
Roles
Creating Login Roles
Creating Group Roles
Database Creation
Template Databases
Using Schemas
Privileges
Types of Privileges
Getting Started
GRANT
Default Privileges
Privilege Idiosyncrasies
Extensions
Installing Extensions
Step 1: Installing on the server
Step 2: Installing into a database
Upgrading to the new extension model
Common Extensions
Popular extensions
Classic extensions
Backup and Restore
Selective Backup Using pg_dump
Systemwide Backup Using pg_dumpall
Restoring Data
Using psql to restore plain-text SQL backups
Using pg_restore
Managing Disk Storage with Tablespaces
Creating Tablespaces
Moving Objects Among Tablespaces
Verboten Practices
Dont Delete PostgreSQL Core System Files and Binaries
Dont Grant Full OS Administrative Privileges to the Postgres
System Account (postgres)
Dont Set shared_buffers Too High
Dont Try to Start PostgreSQL on a Port Already in Use
3. psql
Environment Variables
Interactive versus Noninteractive psql
psql Customizations
Custom Prompts
Timing Executions
Autocommit Commands
Shortcuts
Retrieving Prior Commands
psql Gems
Executing Shell Commands
Watching Statements
Retrieving Details of Database Objects
Crosstabs
Dynamic SQL Execution
Importing and Exporting Data
psql Import
psql Export
Copying from or to Program
Basic Reporting
4. Using pgAdmin
Getting Started
Overview of Features
Connecting to a PostgreSQL Server
Navigating pgAdmin
pgAdmin Features
Autogenerating Queries from Table Definitions
Accessing psql from pgAdmin3
Editing postgresql.conf and pg_hba.conf from pgAdmin3
Creating Database Assets and Setting Privileges
Creating databases and other database assets
Privilege management
Import and Export
Importing files
Exporting queries as a structured file or report in
pgAdmin
Backup and Restore
Backing up an entire database
Backing up systemwide objects
Selective backup of database assets
pgScript
Graphical Explain
Job Scheduling with pgAgent
Installing pgAgent
Scheduling Jobs
Helpful pgAgent Queries
5. Data Types
Numerics
Serials
Generate Series Function
Textuals
String Functions
Splitting Strings into Arrays, Tables, or Substrings
Regular Expressions and Pattern Matching
Temporals
Time Zones: What They Are and Are Not
Datetime Operators and Functions
Arrays
Array Constructors
Unnesting Arrays to Rows
Array Slicing and Splicing
Referencing Elements in an Array
Array Containment Checks
Range Types
Discrete Versus Continuous Ranges
Built-in Range Types
Defining Ranges
Defining Tables with Ranges
Range Operators
Overlap operator
Contains and contained in operators
JSON
Inserting JSON Data
Querying JSON
Outputting JSON
Binary JSON: jsonb
Editing JSONB data
XML
Inserting XML Data
Querying XML Data
Full Text Search
FTS Configurations
TSVectors
TSQueries
Using Full Text Search
Ranking Results
Full Text Stripping
Full Text Support for JSON and JSONB
Custom and Composite Data Types
All Tables Are Custom Data Types
Building Custom Data Types
Composites and NULLs
Building Operators and Functions for Custom Types
6. Tables, Constraints, and Indexes
Tables
Basic Table Creation
Inherited Tables
Partitioned Tables
Unlogged Tables
TYPE OF
Constraints
Foreign Key Constraints
Unique Constraints
Check Constraints
Exclusion Constraints
Indexes
PostgreSQL Stock Indexes
Operator Classes
Functional Indexes
Partial Indexes
Multicolumn Indexes
7. SQL: The PostgreSQL Way
Views
Single Table Views
Using Triggers to Update Views
Materialized Views
Handy Constructions
DISTINCT ON
LIMIT and OFFSET
Shorthand Casting
Multirow Insert
ILIKE for Case-Insensitive Search
ANY Array Search
Set-Returning Functions in SELECT
Restricting DELETE, UPDATE, and SELECT from Inherited
Tables
DELETE USING
Returning Affected Records to the User
UPSERTs: INSERT ON CONFLICT UPDATE
Composite Types in Queries
Dollar Quoting
DO
FILTER Clause for Aggregates
Percentiles and Mode
Window Functions
PARTITION BY
ORDER BY
Common Table Expressions
Basic CTEs
Writable CTEs
Recursive CTE
Lateral Joins
WITH ORDINALITY
GROUPING SETS, CUBE, ROLLUP
8. Writing Functions
Anatomy of PostgreSQL Functions
Function Basics
Triggers and Trigger Functions
Aggregates
Trusted and Untrusted Languages
Writing Functions with SQL
Basic SQL Function
Writing SQL Aggregate Functions
Writing PL/pgSQL Functions
Basic PL/pgSQL Function
Writing Trigger Functions in PL/pgSQL
Writing PL/Python Functions
Basic Python Function
Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions
Basic Functions
Writing Aggregate Functions with PL/V8
Writing Window Functions in PL/V8
9. Query Performance Tuning
EXPLAIN
EXPLAIN Options
Sample Runs and Output
Graphical Outputs
Gathering Statistics on Statements
Writing Better Queries
Overusing Subqueries in SELECT
Avoid SELECT *
Make Good Use of CASE
Using FILTER Instead of CASE
Parallelized Queries
What Does a Parallel Query Plan Look Like?
Parallel Scans
Parallel Joins
Guiding the Query Planner
Strategy Settings
How Useful Is Your Index?
Table Statistics
Random Page Cost and Quality of Drives
Caching
10. Replication and External Data
Replication Overview
Replication Jargon
Evolution of PostgreSQL Replication
Third-Party Replication Options
Setting Up Full Server Replication
Configuring the Master
Configuring the Slaves for Full Server Cluster
Replication
Initiating the Streaming Replication Process
Replicating Only Some Tables or Databases with Logical
Replication
Foreign Data Wrappers
Querying Flat Files
Querying Flat Files as Jagged Arrays
Querying Other PostgreSQL Servers
Querying Other Tabular Formats with ogr_fdw
Querying Nonconventional Data Sources
A. Installing PostgreSQL
Windows and Desktop Linux
CentOS, Fedora, Red Hat, Scientific Linux
Debian, Ubuntu
FreeBSD
macOS
B. PostgreSQL Packaged Command-Line Tools
Database Backup Using pg_dump
Server Backup: pg_dumpall
Database Restore: pg_restore
psql Interactive Commands
psql Noninteractive Commands
Index mniej