Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications, 5th Edition / Освоение PostgreSQL 15: Передовые методы создания масштабируемых, надежных и отказоустойчивых приложений баз данных и управления ими, 5-е издание Год издания: 2023 Автор: Schönig Hans / Шениг Ганс Издательство: Packt Publishing ISBN: 978-1-80324-834-9 Язык: Английский Формат: PDF Качество: Издательский макет или текст (eBook) Интерактивное оглавление: Да Количество страниц: 523 Описание: Master the capabilities of PostgreSQL 15 to efficiently administer and maintain your database. Key Features Using real world examples, get to grips with expert PostgreSQL 15 concepts Explore performance improvement, database replication, data replication, database administration, and more Extend PostgreSQL’s functionalities to suit your organization’s needs with minimal effort Book Description Starting with an introduction to the newly released features of PostgreSQL 15, this updated fifth edition will help you get to grips with PostgreSQL administration and how to build dynamic database solutions for enterprise apps, including designing both physical and technical aspects of the system. You’ll explore advanced PostgreSQL features, such as logical replication, database clusters, advanced indexing, and user management to manage and maintain your database. You’ll then work with the PostgreSQL optimizer, configure PostgreSQL for high speed, and move from Oracle to PostgreSQL. Among the other skills that the book will help you build, you’ll cover transactions, handling recursions, working with JSON and JSONB data, and setting up a Patroni cluster. It will show you how to improve performance with query optimization. You’ll also focus on managing network security and work with backups and replication while exploring useful PostgreSQL extensions that optimize the performance of large databases. By the end of this PostgreSQL book, you’ll be able to use your database to its utmost capacity by implementing advanced administrative tasks with ease. What you will learn Make use of the indexing features in PostgreSQL and fine-tune the performance of your queries Work with stored procedures and manage backup and recovery Get the hang of replication and failover techniques Improve the security of your database server and handle encryption effectively Troubleshoot your PostgreSQL instance for solutions to common and not-so-common problems Perform database migration from Oracle to PostgreSQL with ease Who this book is for This database administration book is for PostgreSQL developers, database administrators, and professionals who want to implement advanced functionalities and master complex administrative tasks with PostgreSQL 15. Prior experience in PostgreSQL and familiarity with the basics of database administration will assist with understanding key concepts covered in the book. Освоите возможности PostgreSQL 15 для эффективного администрирования и обслуживания вашей базы данных. Ключевые функции Используя реальные примеры, познакомьтесь с концепциями PostgreSQL 15 от экспертов Изучите повышение производительности, репликацию базы данных, репликацию данных, администрирование базы данных и многое другое Расширьте функциональные возможности PostgreSQL в соответствии с потребностями вашей организации с минимальными усилиями Описание книги Начиная с ознакомления с недавно выпущенными функциями PostgreSQL 15, это обновленное пятое издание поможет вам разобраться с администрированием PostgreSQL и с тем, как создавать решения для динамических баз данных для корпоративных приложений, включая проектирование как физических, так и технических аспектов системы. Вы познакомитесь с расширенными функциями PostgreSQL, такими как логическая репликация, кластеры баз данных, расширенная индексация и управление пользователями для управления и поддержки вашей базы данных. Затем вы поработаете с оптимизатором PostgreSQL, настроите PostgreSQL на высокую скорость и перейдете с Oracle на PostgreSQL. Среди других навыков, которые поможет вам развить книга, вы узнаете о транзакциях, обработке рекурсий, работе с данными JSON и JSONB и настройке кластера Patroni. В ней будет показано, как повысить производительность с помощью оптимизации запросов. Вы также сосредоточитесь на управлении сетевой безопасностью и работе с резервными копиями и репликацией, изучая полезные расширения PostgreSQL, которые оптимизируют производительность больших баз данных. К концу этой книги по PostgreSQL вы сможете максимально эффективно использовать свою базу данных, с легкостью выполняя расширенные административные задачи. Чему вы научитесь Используйте функции индексации в PostgreSQL и точно настраивайте производительность ваших запросов Работайте с хранимыми процедурами и управляйте резервным копированием и восстановлением Освоите методы репликации и отработки отказа Повысьте безопасность вашего сервера баз данных и эффективно используйте шифрование Устраните неполадки в вашем экземпляре PostgreSQL для решения распространенных и не очень распространенных проблем Легко выполните миграцию базы данных с Oracle на PostgreSQL Для кого предназначена эта книга Эта книга по администрированию баз данных предназначена для разработчиков PostgreSQL, администраторов баз данных и профессионалов, которые хотят реализовать расширенные функциональные возможности и справиться со сложными административными задачами с помощью PostgreSQL 15. Предыдущий опыт работы с PostgreSQL и знакомство с основами администрирования баз данных помогут понять ключевые концепции, описанные в книге.
Примеры страниц
Оглавление
Preface xiii 1 PostgreSQL 15 Overview 1 Making use of DBA-related features 1 Removing support for old pg_dump 1 Deprecating Python 2 2 Fixing the public schema 2 Adding pre-defined roles 2 Adding permissions to variables 3 Improving pg_stat_statements 3 New wait events 4 Adding logging functionality 4 Understanding developer-related features 7 Security invoker views 7 ICU locales 7 Better numeric 8 Handling ON DELETE 9 Working around NULL and UNIQUE 9 Adding the MERGE command to PostgreSQL 10 Using performance-related features 11 Adding multiple compression algorithms 11 Handling parallel queries more efficiently 12 Improved statistics handling 12 Prefetching during WAL recovery 12 Additional replication features 12 Two-phase commit for logical decoding 12 Adding row and column filtering 13 Improving ALTER SUBSCRIPTION 13 Supporting compressed base backups 14 Introducing archiving libraries 15 Summary 15 2 Understanding Transactions and Locking 17 Working with PostgreSQL transactions 17 Handling errors inside a transaction 21 Making use of SAVEPOINT 22 Transactional DDLs 23 Understanding basic locking 24 Avoiding typical mistakes and explicit locking 26 Making use of FOR SHARE and FOR UPDATE 30 Understanding transaction isolation levels 33 Considering serializable snapshot isolation transactions 35 Observing deadlocks and similar issues 36 Utilizing advisory locks 38 Optimizing storage and managing cleanup 39 Configuring VACUUM and autovacuum 41 Watching VACUUM at work 43 Limiting transactions by making use of snapshot too old 47 Making use of more VACUUM features 47 Summary 48 Questions 48 3 Making Use of Indexes 49 Understanding simple queries and the cost model 50 Making use of EXPLAIN 51 Digging into the PostgreSQL cost model 53 Deploying simple indexes 55 Making use of sorted output 56 Using bitmap scans effectively 59 Using indexes in an intelligent way 59 Understanding index de-duplication 62 Improving speed using clustered tables 62 Clustering tables 66 Making use of index-only scans 67 Understanding additional B-tree features 68 Combined indexes 68 Adding functional indexes 69 Reducing space consumption 70 Adding data while indexing 72 Introducing operator classes 72 Creating an operator class for a B-tree 74 Understanding PostgreSQL index types 80 Hash indexes 81 GiST indexes 81 GIN indexes 84 SP-GiST indexes 85 BRINs 86 Adding additional indexes 88 Achieving better answers with fuzzy searching 90 Taking advantage of pg_trgm 90 Speeding up LIKE queries 92 Handling regular expressions 93 Understanding full-text searches 94 Comparing strings 95 Defining GIN indexes 95 Debugging your search 96 Gathering word statistics 98 Taking advantage of exclusion operators 98 Summary 99 Questions 100 4 Handling Advanced SQL 101 Supporting range types 102 Querying ranges efficiently 103 Handling multirange types 105 When to use range types 107 Introducing grouping sets 107 Loading some sample data 108 Applying grouping sets 109 Investigating performance 111 Combining grouping sets with the FILTER clause 113 Making use of ordered sets 114 Understanding hypothetical aggregates 116 Utilizing windowing functions and analytics 117 Partitioning data 118 Ordering data inside a window 119 Using sliding windows 121 Abstracting window clauses 128 Using on-board windowing functions 129 Writing your own aggregates 137 Creating simple aggregates 137 Adding support for parallel queries 141 Improving efficiency 142 Writing hypothetical aggregates 144 Handling recursions 146 UNION versus UNION ALL 147 Inspecting a practical example 148 Working with JSON and JSONB 150 Displaying and creating JSON documents 150 Turning JSON documents into rows 152 Accessing a JSON document 153 Summary 157 5 Log Files and System Statistics 159 Gathering runtime statistics 159 Working with PostgreSQL system views 160 Creating log files 184 Configuring the postgresql.conf file 184 Summary 191 Questions 191 6 Optimizing Queries for Good Performance 193 Learning what the optimizer does 193 A practical example – how the query optimizer handles a sample query 194 Understanding execution plans 209 Approaching plans systematically 209 Spotting problems 211 Understanding and fixing joins 217 Getting joins right 217 Processing outer joins 219 Understanding the join_collapse_limit variable 220 Enabling and disabling optimizer settings 221 Understanding genetic query optimization 225 Partitioning data 226 Creating inherited tables 226 Applying table constraints 229 Modifying inherited structures 231 Moving tables in and out of partitioned structures 232 Cleaning up data 232 Understanding PostgreSQL 15.x partitioning 233 Handling partitioning strategies 233 Using range partitioning 234 Utilizing list partitioning 236 Handling hash partitions 238 Adjusting parameters for good query performance 239 Speeding up sorting 243 Speeding up administrative tasks 246 Making use of parallel queries 247 What is PostgreSQL able to do in parallel? 252 Parallelism in practice 252 Introducing JIT compilation 253 Configuring JIT 254 Running queries 255 Summary 257 7 Writing Stored Procedures 259 Understanding stored procedure languages 259 Understanding the fundamentals of stored procedures versus functions 261 The anatomy of a function 261 Exploring various stored procedure languages 265 Introducing PL/pgSQL 267 Writing stored procedures in PL/pgSQL 290 Introducing PL/Perl 292 Introducing PL/Python 300 Improving functions 304 Reducing the number of function calls 304 Using functions for various purposes 307 Summary 309 Questions 309 8 Managing PostgreSQL Security 311 Managing network security 311 Understanding bind addresses and connections 312 Managing the pg_hba.conf file 316 Handling instance-level security 321 Defining database-level security 326 Adjusting schema-level permissions 328 Working with tables 331 Handling column-level security 332 Configuring default privileges 334 Digging into row-level security 335 Inspecting permissions 340 Reassigning objects and dropping users 344 Summary 345 Questions 346 9 Handling Backup and Recovery 347 Performing simple dumps 347 Running pg_dump 348 Passing passwords and connection information 349 Extracting subsets of data 352 Handling various formats 352 Replaying backups 355 Handling global data 356 Summary 357 Questions 357 10 Making Sense of Backups and Replication 359 Understanding the transaction log 360 Looking at the transaction log 360 Understanding checkpoints 361 Optimizing the transaction log 362 Transaction log archiving and recovery 363 Configuring for archiving 364 Using archiving libraries 365 Configuring the pg_hba.conf file 365 Creating base backups 366 Replaying the transaction log 371 Cleaning up the transaction log archive 375 Setting up asynchronous replication 376 Performing a basic setup 377 Halting and resuming replication 379 Checking replication to ensure availability 380 Performing failovers and understanding timelines 383 Managing conflicts 385 Making replication more reliable 387 Upgrading to synchronous replication 388 Adjusting durability 389 Making use of replication slots 391 Handling physical replication slots 392 Handling logical replication slots 394 Making use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commands 397 Setting up an HA cluster using Patroni 400 Understand how Patroni operates 40 Installing Patroni 401 Creating Patroni templates 406 Summary 418 Questions 419 11 Deciding on Useful Extensions 421 Understanding how extensions work 421 Checking for available extensions 423 Making use of contrib modules 426 Using the adminpack module 426 Applying bloom filters 428 Deploying btree_gist and btree_gin 431 dblink – considering phasing out 432 Fetching files with file_fdw 433 Inspecting storage using pageinspect 435 Investigating caching with pg_buffercache 437 Encrypting data with pgcrypto 439 Prewarming caches with pg_prewarm 439 Inspecting performance with pg_stat_statements 441 Inspecting storage with pgstattuple 441 Fuzzy searching with pg_trgm 443 Connecting to remote servers using postgres_fdw 443 Other useful extensions 449 Summary 449 12 Troubleshooting PostgreSQL 451 Approaching an unknown database 451 Inspecting pg_stat_activity 452 Querying pg_stat_activity 452 Checking for slow queries 455 Inspecting individual queries 456 Digging deeper with perf 457 Inspecting the log 458 Checking for missing indexes 459 Checking for memory and I/O 460 Understanding noteworthy error scenarios 462 Facing clog corruption 462 Understanding checkpoint messages 463 Managing corrupted data pages 464 Careless connection management 465 Fighting table bloat 465 Summary 466 Questions 466 13 Migrating to PostgreSQL 467 Migrating SQL statements to PostgreSQL 467 Using LATERAL joins 468 Using grouping sets 468 Using the WITH clause – common table expressions 469 Using the WITH RECURSIVE clause 470 Using the FILTER clause 471 Using windowing functions 472 Using ordered sets – the WITHIN GROUP clause 472 Using the TABLESAMPLE clause 473 Using limit/offset 474 Using the OFFSET clause 475 Using temporal tables 475 Matching patterns in time series 476 Moving from Oracle to PostgreSQL 476 Using the oracle_fdw extension to move data 476 Using ora_migrator for fast migration 479 CYBERTEC Migrator – migration for the “big boys” 480 Using Ora2Pg to migrate from Oracle 481 Common pitfalls 483 Summary 485 Index 487 Other Books You May Enjoy 500
Schönig Hans / Шениг Ганс - Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications, 5th Edition / Освоение PostgreSQL 15, 5-е издание [2023, PDF, ENG] download torrent for free and without registration
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum You cannot attach files in this forum You can download files in this forum
![[Quote]](./templates/default/images/lang/en/icon_quote.gif "Reply with quote"){kind=icon}
![[Profile]](./templates/default/images/lang/en/icon_profile.gif "View user's profile"){kind=icon}
![[PM]](./templates/default/images/lang/en/icon_pm.gif "Send private message"){kind=icon}
