Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13

Cover
Title Page
Copyright and Credits
Dedication
About Packt
Contributors
Table of Contents
Preface
Section 1: Getting Started
Chapter 01: Introduction to PostgreSQL
	Technical requirements
	PostgreSQL at a glance
		A brief history of PostgreSQL
		What's new in PostgreSQL 12?
		What's new in PostgreSQL 13?
		PostgreSQL release policy, version numbers, and life cycle
	Exploring PostgreSQL terminology
	Installing PostgreSQL 12 or higher
		What to install
		Installing PostgreSQL 12 from binary packages
			Installing PostgreSQL 12 on GNU/Linux Debian, Ubuntu, and derivatives
			Installing PostgreSQL 12 on Linux Fedora
			Installing PostgreSQL 12 on FreeBSD
		Installing PostgreSQL from sources
		Installing PostgreSQL via pgenv
	Summary
	References
Chapter 02: Getting to Know Your Cluster
	Technical requirements
	Managing your cluster
		pg_ctl
		PostgreSQL processes
	Connecting to the cluster
		The template databases
		The psql command-line client
			Entering SQL statements via psql
			A glance at the psql commands
		Introducing the connection string
		Solving common connection problems
			Database "foo" does not exist
			Connection refused
			No pg_hba.conf entry
	Exploring the disk layout of PGDATA
		Objects in the PGDATA directory
			Tablespaces
	Exploring configuration files and parameters
	Summary
	References
Chapter 03: Managing Users and Connections
	Introduction to users and groups
	Managing roles
		Creating new roles
			Role passwords, connections, and availability
		Using a role as a group
		Removing an existing role
		Inspecting existing roles
	Managing incoming connections at the role level
		The syntax of pg_hba.conf
		Order of rules in pg_hba.conf
		Merging multiple rules into a single one
		Using groups instead of single roles
		Using files instead of single roles
	Summary
	References
Section 2: Interacting with the Database
Chapter 04: Basic Statements
	Technical requirements
	Setting up our developing environment
	Creating and managing databases
		Creating a database
		Managing databases
			Listing all databases
			Making a new database from a modified template
		Dropping tables and databases
			Dropping tables
			Dropping databases
		Making a database copy
		Confirming the database size
			The psql method
			The SQL method
		Creating a database
	Managing tables
		The EXISTS option
		Managing temporary tables
		Managing unlogged tables
		Creating a table
	Understanding basic table manipulation statements
		Inserting and selecting  data
			NULL values
				Sorting with NULL values
			Creating a table starting from another table
		Updating data
		Deleting data
	Summary
	References
Chapter 05: Advanced Statements
	Exploring the SELECT statement
		Using the like clause
		Using ilike
		Using distinct
		Using limit and offset
		Using subqueries
			Using the IN/NOT IN condition
			Using the EXISTS/NOT EXISTS condition
		Learning joins
			Using INNER JOIN
				INNER JOIN versus EXISTS/IN
			Using  LEFT JOINS
			Using RIGHT JOIN
			Using  FULL OUTER JOIN
			Using SELF JOIN
		Aggregate functions
			UNION/UNION ALL
			EXCEPT/INTERSECT​
	Using UPSERT
		UPSERT – the PostgreSQL way
		Learning the RETURNING clause for INSERT
		Returning tuples out of queries
			UPDATE related to multiple tables
			Exploring UPDATE .... RETURNING
			DELETE .... RETURNING
	Exploring CTEs
		CTE concept
		CTE in PostgreSQL 12
		CTE – some examples
		Query recursion
			Recursive CTEs
	Summary
	References
Chapter 06: Window Functions
	Using basic statement window functions
		Using the PARTITION BY function and WINDOW clause
		Introducing some useful functions
			The ROW_NUMBER function
			The ORDER BY clause
			FIRST_VALUE
			LAST_VALUE
			RANK
			DENSE_RANK 
			The LAG and LEAD functions
			The CUME_DIST function
			The NTILE function
	Using advanced statement window functions
		The frame clause
			ROWS BETWEEN start_point and end_point
			RANGE BETWEEN start_point and end_point
	Summary
	References
