PostgreSQL: Up and Running: A Practical Guide to the Advanced Open Source Database

by Regina Obe, Leo Hsu

Overview

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 restore
• Apply the psql command-line utility and the pgAdmin graphical administration tool
• Explore PostgreSQL tables, constraints, and indexes
• Learn powerful SQL constructs not generally found in other databases
• Use several different languages to write database functions
• Tune your queries to run as fast as your hardware will allow
• Query external and variegated data sources with foreign data wrappers
• Learn how to use built-in replication to replicate data

Product Details

ISBN-13:	9781491963418
Publisher:	O'Reilly Media, Incorporated
Publication date:	11/12/2017
Edition description:	3rd ed.
Pages:	312
Sales rank:	667,466
Product dimensions:	6.90(w) x 9.10(h) x 0.70(d)

About the Author

Regina Obe is a co-principal of Paragon Corporation, a database consulting company based in Boston. She has over 15 years of professional experience in various programming languages and database systems, with special focus on spatial databases. She is a member of the PostGIS steering committee and the PostGIS core development team. Regina holds a BS degree in mechanical engineering from the Massachusetts Institute of Technology. She co-authored PostGIS in Action.

Leo Hsu is a co-principal of Paragon Corporation, a database consulting company based in Boston. He has over 15 years of professional experience developing and thinking about databases for organizations large and small. Leo holds an MS degree in engineering of economic systems from Stanford Universityand BS degrees in mechanical engineering and economics from the Massachusetts Institute of Technology. He co-authored PostGIS in Action.

Table of Contents

Preface xi

1 The Basics 1

Why PostgreSQL? 1

Why Not PostgreSQL? 3

Where to Get PostgreSQL 4

Administration Tools 4

psql 4

pg Admin 5

phpPgAdmin 6

Adminer 7

PostgreSQL Database Objects 8

What's New in Latest Versions of PostgreSQL? 13

Why Upgrade? 13

Features Introduced in PostgreSQL 10 13

Features Introduced in PostgreSQL 9.6 15

Features Introduced in PostgreSQL 9.5 16

Features Introduced in PostgreSQL 9.4 17

Database Drivers 19

Where to Get Help 20

Notable PostgreSQL Forks 20

2 Database Administration 23

Configuration Files 23

Making Configurations Take Effect 24

The postgresql.conf File 25

The pg_hba.conf File 29

Managing Connections 32

Check for Queries Being Blocked 34

Roles 34

Creating Login Roles 34

Creating Group Roles 35

Database Creation 38

Template Databases 38

Using Schemas 39

Privileges 41

Types of Privileges 41

Getting Started 42

GRANT 42

Default Privileges 43

Privilege Idiosyncrasies 44

Extensions 45

Installing Extensions 46

Common Extensions 48

Backup and Restore 50

Selective Backup Using pg_drump 51

Systemwide Backup Using pg_dumpall 53

Restoring Data 53

Managing Disk Storage with Tablespaces 55

Creating Tablespaces 55

Moving Objects Among Tablespaces 56

Verboten Practices 56

Don't Delete PostgreSQL Core System Files and Binaries 56

Don't Grant Full OS Administrative Privileges to the Postgres System Account (postgres) 57

Don't Set shared_buffers Too High 58

Don't Try to Start PostgreSQL on a Port Already in Use 58

3 psql 59

Environment Variables 59

Interactive versus Noninteractive psql 60

psql Customizations 60

Custom Prompts 61

Timing Executions 63

Autocommit Commands 63

Shortcuts 64

Retrieving Prior Commands 64

psql Gems 65

Executing Shell Commands 65

Watching Statements 65

Retrieving Details of Database Objects 66

Crosstabs 66

Dynamic SQL Execution 67

Importing and Exporting Data 68

psql Import 68

psql Export 70

Copying from or to Program 70

Basic Reporting 71

4 Using pgAdmin 75

Getting Started 76

Overview of Features 76

Connecting to a PostgreSQL Server 77

Navigating pgAdmin 78

pgAdmin Features 79

Autogenerating Queries from Table Definitions 79

Accessing psql from pgAdmin3 80

Editing postgresql.conf and pg_hba.conf from pgAdmin3 81

Creating Database Assets and Setting Privileges 81

Import and Export 84

Backup and Restore 87

pgScript 90

Graphical Explain 92

Job Scheduling with pgAgent 94

Installing pgAgent 94

Scheduling Jobs 95

Helpful pgAgent Queries 97

5 Data Types 99

Numerics 99

Serials 100

Generate Series Function 100

Textuals 101

String Functions 102

Splitting Strings into Arrays, Tables, or Substrings 103

Regular Expressions and Pattern Matching 103

