PostgreSQL implements table inheritance, which can be a useful tool for database designers. (SQL:1999 and later define a type inheritance feature, which differs in many respects from the features described here.) Let's start with an example: suppose we are trying to build a data model for cities. Each state has many cities, but only one capital. We want to be able to quickly retrieve the capital city for any particular state. This can be done by creating two tables, one for state capitals and o

An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database; Section 18.2 explains where these files are located. You can use whatever method you prefer for doing file system backups; for example: tar -cf backup.tar /usr/local/pgsql/data There are two restrictions, however, which make this method impractical, or at least inferior to the pg_dump method: The database server must be shut down in order to get a usable backup. Half-way measu

The executor takes the plan created by the planner/optimizer and recursively processes it to extract the required set of rows. This is essentially a demand-pull pipeline mechanism. Each time a plan node is called, it must deliver one more row, or report that it is done delivering rows. To provide a concrete example, assume that the top node is a MergeJoin node. Before any merge can be done two rows have to be fetched (one from each subplan). So the executor recursively calls itself to process t

NameSELECT, TABLE, WITH -- retrieve rows from a table or view Synopsis [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] [ * | expression [ [ AS ] output_name ] [, ...] ] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY

An index definition can specify an operator class for each column of an index. CREATE INDEX name ON table (column opclass [sort options] [, ...]); The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on the type int4 would use the int4_ops class; this operator class includes comparison functions for values of type int4. In practice the default operator class for the column's data type is usually sufficient. The main reason for having

Namepg_xlogdump -- display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster Synopsis pg_xlogdump [option...] [startseg [endseg] ] Description pg_xlogdump displays the write-ahead log (WAL) and is mainly useful for debugging or educational purposes. This utility can only be run by the user who installed the server, because it requires read-only access to the data directory. Options The following command-line options control the location and format of

The examples in the previous section illustrated full text matching using simple constant strings. This section shows how to search table data, optionally using indexes. 12.2.1. Searching a Table It is possible to do a full text search without an index. A simple query to print the title of each row that contains the word friend in its body field is: SELECT title FROM pgweb WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend'); This will also find related words such as friends

An alternative to the built-in standby mode described in the previous sections is to use a restore_command that polls the archive location. This was the only option available in versions 8.4 and below. In this setup, set standby_mode off, because you are implementing the polling required for standby operation yourself. See the pg_standby module for a reference implementation of this. Note that in this mode, the server will apply WAL one file at a time, so if you use the standby server for queri

Shared Disk Failover Shared disk failover avoids synchronization overhead by having only one copy of the database. It uses a single disk array that is shared by multiple servers. If the main database server fails, the standby server is able to mount and start the database as though it were recovering from a database crash. This allows rapid failover with no data loss. Shared hardware functionality is common in network storage devices. Using a network file system is also possible, though care

The catalog pg_aggregate stores information about aggregate functions. An aggregate function is a function that operates on a set of values (typically one column from each row that matches a query condition) and returns a single value computed from all these values. Typical aggregate functions are sum, count, and max. Each entry in pg_aggregate is an extension of an entry in pg_proc. The pg_proc entry carries the aggregate's name, input and output data types, and other information that is simil