Chapter 07: Server-Side Programming
	Exploring data types
		The concept of extensibility
		Standard data types
			Boolean data type
			Numeric data type
				Integer types
				Numbers with a fixed precision data type
				Numbers with an arbitrary precision data type
			Character data type
				Chars with fixed-length data types
				Chars with variable length with a limit data types
				Chars with a variable length without a limit data types
			Date/timestamp data types
				Date data types
				Timestamp data types
			The NoSQL data type
			The hstore data type
			The JSON data type
	Exploring functions and languages
		Functions
		SQL functions
			Basic functions
			SQL functions returning a set of elements
			SQL functions returning a table
			Polymorphic SQL functions
		PL/pgSQL functions
			First overview
			Declaring function parameters
				IN/OUT parameters
				Function volatility categories
			Control structure
			Conditional statements
				IF statements
				CASE statements
			Loop statements
				The record type
			Exception handling statements
	Summary
	References
Chapter 08: Triggers and Rules
	Exploring rules in PostgreSQL
		Understanding the OLD and NEW variables
		Rules on INSERT
			The ALSO option
			The INSTEAD OF option
		Rules on DELETE / UPDATE
			Creating the new_tags table
			Creating two tables
			Managing  rules on INSERT, DELETE, and UPDATE events 
				INSERT rules
				DELETE rules
				UPDATE rules
	Managing triggers in PostgreSQL
		Trigger syntax
		Triggers on INSERT
			The TG_OP variable
		Triggers on UPDATE / DELETE
	Event triggers
		An example of an event trigger
	Summary
	References
Chapter 09: Partitioning
	Basic concepts
		Range partitioning
		List partitioning
		Hash partitioning
		Table inheritance
			Dropping tables
	Exploring partitioning using inheritance
		An example of list partitioning
			Creating tables
			Creating triggers and functions, and inserting data
			Creating triggers and functions and updating data
	Exploring declarative partitioning
		List partitioning
		Range partitioning
			Partition maintenance
	Summary
	References
Section 3: Administering the Cluster
Chapter 10: Users, Roles, and Database Security
	Understanding roles
		Properties related to new objects
		Properties related to superusers
		Properties related to replication
		Properties related to row-level security
		Changing properties of existing roles: the ALTER ROLE statement
			Renaming an existing role
			SESSION_USER versus CURRENT_USER
			Per-role configuration parameters
		Inspecting roles
		Roles that inherit from other roles
			Understanding how privileges are resolved
			Role inheritance overview
	Access control lists
		Default ACLs
			Knowing default ACLs
	Granting and revoking permissions
		Permissions related to tables
		Column-based permissions
		Permissions related to sequences
		Permissions related to schemas
			ALL objects in the schema
		Permissions related to languages
		Permissions related to routines
		Permissions related to databases
		Other GRANT and REVOKE statements
		Assigning the object owner
		Inspecting ACLs
	Row-level security
	Role password encryption
	SSL connections
		Configuring the cluster for SSL
		Connecting to the cluster via SSL
	Summary
	References
Chapter 11: Transactions, MVCC, WALs, and Checkpoints
	Technical requirements
	Introducing transactions
		Comparing implicit and explicit transactions
			Time within transactions
		More about transaction identifiers – the XID wraparound problem
			Virtual and real transaction identifiers
		Multi-version concurrency control
	Transaction isolation levels
		Read uncommitted
		Read Committed
		Repeatable Read
		Serializable
	Explaining MVCC
	Savepoints
	Deadlocks
	How PostgreSQL handles persistency and consistency: WALs
		Write-Ahead Logs (WALs)
			WALs as a rescue method in the event of a crash
			Checkpoints
		Checkpoint configuration parameters
			checkpoint_timeout and max_wal_size
			Checkpoint throttling
			Manually issuing a checkpoint
	VACUUM
		Manual VACUUM
		Automatic VACUUM
	Summary
	References
Chapter 12: Extending the Database - the Extension Ecosystem
	Introducing extensions
		The extension ecosystem
		Extension components
			The control file
		The script file
	Managing extensions
		Creating an extension
		Viewing installed extensions
		Finding out available extension versions
		Altering an existing extension
		Removing an existing extension
	Exploring the PGXN client
		Installing pgxnclient on Debian GNU/Linux and derivates
		Installing pgxnclient on Fedora Linux
		Installing pgxnclient on FreeBSD
		Installing pgxnclient from sources
		The pgxnclient command-line interface
	Installing extensions
		Installing the extension via pgxnclient
		Installing the extension manually
		Using the installed extension
		Removing an installed extension
			Removing an extension via pgxncliet
			Removing a manually installed extension 
	Creating your own extension
		Defining an example extension
		Creating extension files
		Installing the extension
		Creating an extension upgrade
		Performing an extension upgrade
	Summary
	References
