documentsn.com

Introducing PL/PerlUsing PL/Perl for data type abstractionDeciding between PL/Perl and PL/PerlUMaking use of the SPI interfaceUsing the SPI for set returning functionsEscaping in PL/Perl and support functionsSharing data across function callsWriting triggers in PerlIntroducing PL/PythonWriting simple PL/Python codeUsing the SPI interfaceHandling errorsImproving functionsReducing the number of function callsUsing cached plansAssigning costs to functionsUsing functions for various purposesSummaryQuestions8. ManagingPostgreSQL SecurityManaging network securityUnderstanding bind addresses and connectionsInspecting connections and performanceLiving in a world without TCPManaging pg hba.confHandling SSLHandling instance-level securityCreating and modifying usersDefining database-level securityAdjusting schema-level permissionsWorking with tablesHandling column-level securityConfiguring default privilegesDigging into RLSInspecting permissionsReassigning objects and dropping usersSummary

Questions9. HandlingBackup and RecoveryPerforming simple dumpsRunning pg dumpPassing passwords and connection informationUsing environment variablesMaking use of .pgpassUsing service filesExtracting subsets of dataHandling various formatsReplaying backupsHandling global dataSummaryQuestions10. MakingSense of Backups and ReplicationUnderstanding the transaction logLooking at the transaction logUnderstanding checkpointsOptimizing the transaction logTransaction log archiving and recoveryConfiguring for archivingConfiguring the pg hba.conf fileCreating base backupsReducing the bandwidth of backupsMapping tablespacesUsing different formatsTesting transaction log archivingReplaying the transaction logFinding the right timestampCleaning up the transaction log archiveSetting up asynchronous replicationPerforming a basic setupImproving securityHalting and resuming replicationChecking replication to ensure availabilityPerforming failovers and understanding timelinesManaging conflicts

Making replication more reliableUpgrading to synchronous replicationAdjusting durabilityMaking use of replication slotsHandling physical replication slotsHandling logical replication slotsUse cases for logical replication slotsMaking use of the CREATE PUBLICATION and CREATE SUBSCRIPTION commandsSummaryQuestions11. Decidingon Useful ExtensionsUnderstanding how extensions workChecking for available extensionsMaking use of contrib modulesUsing the adminpack moduleApplying bloom filtersDeploying btree gist and btree gindblink – considering phasing outFetching files with file fdwInspecting storage using pageinspectInvestigating caching with pg buffercacheEncrypting data with pgcryptoPrewarming caches with pg prewarmInspecting performance with pg stat statementsInspecting storage with pgstattupleFuzzy searching with pg trgmConnecting to remote servers using postgres fdwHandling mistakes and typosOther useful extensionsSummary12. TroubleshootingPostgreSQLApproaching an unknown databaseInspecting pg stat activityQuerying pg stat activityTreating Hibernate statementsFiguring out where queries come fromChecking for slow queries

Inspecting individual queriesDigging deeper with perfInspecting the logChecking for missing indexesChecking for memory and I/OUnderstanding noteworthy error scenariosFacing clog corruptionUnderstanding checkpoint messagesManaging corrupted data pagesCareless connection managementFighting table bloatSummaryQuestions13. Migratingto PostgreSQLMigrating SQL statements to PostgreSQLUsing lateral joinsSupporting lateral joinsUsing grouping setsSupporting grouping setsUsing the WITH clause – common table expressionsSupporting the WITH clauseUsing the WITH RECURSIVE clauseSupporting the WITH RECURSIVE clauseUsing the FILTER clauseSupporting the FILTER clauseUsing windowing functionsSupporting windowing and analyticsUsing ordered sets – the WITHIN GROUP clauseSupporting the WITHIN GROUP clauseUsing the TABLESAMPLE clauseSupporting the TABLESAMPLE clauseUsing limit/offsetSupporting the FETCH FIRST clauseUsing the OFFSET clauseSupporting the OFFSET clauseUsing temporal tablesSupporting temporal tables

Matching patterns in time seriesMoving from Oracle to PostgreSQLUsing the oracle fdw extension to move dataUsing ora migrator for fast migrationCYBERTEC Migrator – migration for the "big boys"Using Ora2Pg to migrate from OracleCommon pitfallsHandling data in MySQL and MariaDBChanging column definitionsHandling null valuesExpecting problemsMigrating data and schemasUsing pg chameleonUsing FDWsSummaryOther Books You May EnjoyLeave a review - let other readers know what you think

