It is possible to use SSH to encrypt the network connection between clients and a PostgreSQL server. Done properly, this provides an adequately secure network connection, even for non-SSL-capable clients. First make sure that an SSH server is running properly on the same machine as the PostgreSQL server and that you can log in using ssh as some user. Then you can establish a secure tunnel with a command like this from the client machine: ssh -L 63333:localhost:5432 joe@foo.com The first numbe

Namepg_isready -- check the connection status of a PostgreSQL server Synopsis pg_isready [connection-option...] [option...] Description pg_isready is a utility for checking the connection status of a PostgreSQL database server. The exit status specifies the result of the connection check. Options -d dbname--dbname=dbname Specifies the name of the database to connect to. If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is tr

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