PostgreSQL 15 is a powerful, open-source database system known for its reliability, flexibility, and advanced features. It stores and manages data efficiently, making it ideal for everything from personal projects to large-scale applications. Get started with installation, configuration, and user management. Learn the basics of tables, views, and functions. Deploy your database securely on AWS, migrate seamlessly from other systems, and explore replication for high availability. Write efficient SQL queries, optimize performance, and safeguard your information with backups and recovery techniques. Control user access, implement robust security measures, and monitor your database health for proactive troubleshooting. Solve common errors and optimize performance, Practical tips and real-world scenarios provide a hands-on learning experience, making it easy to apply new knowledge. By the end of this book, you will have the confidence and skills to manage data like a pro, tackle challenges with ease, and unleash the full potential of PostgreSQL 15.

• Cover
• Title Page
• Copyright Page
• Dedication Page
• About the Author
• About the Reviewers
• Acknowledgement
• Preface
• Table of Contents
• 1. Up and Running with PostgreSQL 15
  • Introduction
  • Structure
  • Objectives
  • Introduction to PostgreSQL 15
  • Exploring the history of PostgreSQL
  • Learning about PostgreSQL architecture
  • Learning about PostgreSQL database structure
  • Installation methodology
    • Recipe 1: Working with installation from binaries
    • Recipe 2: Working with installation from source code
    • Recipe 3: Database configuration file
  • Database server and client environment
    • Recipe 4: Installing and configuring pgAdmin client tool
    • Recipe 5: Working with PostgreSQL server access solution
    • Recipe 6: Working with PostgreSQL remote access
    • Recipe 7: Discovering PostgreSQL database structural object
  • Exploring backward compatibility
    • Recipe 8: Working with upgrade on minor release
    • Recipe 9: Working with upgrade on major release
  • Conclusion
• 2. Database Hierarchy
  • Introduction
  • Structure
  • Objectives
  • Access management
  • Database instance and object hierarchy
    • Recipe 10: Adaptation with PostgreSQL Schema
  • Advanced object-relational table concept
    • Recipe 11: Getting insight to tables inheritance
    • Recipe 12: Indexing technique with PostgreSQL database
    • Recipe 13: Workaround with concurrent index
  • Common table expression
    • Recipe 14: Getting insight to SELECT in WITH queries CTE
  • Discover database storage layout
    • Recipe 15: Adapt database storage for PostgreSQL database
    • Recipe 16: Adapt database log directory
    • Recipe 17: Workaround with PostgreSQL TOAST
    • Recipe 18: Parse the database admin specific start-up logs
    • Recipe 19: Getting insight to PostgreSQL memory configuration
      • Local memory area
      • Shared memory area
  • Conclusion
• 3. Cloud Provisioning
  • Introduction
  • Structure
  • Objectives
  • Introduction to cloud solution for PostgreSQL
    • Cloud service model
    • Cloud deployment option
  • Managed or self-managed options
  • Exploring EC2 and RDS instance
    • Recipe 20: Manage PostgreSQL instance with AWS EC2
    • Recipe 21: Managing PostgreSQL instance with AWS RDS
    • Recipe 22: Native backup or restore with AWS EC2 instance
    • Recipe 23: Backup or restore with AWS RDS instance
    • Recipe 24: Working with replication on AWS for PostgreSQL
  • Conclusion
• 4. Migration
  • Introduction
  • Structure
  • Objectives
  • Introduction to migration for PostgreSQL
  • Exploring migration methodology
  • Understanding PostgreSQL database system migration types
    • Recipe 25: On-premise to AWS EC2 instance migration
    • Database migration roadmap
    • Recipe 26: Migrating PostgreSQL from EC2 to RDS instance on AWS
  • Getting ready with pgloader
    • Recipe 27: Setting up pgloader
    • Recipe 28: Getting insight to migrate MariaDB to PostgreSQL on EC2 instance
    • Recipe 29: Using db2topg for migration to PostgreSQL
    • Recipe 30: Setting up foreign data wrapper for PostgreSQL migration
  • Conclusion
