PostgreSQL for Jobseekers Introduction to PostgreSQL administration for modern DBAs (Sonia Valeja, David Gonzalez) (Z-Library)

📄 File Format: PDF

💾 File Size: 43.1 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

(This page has no text content)

📄 Page 2

 i

📄 Page 3

Sonia Valeja David Gonzalez www.bpbonline.com PostgreSQL for Jobseekers Introduction to PostgreSQL administration for modern DBAs

📄 Page 4

ii  Copyright © 2023 BPB Online All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews. Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor BPB Online or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book. BPB Online has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, BPB Online cannot guarantee the accuracy of this information. First published: 2023 Published by BPB Online WeWork 119 Marylebone Road London NW1 5PU UK | UAE | INDIA | SINGAPORE ISBN 978-93-55514-004 www.bpbonline.com

📄 Page 5

 iii Dedicated to My supportive parents – Rupa & Hasanand and my loving husband – Sunil — Sonia Valeja My loved Sofi, the reason I do all this. — David Gonzalez

📄 Page 6

iv  About the Author • Sonia Valeja: Sonia is an Indian DBA who started working in PostgreSQL in the year 2012. Since she has performed multiple migrations from Oracle to PostgreSQL, she learnt Oracle while working with PostgreSQL. She is a SME for beginner-level course on Open Source Database-PostgreSQL in one of her company. She has conducted multiple trainings on PostgreSQL for freshers as well as experienced professionals. Also, she has been an active participant at pgConf and has given lightning talk. She has worked extensively in Taxation as well as manufacturing based projects for Indian as well as African sub- continent. • David Gonzalez: David is a Mexican DBA with 10+ years of hands-on experience with different RDBMS such as PostgreSQL, Oracle, and MySQL. He is good at PostgreSQL Administration and Development and has written multiple blogs on PostgreSQL. He has participated in various projects for banking, IT, and Software companies.

📄 Page 7

 v About the Reviewer Amul Sul is a passionate developer with extensive professional experience in the domain of core database development. He is an active contributor in PostgreSQL feature development as well as in EDB Postgres Advanced Server propriety feature development. He is currently working in EnterpriseDB and is part of the database developer team. Prior to EDB, he worked at NTT Data. Amul holds a Master’s degree from Mumbai University.

📄 Page 8

vi  Acknowledgements I want to express my deepest gratitude to my family and friends for their unwavering support and encouragement throughout this book’s writing, especially my parents Rupa & Hasanand and my better half Sunil. — Sonia Valeja I want to say thank you from the bottom of my heart to my family, my wife Pam, and my beautiful daughter Sofi, for all the support and patience during this book process. They were always there cheering me up and pushing me to reach the goal. — David Gonzalez We are also grateful to BPB Publications for their guidance and expertise in bringing this book to fruition. It was a long journey of revising this book, with valuable participation and collaboration of reviewers, technical experts, and editors. We would also like to acknowledge the valuable contributions of my colleagues and co-worker during many years working in the tech industry, who have taught me so much and provided valuable feedback on my work. Finally, we would like to thank all the readers who have taken an interest in our book and for their support in making it a reality. Your encouragement has been invaluable.

📄 Page 9

 vii Preface Welcome to the world of PostgreSQL! Whether you are a novice exploring the realm of databases or a seasoned professional looking to expand your knowledge, this book is designed to be your comprehensive guide to everything PostgreSQL offers. We have carefully crafted this book to cover a wide range of topics, from the history and installation of PostgreSQL to its intricate internals, advanced tuning techniques, and securing your environment. We also delve into data replication, backup and restore operations, and the process of contributing to the vibrant PostgreSQL community. In the first section of this book, we embark on a journey through time to explore the rich history of the PostgreSQL project. From its humble beginnings as a research project at the University of California, Berkeley, to becoming one of the most powerful and popular open-source databases, you will gain insight into the evolution and key milestones that have shaped PostgreSQL into what it is today. Understanding its roots will help you appreciate the philosophy and ethos behind this remarkable database system. The subsequent chapters focus on the practical aspects of PostgreSQL, starting with the installation process on modern operating systems. We will guide you through each step, providing clear instructions and best practices to ensure a smooth setup. Additionally, we delve into alternative deployment options, such as Docker and Cloud, offering flexibility in how you harness the power of PostgreSQL in your own environment. Once you have PostgreSQL up and running, we dive deep into its internals. Understanding how PostgreSQL processes, stores, and retrieves data is crucial for effective performance tuning and troubleshooting. We unravel the inner workings of the query optimizer, storage engine, and transaction management system, equipping you with the knowledge and tools to optimize your database for optimal performance. Whether dealing with a sluggish query or facing scalability challenges, this section will be your indispensable resource. We also explore the essential backup and restore operations practices, enabling you to protect your data against unforeseen disasters. Later, we shed light on implementing data replication, which not only ensures high availability but also opens doors to advanced use cases like distributed architectures and load

