Mastering PostgreSQL Administration Internals, Operations, Monitoring, and Oracle Migration Strategies (Y V Ravi Kumar , Arun Kumar Samayam etc.) (Z-Library)

Mastering PostgreSQL Administration Internals, Operations, Monitoring, and Oracle Migration Strategies Y V Ravi Kumar Arun Kumar Samayam Phani Kadambari

Mastering PostgreSQL Administration: Internals, Operations, Monitoring, and Oracle Migration Strategies ISBN-13 (pbk): 979-8-8688-1506-5 ISBN-13 (electronic): 979-8-8688-1507-2 https://doi.org/10.1007/979-8-8688-1507-2 Copyright © 2025 by Y V Ravi Kumar, Arun Kumar Samayam, and Phani Kadambari This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Shaul Elson Development Editor: Laura Berendson Coordinating Editor: Gryffin Winkler Cover designed by eStudioCalamar Cover Photo by Mylon Ollila on Unsplash Distributed to the book trade worldwide by Springer Science+Business Media New York, 1 New York Plaza, New York, NY 10004. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm.com, or visit www.springeronline.com. Apress Media, LLC is a Delaware LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at http://www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub (https://github.com/Apress). For more detailed information, please visit https://www. apress.com/gp/services/source-code. If disposing of this product, please recycle the paper Y V Ravi Kumar Irving, TX, USA Arun Kumar Samayam Dallas, TX, USA Phani Kadambari Lewisville, TX, USA

xi About the Authors Y. V. Ravi Kumar is a recipient of the prestigious EB1-A “Einstein Visa” for Extraordinary Ability in the United States. He brings over 27 years of multinational leadership experience across the United States, Seychelles, and India in the banking, financial services, and insurance (BFSI) sectors. A seasoned technologist, Ravi is an Oracle Certified Master, Oracle ACE Director Alum, Snowflake Data Superhero, and Snowflake Squad Member. He holds a wide range of certifications, including Oracle Certified Professional (OCP) in versions 8i through 19c and Oracle Certified Expert (OCE) in GoldenGate, RAC, Performance Tuning, Oracle Cloud Infrastructure (OCI), Terraform, and Oracle Engineered Systems (Exadata, ZDLRA, and ODA). He is also certified in Oracle Security, Maximum Availability Architecture (MAA), Snowflake Pro, PostgreSQL, MySQL, and MySQL HeatWave and is a multi-cloud architect certified in OCI, AWS, and GCP. In addition, he is TOGAF certified and a Senior Member of the IEEE. Ravi Kumar has co-authored six books, including Oracle Database Upgrade and Migration Methods; Oracle High Availability, Disaster Recovery, and Cloud Services; and Mastering MySQL Administration, and has served as a technical reviewer on six more titles: Oracle 19c AutoUpgrade Best Practices, Oracle Autonomous Database in Enterprise Architecture, End-to-End Observability with Grafana, Maximum Availability Architecture (MAA) with Oracle GoldenGate MicroServices in HUB Architecture, The Cloud Computing Journey: Design and Deploy Resilient and Secure Multi-cloud Systems with Practical Guidance, and Azure FinOps Essentials. Ravi Kumar has published over 100 technical articles across platforms such as Oracle Technology Network, ORAWORLD Magazine, UKOUG, and Redgate. A frequent speaker at international conferences, he has presented at Oracle OpenWorld four times and delivered sessions at numerous user groups including IOUG, NYOUG, AUSOUG, and Quest. His work includes designing data centers and implementing mission-critical databases for central banks, and his contributions have been recognized by Oracle in their OCM list and Spotlight on Success stories.