Temporals 105

Time Zones: What They Are and Are Not 107

Datetime Operators and Functions 109

Arrays 111

Array Constructors 111

Unnesting Arrays to Rows 113

Array Slicing and Splicing 114

Referencing Elements in an Array 114

Array Containment Checks 115

Range Types 116

Discrete Versus Continuous Ranges 116

Built-in Range Types 117

Defining Ranges 117

Defining Tables with Ranges 118

Range Operators 119

Json 120

Inserting JSON Data 120

Querying JSON 121

Outputting/SON 122

Binary JSON; jsonb 123

Editing JSONB data 125

XML 127

Inserting XML Data 127

Querying XML Data 128

Full Text Search 130

FTS Configurations 131

TSVectors 134

TSQueries 136

Using Full Text Search 138

Ranking Results 139

Full Text Stripping 140

Full Text Support for JSON and JSONB 141

Custom and Composite Data Types 142

AH Tables Are Custom Data Types 142

Building Custom Data Types 143

Composites and NULLs 143

Building Operators and Functions for Custom Types 144

6 Tables, Constraints, and Indexes 147

Tables 147

Basic Table Creation 147

Inherited Tables 149

Partitioned Tables 150

Unlogged Tables 152

Type of 153

Constraints 154

Foreign Key Constraints 154

Unique Constraints 155

Check Constraints 155

Exclusion Constraints 156

Indexes 157

PostgreSQL Stock Indexes 157

Operator Classes 160

Functional Indexes 161

Partial Indexes 162

Multicolumn Indexes 163

7 SQL: The PostgreSQL Way 165

Views 165

Single Table Views 166

Using Triggers to Update Views 167

Materialized Views 169

Handy Constructions 171

Distinct On 171

Limit and Offset 172

Shorthand Casting 172

Multirow Insert 173

ILIKE for Case-Insensitive Search 173

ANY Array Search 174

Set-Returning Functions in SELECT 174

Restricting DELETE, UPDATE, and SELECT from Inherited Tables 175

Delete using 175

Returning Affected Records to the User 176

UPSERTs: INSERT ON CONFLICT UPDATE 176

Composite Types in Queries 177

Dollar Quoting 179

DO 179

FILTER Clause for Aggregates 181

Percentiles and Mode 182

Window Functions 184

PARTITION BY 185

ORDER BY 186

Common Table Expressions 188

Basic CTEs 188

Writable CTEs 189

Recursive CTE 190

Lateral Joins 191

WITH ORDINALITY 193

GROUPING SETS, CUBE, ROLLUP 195

8 Writing Functions 199

Anatomy of PostgreSQL Functions 199

Function Basics 200

Triggers and Trigger Functions 203

Aggregates 204

Trusted and Untrusted Languages 205

Writing Functions with SQL 206

Basic SQL Function 206

Writing SQL Aggregate Functions 208

Writing PL/pgSQL Functions 210

Basic PL/pgSQL Function 210

Writing Trigger Functions in PL/pgSQL 210

Writing PL/Python Functions 211

Basic Python Function 212

Writing PL/V8, PL/CoffeeScript, and PL/LiveScript Functions 214

Basic Functions 215

Writing Aggregate Functions with PL/V8 217

Writing Window Functions in PL/V8 218

9 Query Performance Tuning 221

EXPLAIN 221

EXPLAIN Options 221

Sample Runs and Output 222

Graphical Outputs 225

Gathering Statistics on Statements 227

Writing Better Queries 228

Overusing Subqueries in SELECT 228

Avoid SELECT* 231

Make Good Use of CASE 232

Using FILTER Instead of CASE 233

Parallelized Queries 233

What Does a Parallel Query Plan Look Like? 234

Parallel Scans 237

Parallel loins 238

Guiding the Query Planner 238

Strategy Settings 238

How Useful Is Your Index? 239

Table Statistics 240

Random Page Cost and Quality of Drives 242

Caching 243

10 Replication and External Data 245

Replication Overview 245

Replication Jargon 246

Evolution of PostgreSQL Replication 248

Third-Party Replication Options 248

Setting Up Full Server Replication 249

Configuring the Master 249

Configuring the Slaves for Full Server Cluster Replication 251

Initiating the Streaming Replication Process 252

Replicating Only Some Tables or Databases with Logical Replication 252

Foreign Data Wrappers 254

Querying Flat Files 255

Querying Flat Files as Jagged Arrays 256

Querying Other PostgreSQL Servers 257

Querying Other Tabular Formats with ogr_fdw 259

Querying Nonconventional Data Sources 262

A Installing PostgreSQL 265

B PostgreSQL Packaged Command-Line Tools 271

Index 281