PostgreSQL 9 Administration Cookbook - Second Edition

Over 150 recipes to help you run an efficient PostgreSQL database in the cloud

PostgreSQL 9 Administration Cookbook - Second Edition

This ebook is included in a Mapt subscription
Simon Riggs et al.

4 customer reviews
Over 150 recipes to help you run an efficient PostgreSQL database in the cloud
$10.00
$59.99
RRP $47.99
RRP $59.99
eBook
Print + eBook
Access every Packt eBook & Video for just $100
 
  • 4,000+ eBooks & Videos
  • 40+ New titles a month
  • 1 Free eBook/Video to keep every month
Find Out More
 
Preview in Mapt

Book Details

ISBN 139781849519069
Paperback504 pages

Book Description

PostgreSQL is a powerful, open source, object-relational database system, fast becoming one of the world's most popular server databases with an enviable reputation for performance and stability and an enormous range of advanced features.

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. This is a practical guide aimed at giving sysadmins and database administrators the necessary toolkit to be able to set up, run, and extend powerful databases with PostgreSQL.

Table of Contents

Chapter 1: First Steps
Introduction
Getting PostgreSQL
Connecting to the PostgreSQL server
Enabling access for network/remote users
Using graphical administration tools
Using the psql query and scripting tool
Changing your password securely
Avoiding hardcoding your password
Using a connection service file
Troubleshooting a failed connection
Chapter 2: Exploring the Database
Introduction
What version is the server?
What is the server uptime?
Locating the database server files
Locating the database server's message log
Locating the database's system identifier
Listing databases on this database server
How many tables in a database?
How much disk space does a database use?
How much disk space does a table use?
Which are my biggest tables?
How many rows in a table?
Quickly estimating the number of rows in a table
Listing extensions in this database
Understanding object dependencies
Chapter 3: Configuration
Introduction
Reading The Fine Manual (RTFM)
Planning a new database
Changing parameters in your programs
Finding the current configuration settings
Which parameters are at nondefault settings?
Updating the parameter file
Setting parameters for particular groups of users
The basic server configuration checklist
Adding an external module to PostgreSQL
Using an installed module
Managing installed extensions
Chapter 4: Server Control
Introduction
Starting the database server manually
Stopping the server safely and quickly
Stopping the server in an emergency
Reloading the server configuration files
Restarting the server quickly
Preventing new connections
Restricting users to only one session each
Pushing users off the system
Deciding on a design for multitenancy
Using multiple schemas
Giving users their own private database
Running multiple servers on one system
Setting up a connection pool
Accessing multiple servers using the same host and port
Chapter 5: Tables and Data
Introduction
Choosing good names for database objects
Handling objects with quoted names
Enforcing the same name and definition for columns
Identifying and removing duplicates
Preventing duplicate rows
Finding a unique key for a set of data
Generating test data
Randomly sampling data
Loading data from a spreadsheet
Loading data from flat files
Chapter 6: Security
Introduction
The PostgreSQL superuser
Revoking user access to a table
Granting user access to a table
Creating a new user
Temporarily preventing a user from connecting
Removing a user without dropping their data
Checking whether all users have a secure password
Giving limited superuser powers to specific users
Auditing DDL changes
Auditing data changes
Always knowing which user is logged in
Integrating with LDAP
Connecting using SSL
Using SSL certificates to authenticate the client
Mapping external usernames to database roles
Encrypting sensitive data
Chapter 7: Database Administration
Introduction
Writing a script that either succeeds entirely or fails entirely
Writing a psql script that exits on the first error
Performing actions on many tables
Adding/removing columns on a table
Changing the data type of a column
Changing the definition of a data type
Adding/removing schemas
Moving objects between schemas
Adding/removing tablespaces
Moving objects between tablespaces
Accessing objects in other PostgreSQL databases
Accessing objects in other foreign databases
Updatable views
Using materialized views
Chapter 8: Monitoring and Diagnosis
Introduction
Checking whether a user is connected
Checking which queries are running
Checking which queries are active or blocked
Knowing who is blocking a query
Killing a specific session
Detecting an in-doubt prepared transaction
Knowing whether anybody is using a specific table
Knowing when a table was last used
Usage of disk space by temporary data
Understanding why queries slow down
Investigating and reporting a bug
Producing a daily summary of log file errors
Analyzing the real-time performance of your queries
Chapter 9: Regular Maintenance
Introduction
Controlling automatic database maintenance
Avoiding auto-freezing and page corruptions
Avoiding transaction wraparound
Removing old prepared transactions
Actions for heavy users of temporary tables
Identifying and fixing bloated tables and indexes
Maintaining indexes
Adding a constraint without checking existing rows
Finding unused indexes
Carefully removing unwanted indexes
Planning maintenance
Chapter 10: Performance and Concurrency
Introduction
Finding slow SQL statements
Collecting regular statistics from pg_stat* views
Finding out what makes SQL slow
Reducing the number of rows returned
Simplifying complex SQL queries
Speeding up queries without rewriting them
Why a query is not using an index
Forcing a query to use an index
Using optimistic locking
Reporting performance problems
Chapter 11: Backup and Recovery
Introduction
Understanding and controlling crash recovery
Planning backups
Hot logical backup of one database
Hot logical backup of all databases
Hot logical backup of all tables in a tablespace
Backup of database object definitions
Standalone hot physical database backup
Hot physical backup and continuous archiving
Recovery of all databases
Recovery to a point in time
Recovery of a dropped/damaged table
Recovery of a dropped/damaged tablespace
Recovery of a dropped/damaged database
Improving performance of backup/recovery
Incremental/differential backup and restore
Hot physical backups with Barman
Recovery with Barman
Chapter 12: Replication and Upgrades
Introduction
Replication best practices
Setting up file-based replication – deprecated
Setting up streaming replication
Setting up streaming replication security
Hot Standby and read scalability
Managing streaming replication
Using repmgr
Using Replication Slots
Monitoring replication
Performance and Synchronous Replication
Delaying, pausing, and synchronizing replication
Logical Replication
Bi-Directional Replication
Archiving transaction log data
Upgrading – minor releases
Major upgrades in-place
Major upgrades online