📄 Page 10

viii  balancing. Moreover, we delve into securing the access and integrity of your data, covering topics such as authentication and authorization. Finally, this book wouldn’t be complete without acknowledging the vibrant and collaborative PostgreSQL community. We will learn about some of the most used open-source projects, in the form of extensions and compatible tools that can enhance the native PostgreSQL capabilities. We share valuable insights on how you can contribute to this remarkable community through code contributions, documentation, or engaging in user forums. By becoming an active member, you not only enhance your knowledge and skills but also play a role in shaping the future of PostgreSQL. Now, fasten your seatbelt and get ready for an exhilarating journey into the world of PostgreSQL. This book is your trusty companion, empowering you to master every facet of this powerful database system. Let’s dive in and unlock the true potential of PostgreSQL together! Chapter 1: Introduction to Opensource Database - PostgreSQL - introduces the open-source paradigm and covers the main general aspects of PostgreSQL like PostgreSQL history, what kind of applications work with PostgreSQL database and much more.Furthermore, the chapter also gives the reader an overview of the PostgreSQL, a brief history of PostgreSQL, PostgreSQL release cycle and the current impact of PostgreSQL in the market. Chapter 2: Getting PostgreSQL to work - presents a detailed overview two ways of installing PostgreSQL - Source Code Installation and Binary Installation. Chapter 3: Modern Options to get PostgreSQL - covers some other options to get PostgreSQL such as DBaaS/Cloud vendors and containers/Kubernetes. Chapter 4: Global Objects in PostgreSQL - allows the reader to learn fundamental concepts of the global objects in a PostgreSQL cluster. These are handled per instance rather than per database. It describes Roles/Users/Groups, Tablespaces and Databases with examples. Chapter 5: Architecture of PostgreSQL - gives special attention to the main components with respect to Memory Architecture, Background Processes and Physical Architecture. Chapter 6: PostgreSQL Internals - explains Internals of the PostgreSQL from the perspective of ACID,MVCC, Transaction Isolation levels, Query Processing, and Vacuum.

📄 Page 11

 ix Chapter 7: Backup and Restore in PostgreSQL - explains the importance of a backup strategy for a PostgreSQL solution, and teaches the different ways to get them. Then it shows how to restore these backups into a working service. Finally, it presents some of the most widely used tools for backup and restore such pgBackRest, WAL-G and so on Chapter 8: Replicating Data - is dedicated to high availability architecture. It covers the main current replication techniques. It explains their differences, how to configure them, and their advantages and limitations. Chapter 9: Security and Access Control - explains the importance of access control and security, and how PostgreSQL handles the authentication and authorization. The pg_hba and the GRANT/REVOKE are explored here. Chapter 10: Most used Extensions/Tools - focus on some of the important extensions used which helps in extending the default behaviour of Postgresql in most frequent utilities. It cover the extensions like pg_stat_statements & pg_repack. Chapter 11: Basic Database Objects - touches the concepts of database schemas, DDL command, DML Queries and DCL commands that are important from PostgreSQL Developer perspective. The main topics covered are Schema management, Data Definition Language command, Data Manipulation Language commands, control statements and constraints. Chapter 12: Advance Database Objects - covers the advanced PL/PgSQL concepts like procedures, triggers and rules. It also covers details on custom data types in PostgreSQL. Chapter 13: Performance Tuning - focuses on index creations along with best practices to be followed in configuration files. Once data size increases, there comes need to tune the database queries using Index and Explain Plan. Chapter 14: Troubleshooting - gives insights on analyzing log files using database commands as well as OS commands. A DBA should also be aware on basics command of Operating System which are also covered from the DBA perspective. Chapter 15: Contributing to PostgreSQL Community - discusses how to be part of PostgreSQL Community and build online presence.

