PostgreSQL Administration cookbook: Effective database management using PostgreSQL 9.5/9.6

Cover 1 Copyright 3 Credits 4 About the Authors 5 About the Reviewer 6 www.PacktPub.com 7 Customer Feedback 8 Table of Contents 9 Preface 31 Chapter 1: First Steps 39 Introduction 39 Introducing PostgreSQL 9.6 39 What makes PostgreSQL different? 40 Robustness 42 Security 42 Ease of use 43 Extensibility 43 Performance and concurrency 44 Scalability 44 SQL and NoSQL 44 Popularity 45 Commercial support 45 Research and development funding 46 Getting PostgreSQL 46 How to do it... 46 How it works... 47 There's more... 47 Connecting to the PostgreSQL server 48 Getting ready 48 How to do it... 48 How it works... 49 There's more... 50 See also 51 Enabling access for network/remote users 51 How to do it... 51 How it works... 52 There's more... 54 See also 54 Using graphical administration tools 54 How to do it... 54 How it works... 57 See also 58 Using the psql query and scripting tool 58 Getting ready 59 How to do it... 59 How it works... 61 There's more... 62 See also 62 Changing your password securely 63 How to do it... 63 How it works... 63 Avoiding hardcoding your password 64 Getting ready 64 How to do it... 64 How it works... 65 There's more... 65 Using a connection service file 66 How to do it... 66 How it works... 67 Troubleshooting a failed connection 67 How to do it... 67 There's more... 69 Chapter 2: Exploring the Database 70 Introduction 70 What version is the server? 71 How to do it... 71 How it works... 71 There's more... 72 What is the server uptime? 73 How to do it... 73 How it works... 74 See also 74 Locating the database server files 74 Getting ready 74 How to do it... 75 How it works... 76 There's more... 77 Locating the database server's message log 78 Getting ready 78 How to do it... 78 How it works... 79 There's more... 80 Locating the database's system identifier 81 Getting ready 81 How to do it... 81 How it works... 82 Listing databases on this database server 83 How to do it... 83 How it works... 84 There's more... 85 How many tables are there in a database? 86 How to do it... 86 How it works... 88 There's more... 88 How much disk space does a database use? 89 How to do it... 89 How it works... 90 How much disk space does a table use? 90 How to do it... 90 How it works... 91 There's more... 91 Which are my biggest tables? 92 How to do it... 92 How it works... 93 How many rows are there in a table? 93 How to do it... 93 How it works... 93 Quickly estimating the number of rows in a table 94 How to do it... 95 How it works... 95 There's more... 96 Function 1 - estimating the number of rows 97 Function 2 - computing the size of a table without locks 98 Listing extensions in this database 100 Getting ready 100 How to do it... 100 How it works... 101 There's more... 101 Understanding object dependencies 101 Getting ready 102 How to do it... 102 How it works... 103 There's more... 103 Chapter 3: Configuration 104 Introduction 104 Reading the fine manual 105 How to do it... 105 How it works... 106 There's more... 106 Planning a new database 106 Getting ready 106 How to do it... 107 How it works... 107 There's more... 108 Changing parameters in your programs 108 How to do it... 108 How it works... 109 There's more... 109 Finding the current configuration settings 110 How to do it... 110 There's more... 111 How it works... 112 Which parameters are at non-default settings? 112 How to do it... 113 How it works... 113 There's more... 114 Updating the parameter file 114 Getting ready 114 How to do it... 114 How it works... 115 There's more... 116 Setting parameters for particular groups of users 117 How to do it... 117 How it works... 117 The basic server configuration checklist 118 Getting ready 118 How to do it... 118 There's more... 119 Adding an external module to PostgreSQL 120 Getting ready 121 How to do it... 122 [Installing modules using a software installer] 122 Installing modules using a software installer 122 Installing modules from PGXN 123 Installing modules from a manually downloaded package 124 Installing modules from source code 124 How it works... 125 Using an installed module 125 Getting ready 126 How to do it... 126 How it works... 127 Managing installed extensions 127 [How to do it...] 127 How to do it... 127 How it works... 129 There's more... 130 Chapter 4: Server Control 131 Introduction 131 Starting the database server manually 133 Getting ready 133 How to do it... 133 How it works... 136 Stopping the server safely and quickly 136 How to do it... 136 How it works... 137 See also 138 Stopping the server in an emergency 138 How to do it... 138 How it works... 138 Reloading the server configuration files 139 How to do it... 139 How it works... 140 There's more... 141 Restarting the server quickly 141 How to do it... 142 There's more... 143 Preventing new connections 144 How to do it... 144 How it works... 145 Restricting users to only one session each 145 How to do it... 146 How it works... 146 Pushing users off the system 147 How to do it... 147 How it works... 148 Deciding on a design for multitenancy 149 How to do it... 149 How it works... 150 Using multiple schemas 151 Getting ready 151 How to do it... 151 How it works... 153 Giving users their own private database 153 Getting ready 153 How to do it... 154 How it works... 155 There's more... 155 See also 155 Running multiple servers on one system 155 Getting ready 155 How to do it... 156 How it works... 157 Setting up a connection pool 157 Getting ready 158 How to do it... 158 How it works... 159 There's more... 160 Accessing multiple servers using the same host and port 161 Getting ready 162 How to do it... 162 There's more... 163 Chapter 5: Tables and Data 165 Introduction 165 Choosing good names for database objects 165 Getting ready 166 How to do it... 166 There's more... 167 Handling objects with quoted names 168 Getting ready 168 How to do it... 169 How it works... 169 There's more... 170 Enforcing the same name and definition for columns 170 Getting ready 170 How to do it... 171 How it works... 173 There's more... 173 Identifying and removing duplicates 174 Getting ready 174 How to do it... 175 How it works... 177 There's more... 178 Preventing duplicate rows 179 Getting ready 179 How to do it... 180 How it works... 182 There's more... 182 Duplicate indexes 183 Uniqueness without indexes 183 Real-world example - IP address range allocation 183 Real-world example - range of time 185 Real-world example - prefix ranges 185 Finding a unique key for a set of data 185 Getting ready 185 How to do it... 186 How it works... 188 Generating test data 188 How to do it... 188 How it works... 191 There's more... 191 See also 192 Randomly sampling data 192 How to do it... 193 How it works... 194 Loading data from a spreadsheet 196 Getting ready 196 How to do it... 197 How it works... 199 There's more... 199 Loading data from flat files 199 Getting ready 199 How to do it... 199 How it works... 201 There's more... 201 Chapter 6: Security 203 Introduction 204 Typical user role 204 The PostgreSQL superuser 205 How to do it... 205 How it works... 205 There's more... 206 Other superuser-like attributes 206 Attributes are never inherited 206 See also 206 Revoking user access to a table 206 Getting ready 206 How to do it... 207 How it works... 208 There's more... 209 Database creation scripts 209 Default search path 209 Securing views 210 Granting user access to a table 211 Getting ready 211 How to do it... 211 How it works... 211 There's more... 211 Access to the schema 212 Granting access to a table through a group role 212 Granting access to all objects in a schema 212 Granting user access to specific columns 213 Getting ready 213 How to do it... 213 How it works... 214 There's more... 214 Granting user access to specific rows 215 Getting ready 215 How to do it... 215 How it works... 217 There's more... 217 Creating a new user 218 Getting ready 218 How to do it... 218 How it works... 218 There's more... 219 Temporarily preventing a user from connecting 219 Getting ready 219 How to do it... 219 How it works... 220 There's more... 220 Limiting the number of concurrent connections by a user 220 Forcing NOLOGIN users to disconnect 220 Removing a user without dropping their data 221 Getting ready 221 How to do it... 221 How it works... 221 Checking whether all users have a secure password 222 How to do it... 222 How it works... 223 Giving limited superuser powers to specific users 223 Getting ready 224 How to do it... 224 How it works... 225 There's more... 225 Writing a debugging_info function for developers 226 Auditing DDL changes 227 Getting ready 227 How to do it... 227 How it works... 228 There's more... 228 Was the change committed? 228 Who made the change? 228 Can I find this information from the database? 228 You may still miss some DDL... 229 Auditing data changes 229 Getting ready 230 How to do it... 230 Collecting data changes from the server log 230 Collecting changes using triggers 230 Collecting changes using triggers and saving them in another database 232 Always knowing which user is logged in 235 Getting ready 236 How to do it... 236 How it works... 237 There's more... 237 Not inheriting user attributes 237 Integrating with LDAP 238 Getting ready 238 How to do it... 238 How it works... 238 There's more... 238 Setting up the client to use LDAP 239 Replacement for the User Name Map feature 239 See also 239 Connecting using SSL 239 Getting ready 240 How to do it... 240 How it works... 240 There's more... 240 Getting the SSL key and certificate 240 Setting up a client to use SSL 241 Checking server authenticity 242 Using SSL certificates to authenticate 242 Getting ready 243 How to do it... 243 How it works... 244 There's more... 244 Avoiding duplicate SSL connection attempts 245 Using multiple client certificates 245 Using the client certificate to select the database user 245 See also 246 Mapping external usernames to database roles 246 Getting ready 247 How to do it... 247 How it works... 247 There's more... 248 Encrypting sensitive data 248 Getting ready 248 How to do it... 249 How it works... 251 There's more... 252 For really sensitive data 252 For really, really, really sensitive data! 253 See also 253 Chapter 7: Database Administration 254 Introduction 254 Writing a script that either succeeds entirely or fails entirely 255 How to do it... 256 How it works... 256 There's more... 258 Writing a psql script that exits on the first error 260 Getting ready 260 How to do it... 260 How it works... 261 There's more... 261 Investigating a psql error 261 Getting ready 262 How to do it 263 There's more... 263 Performing actions on many tables 263 Getting ready 264 How to do it... 264 How it works... 265 There's more... 266 Adding/removing columns on a table 269 How to do it... 269 How it works... 269 There's more... 270 Changing the data type of a column 271 Getting ready 271 How to do it... 271 How it works... 272 There's more... 273 Changing the definition of a data type 274 Getting ready 275 How to do it... 275 How it works... 275 There's more... 276 Adding/removing schemas 276 How to do it... 276 There's more... 278 Using schema-level privileges 278 Moving objects between schemas 278 How to do it... 278 How it works... 279 There's more... 279 Adding/removing tablespaces 279 Getting ready 279 How to do it... 280 How it works... 282 There's more... 283 Putting pg_xlog on a separate device 283 Tablespace-level tuning 284 Moving objects between tablespaces 284 Getting ready 284 How to do it... 284 How it works... 285 There's more... 286 Accessing objects in other PostgreSQL databases 287 Getting ready 288 How to do it... 288 How it works... 294 There's more... 295 There's more... 297 Accessing objects in other foreign databases 298 Getting ready 299 How to do it... 299 How it works... 300 There's more... 300 Updatable views 301 Getting ready 301 How to do it... 303 How it works... 304 There's more... 306 Using materialized views 308 Getting ready 308 How to do it... 309 How it works... 309 There's more... 310 Chapter 8: Monitoring and Diagnosis 311 Introduction 312 Providing PostgreSQL information to monitoring tools 313 Finding more information about generic monitoring tools 315 Real-time viewing using pgAdmin 315 Checking whether a user is connected 316 Getting ready 317 How to do it... 317 How it works... 317 There's more... 317 What if I want to know whether a computer is connected? 318 What if I want to repeatedly execute a query in psql? 319 Checking which queries are running 319 Getting ready 319 How to do it... 320 How it works... 320 There's more... 320 Catching queries that only run for a few milliseconds 321 Watching the longest queries 321 Watching queries from ps 322 See also 322 Checking which queries are active or blocked 322 Getting ready 322 How to do it... 323 How it works... 323 There's more... 323 No need for the = true part 324 Do we catch all queries waiting on locks? 324 Knowing who is blocking a query 324 Getting ready 324 How to do it... 325 How it works... 326 Killing a specific session 326 How to do it... 327 How it works... 327 There's more... 327 Try to cancel the query first 327 What if the backend won't terminate? 328 Using statement_timeout to clean up queries that take too long to run 328 Killing idle in transaction queries 329 Killing the backend from the command line 329 Detecting an in-doubt prepared transaction 330 How to do it... 330 Knowing whether anybody is using a specific table 330 Getting ready 331 How to do it... 331 How it works... 331 There's more... 331 The quick-and-dirty way 331 Collecting daily usage statistics 332 Knowing when a table was last used 332 Getting ready 332 How to do it... 333 How it works... 334 There's more... 334 Usage of disk space by temporary data 334 Getting ready 334 How to do it... 335 How it works... 337 There's more... 337 Finding out whether a temporary file is in use any more 337 Logging temporary file usage 338 Understanding why queries slow down 338 Getting ready 338 How to do it... 339 How it works... 339 There's more... 339 Do the queries return significantly more data than they did earlier? 339 Do the queries also run slowly when they are run alone? 340 Is the second run of the same query also slow? 340 Table and index bloat 341 See also 341 Investigating and reporting a bug 342 Getting ready 342 How to do it... 342 How it works... 343 Producing a daily summary of log file errors 343 Getting ready 343 How to do it... 345 How it works... 346 There's more... 346 See also 347 Analyzing the real-time performance of your queries 347 Getting ready 347 How to do it... 347 How it works... 348 There's more... 348 Chapter 9: Regular Maintenance 350 Introduction 350 Controlling automatic database maintenance 351 Getting ready 351 How to do it... 352 How it works... 353 There's more... 355 See also 356 Avoiding auto-freezing and page corruptions 357 How to do it... 357 Removing issues that cause bloat 358 Getting ready 359 How to do it... 359 How it works... 360 There's more... 360 Removing old prepared transactions 360 Getting ready 360 How to do it... 361 How it works... 361 There's more... 362 Actions for heavy users of temporary tables 363 How to do it... 363 How it works... 363 Identifying and fixing bloated tables and indexes 365 How to do it... 366 How it works... 367 There's more... 369 Monitoring and tuning vacuum 370 Getting ready 370 How to do it... 370 How it works... 371 There's more... 372 Maintaining indexes 372 Getting ready 373 How to do it... 374 How it works... 376 There's more... 376 Adding a constraint without checking existing rows 377 Getting ready 377 How to do it... 378 How it works... 379 Finding unused indexes 379 How to do it... 380 How it works... 380 Carefully removing unwanted indexes 381 Getting ready 381 How to do it... 382 How it works... 383 Planning maintenance 383 How to do it... 383 How it works... 384 Chapter 10: Performance and Concurrency 386 Introduction 386 Finding slow SQL statements 387 Getting ready 387 How to do it... 388 How it works... 389 There's more... 389 Collecting regular statistics from pg_stat* views 390 Getting ready 390 How to do it... 390 How it works... 391 There's more... 391 Another statistics collection package 392 Finding out what makes SQL slow 392 Getting ready 392 How to do it... 392 There's more... 395 Not enough CPU power or disk I/O capacity for the current load 395 Locking problems 396 EXPLAIN options 396 See also 396 Reducing the number of rows returned 397 How to do it... 397 There's more... 398 See also 399 Simplifying complex SQL queries 400 Getting ready 400 How to do it... 401 There's more... 404 Using materialized views (long-living, temporary tables) 406 Using set-returning functions for some parts of queries 407 Speeding up queries without rewriting them 407 How to do it... 407 Increasing work_mem 407 More ideas with indexes 408 There's more... 409 Partitioning 409 Using a TABLESAMPLE view 410 In case of many updates, set fillfactor on the table 410 Rewriting the schema - a more radical approach 410 Discovering why a query is not using an index 411 Getting ready 411 How to do it... 411 How it works... 412 There's more... 412 Forcing a query to use an index 413 Getting ready 413 How to do it... 414 There's more... 415 Using parallel query 415 How to do it... 415 How it works... 416 There's more... 417 Using optimistic locking 418 How to do it... 418 How it works... 418 There's more... 419 Reporting performance problems 420 How to do it... 420 There's more... 420 Chapter 11: Backup and Recovery 421 Introduction 422 Understanding and controlling crash recovery 422 How to do it... 423 How it works... 424 There's more... 425 Planning backups 425 How to do it... 426 Hot logical backups of one database 428 How to do it... 428 How it works... 429 There's more... 430 See also 431 Hot logical backups of all databases 431 How to do it... 431 How it works... 431 See also 432 Backups of database object definitions 432 How to do it... 432 There's more... 433 Standalone hot physical database backup 433 How to do it... 434 How it works... 436 There's more... 437 See also 437 Hot physical backup and continuous archiving 438 Getting ready 438 How to do it... 439 How it works... 440 Recovery of all databases 441 Getting ready 441 How to do it... 442 Logical - from custom dump taken with pg_dump -F c 442 Logical - from the script dump created by pg_dump -F p 442 Logical - from the script dump created by pg_dumpall 443 Physical 443 How it works... 445 There's more... 445 See also 446 Recovery to a point in time 446 Getting ready 446 How to do it... 446 How it works... 447 There's more... 448 See also 448 Recovery of a dropped/damaged table 448 How to do it... 449 Logical - from custom dump taken with pg_dump -F c 449 Logical - from the script dump 450 Physical 451 How it works... 452 See also 452 Recovery of a dropped/damaged database 452 How to do it... 452 Logical - from the custom dump -F c 452 Logical - from the script dump created by pg_dump 453 Logical - from the script dump created by pg_dumpall 453 Physical 453 Improving performance of backup/recovery 454 Getting ready 454 How to do it... 454 How it works... 456 There's more... 456 See also 456 Incremental/differential backup and restore 457 How to do it... 457 How it works... 458 There's more... 458 Hot physical backups with Barman 459 Getting ready 460 How to do it... 461 How it works... 465 There's more... 466 Recovery with Barman 468 Getting ready 469 How to do it... 470 How it works... 471 There's more... 472 Chapter 12: Replication and Upgrades 475 Introduction 476 Replication concepts 477 Topics 477 Basic concepts 478 History and scope 478 Practical aspects 480 Data loss 481 Single-master replication 481 Multinode architectures 482 Clustered or massively parallel databases 482 Multimaster replication 483 Scalability tools 484 Other approaches to replication 484 Replication best practices 484 How to do it... 485 There's more... 486 Setting up file-based replication - deprecated 487 Getting ready 487 How to do it... 487 How it works... 489 There's more... 490 See also 490 Setting up streaming replication 491 Getting ready 491 How to do it... 491 How it works... 494 There's more... 495 Setting up streaming replication security 496 Getting ready 497 How to do it... 497 How it works... 498 There's more... 498 Hot Standby and read scalability 498 Getting ready 499 How to do it... 500 How it works... 503 Managing streaming replication 503 Getting ready 503 How to do it... 504 There's more... 505 See also 506 Using repmgr 506 Getting ready 506 How to do it... 506 How it works... 508 There's more... 509 Using replication slots 509 Getting ready 510 How to do it... 510 There's more... 511 See also 511 Monitoring replication 511 Getting ready 511 How to do it... 512 There's more... 515 Performance and synchronous replication 515 Getting ready 515 How to do it... 516 How it works... 518 There's more... 518 Delaying, pausing, and synchronizing replication 519 Getting ready 519 How to do it... 519 There's more... 520 See also 521 Logical replication 521 Getting ready 522 How to do it... 524 How it works... 526 There's more... 527 Bi-directional replication 527 Getting ready 528 How to do it... 529 How it works... 529 There's more... 530 Archiving transaction log data 531 Getting ready 531 How to do it... 532 There's more... 532 See also 533 Upgrading - minor releases 533 Getting ready 533 How to do it... 533 How it works... 534 Major upgrades in-place 534 Getting ready 535 How to do it... 535 How it works... 536 Major upgrades online 536 How to do it... 536 How it works... 537 Index 538 Over 90 recipes to help you administer your PostgreSQL database more efficientlyAbout This Book* Get to grips with the capabilities of PostgreSQL 9.6 to administer your database more efficiently, * Monitor, replicate and make your database highly secure and available, * A step-by-step, recipe-based guide to help you tackle any problem in PostgreSQL administration with easeWho This Book Is ForIf you are an administrator who wants to leverage the useful PostgreSQL functionalities to create and manage databases efficiently, this is the book for you. This book assumes a basic working knowledge of PostgreSQL, and some previous experience in PostgreSQL administration is required. What you will learn* Implement PostgreSQL features for reliability and performance* Harness the power of the latest PostgreSQL 9.5 & 9.6 features* Manage PostgreSQL versions 9.5 & 9.6* Advanced technical tips for experienced users* Explore best practices for planning and designing live databases* Select and implement robust backup and recovery techniques* Concise and clear guidance on replication and high availability* Latest details on Logical Replication and Bi-Directional ReplicationIn DetailPostgreSQL is an open-source database management tool used for handling large data sets (big data) and also as a JSON document database. Starting with short and simple recipes to get you back up and running with an exploration of the ins and outs of your database, you will soon dive into core features such as configuration, server control, tables, and data. You will tackle a variety of problems a database administrator usually encounters - spanning from creating tables to managing views, from improving performance to securing your database, and from using monitoring tools to using storage engines. Recipes based on important topics like high availability, concurrency, replication, backup and recovery, as well as diagnostics and troubleshooting are also given special importance. By the end of this book, you will have all the knowledge you need to run, manage and maintain your PostgreSQL efficiently. DBAs of all levels will be catered for with recipes of varying difficulty, allowing the reader to administer PostgreSQL efficiently. Tagline: Over XXX recipes to help you administer your PostgreSQL database more efficiently