xii Arun Kumar Samayam is an experienced technology architect and a seasoned database professional with a profound passion for innovation. He is a Principal Cloud Solutions Architect in the Cloud and Engineering platform team at a global airline company. He is part of the Cloud Center of Excellence (CCoE) team, where he drives the organization’s cloud transformation journey through cloud governance practices. Before this role, Arun worked as a Product Technical Leader for the Enterprise Database Services team, where he had the opportunity to develop and hone his technical proficiency through engineering, managing, and supporting multiple database platforms like Oracle, MySQL, PostgreSQL, SQL server, and MongoDB. In addition, Arun is a multi-cloud certified professional, and his industry knowledge and experience have made him a speaker at prestigious events like Oracle OpenWorld, where he shared his insights and expertise on database products. Sri Ram Phani Kiran Kadambari is a seasoned IT professional with over 16 years of experience in database management, cloud solutions, and diverse technology domains. Passionate about databases, he specializes in Oracle (including Engineered Systems), PostgreSQL, MySQL with HeatWave, and Snowflake, along with extensive expertise in cloud platforms such as Oracle Cloud Infrastructure (OCI), AWS, and Azure. Phani’s technical skills have been instrumental in helping organizations successfully migrate their databases and mission-critical applications to the cloud. Phani’s journey began as a database trainer, and his passion for knowledge-sharing has driven him to mentor and train countless professionals, guiding them to build successful careers in database engineering. He holds multiple certifications in Oracle, MySQL, PostgreSQL, Snowflake, and Oracle Cloud Infrastructure. A respected contributor to the database community, Phani shared his insights as a speaker at KSAOUG Connect – 2024, where he presented on MySQL High Availability and Disaster Recovery. He has also served as a technical reviewer for the book Mastering MySQL Administration (Apress, 2024), further showcasing his commitment to advancing the field. Phani’s blend of technical expertise, industry experience, and dedication to mentoring makes him a valuable contributor to the IT and database community. abouT The auThors

xiii About the Technical Reviewers Velu Natarajan is a seasoned expert with over 20 years of experience architecting scalable, high-performance data and database solutions across a wide range of platforms. His passion lies in the relentless pursuit of modernizing data platforms – ensuring they are not only efficient and high-performing but also scalable and durable to meet the evolving needs of the industry. As an industry trailblazer, Velu has been an early advocate for Cloud FinOps frameworks, leveraging them to drive business success and achieve significant cost savings. As a leader in the Database Cloud Center of Excellence (CCoE), Velu plays a crucial role in advancing his organization’s cloud transformation journey. His deep expertise in database practices enables him to lead initiatives that ensure seamless migrations, business continuity, and operational efficiency. Velu’s pioneering work with Cloud FinOps has been instrumental in delivering measurable business outcomes. His application of the FinOps framework to control costs in cloud-based data platforms earned him the Discover President Award in 2024. Through effective collaboration with cross-functional teams, he has optimized performance, managed costs, and established best practices – developing self-service tools and processes, identifying potential issues, and creating comprehensive cost management plans. A respected contributor to the data and financial operations community, Velu has shared his insights at prestigious events such as Snowflake Summit 2024, where he spoke on optimizing performance and cost on Snowflake, maximizing business value, reducing budget risk, and enhancing the user experience. He served as a technical reviewer for Azure FinOps Essentials and is currently co-authoring FinOps for Snowflake – further demonstrating his commitment to advancing financial operations and data platform excellence.

xiv Velu holds certifications as an AWS Certified Database Specialty, FinOps Certified Practitioner, and Snowflake Architect and is widely recognized for his expertise in data products and his dedication to excellence. Outside of work, he enjoys playing soccer and watching epic movies. Raghavendra S (Raghav) is a Database Cloud Architect who specializes in designing and implementing cloud- based database solutions for organizations. With a deep understanding of both database management systems and cloud computing technologies, he helps businesses leverage the power of the cloud to optimize their database operations. His expertise lies in developing architecture strategies that align with business goals, ensuring scalability, high availability, and performance of database systems in the cloud. He has hands-on experience with leading cloud platforms, including Oracle Cloud Infrastructure (OCI), Amazon Web Services (AWS), and Google Cloud Platform (GCP). Raghav works closely with stakeholders to assess their database requirements, plan migration strategies, and design secure and cost-effective cloud-based database solutions. He possesses a strong understanding of database technologies such as Oracle Database, MySQL, and PostgreSQL, enabling him to provide expert guidance on data modeling, schema design, and query optimization. He also has a solid understanding of big data technologies, including Hadoop, HDFS, Hive, HBase, Kafka, and Data Lake/Lakehouse architectures. abouT The TeChniCal reviewers