📄 Page 12

x  Coloured Images Please follow the link to download the Coloured Images of the book: https://rebrand.ly/gcsuy63 The code bundle for the book is also hosted on GitHub at https://github.com/ bpbpublications/PostgreSQL-for-Jobseekers. In case there’s an update to the code, it will be updated on the existing GitHub repository. We have code bundles from our rich catalogue of books and videos available at https://github.com/bpbpublications. Check them out! Errata We take immense pride in our work at BPB Publications and follow best practices to ensure the accuracy of our content to provide with an indulging reading experience to our subscribers. Our readers are our mirrors, and we use their inputs to reflect and improve upon human errors, if any, that may have occurred during the publishing processes involved. To let us maintain the quality and help us reach out to any readers who might be having difficulties due to any unforeseen errors, please write to us at : errata@bpbonline.com Your support, suggestions and feedbacks are highly appreciated by the BPB Publications’ Family. Did you know that BPB offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.bpbonline.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at : business@bpbonline.com for more details. At www.bpbonline.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on BPB books and eBooks.

📄 Page 13

 xi Piracy If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at business@bpbonline.com with a link to the material. If you are interested in becoming an author If there is a topic that you have expertise in, and you are interested in either writing or contributing to a book, please visit www.bpbonline.com. We have worked with thousands of developers and tech professionals, just like you, to help them share their insights with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea. Reviews Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions. We at BPB can understand what you think about our products, and our authors can see your feedback on their book. Thank you! For more information about BPB, please visit www.bpbonline.com. Join our book's Discord space Join the book's Discord Workspace for Latest updates, Offers, Tech happenings around the world, New Release and Sessions with the Authors: https://discord.bpbonline.com

📄 Page 14

xii  Table of Contents 1. Introduction to Opensource Database - PostgreSQL ..................................... 1 Introduction .............................................................................................................. 1 Structure .................................................................................................................... 1 Objectives .................................................................................................................. 2 Open-source introduction ...................................................................................... 2 The origin of free software .................................................................................2 The open source concept ....................................................................................3 The open source nowadays ................................................................................5 A brief history of PostgreSQL ................................................................................. 6 The POSTGRES project .....................................................................................6 Postgres95 ..........................................................................................................7 PostgreSQL ........................................................................................................7 PostgreSQL versions key features .................................................................8 PostgreSQL in stats on a single image .......................................................10 PostgreSQL release cycle ....................................................................................... 10 The current impact of PostgreSQL on the market ..........................................12 Companies that use PostgreSQL .......................................................................... 14 Companies that help in enhancing PostgreSQL .............................................14 Advantages of PostgreSQL .........................................................................16 Distributed architecture with PostgreSQL ......................................................17 StatefulSet with PostgreSQL ...........................................................................18 Conclusion .............................................................................................................. 18 Bibliography ............................................................................................................ 19 2. Getting PostgreSQL to work .........................................................................21 Introduction ............................................................................................................ 21 Structure .................................................................................................................. 21 Objectives ................................................................................................................ 22 Source code installation ........................................................................................ 22 Short version of source code installation ........................................................22 Pre-requisites ...................................................................................................23 Downloading the source ..................................................................................23 Installation procedure ............................................................................................ 25

