PostgreSQL 10 Administration Cookbook: Over 165 effective recipes for database management and maintenance in PostgreSQL 10. 4 Ed

Title Page....2 Copyright and Credits....3 PostgreSQL 10 Administration Cookbook....4 Packt Upsell....5 Why subscribe?....6 PacktPub.com....7 Contributors....8 About the authors....9 About the reviewer....10 Packt is searching for authors like you....11 Preface....33 Who this book is for....34 What this book covers....35 To get the most out of this book....38 Download the example code files....39 Download the color images....40 Conventions used....41 Sections....42 Getting ready....43 How to do it&x2026;....44 How it works&x2026;....45 There's more&x2026;....46 See also....47 Get in touch....48 Reviews....49 First Steps....50 Introduction....51 Introducing PostgreSQL 10....52 What makes PostgreSQL different?....53 Robustness....55 Security....56 Ease of use....57 Extensibility....58 Performance and concurrency....59 Scalability....60 SQL and NoSQL....61 Popularity....62 Commercial support....63 Research and development funding....64 Getting PostgreSQL....65 How to do it.......66 How it works.......67 There's more&x2026;....68 Connecting to the PostgreSQL server....69 Getting ready....70 How to do it&x2026;....71 How it works&x2026;....73 There's more&x2026;....75 See also....76 Enabling access for networkremote users....77 How to do it&x2026;....78 How it works&x2026;....79 There's more&x2026;....81 See also....82 Using graphical administration tools....83 How to do it&x2026;....84 How it works&x2026;....88 OmniDB....90 See also....93 Using the psql query and scripting tool....94 Getting ready....95 How to do it&x2026;....96 How it works&x2026;....98 There's more&x2026;....100 See also....101 Changing your password securely....102 How to do it&x2026;....103 How it works&x2026;....104 Avoiding hardcoding your password....105 Getting ready....106 How to do it&x2026;....107 How it works&x2026;....108 There's more&x2026;....109 Using a connection service file....110 How to do it&x2026;....111 How it works&x2026;....112 Troubleshooting a failed connection....113 How to do it&x2026;....114 There's more&x2026;....116 Exploring the Database....117 Introduction....118 What version is the server?....119 How to do it&x2026;....120 How it works&x2026;....121 There's more&x2026;....122 What is the server uptime?....123 How to do it&x2026;....124 How it works.......125 See also....126 Locating the database server files....127 Getting ready....128 How to do it.......129 How it works.......131 There's more&x2026;....133 Locating the database server's message log....135 Getting ready....136 How to do it.......137 How it works.......138 There's more.......140 Locating the database's system identifier....141 Getting ready....142 How to do it&x2026;....143 How it works&x2026;....144 Listing databases on this database server....146 How to do it&x2026;....147 How it works.......148 There's more.......149 How many tables are there in a database?....151 How to do it.......152 How it works&x2026;....154 There's more&x2026;....155 How much disk space does a database use?....157 How to do it.......158 How it works.......159 How much disk space does a table use?....160 How to do it&x2026;....161 How it works&x2026;....162 There's more&x2026;....163 Which are my biggest tables?....164 How to do it.......165 How it works&x2026;....166 How many rows are there in a table?....167 How to do it&x2026;....168 How it works.......169 Quickly estimating the number of rows in a table....171 How to do it&x2026;....172 How it works&x2026;....173 There's more&x2026;....174 Function 1 &x2013; Estimating the number of rows....176 Function 2 &x2013; Computing the size of a table without locks....177 Listing extensions in this database....179 Getting ready....180 How to do it&x2026;....181 How it works&x2026;....182 There's more&x2026;....183 Understanding object dependencies....184 Getting ready....185 How to do it&x2026;....186 How it works&x2026;....187 There's more&x2026;....188 Configuration....189 Introduction....190 Reading the fine manual....191 How to do it&x2026;....192 How it works&x2026;....193 There's more&x2026;....194 Planning a new database....195 Getting ready....196 How to do it&x2026;....197 How it works&x2026;....198 There's more&x2026;....199 Changing parameters in your programs....200 How to do it&x2026;....201 How it works&x2026;....202 There's more&x2026;....203 Finding the current configuration settings....204 How to do it&x2026;....205 There's more&x2026;....207 How it works&x2026;....208 Which parameters are at non-default settings?....209 How to do it&x2026;....210 How it works.......211 There's more.......212 Updating the parameter file....213 Getting ready....214 How to do it&x2026;....215 How it works&x2026;....217 There's more&x2026;....218 Setting parameters for particular groups of users....219 How to do it&x2026;....220 How it works&x2026;....221 The basic server configuration checklist....222 Getting ready....223 How to do it&x2026;....224 There's more&x2026;....225 Adding an external module to PostgreSQL....227 Getting ready....228 How to do it&x2026;....229 Installing modules using a software installer....230 Installing modules from PGXN....232 Installing modules from a manually downloaded package....233 Installing modules from source code....234 How it works.......235 Using an installed module....236 Getting ready....237 How to do it&x2026;....238 How it works.......239 Managing installed extensions....240 How to do it&x2026;....241 How it works&x2026;....243 There's more&x2026;....244 Server Control....245 Introduction....246 Starting the database server manually....248 Getting ready....249 How to do it&x2026;....250 How it works&x2026;....253 Stopping the server safely and quickly....254 How to do it&x2026;....255 How it works&x2026;....256 See also....257 Stopping the server in an emergency....258 How to do it&x2026;....259 How it works&x2026;....260 Reloading the server configuration files....261 How to do it&x2026;....262 How it works&x2026;....264 There's more&x2026;....265 Restarting the server quickly....266 How to do it&x2026;....267 There's more&x2026;....269 Preventing new connections....270 How to do it&x2026;....271 How it works&x2026;....273 Restricting users to only one session each....274 How to do it&x2026;....275 How it works&x2026;....276 Pushing users off the system....277 How to do it&x2026;....278 How it works&x2026;....280 Deciding on a design for multitenancy....282 How to do it&x2026;....283 How it works&x2026;....285 Using multiple schemas....286 Getting ready....287 How to do it&x2026;....288 How it works&x2026;....290 Giving users their own private database....291 Getting ready....292 How to do it&x2026;....293 How it works&x2026;....294 There's more&x2026;....295 See also....296 Running multiple servers on one system....297 Getting ready....298 How to do it&x2026;....299 How it works&x2026;....301 Setting up a connection pool....302 Getting ready....303 How to do it&x2026;....304 How it works&x2026;....306 There's more&x2026;....307 Accessing multiple servers using the same host and port....309 Getting ready....310 How to do it&x2026;....311 There's more&x2026;....313 Tables and Data....314 Choosing good names for database objects....315 Getting ready....316 How to do it&x2026;....317 There's more&x2026;....318 Handling objects with quoted names....320 Getting ready....321 How to do it.......322 How it works&x2026;....323 There's more&x2026;....324 Enforcing the same name and definition for columns....325 Getting ready....326 How to do it.......327 How it works&x2026;....329 There's more&x2026;....330 Identifying and removing duplicates....331 Getting ready....332 How to do it&x2026;....333 How it works&x2026;....336 There's more&x2026;....338 Preventing duplicate rows....339 Getting ready....340 How to do it&x2026;....341 How it works&x2026;....344 There's more.......345 Duplicate indexes....346 Uniqueness without indexes....347 Real-world example &x2013; IP address range allocation....348 Real-world example &x2013; range of time....350 Real-world example &x2013; prefix ranges....351 Finding a unique key for a set of data....352 Getting ready....353 How to do it&x2026;....354 How it works&x2026;....357 Generating test data....358 How to do it.......359 How it works&x2026;....362 There's more&x2026;....363 See also....364 Randomly sampling data....365 How to do it&x2026;....366 How it works.......368 Loading data from a spreadsheet....370 Getting ready....371 How to do it.......372 How it works.......374 There's more.......375 Loading data from flat files....376 Getting ready....377 How to do it.......378 How it works&x2026;....380 There's more&x2026;....381 Security....383 Introduction....384 Typical user role....385 The PostgreSQL superuser....386 How to do it&x2026;....387 How it works&x2026;....388 There's more&x2026;....389 Other superuser-like attributes....390 Attributes are never inherited....391 See also....392 Revoking user access to a table....393 Getting ready....394 How to do it&x2026;....395 How it works&x2026;....397 There's more&x2026;....398 Database creation scripts....399 Default search path....400 Securing views....401 Granting user access to a table....403 Getting ready....404 How to do it&x2026;....405 How it works.......406 There's more&x2026;....407 Access to the schema....408 Granting access to a table through a group role....409 Granting access to all objects in a schema....410 Granting user access to specific columns....411 Getting ready....412 How to do it&x2026;....413 How it works&x2026;....414 There's more&x2026;....415 Granting user access to specific rows....416 Getting ready....417 How to do it&x2026;....418 How it works&x2026;....420 There's more.......421 Creating a new user....422 Getting ready....423 How to do it.......424 How it works&x2026;....425 There's more&x2026;....426 Temporarily preventing a user from connecting....427 Getting ready....428 How to do it&x2026;....429 How it works.......430 There's more&x2026;....431 Limiting the number of concurrent connections by a user....432 Forcing NOLOGIN users to disconnect....433 Removing a user without dropping their data....434 Getting ready....435 How to do it&x2026;....436 How it works&x2026;....437 Checking whether all users have a secure password....438 How to do it&x2026;....439 How it works&x2026;....440 Giving limited superuser powers to specific users....441 Getting ready....442 How to do it&x2026;....443 How it works&x2026;....445 There's more&x2026;....446 Writing a debugging_info function for developers....447 Auditing database access....448 Getting ready....449 Auditing SQL....450 Auditing table access....452 Managing the audit log....453 Auditing data changes....454 Always knowing which user is logged in....456 Getting ready....457 How to do it&x2026;....458 How it works&x2026;....459 There's more&x2026;....460 Not inheriting user attributes....461 Integrating with LDAP....462 Getting ready....463 How to do it&x2026;....464 How it works&x2026;....465 There's more&x2026;....466 Setting up the client to use LDAP....467 Replacement for the User Name Map feature....468 See also....469 Connecting using SSL....470 Getting ready....471 How to do it&x2026;....472 How it works&x2026;....473 There's more&x2026;....474 Getting the SSL key and certificate....475 Setting up a client to use SSL....476 Checking server authenticity....478 Using SSL certificates to authenticate....479 Getting ready....480 How to do it&x2026;....481 How it works&x2026;....482 There's more&x2026;....483 Avoiding duplicate SSL connection attempts....484 Using multiple client certificates....485 Using the client certificate to select the database user....486 See also....487 Mapping external usernames to database roles....488 Getting ready....489 How to do it&x2026;....490 How it works&x2026;....491 There's more&x2026;....492 Encrypting sensitive data....493 Getting ready....494 How to do it&x2026;....495 How it works&x2026;....497 There's more&x2026;....498 For really sensitive data....499 For really, really, really sensitive data!....500 See also....501 Database Administration....502 Introduction....503 Writing a script that either succeeds entirely or fails entirely....504 How to do it&x2026;....505 How it works&x2026;....506 There's more&x2026;....508 Writing a psql script that exits on the first error....510 Getting ready....511 How to do it&x2026;....512 How it works&x2026;....513 There's more&x2026;....514 Using psql variables....515 Getting ready....516 How to do it&x2026;....517 How it works&x2026;....518 There&x2019;s more&x2026;....519 Placing query output into psql variables....520 Getting ready....521 How to do it&x2026;....522 How it works&x2026;....523 There&x2019;s more&x2026;....524 Writing a conditional psql script....525 Getting ready....526 How to do it&x2026;....527 How it works&x2026;....528 There&x2019;s more&x2026;....529 Investigating a psql error....530 Getting ready....531 How to do it&x2026;....532 There's more&x2026;....533 Performing actions on many tables....534 Getting ready....535 How to do it&x2026;....536 How it works&x2026;....538 There's more&x2026;....540 Addingremoving columns on a table....543 How to do it&x2026;....544 How it works&x2026;....545 There's more&x2026;....547 Changing the data type of a column....549 Getting ready....550 How to do it&x2026;....551 How it works&x2026;....553 There's more&x2026;....554 Changing the definition of a data type....556 Getting ready....557 How to do it&x2026;....558 How it works&x2026;....559 There's more&x2026;....560 Addingremoving schemas....561 How to do it&x2026;....562 There's more&x2026;....564 Using schema-level privileges....565 Moving objects between schemas....566 How to do it&x2026;....567 How it works&x2026;....568 There's more&x2026;....569 Addingremoving tablespaces....570 Getting ready....571 How to do it&x2026;....572 How it works&x2026;....574 There's more&x2026;....575 Putting pg_wal on a separate device....576 Tablespace-level tuning....577 Moving objects between tablespaces....578 Getting ready....579 How to do it&x2026;....580 How it works&x2026;....581 There's more&x2026;....582 Accessing objects in other PostgreSQL databases....583 Getting ready....584 How to do it&x2026;....585 How it works&x2026;....590 There's more&x2026;....592 There's more&x2026;....594 Accessing objects in other foreign databases....595 Getting ready....596 How to do it&x2026;....597 How it works&x2026;....598 There's more&x2026;....599 Updatable views....600 Getting ready....601 How to do it&x2026;....603 How it works&x2026;....607 There's more&x2026;....609 Using materialized views....611 Getting ready....612 How to do it&x2026;....613 How it works&x2026;....614 There's more&x2026;....615 Monitoring and Diagnosis....616 Providing PostgreSQL information to monitoring tools....619 Finding more information about generic monitoring tools....621 Real-time viewing using pgAdmin&xA0;or OmniDB....622 Getting ready....623 How to do it&x2026; (with pgAdmin)....624 How to do it&x2026; (with OmniDB)....626 Checking whether a user is connected....628 Getting ready....629 How to do it&x2026;....630 How it works&x2026;....631 There's more&x2026;....632 Checking whether a computer is connected....633 How to do it&x2026;....634 There's more&x2026;....635 Repeatedly executing a query in psql....636 How to do it&x2026;....637 There's more&x2026;....638 Checking which queries are running....639 Getting ready....640 How to do it&x2026;....641 How it works&x2026;....642 There's more&x2026;....643 Catching queries that&xA0;only run for a few milliseconds....644 Watching the longest queries....645 Watching queries from ps....646 See also....647 Checking which queries are active or blocked....648 Getting ready....649 How to do it&x2026;....650 How it works&x2026;....651 There's more&x2026;....652 No need for the true part....653 Do we catch all queries waiting on locks?....654 Knowing who is blocking a query....655 Getting ready....656 How to do it&x2026;....657 How it works&x2026;....658 Killing a specific session....659 How to do it&x2026;....660 How it works&x2026;....661 There's more&x2026;....662 Try to cancel the query first....663 What if the backend won't terminate?....664 Using statement_timeout to clean up queries that take too long to run....665 Killing idle in transaction queries....666 Killing the backend from the command line....667 Detecting an in-doubt prepared transaction....668 How to do it&x2026;....669 Knowing whether anybody is using a specific table....670 Getting ready....671 How to do it&x2026;....672 How it works&x2026;....673 There's more.......674 The quick-and-dirty way....675 Collecting daily usage statistics....676 Knowing when a table was last used....677 Getting ready....678 How to do it&x2026;....679 How it works.......681 There's more&x2026;....682 Usage of disk space by temporary data....683 Getting ready....684 How to do it&x2026;....685 How it works&x2026;....687 There's more&x2026;....688 Finding out whether a temporary file is in use any more....689 Logging temporary file usage....690 Understanding why queries slow down....691 Getting ready....692 How to do it&x2026;....693 How it works&x2026;....694 There's more&x2026;....695 Do the queries return significantly more data than they did earlier?....696 Do the queries also run slowly when they are run alone?....697 Is the second run of the same query also slow?....698 Table and index bloat....699 See also....700 Investigating and reporting a bug....701 Getting ready....702 How to do it&x2026;....703 How it works&x2026;....704 Producing a daily summary of log file errors....705 Getting ready....706 How to do it&x2026;....709 How it works&x2026;....710 There's more&x2026;....711 Analyzing the real-time performance of your queries....712 Getting ready....713 How to do it&x2026;....714 How it works&x2026;....715 There's more&x2026;....716 Regular Maintenance....717 Controlling automatic database maintenance....719 Getting ready....720 How to do it&x2026;....721 How it works&x2026;....723 There's more&x2026;....726 See also....728 Avoiding auto-freezing and page corruptions....729 How to do it&x2026;....730 Removing issues that cause bloat....732 Getting ready....733 How to do it&x2026;....734 How it works&x2026;....735 There's more&x2026;....736 Removing old prepared transactions....737 Getting ready....738 How to do it&x2026;....739 How it works&x2026;....740 There's more&x2026;....741 Actions for heavy users of temporary tables....743 How to do it&x2026;....744 How it works&x2026;....745 Identifying and fixing bloated tables and indexes....746 How to do it&x2026;....748 How it works&x2026;....750 There's more&x2026;....753 Monitoring and tuning vacuum....754 Getting ready....755 How to do it&x2026;....756 How it works&x2026;....757 There's more&x2026;....759 Maintaining indexes....760 Getting ready....761 How to do it&x2026;....762 How it works&x2026;....763 There's more&x2026;....764 Adding a constraint without checking existing rows....765 Getting ready....766 How to do it&x2026;....767 How it works&x2026;....769 Finding unused indexes....770 How to do it&x2026;....771 How it works&x2026;....772 Carefully removing unwanted indexes....773 Getting ready....774 How to do it&x2026;....775 How it works&x2026;....776 Planning maintenance....777 How to do it&x2026;....778 How it works&x2026;....779 Performance and Concurrency....781 Introduction....782 Finding slow SQL statements....783 Getting ready....784 How to do it&x2026;....785 How it works&x2026;....786 There's more&x2026;....787 Collect regular statistics from pg_stat views....788 Getting ready....789 How to do it&x2026;....790 How it works&x2026;....791 There's more&x2026;....792 Another statistics collection package....793 Finding out what makes SQL slow....794 Getting ready....795 How to do it&x2026;....796 There's more&x2026;....799 Not enough CPU power or disk IO capacity for the current load....800 Locking problems....801 EXPLAIN options....802 See also....803 Reducing the number of rows returned....804 How to do it&x2026;....805 There's more&x2026;....807 See also....808 Simplifying complex SQL queries....809 Getting ready....810 How to do it&x2026;....812 There's more&x2026;....815 Using materialized views (long-living, temporary tables)....817 Using set-returning functions for some parts of queries....818 Speeding up queries without rewriting them....819 How to do it&x2026;....820 Increasing work_mem....821 More ideas with indexes....822 There's more&x2026;....824 Time Series Partitioning....825 Using a TABLESAMPLE view....827 In case of many updates, set fillfactor on the table....828 Rewriting the schema&xA0;&x2013;&xA0;a more radical approach....829 Discovering why a query is not using an index....830 Getting ready....831 How to do it&x2026;....832 How it works&x2026;....833 There's more&x2026;....834 Forcing a query to use an index....835 Getting ready....836 How to do it&x2026;....837 There's more&x2026;....839 There's even more....840 Using parallel query....841 How to do it&x2026;....842 How it works&x2026;....843 There's more&x2026;....845 Using optimistic locking....846 How to do it&x2026;....847 How it works&x2026;....848 There's more&x2026;....849 Reporting performance problems....851 How to do it&x2026;....852 There's more&x2026;....853 Backup and Recovery....854 Introduction....855 Understanding and controlling crash recovery....856 How to do it&x2026;....857 How it works&x2026;....859 There's more&x2026;....860 Planning backups....861 How to do it&x2026;....863 Hot logical backups of one database....866 How to do it&x2026;....867 How it works&x2026;....868 There's more&x2026;....870 See also....871 Hot logical backups of all databases....872 How to do it&x2026;....873 How it works&x2026;....874 See also....875 Backups of database object definitions....876 How to do it&x2026;....877 There's more&x2026;....878 Standalone hot physical database backup....879 Getting ready....880 How to do it&x2026;....881 How it works&x2026;....884 There's more&x2026;....886 See also....887 Hot physical backup and continuous archiving....888 Getting ready....889 How to do it&x2026;....890 How it works&x2026;....892 Recovery of all databases....894 Getting ready....895 How to do it&x2026;....896 Logical &x2013; from custom dump taken with pg_dump -F c....897 Logical &x2013; from the script dump created by pg_dump -F p....898 Logical &x2013; from the script dump created by pg_dumpall....899 Physical....900 How it works&x2026;....902 There's more&x2026;....903 See also....904 Recovery to a point in time....905 Getting ready....906 How to do it&x2026;....907 How it works&x2026;....908 There's more&x2026;....910 See also....911 Recovery of a droppeddamaged table....912 How to do it&x2026;....913 Logical - from custom dump taken with pg_dump -F c....914 Logical &x2013; from the script dump....916 Physical....917 How it works&x2026;....918 See also....919 Recovery of a droppeddamaged database....920 How to do it&x2026;....921 Logical &x2013; from the custom dump -F c....922 Logical &x2013; from the script dump created by pg_dump....923 Logical &x2013; from the script dump created by pg_dumpall....924 Physical....925 Improving performance of backuprecovery....926 Getting ready....927 How to do it&x2026;....928 How it works&x2026;....930 There's more&x2026;....931 See also....932 Incrementaldifferential backup and restore....933 How to do it&x2026;....934 How it works&x2026;....936 There's more&x2026;....937 Hot physical backups with Barman....939 Getting ready....941 How to do it&x2026;....943 How it works&x2026;....947 There's more&x2026;....949 Recovery with Barman....952 Getting ready....953 How to do it&x2026;....955 How it works&x2026;....957 There's more&x2026;....958 Replication and Upgrades....961 Replication concepts....963 Topics....964 Basic concepts....965 History and scope....966 Practical aspects....968 Data loss....970 Single-master replication....971 Multinode architectures....972 Clustered or massively parallel databases....973 Multimaster replication....974 Scalability tools....975 Other approaches to replication....976 Replication best practices....977 Getting ready....978 How to do it&x2026;....979 There's more&x2026;....981 Setting up file-based replication&xA0;&x2013; deprecated....982 Getting ready....983 How to do it&x2026;....984 How it works&x2026;....986 There's more&x2026;....988 See also....989 Setting up streaming replication....990 Getting ready....991 How to do it&x2026;....992 How it works&x2026;....995 There's more&x2026;....996 Setting up streaming replication security....998 Getting ready....999 How to do it&x2026;....1000 How it works&x2026;....1001 There's more&x2026;....1002 Hot Standby and read scalability....1003 Getting ready....1004 How to do it&x2026;....1006 How it works&x2026;....1009 Managing streaming replication....1010 Getting ready....1011 How to do it&x2026;....1012 There's more&x2026;....1014 See also....1015 Using repmgr....1016 Getting ready....1017 How to do it&x2026;....1018 How it works&x2026;....1020 There's more&x2026;....1021 Using replication slots....1022 Getting ready....1023 How to do it&x2026;....1024 There's more&x2026;....1025 See also....1026 Monitoring replication....1027 Getting ready....1028 How to do it&x2026;....1030 There's more&x2026;....1033 Performance and synchronous replication....1034 Getting ready....1035 How to do it.......1036 How it works&x2026;....1038 There's more&x2026;....1039 Delaying, pausing, and synchronizing replication....1040 Getting ready....1041 How to do it&x2026;....1042 There's more&x2026;....1043 See also....1045 Logical replication....1046 Getting ready....1048 How to do it&x2026;....1051 How it works&x2026;....1052 There's more&x2026;....1054 See also....1055 Bi-directional replication....1057 Getting ready....1059 How to do it&x2026;....1060 How it works.......1061 There's more&x2026;....1062 Archiving transaction log data....1063 Getting ready....1064 How to do it&x2026;....1065 There's more&x2026;....1066 See also....1067 Upgrading minor releases....1068 Getting ready....1069 How to do it&x2026;....1070 How it works&x2026;....1071 Major upgrades in-place....1072 Getting ready....1073 How to do it&x2026;....1074 How it works&x2026;....1075 Major upgrades online....1076 How to do it.......1077 How it works.......1078 Other Books You May Enjoy....1079 Leave a review - let other readers know what you think....1081