PostgreSQL Administration Cookbook, 9.5/9.6 Edition /
Over 150 recipes to help you administer your PostgreSQL database more efficiently About This Book Get to grips with the capabilities of PostgreSQL 9.6 to administer your database more efficiently Monitor, tune, secure and protect your database A step-by-step, recipe-based guide to help you tackle an...
Saved in:
| Main Authors: | , , |
|---|---|
| Format: | Electronic eBook |
| Language: | English |
| Published: |
Birmingham :
Packt Publishing,
2017.
|
| Subjects: | |
| Online Access: | Full text (Emerson users only) Full text (Emmanuel users only) Full text (NECO users only) Full text (MCPHS users only) Full text (Wentworth users only) |
Table of Contents:
- Cover
- Copyright
- Credits
- About the Authors
- About the Reviewer
- www.PacktPub.com
- Customer Feedback
- Table of Contents
- Preface
- Chapter 1: First Steps
- Introduction
- Introducing PostgreSQL 9.6
- What makes PostgreSQL different?
- Robustness
- Security
- Ease of use
- Extensibility
- Performance and concurrency
- Scalability
- SQL and NoSQL
- Popularity
- Commercial support
- Research and development funding
- Getting PostgreSQL
- How to do it ...
- How it works ...
- There's more ...
- Connecting to the PostgreSQL server
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- See also
- Enabling access for network/remote users
- How to do it ...
- How it works ...
- There's more ...
- See also
- Using graphical administration tools
- How to do it ...
- How it works ...
- See also
- Using the psql query and scripting tool
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- See also
- Changing your password securely
- How to do it ...
- How it works ...
- Avoiding hardcoding your password
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Using a connection service file
- How to do it ...
- How it works ...
- Troubleshooting a failed connection
- How to do it ...
- There's more ...
- Chapter 2: Exploring the Database
- Introduction
- What version is the server?
- How to do it ...
- How it works ...
- There's more ...
- What is the server uptime?
- How to do it ...
- How it works ...
- See also
- Locating the database server files
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Locating the database server's message log
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Locating the database's system identifier
- Getting ready
- How to do it ...
- How it works ...
- Listing databases on this database server
- How to do it ...
- How it works ...
- There's more ...
- How many tables are there in a database?
- How to do it ...
- How it works ...
- There's more ...
- How much disk space does a database use?
- How to do it ...
- How it works ...
- How much disk space does a table use?
- How to do it ...
- How it works ...
- There's more ...
- Which are my biggest tables?
- How to do it ...
- How it works ...
- How many rows are there in a table?
- How to do it ...
- How it works ...
- Quickly estimating the number of rows in a table
- How to do it ...
- How it works ...
- There's more ...
- Function 1
- estimating the number of rows
- Function 2
- computing the size of a table without locks
- Listing extensions in this database
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Understanding object dependencies
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Chapter 3: Configuration
- Introduction
- Reading the fine manual
- How to do it ...
- How it works ...
- There's more ...
- Planning a new database
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Changing parameters in your programs
- How to do it ...
- How it works ...
- There's more ...
- Finding the current configuration settings
- How to do it ...
- There's more ...
- How it works ...
- Which parameters are at non-default settings?
- How to do it ...
- How it works ...
- There's more ...
- Updating the parameter file
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Setting parameters for particular groups of users
- How to do it ...
- How it works ...
- The basic server configuration checklist
- Getting ready
- How to do it ...
- There's more ...
- Adding an external module to PostgreSQL
- Getting ready
- How to do it ...
- [Installing modules using a software installer]
- Installing modules using a software installer
- Installing modules from PGXN
- Installing modules from a manually downloaded package
- Installing modules from source code
- How it works ...
- Using an installed module
- Getting ready
- How to do it ...
- How it works ...
- Managing installed extensions
- [How to do it ...]
- How to do it ...
- How it works ...
- There's more ...
- Chapter 4: Server Control
- Introduction
- Starting the database server manually
- Getting ready
- How to do it ...
- How it works ...
- Stopping the server safely and quickly
- How to do it ...
- How it works ...
- See also
- Stopping the server in an emergency
- How to do it ...
- How it works ...
- Reloading the server configuration files
- How to do it ...
- How it works ...
- There's more ...
- Restarting the server quickly
- How to do it ...
- There's more ...
- Preventing new connections
- How to do it ...
- How it works ...
- Restricting users to only one session each
- How to do it ...
- How it works ...
- Pushing users off the system
- How to do it ...
- How it works ...
- Deciding on a design for multitenancy
- How to do it ...
- How it works ...
- Using multiple schemas
- Getting ready
- How to do it ...
- How it works ...
- Giving users their own private database
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- See also
- Running multiple servers on one system
- Getting ready
- How to do it ...
- How it works ...
- Setting up a connection pool
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Accessing multiple servers using the same host and port
- Getting ready
- How to do it ...
- There's more ...
- Chapter 5: Tables and Data
- Introduction
- Choosing good names for database objects
- Getting ready
- How to do it ...
- There's more ...
- Handling objects with quoted names
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Enforcing the same name and definition for columns
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Identifying and removing duplicates
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Preventing duplicate rows
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Duplicate indexes
- Uniqueness without indexes
- Real-world example
- IP address range allocation
- Real-world example
- range of time
- Real-world example
- prefix ranges
- Finding a unique key for a set of data
- Getting ready
- How to do it ...
- How it works ...
- Generating test data
- How to do it ...
- How it works ...
- There's more ...
- See also
- Randomly sampling data
- How to do it ...
- How it works ...
- Loading data from a spreadsheet
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Loading data from flat files
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Chapter 6: Security
- Introduction
- Typical user role
- The PostgreSQL superuser
- How to do it ...
- How it works ...
- There's more ...
- Other superuser-like attributes
- Attributes are never inherited
- See also
- Revoking user access to a table
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Database creation scripts
- Default search path
- Securing views
- Granting user access to a table
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Access to the schema
- Granting access to a table through a group role
- Granting access to all objects in a schema
- Granting user access to specific columns
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Granting user access to specific rows
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Creating a new user
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Temporarily preventing a user from connecting
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Limiting the number of concurrent connections by a user
- Forcing NOLOGIN users to disconnect
- Removing a user without dropping their data
- Getting ready
- How to do it ...
- How it works ...
- Checking whether all users have a secure password
- How to do it ...
- How it works ...
- Giving limited superuser powers to specific users
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Writing a debugging_info function for developers
- Auditing DDL changes
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Was the change committed?
- Who made the change?
- Can I find this information from the database?
- You may still miss some DDL ...
- Auditing data changes
- Getting ready
- How to do it ...
- Collecting data changes from the server log
- Collecting changes using triggers
- Collecting changes using triggers and saving them in another database
- Always knowing which user is logged in
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Not inheriting user attributes
- Integrating with LDAP
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Setting up the client to use LDAP
- Replacement for the User Name Map feature
- See also
- Connecting using SSL
- Getting ready
- How to do it ...
- How it works ...
- There's more ...
- Getting the SSL key and certificate
- Setting up a client to use SSL
- Checking server authenticity
- Using SSL certificates to authenticate
- Getting ready
- How to do it ...