📄 Page 15

 xiii Verifying directory structure ...........................................................................27 Adding postgres user .......................................................................................27 Creating data directory ...................................................................................28 Initializing PostgreSQL ......................................................................................... 28 Validating the data directory ..........................................................................30 Start PostgreSQL database ..............................................................................31 Verify postgres process is running ...................................................................31 Binary installation .................................................................................................. 33 Create repository configuration ......................................................................34 Import the repository signing key ...................................................................35 Update the package list ...................................................................................35 Installing PostgreSQL ............................................................................................ 36 Conclusion .............................................................................................................. 38 Bibliography ............................................................................................................ 39 3. Modern Options to get PostgreSQL .............................................................41 Introduction ............................................................................................................ 41 Structure .................................................................................................................. 41 Objectives ................................................................................................................ 42 Other ways to get PostgreSQL .............................................................................. 42 On-premise, virtualization, containers, and cloud ........................................42 On-premise .................................................................................................42 Virtualization .............................................................................................43 Containers ..................................................................................................44 The Cloud ...................................................................................................45 Getting PostgreSQL on modern systems ............................................................ 46 PostgreSQL on Docker ....................................................................................47 PostgreSQL on Kubernetes ..............................................................................48 PostgreSQL on The Cloud ...............................................................................53 Conclusion .............................................................................................................. 58 Bibliography ............................................................................................................ 58 4. Global Objects in PostgreSQL ......................................................................59 Introduction ............................................................................................................ 59 Structure .................................................................................................................. 59 Objectives ................................................................................................................ 59

📄 Page 16

xiv  Users/Groups/Roles ............................................................................................... 60 Tablespaces .............................................................................................................. 64 Databases ................................................................................................................. 65 CREATE DATABASE command ....................................................................67 createdb program ............................................................................................68 Using pgAdmin Wizard ..................................................................................69 Conclusion .............................................................................................................. 72 Bibliography ............................................................................................................ 72 5. Architecture of PostgreSQL ..........................................................................73 Introduction ............................................................................................................ 73 Structure .................................................................................................................. 73 Objectives ................................................................................................................ 74 Memory architecture ............................................................................................. 74 Shared memory ...............................................................................................74 Shared buffers ..................................................................................................75 WAL buffers .....................................................................................................75 CLOG buffers ..................................................................................................75 Process memory ...............................................................................................76 Temporary buffers ......................................................................................76 Work memory ..................................................................................................76 Maintenance work memory ............................................................................77 Background processes ........................................................................................... 77 Postmaster .......................................................................................................77 Checkpointer ....................................................................................................78 Writer or background writer ..........................................................................78 Autovacuum ....................................................................................................79 Stats collector ...................................................................................................79 Logger ..............................................................................................................79 Archiver ...........................................................................................................79 WAL writer ......................................................................................................80 WAL sender .....................................................................................................80 WAL receiver ...................................................................................................81 Physical files ............................................................................................................ 81 Data files ..........................................................................................................84 WAL files .........................................................................................................84

📄 Page 17

 xv Temporary files ................................................................................................85 CLOG files .......................................................................................................86 Stat files ............................................................................................................86 Log files ............................................................................................................86 WAL archive files .............................................................................................86 Conclusion .......................................................................................................87 Bibliography ............................................................................................................ 89 6. PostgreSQL Internals ....................................................................................91 Introduction ............................................................................................................ 91 Structure .................................................................................................................. 91 Objectives ................................................................................................................ 92 ACID ........................................................................................................................ 92 Atomicity .........................................................................................................92 Consistency ......................................................................................................92 Isolation ...........................................................................................................92 Durability ........................................................................................................92 MVCC ...................................................................................................................... 93 Vacuum .................................................................................................................... 94 Autovacuum ....................................................................................................94 VACUUM FULL .............................................................................................95 Manual VACUUM ..........................................................................................95 pg_repack ....................................................................................................96 Preventing transaction ID wraparound failures ............................................97 Transaction isolation levels ................................................................................... 97 Phenomena ......................................................................................................97 Dirty read ........................................................................................................97 Non-repetable read ..........................................................................................98 Phantom read ..................................................................................................99 Serialization anomaly .....................................................................................99 Isolation levels ...............................................................................................100 Read uncommitted/committed .....................................................................100 Repeatable read ........................................................................................101 Serializable ...............................................................................................102 Query processing .................................................................................................102 Parser .............................................................................................................103