• 5. Transaction Log
  • Introduction
  • Structure
  • Objectives
  • Introducing transaction log
  • Understanding WAL
  • Exploring the internal layout of WAL segment
  • Exploring the internal layout of XLOG record
  • Configuring the WAL file
    • PostgreSQL WAL benefits
    • Recipe 31: How to enable/disable archive mode
    • Recipe 32: Working with remote WAL Archive options
    • Recipe 33: Working with WAL compression option for space management
  • Explore SQL statement transaction sizing
    • Recipe 34: Configuring and managing WAL performance parameter
    • Recipe 35: Administering continuous archiving
  • Managing the WAL writer process
    • PostgreSQL 15 WAL writer process versus checkpoint processing
  • Optimizing checkpoint processing
  • Automating vacuum process
    • Recipe 36: Getting insight to vacuum process in PostgreSQL database
    • Recipe 37: Debug PostgreSQL autovacuum problem
  • Implementing continuous archiving and archive logs
    • Recipe 38: PostgreSQL 15 continuous archiving and archive logs examples
  • Enhancing performance and benefits of WAL
    • Recipe 39: Proactive solution to delete PostgreSQL archive logs
  • Conclusion
• 6. Partitioning and Sharding
  • Introduction
  • Structure
  • Objectives
  • Partitioning and sharding
  • Partitioning classifications
    • Recipe 40: Setup and exploit partitioning in PostgreSQL
    • Recipe 41: Partition management with PostgreSQL 15
    • Recipe 42: Getting insight to vertical and horizontal partitioning
      • Horizontal partitioning
      • Vertical partitioning
    • Recipe 43: Working with automatic partition with PostgreSQL
  • Manage partition using pg_partman extension
    • Recipe 44: Manage partition using custom scripts with a scheduler
    • Recipe 45: Getting insight to partition pruning
  • Partitioning versus sharding
  • Shard strategies and rebalancer
    • Recipe 46: Configure sharding with Citus Data
    • Rebalancer
  • Foreign data wrapper
  • Conclusion
• 7. Replication and High Availability
  • Introduction
  • Structure
  • Objectives
  • Replication and high availability
  • Understanding the CAP theorem
  • Replication classification
    • Recipe 47: Scaling Postgres with primary-standby replication
  • Using replication slots
    • Recipe 48: Scaling Postgres with multi-master replication
    • Recipe 49: Setup delay standby in PostgreSQL
    • Recipe 50: Performing PITR recovery using delay standby
    • Recipe 51: PostgreSQL 15 promote standby database to primary
    • Recipe 52: Installing and configuring repmgr for PostgreSQL cluster
  • Switchover versus failover
    • Recipe 53: Switchover with repmgr for PostgreSQL
    • Recipe 54: Failover with repmgr for PostgreSQL
  • Understanding proxy and load balancing
    • Recipe 55: Deploying PostgreSQL Automatic Failover for HA solution
    • Recipe 56: Using HAProxy for HA solution
  • Monitoring replication
  • Conclusion
• 8. Leveraging SQL
  • Introduction
  • Structure
  • Objectives
  • Exploring data access
  • Working with tables and data
    • Recipe 57: Tables and Data Operations
    • Table creation
    • Data insertion
    • Data retrieval
    • Data modification
    • Table operations
    • Data integrity and transactions
    • Recipe 58: Querying PostgreSQL data
    • Recipe 59: Querying using shell script in PostgreSQL
  • Exploring DML, DDL, TCL and DCL
    • Recipe 60: Database Query and Control Language
    • Data manipulation language
    • Data definition language
    • Transaction control language
    • Data control language
    • Recipe 61: DDL adaptation
    • Recipe 62: Working with dataset export/import
    • Recipe 63: Dataset load from spreadsheet/flat files
    • Recipe 64: Structuring query access with psql
    • Recipe 65: Exploring PostgreSQL join and subqueries
    • Recipe 66: Querying JSON data
    • Recipe 67: Working with PostgreSQL CAST operator
    • Recipe 68: Working with database consistency and integrity
    • Unique constraints
    • Foreign key constraints
    • Recipe 69: Getting insight to Python and Java connection
    • Recipe 70: Importing BLOB data types into PostgreSQL
  • Conclusion
