Index
A
- access
- enabling, for network/remote users / Enabling access for network/remote users, How it works…
- access control list
- about / How to do it…
- actions
- performing, on tables / Performing actions on many tables, How to do it…, How it works…, There's more…
- alerts
- about / Introduction
- ANALYZE command / Introduction
- application-level replication / Other approaches to replication
- application-specific test data, PostgreSQL
- references / There's more…
- apply delay / Practical aspects
- asymmetric cryptography / For really sensitive data
- asynchronous replication / Data loss
- auto-freezing
- avoiding / Getting ready, How to do it…, There's more…
- automatic database maintenance
- controlling / Getting ready, How to do it…, How it works…
- autonomous transactions / Accessing objects in other PostgreSQL databases
- autovacuum
- about / Controlling automatic database maintenance
- autovacuum_freeze_max_age parameter / See also
- auto_explain contrib module
- reference link / See also
- AXLE project
- URL / Research and development funding
B
- backend
- killing, from command line / Killing the backend from the command line
- backup
- performance, improving of / Improving performance of backup/recovery, How to do it…, There's more…
- backup catalogue / There's more…
- backups
- planning / Planning backups, How to do it…
- Barman
- URL / Hot physical backup and continuous archiving, Getting ready
- used, for hot physical backups / Hot physical backups with Barman, Getting ready, How to do it…, How it works…
- about / Hot physical backups with Barman
- references / There's more…, There's more…
- Barman, GNU GPL 3
- URL / There's more…
- base backup / Practical aspects
- basic server configuration
- checklist / The basic server configuration checklist, How to do it…, There's more…
- Berkeley Distribution Software (BSD) license / Getting PostgreSQL
- best practices, replication / Replication best practices, How to do it…, There's more…
- best practices, tablespaces / There's more…
- best practices, user and role management
- about / There's more…
- database creation scripts / Database creation scripts
- default search path / Default search path
- views, securing / Securing views
- Bi-Directional Replication (BDR)
- about / Bi-Directional Replication, Getting ready, How it works…
- cascading / There's more…
- circular replication / There's more…
- group to group / There's more…
- central rollup / There's more…
- central broadcast / There's more…
- Bidirectional Replication (BDR)
- about / Multimaster replication
- bloated tables
- fixing / Identifying and fixing bloated tables and indexes, How to do it…, How it works…
- identifying / Identifying and fixing bloated tables and indexes, How to do it…, How it works…
- bug
- investigating / Getting ready, How it works…
- reporting / Getting ready
- business intelligence / There's more…
- Business Intelligence/Reporting Systems / Topics
C
- Cacti
- about / Introduction
- URL / Finding more information about generic monitoring tools
- cascading / Basic concepts
- casts
- about / There's more…
- Certificate Authority (CA) / Getting the SSL key and certificate
- checkpoints / How to do it…
- check_postgres
- reference link / Providing PostgreSQL information to monitoring tools
- client
- setting up, for SSL usage / Setting up a client to use SSL
- authenticating, SSL certificates used / Using SSL certificates to authenticate the client, How to do it…
- client certificate
- used, for selecting database user / Using the client certificate to select the database user
- clustered parallel databases / Clustered or massively parallel databases
- clustering tables, on specific indexes / How to do it…
- collect_deltas() function
- about / There's more…
- column
- data type, modifying of / Changing the data type of a column, Getting ready, How to do it…, How it works…
- columns
- name, enforcing for / Enforcing the same name and definition for columns, How to do it…, How it works…
- definition, enforcing for / Enforcing the same name and definition for columns, How to do it…, How it works…
- adding, to table / How to do it…, There's more…
- removing, from table / How to do it…, How it works…
- command line
- backend, killing from / Killing the backend from the command line
- commands, Barman
- global / How it works…
- server / How it works…
- Common Table Expressions (CTEs)
- about / How to do it…
- URL / How to do it…
- complex SQL queries
- simplifying / Simplifying complex SQL queries, How to do it…, There's more…
- computer connection
- checking / What if I want to know whether that computer is connected?
- concurrency / Introduction
- concurrent user connections
- limiting / Limiting the number of concurrent connections by a user
- conditional indexes / How to do it…
- connection pool
- about / Setting up a connection pool
- setting up / Getting ready, How to do it…, How it works…
- connection service file
- about / Using a connection service file
- using / How it works…
- constraint
- adding, without checking existing rows / Adding a constraint without checking existing rows, Getting ready, How to do it…
- contrib
- about / Getting ready
- URL / Getting ready
- Coordinated Universal Time (UTC) / How to do it…
- COPY command / How to do it…
- covering indexes / How to do it…
- crash recovery
- about / Understanding and controlling crash recovery
- controlling / How to do it…, How it works…
- CREATE INDEX CONCURRENTLY statement / How it works…
- cross-tab query / How to do it…
- current configuration settings
- finding / Finding the current configuration settings, How to do it…, There's more…
- custom format / How it works…
D
- data
- sampling, randomly / Randomly sampling data, How to do it…, How it works…
- loading, from spreadsheet / Loading data from a spreadsheet, How to do it…, How it works…
- loading, from flat files / Getting ready, How it works…, There's more…
- database
- tables, counting in / How many tables in a database?, How it works…
- extensions, listing in / Listing extensions in this database, How to do it…, How it works…
- planning / Planning a new database, How it works…
- database administration / Writing a script that either succeeds entirely or fails entirely
- Database Connection Control Functions
- URL / What if I want to know whether that computer is connected?
- database maintenance
- planning / Planning maintenance, How it works…
- tasks / How it works…
- database management system (DBMS) / Introduction
- database object definitions
- backing up / Backup of database object definitions, How to do it…
- database objects
- naming, considerations / How to do it…
- database replication / Topics
- database roles
- external usernames, mapping to / Mapping external usernames to database roles, How it works…
- databases
- system identifier, locating / Locating the database's system identifier, How it works…
- listing, on database server / Listing databases on this database server, How to do it…, How it works…, There's more…
- disk space, monitoring / How much disk space does a database use?
- about / Introduction
- recovering / Recovery of all databases, Logical – from the script dump created by pg_dump –F p, Physical, How it works…, There's more…
- database server
- message log, locating / Locating the database server's message log, How to do it…, How it works…
- databases, listing on / Listing databases on this database server, How to do it…, How it works…, There's more…
- about / Introduction
- starting, manually / Starting the database server manually, How to do it…
- stopping, safely / Stopping the server safely and quickly, How it works…
- stopping, in emergency / Stopping the server in an emergency
- restarting, quickly / Restarting the server quickly
- users, removing from / Pushing users off the system, How to do it…, How it works…
- database server files
- locating / Locating the database server files, How to do it…, How it works…
- database version
- about / There's more…
- data blocks / There's more…
- data changes
- auditing / Auditing data changes
- collecting, from server logs / Collecting data changes from the server log
- collecting, triggers used / Collecting changes using triggers, Collecting changes using triggers and saving them in another database using dblink or plproxy
- saving in database, dblink used / Collecting changes using triggers and saving them in another database using dblink or plproxy
- saving in database, plproxy used / Collecting changes using triggers and saving them in another database using dblink or plproxy
- Data Definition Language (DDL) / Writing a script that either succeeds entirely or fails entirely
- data directory
- about / Introduction
- data generator
- key features / There's more…
- data type
- modifying, of column / Changing the data type of a column, Getting ready, How to do it…, How it works…
- definition, modifying of / Changing the definition of a data type, How it works…
- daylight saving time / How to do it…
- dblink
- used, for saving data changes in database / Collecting changes using triggers and saving them in another database using dblink or plproxy
- dblink module / How it works…
- DDL / Auditing DDL changes
- DDL changes
- auditing / Auditing DDL changes, How to do it…, Can I find this information from the database?
- Debian/Ubuntu layout
- about / How it works…
- debugging_info function
- writing, for developers / Writing a debugging_info function for developers
- definition
- enforcing, for columns / Enforcing the same name and definition for columns, How to do it…, How it works…
- modifying, of data type / Changing the definition of a data type, How it works…
- developers
- debugging_info function, writing for / Writing a debugging_info function for developers
- differential backup / Incremental/differential backup and restore
- disk space usage
- by temporary data / Usage of disk space by temporary data, How to do it…
- documents, PostgreSQL release
- references / How to do it…
- dropped/damaged database
- recovering / Recovery of a dropped/damaged database, Physical
- dropped/damaged table
- recovering / Recovery of a dropped/damaged table, Logical – from the custom dump taken with pg_dump -F c, Logical – from the script dump, How it works…
- dropped/damaged tablespace
- recovering / Recovery of a dropped/damaged tablespace, Logical – from the script dump, There's more…
- duplicate indexes
- about / Duplicate indexes
- duplicate rows
- preventing / Preventing duplicate rows, How to do it…, How it works…
- duplicates
- identifying / Identifying and removing duplicates, How to do it…, How it works…, There's more…
- removing / Identifying and removing duplicates, How to do it…, How it works…, There's more…
- duplicate SSL connection attempts
- avoiding / Avoiding duplicate SSL connection attempts
- dynamic scripting / How it works…
E
- equal probability of selection (EPS) / How it works…
- event triggers mechanism / You may still miss some DDL…
- eventually consistent / How it works…
- EXPLAIN SQL command
- reference link / See also
- extension infrastructure
- modules, installing with / Using the extension infrastructure
- extensions
- about / Listing extensions in this database
- listing, in database / Listing extensions in this database, How to do it…, How it works…
- extensions, PostgreSQL
- reference link / Collecting regular statistics from pg_stat* views
- external module
- adding, to PostgreSQL / Adding an external module to PostgreSQL, Getting ready, Installing modules using a software installer, Installing modules from PGXN, Installing modules from source code, How it works…
- external usernames
- mapping, to database roles / Mapping external usernames to database roles, How it works…
- Extra Packages Enterprise Linux (EPEL)
- URL / Getting ready
- extrapolation / Function 1 – estimating the number of rows
F
- fact / Getting ready
- failed connection
- troubleshooting / Troubleshooting a failed connection, How to do it…
- failover / Single-master replication
- file-based replication
- setting up / Setting up file-based replication – deprecated, How to do it…, How it works…, There's more…
- Filesystem Hierarchy Standard (FHS) / How it works…
- flat files
- data, loading from / Getting ready, How it works…, There's more…
- foreign databases
- objects, accessing in / Accessing objects in other foreign databases, How to do it…, How it works…
- foreign data wrapper infrastructure / Getting ready
- forks / How it works…
- function side-effects / Accessing objects in other PostgreSQL databases
G
- Ganglia
- about / Introduction
- URL / Finding more information about generic monitoring tools
- generic monitoring tools
- about / Finding more information about generic monitoring tools
- genetic query optimization (GEQO) / There's more…
- Geographical Information System (GIS) / Extensibility
- Global Development Group RPM repository
- URL / Getting ready
- Global Transaction Manager (GTM) / Clustered or massively parallel databases
- graphical administration tools
- using / Using graphical administration tools, How to do it…, How it works…
- groups, of users
- parameters, setting for / Setting parameters for particular groups of users, How it works…
H
- Heap-only Tuples (HOT) / Identifying and fixing bloated tables and indexes
- Heap-only Tuples (HOT) updates / In case of many updates, set fillfactor on the table
- help command
- about / How it works…
- types / How it works…
- horizontal partitioning
- about / There's more…
- host-based authentication (HBA) / How to do it…
- hot logical backup, multiple database / Hot logical backup of all databases, How it works…
- hot logical backup, of tables in tablespace / Hot logical backup of all tables in a tablespace, How it works…
- hot logical backup, single database / Hot logical backup of one database, How it works…
- hot physical backup, continuous archiving
- setting up / Hot physical backup and continuous archiving, Getting ready, How to do it…, How it works…
- hot physical backups, with Barman / Hot physical backups with Barman, Getting ready, How to do it…, How it works…
- Hot Standby
- about / Hot Standby and read scalability, Getting ready, How to do it…, How it works…
- Hot Standby feature / Scalability
- Hot standby mode / There's more…
- hstore / What makes PostgreSQL different?
I
- IANA
- about / How it works…
- URL / How it works…
- Icinga
- about / Introduction
- URL / Finding more information about generic monitoring tools
- Idle
- killing, in transaction queries / Killing Idle in transaction queries
- in-doubt prepared transaction
- detecting / Detecting an in-doubt prepared transaction, How to do it…
- incremental backup / Incremental/differential backup and restore
- index-only scans / How to do it…
- index bloat / Table and index bloat
- indexes
- fixing / Identifying and fixing bloated tables and indexes, How to do it…, How it works…
- identifying / Identifying and fixing bloated tables and indexes, How to do it…, How it works…
- maintaining / Maintaining indexes, How to do it…
- indexes, standard names
- about / There's more…
- inet data type / Real-world example – IP address range allocation
- inheritance
- reference link / There's more…
- INI format configuration file
- global options / How it works…
- server options / How it works…
- initdb utility
- about / How to do it…
- installed extensions
- managing / Managing installed extensions, How to do it…, How it works…
- installed module
- using / Using an installed module, Getting ready, How it works…
- Internet Assigned Numbers Authority (IANA)
- about / Introduction
- ip4r
- about / Real-world example – IP address range allocation
- URL / Real-world example – IP address range allocation
- IP address range allocation example / Real-world example – IP address range allocation
J
- Java Transaction API (JTA) / Removing old prepared transactions
L
- latency / Practical aspects
- LDAP
- integrating with / Integrating with LDAP, How to do it…
- limited superuser powers
- giving, to specific users / Giving limited superuser powers to specific users, How to do it…, How it works…
- log file errors
- daily summary, producing of / Producing a daily summary of log file errors, Getting ready, How it works…
- logged-in role user
- identifying / Always knowing which user is logged in, How to do it…, How it works…
- logging collector / There's more…
- logical backup / How to do it…
- Logical Log Streaming Replication (LLSR) / History and scope
- logical recovery / How it works…
- logical replication
- about / Logical Replication, Getting ready, How it works…
- benefits / Logical Replication
- logical restore / How to do it…
- logical streaming replication (LSR) / Logical Replication
- Logical Streaming Replication (LSR) / History and scope
- log rotation / There's more…
- Log Sequence Number (LSN) / There's more…, How to do it…
- log_destination parameter / There's more…
- Londiste / History and scope
- loosely coupled database clusters / Multinode architectures
M
- major upgrades
- about / Major upgrades in-place, How to do it…
- online / Major upgrades online, How it works…
- man-in-the-middle attack / Setting up a client to use SSL
- manually downloaded package
- modules, installing from / Installing modules from a manually downloaded package
- massively parallel databases / Clustered or massively parallel databases
- materialized views / How to do it…
- using / Using materialized views, Getting ready, How to do it…, There's more…, Using materialized views (long-living, temporary tables)
- reference link / Using materialized views (long-living, temporary tables)
- message log, database server
- locating / Locating the database server's message log, How to do it…, How it works…
- metacommand
- about / How it works…
- minor release upgrades
- about / Upgrading – minor releases, How it works…
- modules
- about / Listing extensions in this database
- installing, software installer used / Installing modules using a software installer
- installing, from PGXN / Installing modules from PGXN
- installing, from manually downloaded package / Installing modules from a manually downloaded package
- installing, from source code / Installing modules from source code
- installing, with extension infrastructure / Using the extension infrastructure
- installing, without extension infrastructure / Without the extension infrastructure
- monitoring tools
- PostgreSQL information, providing to / Providing PostgreSQL information to monitoring tools
- Multi-Version Concurrency Control (MVCC) / How it works…
- Multicorn
- URL / There's more…
- multimaster replication / Multimaster replication
- multinode architectures
- about / Multinode architectures
- tightly coupled database clusters / Multinode architectures
- loosely coupled database clusters / Multinode architectures
- multiple client certificates
- using / Using multiple client certificates
- multiple schemas
- using / Using multiple schemas, How to do it…, How it works…
- multiple servers
- running, on system / Running multiple servers on one system, How to do it…, How it works…
- accessing, with same host and port / Accessing multiple servers using the same host and port, How to do it…
- multitenancy
- design, deciding / Deciding on a design for multitenancy, How to do it…, How it works…
- about / Deciding on a design for multitenancy
- Multiversion Concurrency Control (MVCC) / Table and index bloat, Identifying and fixing bloated tables and indexes
- Multiversion concurrency control (MVCC) / What makes PostgreSQL different?
- Munin
- about / Introduction
- URL / Finding more information about generic monitoring tools
- MVCC / See also
N
- Nagios
- about / Introduction
- URL / Finding more information about generic monitoring tools
- name
- enforcing, for columns / Enforcing the same name and definition for columns, How to do it…, How it works…
- network/remote users
- access, enabling for / Enabling access for network/remote users, How it works…
- new connections
- preventing / Preventing new connections, How it works…
- NOLOGIN users
- forcing, to disconnect / Forcing NOLOGIN users to disconnect
- nondefault settings, parameters / Which parameters are at nondefault settings?, How to do it…, How it works…
- number of rows
- estimating, in table / Quickly estimating the number of rows in a table, How it works…, There's more…, Function 1 – estimating the number of rows
O
- object dependencies
- about / Getting ready, How to do it…, There's more…
- Object Relational Mappers (ORMs) / There's more…
- objects
- handling, with quoted names / Handling objects with quoted names, How it works…
- moving, between schemas / Moving objects between schemas
- moving, between tablespaces / Moving objects between tablespaces, How it works…
- accessing, in PostgreSQL databases / Accessing objects in other PostgreSQL databases, How to do it…, How it works…, There's more…
- accessing, in foreign databases / Accessing objects in other foreign databases, How to do it…, How it works…
- obscure table
- user, verifying / Knowing whether anybody is using a specific table, The quick and dirty way
- old prepared transactions
- removing / Removing old prepared transactions, How it works…, There's more…
- OmniPITR / Hot physical backup and continuous archiving
- URL / Hot physical backup and continuous archiving
- online transaction processing (OLTP) / Catching queries which only run for a few milliseconds
- online upgrade
- about / Major upgrades online
- ON_ERROR_STOP variable / How it works…
- OpenSSL library
- URL / See also
- Optimal Flexible Architecture (OFA) / How it works…
- optimistic locking
- using / Using optimistic locking, How it works…, There's more…
- Oracle compatibility module
- URL, for example / Installing modules from a manually downloaded package
- oracle_fdw documentation
- reference link / Getting ready
- OS-level replication / Other approaches to replication
- Out-of-memory (OOM) / How to do it…
P
- page corruptions
- avoiding / Getting ready, How to do it…, There's more…
- pages / There's more…
- parameter file
- updating / Updating the parameter file, How it works…, There's more…
- parameters
- modifying, in programs / Changing parameters in your programs, There's more…
- nondefault settings / Which parameters are at nondefault settings?, How to do it…, How it works…
- setting, for groups of users / Setting parameters for particular groups of users, How it works…
- parameters, pg_hba.conf file
- type / How it works…
- database / How it works…
- user / How it works…
- CIDR-ADDRESS / How it works…
- method / How it works…
- partitioning
- reference link / There's more…, There's more…
- password
- modifying, securely / Changing your password securely
- hardcoding, avoiding / Avoiding hardcoding your password, How it works…
- performance / Introduction
- improving, of backup/recovery / Improving performance of backup/recovery, How to do it…, There's more…
- performance mailing list
- reference link / How to do it…
- performance optimization
- reference link / There's more…
- performance problem report
- reference link / How to do it…
- performance problems
- reporting / Reporting performance problems
- performance replication / Performance and Synchronous Replication, How to do it…, How it works…
- period
- about / Real-world example – range of time
- periodical full backup / There's more…
- pg-rman
- about / Hot physical backup and continuous archiving
- URL / Hot physical backup and continuous archiving
- pgAdmin
- about / How to do it…
- URL / There's more…
- used, for real-time viewing / Real-time viewing using pgAdmin
- pgAdmin3
- about / How to do it…, There's more…
- pgBadger / Getting ready
- URL / Getting ready, See also
- PgBouncer
- about / Setting up a connection pool, How it works…
- SHOW commands / There's more…
- pgfincore extension
- about / There's more…
- reference link / There's more…
- pgFouine / Getting ready
- pgFoundry
- about / Getting ready
- URL / Getting ready
- pgloader
- URL / Getting ready
- pgsnmpd / Introduction
- PGXN
- about / Getting ready
- URL / Getting ready, Installing modules from PGXN
- modules, installing from / Installing modules from PGXN
- pg_available_extension_versions system view / There's more…
- pg_batch tool
- URL / Using pg_batch to run tasks in parallel
- used, for running tasks in parallel / Using pg_batch to run tasks in parallel
- pg_ctlcluster wrapper
- about / How it works…
- pg_hba.conf file / How it works…
- pg_hint_plan extension
- reference link / There's more…
- pg_lsclusters utility
- about / How to do it…
- pg_receivexlog / Archiving transaction log data
- pg_recvlogical / Archiving transaction log data
- pg_relation_size function / How it works…
- pg_rewind utility / There's more…
- pg_shard / Scalability tools
- pg_stat* views
- regular statistics, collecting from / Collecting regular statistics from pg_stat* views, Getting ready, How it works…
- pg_statsinfo package
- reference link / Another statistics collection package
- pg_stat_activity.waiting field / This catches only queries waiting on locks
- pg_stat_activity function / There's more…
- pg_stat_activity system view
- changes, in PostgreSQL / How it works…, There's more…
- reference link / There's more…
- pg_stat_database view
- reference link / How to do it…
- pg_stat_plans extension
- reference link / See also
- pg_terminate_backend() function / How to do it…, How it works…
- pg_upgrade / Major upgrades in-place
- pg_xlog
- putting, on separate device / Putting pg_xlog on a separate device
- physical backup / How to do it…
- Physical Log Streaming Replication (PLSR) / History and scope
- physical recovery / How it works…
- physical restore / How to do it…
- physical streaming replication (PSR) / Logical Replication
- Physical Streaming Replication (PSR) / History and scope
- PID (process ID) / How to do it…
- pivot query / How to do it…
- PL/Proxy
- URL / Collecting changes using triggers and saving them in another database using dblink or plproxy
- plproxy
- used, for saving data changes in database / Collecting changes using triggers and saving them in another database using dblink or plproxy
- point-in-time recovery (PITR) / Recovery to a point in time, How it works…, There's more…
- PostgreSQL
- about / Introduction, Introducing PostgreSQL 9
- users / What makes PostgreSQL different?
- URL, for security team / Security
- URL, for professional support / Commercial support
- obtaining / How to do it…, How it works…
- URL, for downloading / How to do it…
- URL, for announce mailing list / How to do it…
- URL, for software catalogue / See also
- URL / There's more…
- URL, for kernel resources / There's more…
- external module, adding to / Adding an external module to PostgreSQL, Getting ready, Installing modules using a software installer, Installing modules from PGXN, Installing modules from source code, How it works…
- URL, for online documentation / See also
- PostgreSQL, features
- about / Introducing PostgreSQL 9
- robustness / Robustness
- security / Security
- ease of use / Ease of use
- extensibility / Extensibility
- performance and concurrency / Performance and concurrency
- scalability / Scalability
- SQL / SQL and NoSQL
- NoSQL / SQL and NoSQL
- popularity / Popularity
- commercial support / Commercial support
- research and development funding / Research and development funding
- postgresql.conf file / There's more…
- PostgreSQL 8.4, on 64-bit architecture
- URL, for package / Installing modules from a manually downloaded package
- PostgreSQL 9
- about / Introducing PostgreSQL 9
- PostgreSQL databases
- objects, accessing in / Accessing objects in other PostgreSQL databases, Getting ready, How to do it…, How it works…, There's more…
- PostgreSQL database server
- subdirectories / There's more…
- PostgreSQL documents
- URL / There's more…
- PostgreSQL Flexible Architecture (PFA) / How it works…
- PostgreSQL foreign data wrapper / Getting ready
- PostgreSQL information
- providing, to monitoring tools / Providing PostgreSQL information to monitoring tools
- PostgreSQL manual / Reading The Fine Manual (RTFM), How it works…
- PostgreSQL plugins, Munin
- reference link / Providing PostgreSQL information to monitoring tools
- PostgreSQL project
- objectives / What makes PostgreSQL different?
- PostgreSQL server
- connecting to / Connecting to the PostgreSQL server, How to do it…, How it works…
- PostgreSQL severity / How it works…
- DEBUG 1 to DEBUG 5 / How it works…
- INFO / How it works…
- NOTICE / How it works…
- WARNING / How it works…
- LOG / How it works…
- FATAL / How it works…
- PANIC / How it works…
- postgres_fdw extension / How it works…
- postmaster
- about / Introduction
- prefix ranges
- example / Real-world example – prefix ranges
- URL / Real-world example – prefix ranges
- prepared statements
- reference link / How to do it…
- private database
- user, providing with / Giving users their own private database, How to do it…, How it works…
- programs
- parameters, modifying in / Changing parameters in your programs, There's more…
- promote action / How to do it…
- ps
- queries, viewing from / Watching queries from ps
- psql
- about / Introduction
- features / See also
- psql query tool
- using / Using the psql query and scripting tool, How to do it…, How it works…
- psql script
- writing / Writing a psql script that exits on the first error, Getting ready, How it works…
- psql scripting tool
- using / Using the psql query and scripting tool, How to do it…, How it works…
- psql utility
- URL / What if I want to repeatedly execute a query in psql?
- public-key cryptography / For really sensitive data
Q
- queries
- checking / Getting ready, How to do it…
- catching, which run for few milliseconds / Catching queries which only run for a few milliseconds
- longest queries, viewing / Watching the longest queries
- viewing, from ps / Watching queries from ps
- activation, verifying / Checking which queries are active or blocked, This catches only queries waiting on locks
- slow down reason, checking / Understanding why queries slow down, How to do it…, Do the queries return significantly more data than they did earlier?, Is the second run of the same query also slow?
- real-time performance, analyzing of / Analyzing the real-time performance of your queries, How it works…
- speeding up, without rewriting / Speeding up queries without rewriting them, How to do it…
- query
- blocking, investigating / Knowing who is blocking a query, How it works…
- reasons, for not using index / Why a query is not using an index, How to do it…
- force index usage / Forcing a query to use an index, How to do it…, There's more…
- query, in psql
- executing, repeatedly / What if I want to repeatedly execute a query in psql?
- query normalization / Analyzing the real-time performance of your queries
- query rewrite rules / Updatable views
- quoted names
- objects, handling with / Handling objects with quoted names, How it works…
- quote_ident() function
- about / There's more…
R
- range of time example / Real-world example – range of time
- read scalability
- about / Hot Standby and read scalability, Getting ready, How to do it…, How it works…
- real-time performance
- analyzing, of queries / Analyzing the real-time performance of your queries, How it works…
- recovery
- performance, improving of / Improving performance of backup/recovery, How to do it…, There's more…
- recovery, Barman
- about / Recovery with Barman, Getting ready, How to do it…, How it works…, There's more…
- local / Recovery with Barman
- remote / Recovery with Barman
- Recovery Point Objective (RPO) / There's more…
- recovery target / How to do it…
- Red Hat layout
- about / How it works…
- Reference Data Management / Topics
- referential integrity / Getting ready
- regular statistics
- collecting, from pg_stat* views / Collecting regular statistics from pg_stat* views, Getting ready, How it works…
- relay / Basic concepts
- release support policy, PostgreSQL
- URL / How it works…
- replication
- about / Introduction
- concepts / Replication concepts
- topics / Topics
- basic concepts / Basic concepts
- history / History and scope
- scope / History and scope
- practical aspects / Practical aspects
- other approaches / Other approaches to replication
- best practices / Replication best practices, How to do it…, There's more…
- monitoring / Monitoring replication, How to do it…, There's more…
- delaying / Getting ready, There's more…
- pausing / Getting ready, There's more…
- synchronizing / Getting ready, There's more…
- replication delay / Practical aspects
- replication sets / Practical aspects
- Replication Slots
- about / Using Replication Slots
- using / Using Replication Slots, How to do it…
- repmgr
- using / Using repmgr, How to do it…, There's more…
- URL / Using repmgr
- RESET command / How to do it…
- role
- about / Introduction
- roll back / How it works…
- Round Robin Database Tool (RRDtool) / Introduction
- row / There's more…
- rows
- counting, in table / How many rows in a table?, How it works…
- unnecessary rows, reducing / Reducing the number of rows returned, How to do it…
- RRDtool
- URL / Finding more information about generic monitoring tools
- RTFM
- about / Reading The Fine Manual (RTFM)
S
- scalability tools
- about / Scalability tools
- schema
- user access, granting to / Access to the schema
- schema-level privileges
- using / Using schema-level privileges
- schemas
- adding / How to do it…, There's more…
- removing / How to do it…
- objects, moving between / Moving objects between schemas
- script
- writing / Writing a script that either succeeds entirely or fails entirely, How to do it…, How it works…
- search+bind mode / Replacement for the User Name Map feature
- security definer functions / Giving limited superuser powers to specific users
- selective replication / Practical aspects
- sensitive data
- encrypting / Encrypting sensitive data, Getting ready, How to do it…, How it works…
- server
- version, identifying / What version is the server?, How it works…
- server authenticity
- checking / Checking server authenticity
- server configuration files
- reloading / Reloading the server configuration files, How it works…
- server log
- data changes, collecting from / Collecting data changes from the server log
- Server Signaling Functions section, PostgreSQL documentation
- URL / There's more…
- server uptime
- about / What is the server uptime?, How it works…
- session
- killing / Killing a specific session, How it works…, Using statement timeout to clean up queries that take too long to run
- sessions
- about / Connecting to the PostgreSQL server
- set-returning function
- using / Using set-returning functions for some parts of queries
- SET command / How to do it…
- SET LOCAL command / There's more…
- set of data
- unique key, finding for / Finding a unique key for a set of data, How to do it…
- setuid flag / How it works…
- shared_buffers configuration parameter
- reference link / There's more…
- SHOW commands, PgBouncer
- SHOW STATS / There's more…
- SHOW SERVERS / There's more…
- SHOW CLIENTS / There's more…
- SHOW POOLS / There's more…
- SHOW LISTS / There's more…
- SHOW USERS / There's more…
- SHOW DATABASES / There's more…
- SHOW CONFIG / There's more…
- SHOW FDS / There's more…
- SHOW SOCKETS / There's more…
- SHOW VERSION / There's more…
- shutdown abort / How it works…
- Simple Network Management Protocol (SNMP) / Introduction
- single-master replication / Single-master replication
- single audit trigger
- used, for collecting data changes from multiple tables / Using a single audit trigger to collect changes from multiple tables
- Slony / History and scope
- slow SQL statements
- finding / Finding slow SQL statements, How to do it…
- snapshot conflicts / How to do it…
- snapshot export feature / How it works…
- snapshot isolation / What makes PostgreSQL different?
- software installer
- used, for installing modules / Installing modules using a software installer
- source code
- modules, installing from / Installing modules from source code
- split-brain situation / How to do it…
- spreadsheet
- data, loading from / Loading data from a spreadsheet, How to do it…, How it works…
- SQL
- reasons, for slow performance / Finding out what makes SQL slow, How to do it…, There's more…, Locking problems, Not enough CPU power or disk I/O capacity for the current load
- SSL certificates
- used, for authenticating client / Using SSL certificates to authenticate the client, How to do it…
- SSL connection
- setting up / Connecting using SSL, How it works…
- SSL keys and certificates
- URL / See also
- staging/development / There's more…
- standalone backend / There's more…
- standalone hot physical database backup
- about / Standalone hot physical database backup, How to do it…, How it works…
- start.conf file
- auto parameter / How it works…
- manual parameter / How it works…
- disabled parameter / How it works…
- statistics, used by planner
- reference link / How to do it…
- streaming replication
- setting up / Setting up streaming replication, How to do it…, How it works…, There's more…
- managing / Managing streaming replication, How to do it…, There's more…
- streaming replication security
- setting up / Setting up streaming replication security, How it works…, There's more…
- subdirectories, PostgreSQL database server
- base / There's more…
- global / There's more…
- pg_clog / There's more…
- pg_dynshmem / There's more…
- pg_multixact / There's more…
- pg_notify / There's more…
- pg_replslot / There's more…
- pg_serial / There's more…
- pg_snapshot / There's more…
- pg_stat / There's more…
- pg_stat_tmp / There's more…
- pg_subtrans / There's more…
- pg_tblspc / There's more…
- pg_twophase / There's more…
- pg_xlog / There's more…
- superuser
- about / The PostgreSQL superuser
- superuser-like attributes / Other superuser-like attributes
- switchover / Single-master replication
- synchronous replication / Data loss
- about / Performance and Synchronous Replication, How to do it…, How it works…
- system
- multiple servers, running on / Running multiple servers on one system, How to do it…, How it works…
- system identifier, database
- locating / Locating the database's system identifier, How it works…
T
- table
- rows, counting in / How many rows in a table?, How it works…
- number of rows, estimating in / Quickly estimating the number of rows in a table, How it works…, There's more…, Function 1 – estimating the number of rows
- user access, revoking to / Revoking user access to a table, How to do it…, How it works…
- user access, granting to / Granting user access to a table, Granting access to a table through a group role
- columns, adding to / How to do it…, How it works…
- columns, removing from / How to do it…, How it works…
- last used information, retrieving / Getting ready, How it works…
- table bloat / Table and index bloat
- table partitioning / There's more…
- tables
- counting, in database / How many tables in a database?, How it works…
- disk space, monitoring / How it works…
- biggest tables, identifying / Which are my biggest tables?, How it works…
- actions, performing on / Performing actions on many tables, How to do it…, How it works…, There's more…
- table size
- computing, without locks / Function 2 – computing the size of a table without locks
- tablespace-level tuning / Tablespace-level tuning
- tablespaces
- about / Adding/removing tablespaces
- adding / How to do it…, How it works…
- removing / How to do it…, How it works…
- best practices / There's more…
- objects, moving between / Moving objects between tablespaces, How it works…
- temporary file usage
- verifying / Finding out whether a temporary file is in use any more
- logging / Logging temporary file usage
- temporary tables
- actions, for heavy users of / Actions for heavy users of temporary tables, How it works…
- test data
- generating / How to do it…, How it works…
- The PostgreSQL License (TPL) / Getting PostgreSQL
- TOAST
- about / There's more…
- transaction ID (xid) / How it works…
- transaction isolation level
- reference link / How to do it…
- transaction log data
- archiving / Archiving transaction log data, How to do it…
- Transaction Manager (TM) / Removing old prepared transactions
- transaction queries
- Idle, killing in / Killing Idle in transaction queries
- transaction system / Writing a script that either succeeds entirely or fails entirely
- transaction wraparound
- avoiding / Getting ready, How it works…, There's more…
- trigger-based replication / History and scope
- trigger file / How to do it…
- triggers
- used, for collecting data changes / Collecting changes using triggers, Collecting changes using triggers and saving them in another database using dblink or plproxy
- tuple / There's more…
- two-phase commit (2PC) / Detecting an in-doubt prepared transaction
- two-phase commit feature (2PC) / Removing old prepared transactions
- typical user role / Typical user role
U
- unique key
- finding, for set of data / Finding a unique key for a set of data, How to do it…
- uniqueness, without indexes / Uniqueness without indexes
- UNLOGGED table / How to do it…
- unused indexes
- fixing / Finding unused indexes, How it works…
- unwanted indexes
- removing / Carefully removing unwanted indexes, How it works…
- user
- creating / Creating a new user, How it works…
- removing, without dropping data / Removing a user without dropping their data, How it works…
- user access
- revoking, to table / Revoking user access to a table, How to do it…, How it works…
- granting, to table / Granting user access to a table
- granting, to schema / Access to the schema
- granting, to table through group role / Granting access to a table through a group role
- granting, to objects in schema / Granting access to all objects in a schema
- user attributes / Not inheriting the user attributes
- user connection
- preventing, temporarily / Temporarily preventing a user from connecting, How it works…
- checking / Checking whether a user is connected, How to do it…
- username map feature / Getting ready
- user password
- strength, checking / Checking whether all users have a secure password, How it works…
- users
- restricting, to one session each / How to do it…, How it works…
- removing, from database server / Pushing users off the system, How to do it…, How it works…
- providing, with private database / Giving users their own private database, How to do it…, How it works…
- USING clause
- about / There's more…
V
- VACUUM command / Introduction
- views
- updating / Updatable views, Getting ready, How to do it…, How it works…, There's more…
- virtual private network (VPN) / Connecting using SSL
W
- Write-ahead Log (WAL) / How to do it…
- write-ahead log (WAL) / There's more…
X
- XA protocol / Removing old prepared transactions
Z
- Zabbix / Introduction
- URL / Finding more information about generic monitoring tools