📄 Page 18

xvi  Rewriter .........................................................................................................104 Planner ..........................................................................................................104 Executor .........................................................................................................105 Conclusion ............................................................................................................105 Bibliography ..........................................................................................................106 7. Backup and Restore in PostgreSQL ............................................................107 Introduction ..........................................................................................................107 Structure ................................................................................................................107 Objectives ..............................................................................................................108 Backup ...................................................................................................................108 Physical backup .............................................................................................109 pg_basebackup .........................................................................................109 Point in time Recovery/Archival ...................................................................111 Pros and cons of physical backup..................................................................111 Logical backup ...............................................................................................112 pg_dump ...................................................................................................112 pg_dumpall ...............................................................................................112 Pros and cons of logical backup ....................................................................115 Restore ...................................................................................................................115 psql ............................................................................................................115 pg_restore .................................................................................................117 Useful backup and restore tools .........................................................................118 pgBackRest .....................................................................................................119 Barman ..........................................................................................................125 pg_probackup ................................................................................................130 Conclusion ............................................................................................................133 Bibliography ..........................................................................................................134 8. Replicating Data ..........................................................................................135 Introduction ..........................................................................................................135 Structure ................................................................................................................135 Objectives ..............................................................................................................135 Physical replication ..............................................................................................136 Hot standby ...................................................................................................138 Archive recovery ............................................................................................139

📄 Page 19

 xvii Streaming replication ....................................................................................141 Cascading ......................................................................................................142 Delayed replica ..............................................................................................144 Configuration ................................................................................................146 Logical replication ................................................................................................146 Architecture ...................................................................................................147 Publication .....................................................................................................147 Subscription ...................................................................................................148 Publisher node as well as subscription node ................................................148 Conclusion ............................................................................................................150 9. Security and Access Control .......................................................................151 Introduction ..........................................................................................................151 Structure ................................................................................................................151 Objectives ..............................................................................................................152 Authentication ......................................................................................................152 The pg_hba.conf ............................................................................................152 local ...........................................................................................................153 host ............................................................................................................153 hostssl ........................................................................................................154 hostnossl ....................................................................................................154 hostgssenc .................................................................................................154 hostnogssenc .............................................................................................154 Database ........................................................................................................154 User ................................................................................................................155 Address ...........................................................................................................155 Method ...........................................................................................................156 [Options] .......................................................................................................156 Authentication methods ......................................................................................157 The pg_ident.conf ..........................................................................................158 Examples ..................................................................................................159 Authorization ........................................................................................................161 Role attributes ...............................................................................................161 Object ownership ...........................................................................................163 Objects privileges ...........................................................................................164 Conclusion ............................................................................................................165

📄 Page 20

xviii  Bibliography ..........................................................................................................165 10. Most used Extensions/Tools ........................................................................167 Introduction ..........................................................................................................167 Structure ................................................................................................................167 Objectives ..............................................................................................................168 Extensions .............................................................................................................168 pg_cron ..........................................................................................................169 pg_stat_statements ........................................................................................171 pg_repack .......................................................................................................174 Tools ...............................................................................................................177 pgbadger ...................................................................................................177 pgbench .....................................................................................................179 pgbouncer .................................................................................................181 Conclusion ............................................................................................................183 Bibliography ..........................................................................................................183 11. Basic Database Objects ...............................................................................185 Introduction ..........................................................................................................185 Structure ................................................................................................................185 Objectives ..............................................................................................................185 Managing schemas ...............................................................................................186 DB cluster ......................................................................................................186 Users/roles ......................................................................................................188 Databases.......................................................................................................188 Tablespaces ....................................................................................................188 Schemas .........................................................................................................188 Default - Public Schema................................................................................188 SEARCH_PATH in Schema ....................................................................189 Managing DB Objects using DDL commands .................................................192 Data types ......................................................................................................192 Table ...............................................................................................................193 Create table ...............................................................................................193 Alter table .................................................................................................194 Drop table .................................................................................................194 Truncate ....................................................................................................194