What You Will Learn

  • Implement PostgreSQL features for reliability and performance
  • Harness the power of the latest PostgreSQL 9.4 features
  • Manage PostgreSQL versions 9.0, 9.1, 9.2, 9.3, and 9.4
  • 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 Replication

Authors

Table of Contents

Chapter 1: First Steps
Introduction
Getting PostgreSQL
Connecting to the PostgreSQL server
Enabling access for network/remote users
Using graphical administration tools
Using the psql query and scripting tool
Changing your password securely
Avoiding hardcoding your password
Using a connection service file
Troubleshooting a failed connection
Chapter 2: Exploring the Database
Introduction
What version is the server?
What is the server uptime?
Locating the database server files
Locating the database server's message log
Locating the database's system identifier
Listing databases on this database server
How many tables in a database?
How much disk space does a database use?
How much disk space does a table use?
Which are my biggest tables?
How many rows in a table?
Quickly estimating the number of rows in a table
Listing extensions in this database
Understanding object dependencies
Chapter 3: Configuration
Introduction
Reading The Fine Manual (RTFM)
Planning a new database
Changing parameters in your programs
Finding the current configuration settings
Which parameters are at nondefault settings?
Updating the parameter file
Setting parameters for particular groups of users
The basic server configuration checklist
Adding an external module to PostgreSQL
Using an installed module
Managing installed extensions
Chapter 4: Server Control
Introduction
Starting the database server manually
Stopping the server safely and quickly
Stopping the server in an emergency
Reloading the server configuration files
Restarting the server quickly
Preventing new connections
Restricting users to only one session each
Pushing users off the system
Deciding on a design for multitenancy
Using multiple schemas
Giving users their own private database
Running multiple servers on one system
Setting up a connection pool
Accessing multiple servers using the same host and port
Chapter 5: Tables and Data
Introduction
Choosing good names for database objects
Handling objects with quoted names
Enforcing the same name and definition for columns
Identifying and removing duplicates
Preventing duplicate rows
Finding a unique key for a set of data
Generating test data
Randomly sampling data
Loading data from a spreadsheet
Loading data from flat files
Chapter 6: Security
Introduction
The PostgreSQL superuser
Revoking user access to a table
Granting user access to a table
Creating a new user
Temporarily preventing a user from connecting
Removing a user without dropping their data
Checking whether all users have a secure password
Giving limited superuser powers to specific users
Auditing DDL changes
Auditing data changes
Always knowing which user is logged in
Integrating with LDAP
Connecting using SSL
Using SSL certificates to authenticate the client
Mapping external usernames to database roles
Encrypting sensitive data
Chapter 7: Database Administration
Introduction
Writing a script that either succeeds entirely or fails entirely
Writing a psql script that exits on the first error
Performing actions on many tables
Adding/removing columns on a table
Changing the data type of a column
Changing the definition of a data type
Adding/removing schemas
Moving objects between schemas
Adding/removing tablespaces
Moving objects between tablespaces
Accessing objects in other PostgreSQL databases
Accessing objects in other foreign databases
Updatable views
Using materialized views
Chapter 8: Monitoring and Diagnosis
Introduction
Checking whether a user is connected
Checking which queries are running
Checking which queries are active or blocked
Knowing who is blocking a query
Killing a specific session
Detecting an in-doubt prepared transaction
Knowing whether anybody is using a specific table
Knowing when a table was last used
Usage of disk space by temporary data
Understanding why queries slow down
Investigating and reporting a bug
Producing a daily summary of log file errors
Analyzing the real-time performance of your queries
Chapter 9: Regular Maintenance
Introduction
Controlling automatic database maintenance
Avoiding auto-freezing and page corruptions
Avoiding transaction wraparound
Removing old prepared transactions
Actions for heavy users of temporary tables
Identifying and fixing bloated tables and indexes
Maintaining indexes
Adding a constraint without checking existing rows
Finding unused indexes
Carefully removing unwanted indexes
Planning maintenance
Chapter 10: Performance and Concurrency
Introduction
Finding slow SQL statements
Collecting regular statistics from pg_stat* views
Finding out what makes SQL slow
Reducing the number of rows returned
Simplifying complex SQL queries
Speeding up queries without rewriting them
Why a query is not using an index
Forcing a query to use an index
Using optimistic locking
Reporting performance problems
Chapter 11: Backup and Recovery
Introduction
Understanding and controlling crash recovery
Planning backups
Hot logical backup of one database
Hot logical backup of all databases
Hot logical backup of all tables in a tablespace
Backup of database object definitions
Standalone hot physical database backup
Hot physical backup and continuous archiving
Recovery of all databases
Recovery to a point in time
Recovery of a dropped/damaged table
Recovery of a dropped/damaged tablespace
Recovery of a dropped/damaged database
Improving performance of backup/recovery
Incremental/differential backup and restore
Hot physical backups with Barman
Recovery with Barman
Chapter 12: Replication and Upgrades
Introduction
Replication best practices
Setting up file-based replication – deprecated
Setting up streaming replication
Setting up streaming replication security
Hot Standby and read scalability
Managing streaming replication
Using repmgr
Using Replication Slots
Monitoring replication
Performance and Synchronous Replication
Delaying, pausing, and synchronizing replication
Logical Replication
Bi-Directional Replication
Archiving transaction log data
Upgrading – minor releases
Major upgrades in-place
Major upgrades online

Book Details

ISBN 139781849519069
Paperback504 pages
Read More
From 4 reviews

Read More Reviews