• 9. Server Controls and Auditing
  • Introduction
  • Structure
  • Objectives
  • Introduction to server control and auditing
    • Recipe 71: Database privileges
    • Recipe 72: PostgreSQL role management and authorization
  • SSL/TLS authentication
    • Recipe 73: Setting up SSL authentication in PostgreSQL
  • Exploring TDE and encryption in PostgreSQL
    • Recipe 74: Encryption with pgcrypto in PostgreSQL
  • PostgreSQL auditing
    • Recipe 75: Installing and configuring pgaudit
    • Recipe 76: Using audit log with PostgreSQL trigger
    • Recipe 77: Working with log_statement
  • LDAP authentication
    • Recipe 78: Getting ready with LDAP authentication
  • Conclusion
• 10. Backup
  • Introduction
  • Structure
  • Objectives
  • Introduction to database backup
  • Exploring SQL dump and file system level backup
    • Recipe 79: Working with logical backup
    • Recipe 80: Working with physical backup
    • Recipe 81: Automating backup
  • Exploring backup tools
    • Recipe 82: Working with pg_probackup, pgBackRest tool
    • Recipe 83: Installing and configuring pg_probackup for PostgreSQL
    • Recipe 84: Installing and configuring pgBackRest for PostgreSQL
    • Recipe 85: Installing and configuring Barman
    • Recipe 86: Incremental/differential backup
    • Recipe 87: Working with schema level backup
    • Recipe 88: Monitoring backup
  • Improving backup performance
  • Conclusion
• 11. Recovery
  • Introduction
  • Structure
  • Objectives
  • Introduction to database recovery
  • Planning recovery
    • Recipe 89: Recovery from logical backup
    • Recipe 90: Recovery from physical backup
  • Understanding crash recovery
  • Recovering with PITR
    • Recipe 91: Point-in-time recovery
  • Incremental/differential restore
    • Recipe 92: Incremental/differential restore with Barman
    • Recipe 93: Restoring database with Barman
  • Comparing dropped versus damaged table recovery
    • Recipe 94: Working with tables recovery
    • Recipe 95: Working with schema level restore
  • Conclusion
• 12. Monitoring and Diagnosis
  • Introduction
  • Structure
  • Objectives
  • Introducing monitoring and diagnosis
  • System resource monitoring
  • Database monitoring
  • Discovering monitoring script
    • Recipe 96: Utilizing script-based monitoring
  • Prometheus and Grafana
    • Recipe 97: Managing Prometheus and Grafana
    • Recipe 98: Kill/terminate hung query
  • Explore statistics
    • Recipe 99: Working with statistic collector and analyzer
  • Monitoring with system view
    • Recipe 100: Getting insight into query monitoring
    • Recipe 101: Getting insight to monitor database lock
    • Recipe 102: Getting insight to monitor active session
    • Recipe 103: Working with query plan
  • Vacuum and bloat consideration
    • Recipe 104: Getting insight into vacuum and bloat
  • Explore fsync
    • Recipe 105: Getting insight into fsync
  • PostgreSQL other monitoring use cases
    • Recipe 106: Monitoring cache hit ratio
    • Recipe 107: Monitor long-running query
  • Conclusion
• 13. Troubleshooting
  • Introduction
  • Structure
  • Objectives
  • Transaction log and checkpoint
    • Recipe 108: Adapt log file rotation
    • Recipe 109: Tune transaction log and checkpoints
  • Benchmarking
    • Recipe 110: Benchmark performance with pgbench
    • Recipe 111: Practice with key parameters in PostgreSQL
  • Data checksum
    • Recipe 112: Working with pg_checksums
      • Resolution steps:
  • Conclusion
• Index