PrefaceThis updated fourth edition of Mastering PostgreSQL 13 will help youunderstand PostgreSQL administration and build dynamic databasesolutions for enterprise apps with the latest release of PostgreSQL, teachingyou how to design both physical and technical aspects of systemarchitecture with ease.Starting with an introduction to the newly released features in PostgreSQL13, this book will help you build efficient and fault-tolerant PostgreSQLapps. You'll also delve into advanced PostgreSQL features, includinglogical replication, database clusters, performance tuning, advancedindexing, monitoring, and user management, to manage and maintain yourdatabase. You will then work with the PostgreSQL optimizer, configurePostgreSQL for high speed, and move from Oracle to PostgreSQL. Afterthat, you'll cover transactions, locking, and indexes, and learn how toperform query optimization for improved performance. Later, you'll focuson how to manage network security, and delve into backup and replicationwhile exploring useful PostgreSQL extensions to help you optimize theperformance of large databases.By the end of this book, you'll be able to get the most out of your databaseby performing advanced administrative tasks.

Who this book is forThis database administrator book is for PostgreSQL developers andadministrators and database professionals who want to implement advancedfunctionalities and master complex administrative tasks with PostgreSQL13. Prior experience in PostgreSQL and familiarity with the basics ofdatabase administration are expected.

What this book covers, PostgreSQL 13 Overview, provides an overview of PostgreSQLand its features. You will learn about the new functionalities available inPostgreSQL 13.Chapter 1, Understanding Transactions and Locking, covers one of the mostimportant aspects of any database system: proper database work is usuallynot possible without the existence of transactions. Understandingtransactions and locking is vital to performance, as well as professionalwork.Chapter 2, Making Use of Indexes, covers everything you need to know aboutindexes. Indexes are key to performance and are therefore an importantcornerstone if you want a good user experience and high throughput. All theimportant aspects of indexing will be covered.Chapter 3, Handling Advanced SQL, introduces you to some of the mostimportant concepts of modern SQL. You will learn about windowingfunctions as well as other important current elements of SQL.Chapter 4, Log Files and System Statistics, guides you through administrativetasks such as log file management and monitoring. You will learn how toinspect your servers and extract runtime information from PostgreSQL.Chapter 5, Optimizing Queries for Good Performance, tells you everythingyou need to know about good PostgreSQL performance. The chapter coversSQL tuning as well as information about memory management.Chapter 6, Writing Stored Procedures, teaches you some of the advancedtopics related to server-side code. The most important server-sideprogramming languages are covered and important aspects are pointed out.Chapter 7

, Managing PostgreSQL Security, is designed to help you improvethe security of your server. The chapter features everything from usermanagement to Row-Level Security (RLS). Information about encryptionis also included.Chapter 8, Handling Backup and Recovery, is all about backups and datarecovery. You will learn how to back up your data, which will enable you torestore things in the event of a disaster.Chapter 9, Making Sense of Backups and Replication, is all aboutredundancy. You will learn how to asynchronously and synchronouslyreplicate PostgreSQL database systems. All modern features are covered asextensively as possible.Chapter 10, Deciding on Useful Extensions, describes widely used modulesthat add more functionality to PostgreSQL. You will learn about the mostcommon extensions.Chapter 11, Troubleshooting PostgreSQL, offers a systematic approach tofixing problems in PostgreSQL. It will enable you to spot commonproblems and approach them in an organized way.Chapter 12, Migrating to PostgreSQL, is the final chapter of this book andshows you how to migrate from commercial databases to PostgreSQL. Thechapter covers the most important databases migrated these days.Chapter 13To get the most out of this bookThis book has been written for a broad audience. In order to follow theexamples presented in this book, it makes sense to have at least someexperience with SQL and, perhaps, even PostgreSQL in general (althoughthis is not mandatory). In general, it is a good idea to have some familiaritywith the Unix command-line interface as well.