Chapter 13: Indexes and Performance Optimization
	Technical requirements
	Execution of a statement
		Execution stages
		The optimizer
		Nodes that the optimizer uses
			Sequential nodes
				Sequential Scan
				Index nodes
				Join nodes
			Parallel nodes
				Gather nodes
				Parallel scans
				Parallel joins
				Parallel aggregations
			When does the optimizer choose a parallel plan?
			Utility nodes
		Node costs
	Indexes
		Index types
		Creating an index
		Inspecting indexes
		Dropping an index
		Invalidating an index
		Rebuilding an index
	The EXPLAIN statement
		EXPLAIN output formats
		EXPLAIN ANALYZE
		EXPLAIN options
	An example of query tuning
	ANALYZE and how to update statistics
	Auto-explain
	Summary
	References
Chapter 14: Logging and Auditing
	Technical requirements
	Introduction to logging
		Where to log
		When to log
		What to log
		A complete example of logging configuration
	Extracting information from logs – PgBadger
		Installing PgBadger
		Configuring PostgreSQL logging for PgBadger usage
		Using PgBadger
		Scheduling PgBadger
	Implementing auditing
		Installing PgAudit
		Configuring PostgreSQL to exploit PgAudit
		Configuring PgAudit
		Auditing by session
		Auditing by role
	Summary
	References
Chapter 15: Backup and Restore
	Technical requirements
	Introducing various types of backups and restores
	Exploring logical backups
		Dumping a single database
		Restoring a single database
		Limiting the amount of data to back up
		Dump formats and pg_restore
		Performing a selective restore
		Dumping a whole cluster
		Parallel backups
		Backup automation
	Exploring physical backups
		Performing a manual physical backup
			pg_verifybackup
		Starting the cloned cluster
		Restoring from a physical backup
	Summary
	Further reading
Chapter 16: Configuration and Monitoring
	Technical requirements
	Cluster configuration
		Inspecting all the configuration parameters
		Finding configuration errors
		Nesting configuration files
		Configuration contexts
		Main configuration settings
			WAL settings
			Memory-related settings
			Process information settings
			Networking-related settings
			Archive and replication settings
			Vacuum andautovacuum-related settings
			Optimizer settings
			Statistics collector
		Modifying the configuration from a live system
		Configuration generators
	Monitoring the cluster
		Information about running queries
		Inspecting locks
		Inspecting databases
		Inspecting tables and indexes
		More statistics
	Advanced statistics with pg_stat_statements
		Installing the pg_stat_statements extension
		Using pg_stat_statements
		Resetting data collected from pg_stat_statements
		Tuning pg_stat_statements
	Summary
	Further Reading
Section 4: Replication
Chapter 17: Physical Replication
	Exploring basic concepts
		WAL
			The wal_level directive
		Preparing the environment setup for streaming replication
	Learning WAL archiving and PITR
		PITR – the manual way
			The WAL archive
			Basebackup
			Recovery
	Managing streaming replication
		Basic concept
		Replication environment
		The wal_keep_segments option
		The slot way
		The pg_basebackup command
		Asynchronous replication
			Replica monitoring
			Cascading replication
		Synchronous replication
			PostgreSQL settings
				Master server
				Standby server
	Summary
	References
Chapter 18: Logical Replication
	Understanding basic concepts
		Comparing logical replication and physical replication 
	Exploring logical replication setup
		Logical replication environment settings
			The replica role
			Master server – postgresql.conf
			Replica server – postgresql.conf
			The pg_hba.conf file
		Logical replication setup
		Monitoring logical replication
		Comparing physical replication and logical replication
			Simulating on test versus bloating elimination
			Read-only versus write allowed
		DDL commands
		Disabling logical replication
	Summary
	References
Section 5: The PostegreSQL Ecosystem
Chapter 19: Useful Tools and Extensions
	Exploring the pg_trgm extension
	Using foreign data wrappers and the postgres_fdw extension
	Exploring the btree_gin extension
	Managing the pgbackrest tool
		Basic concepts
		Environment setting
			The exchange of public keys
		Installing pgbackrest
		Configuring pgbackrest
			The repository configuration 
			The PostgreSQL server configuration
				The postgresql.conf file
				The pgbackrest.conf file
		Creating and managing continuous backups
			Creating the stanza
			Checking the stanza
			Managing basebackups
			Managing PITR
	Summary
	References
Chapter 20: Toward PostgreSQL 13
	Introducing PostgreSQL 13's new features
		Replication
		Administration
		psql
		Performance
		Backup tools
	Upgrading to PostgreSQL 13
	Summary
	References
Other Books You May Enjoy
Index