xv Acknowledgments I am grateful to God who gave me all the strength, courage, perseverance, and patience in this sincere and honest attempt of knowledge sharing. This seventh book of mine as a co-author would not have been possible without the following people: Shri Yenugula Venkata Pathi and Smt. Yenugula Krishna Kumari, my parents who instilled in me good thoughts and values, and Shri B. Suresh Kamath (founder of LaserSoft and Patterns Cognitive), my mentor, my guru, my strength, and my guide, who has inspired me for the last 27 years. B. Suresh Kamath is an immensely talented and technically sound individual. He taught me how to be well read with no compromises. He led by example in being content yet hungry for knowledge. He motivated me to go that extra mile in attempting and experimenting with newer technologies/environments and in being regularly out of my comfort zone. Anitha Ravi Kumar, my wife, was immensely tolerant with me. “Behind every successful man, there is a good woman,” as they say. I believe she is the embodiment of this well-known belief. Special thanks to my daughter, Sai Hansika, and my son, Sai Theeraz, for giving me time to write a seventh book in the last seven years. I would like to thank Velu Natarajan and Raghavendra S (Raghav) for accepting to be technical reviewers for this book. Special thanks to Shaul Elson, Gryffin Winkler, Nirmal Selvaraj, Celestin Suresh John, and Laura Berendson at Apress for giving me the opportunity to write my sixth book for Apress. Thank you to the readers for picking up this book. We have attempted to be as simple and straightforward as possible when sharing this knowledge, and we truly believe that it will help you to steadily deep dive into various interesting concepts and procedures. I would also like to thank the complete Infolob Global team – Vijay Cherukuri, Tim Fox, Josh Turnbull, Nivas Nadimpalli, Satyendra Pasalapudi – and LaserSoft colleagues. —Venkata Ravi Kumar Yenugula I am forever grateful for the love and support of those who have shaped my life journey and made this book possible.

xvi To my parents, Ram Kumar and Lakshmi Sarada, for nurturing me with unwavering love, kindness, and the freedom to pursue my passions. Your encouragement and support have fueled my determination to pursue my dreams. To my beloved wife, Ramya, and our daughter, Iraa, whose boundless love and unwavering support have been the bedrock of strength in my life. Your belief in me has propelled me forward, urging me to strive for excellence in all endeavors. To my mentor, Venkata Ravi Kumar Yenugula, for recognizing my potential, guiding me through challenges, and opening doors to new opportunities. Your wisdom, dedication, and noble pursuit of knowledge have been a constant source of inspiration. To my co-authors, Venkata Ravi Kumar Yenugula and Phani Kadambari, and technical reviewers, Velu Natarajan and Raghavendra S – your dedication, collaboration, and unwavering support throughout the writing process have enriched this book and made it truly special. To my esteemed American Airlines leaders, Rasika Vaidya and Echo Jiang, for their unwavering support and encouragement on this journey. To my Apress publishing team – Shaul Elson, Gryffin Winkler, and Nirmal Selvaraj – thank you for entrusting me with the opportunity. I am confident this book will be a practical guide for many aspiring database technologists. Thank you all for being an integral part of this incredible journey. —Arun Kumar Samayam Writing the very first book in my career has been an incredible journey, and I am deeply grateful to the many people who supported me along the way. I want to use this opportunity to express my gratitude to God and bow down to my parents, Shri Kadambari Rama Badri Raju and Smt. Satyavathi, for this life, their teachings, and invaluable lessons at every phase of my life. Special mention to my sister, Smt. Poornima Garimella, whose sacrifice, support, and encouragement inspired me to strive for my achievements. Special thanks to my beloved wife, Praneetha Kadambari, undoubtedly the pillar in my life for providing unconditional love and support in whatever I do and the decisions I take. I thank our daughters Aadhya and Nihidaa for their mature understanding and giving me space and time to complete this book. aCknowledgmenTs