Software/hardware covered in the bookOS requirementsPostgreSQL 13Windows/Linux/macOSIf you are using the digital version of this book, we advise you to typethe code yourself or access the code via the GitHub repository (linkavailable in the next section). Doing so will help you avoid any potentialerrors related to the copying and pasting of code.At the end of each chapter, you will find questions for self-assessment. Theanswers to those questions can be found in the GitHub repository (link inthe following section).Download the example code filesYou can download the example code files for this book from GitHub at reSQL-13-Fourth-Edition. In casethere's an update to the code, it will be updated on the existing GitHubrepository.We also have other code bundles from our rich catalog of books and videosavailable at https://github.com/PacktPublishing/. Check them out!Conventions usedThere are a number of text conventions used throughout this book.: Indicates code words in text, database table names, folder names,filenames, file extensions, pathnames, dummy URLs, user input, andTwitter handles. Here is an example: "You can just use COMMIT, COMMIT WORK, orCOMMIT TRANSACTION."CodeInText

A block of code is set as follows:test # \h COMMITCommand: COMMITDescription: commit the current transactionSyntax:COMMIT [ WORK TRANSACTION ] [ AND [ NO ] CHAIN ]URL: Bold: Indicates a new term, an important word, or words that you seeonscreen. For example, words in menus or dialog boxes appear in the textlike this. Here is an example: "For normal Online Transaction Processing(OLTP), read committed has various advantages, because changes can beseen much earlier and the odds of unexpected errors are usually lower."Warnings or important notes appear like this.Tips and tricks appear like this.Get in touchFeedback from our readers is always welcome.General feedback: If you have questions about any aspect of thisbook, mention the book title in the subject of your message and email usat [email protected]: Although we have taken every care to ensure the accuracy of ourcontent, mistakes do happen. If you have found a mistake in this book, wewould be grateful if you would report this to us. Please visit www.packtpub.com/support/errata, selecting your book, clicking on the Errata Submission Formlink, and entering the details.Piracy: If you come across any illegal copies of our works in any form onthe Internet, we would be grateful if you would provide us with the locationaddress or website name. Please contact us at [email protected] with a linkto the material.

If you are interested in becoming an author: If there is a topic that youhave expertise in and you are interested in either writing or contributing to abook, please visit authors.packtpub.com.ReviewsPlease leave a review. Once you have read and used this book, why notleave a review on the site that you purchased it from? Potential readers canthen see and use your unbiased opinion to make purchase decisions, we atPackt can understand what you think about our products, and our authorscan see your feedback on their book. Thank you!For more information about Packt, please visit packt.com.

PostgreSQL 13 OverviewPostgreSQL has come a long way. Over 30 years of constant developmenthave provided us with one of the most exceptional open source productsaround. Over the years, PostgreSQL has become more and more popular.PostgreSQL 13 offers many more features and will give solutions an evenbigger boost than ever before. Many of the new features open the door tofurther development in the future and will enable developers to implementcutting-edge technologies for decades to come. In this chapter, you will beintroduced to these new features and will get an overview of what has beenimproved, added, and even changed.The following topics will be covered:What's new in PostgreSQL 13?SQL and developer-related featuresBackup, recovery, and replicationPerformance-related topicsStorage-related topicsAll relevant features will be covered. Of course, there is always more, andthousands of other tiny changes have made it into PostgreSQL 13. Whatyou will see in this chapter are the highlights of the new release.What's new in PostgreSQL 13?PostgreSQL 13 is a major milestone and many big and small features havemade it into the database core this time. In this chapter, you will beintroduced to the most important developments in the PostgreSQL world.Let's get started and see what the developers have come up with.Digging into SQL and developer-related topics

PostgreSQL 13 provides some new features that are especially important todevelopers.Improving psql command-line handlingIn PostgreSQL 13 a small extension has been committed that basicallyhelps to track the state of a session in psql better. You can now see whether atransaction is running successfully or not. Let's take a look at a simplelisting:test # BEGIN;BEGINtest *# SELECT 1;?column?---------1(1 row)test *# SELECT 1 / 0;ERROR: division by zerotest !# SELECT 1 / 0;ERROR: current transaction is aborted, commands ignored until end of transactionblocktest !# COMMIT;ROLLBACKWhat is so special about this code block? The thing to notice is that theprompt has changed. The reason is that %x has been added to PROMPT1 andPROMPT2. We can now see instantly if a transaction is active, in dire straits, orif no transaction is active. This makes command-line handling a bit easierand the expectation is that fewer errors will happen.Improving pgbenchis one of the most commonly used tools to benchmark a PostgreSQLdatabase. In older versions, pgbench created some standard tables. With theintroduction of PostgreSQL 13, the default dataset can now be partitioned(out of the box).pgbenchHere is what happens:% pgbench -i -s 100 --partitions 10.done in 19.70 s (drop tables 0.00 s, create tables 0.03 s, generate 7.34 s, vacuum10.24 s, primary keys 2.08 s).

test # \d List OF relationsSchema Name TYPE -------- --------------------- ------------------- public pgbench accounts partitioned TABLE public pgbench accounts 1 TABLE public pgbench accounts 10 TABLE public pgbench accounts 2 TABLE public pgbench accounts 3 TABLE .The -- partitions tell pgbench how many partitions to create. It is now possibleto benchmark a partitioned table in relation to a non-partitioned defaultdataset more easily.Generating random UUIDs more easilyIn older versions, we had to load an extension to handle UUIDs. Manyusers did not know that those extensions actually existed or did not want toenable them for whatever reason. In PostgreSQL 13, it is possible togenerate random UUIDs easily without any extension. Here is how itworks:test # SELECT gen random uuid();gen random b5d5-442b-a40b-9c6a9d51a9a1(1 row)Dropping databases fasterThe ability to drop databases faster is an important one and definitely one ofmy favorite new features. What was the problem?postgres # DROP DATABASE test;ERROR: database "test" is being accessed by other usersDETAIL: There is 1 other session using the database.A database could only be dropped if nobody else was connected to thedatabase. For many users, this was hard to achieve. Often, new connectionscome in all the time, and running ALTER DATABASE to block new connectionsand drop existing ones just to kill the database was simply too cumbersome.The WITH (force) option dramatically simplifies things from the end user'sperspective:

postgres # DROP DATABASE test WITH (force);DROP DATABASEThe database will be dropped regardless of what is going on in the system,which makes the process much more reliable.Adding ALTER TABLE . DROP EXPRESSION .PostgreSQL has the ability to materialize the output of an expression. Thefollowing listing shows how a generated column can be used:test # CREATE TABLE t test (aint,bint,c int GENERATED ALWAYS AS (a * b) STORED);CREATE TABLEtest # INSERT INTO t test (a, b) VALUES (10, 20);INSERT 0 1As you can see, one row has been added to the table:test # SELECT * FROM t test;a b c---- ---- ----10 20 200(1 row)The question now is: How can we get rid of generated expressions again?PostgreSQL 13 has the answer:test # ALTER TABLE t test ALTER COLUMN c DROP EXPRESSION ;ALTER TABLEcis now an ordinary column just like all the others:test # \d t test;Table "public.t test"Column Type Collation Nullable Default-------- --------- ----------- ---------- --------a integer b integer c integer You can now insert into c directly without getting an error.Making use of performance improvements

PostgreSQL 13 adds a couple of performance improvements. In thissection, you will learn about some of the more relevant improvements thatcan make a difference in real life. Of course, there are hundreds of smallchanges that have been made here, but the aim of this chapter is really tofocus on the most important aspects that make a real difference.Deduplication of B-tree indexesB-trees are by far the most important index structure in the PostgreSQLworld. Most indexes out there are B-trees. In PostgreSQL 13, B-trees havebeen improved substantially. In older versions, identical entries were storedindividually in the index. If you had 1 million table entries for hans, theindex would actually have 1 million copies of "hans" in the data structure aswell. In PostgreSQL 12, the typical index entry used to be "(value, ctid)".This has been changed. PostgreSQL will now handle duplicates much moreefficiently.Let's create an example and see how this works:test #CREATEtest #INSERTCREATE TABLE tab (a int, b int);TABLEINSERT INTO tab SELECT id, 1 FROM generate series(1, 5000000) AS id;0 5000000In this case, we have created 5 million rows. The a column has 5 milliondifferent values while the b column contains 5 million identical entries. Let'screate two indexes and see what happens:test #CREATEtest #CREATECREATE INDEX idx a ON tab (a);INDEXCREATE INDEX idx b ON tab (b);INDEXThe size of the table itself has not changed in PostgreSQL 13. We will seethe same size as before:test # \d List o

several books about PostgreSQL. About the reviewer Marcelo Diaz is a software engineer with more than 15 years of experience, and with a special focus on PostgreSQL. He is passionate about open source software and has promoted its application in critical and high-demand environments where he has worked as a software developer andFile Size: 3MB