xvii My respectful obeisance and my heartfelt gratitude to my gurus: – Shri Namburi Bhima Shankar Acharya, a revered individual, whose blessings have brought me here today. – M.V. Madhusudan, Founder and M.D. of Wilshire Software Technologies, who offered me my first opportunity as a Database Administrator and played a pivotal role in shaping my career. – Venkata Ramakrishna Gandi, more of a brother than a mentor. His guidance has inspired me to continuously learn and maintain a calm, composed mindset in every situation. – Venkata Ravi Kumar Yenugula, an inspiration to many aspirants whose mentorship has played a significant role in shaping my profes- sional growth. I am grateful to my co-authors, Arun Kumar Samayam and Y V Ravi Kumar, and the technical reviewers, Velu Natarajan and Raghavendra S, for considering me for this project and for their time and efforts in contributing to and producing great content. I extend my thanks to all my friends, well-wishers, and my colleagues at work for their continuous encouragement and support to develop individually and professionally. To my Apress publishing team – Shaul Elson, Gryffin Winkler, Nirmal Selvaraj, and Laura Berendson – thank you for your trust and the opportunity given to write my first book. —Sri Ram Phani Kiran Kadambari aCknowledgmenTs

1 © Y V Ravi Kumar, Arun Kumar Samayam, and Phani Kadambari 2025 Y. V. Ravi Kumar et al., Mastering PostgreSQL Administration, https://doi.org/10.1007/979-8-8688-1507-2_1 CHAPTER 1 PostgreSQL System Architecture Introduction In this chapter, we learn about the origin of PostgreSQL, its architecture, and its components and understand the various components of PostgreSQL during a client connection request. We cover the following topics: • Origin of PostgreSQL • PostgreSQL key architectural components • PostgreSQL server process tree • PostgreSQL architecture flow PostgreSQL is one of the most popular open source object-relational database management systems (ORDBMS). It is based on POSTGRES, Version 4.2, initially developed at the University of California at Berkeley’s Computer Science department. PostgreSQL is based on the Berkeley code and follows the SQL standard, offering various features we will cover in this book. The PostgreSQL architecture is designed to handle transactions efficiently and maintain data integrity, supporting concurrent sessions. In this chapter, we will learn about the various components of the PostgreSQL internal architecture and their functions. PostgreSQL follows a typical client/server architecture. The client and server are usually on different hosts and communicate over a TCP/IP network connection. Several core components work together to handle queries, manage data storage, and ensure efficient execution of the query operations.

2 PostgreSQL Components The key components in a PostgreSQL architecture are 1. Server process 2. Utility process 3. System memory 4. Physical files Each of these core components further relies on several minor components. Let’s learn about each core component in detail and its function. 1. Server process A server process consists of the following components: Postmaster process: This is the central process that starts and controls all other PostgreSQL processes. It listens for incoming client connections and spawns background processes to handle each connection. postgres background process: Each incoming client connection is served by a dedicated backend process responsible for query parsing, planning, execution, and client interaction. The server process is represented in the figure below. Chapter 1 postgresQL system arChiteCture

3 Figure 1-1. PostgreSQL server process In Figure 1-1, P1/P2/P3 are the backend processes, and BW1/BW2/ BW3 are the corresponding background worker processes. 2. Utility process The utility process is a collection of auxiliary processes. These secondary or supplementary processes perform all the tasks initiated by the server process. Let’s learn about the different auxiliary processes. Background writer: The background writer handles periodic writes of dirty buffers from shared memory to the disk. Dirty buffers are modified data that has yet to be written to the disk and can get lost if a server restart happens. So flushing the data from the shared buffers is essential, and the background writer handles this periodically, freeing up space from the shared buffer. Chapter 1 postgresQL system arChiteCture