System Configuration Files

I l @ ve RuBoard

pg_options/postgresql.conf

Description

This is the configuration file that specifies what options are to be used when the server is started as postmaster . The name of this file has changed between versions and will either be called pg_options , postmaster.opts , or postgresql.conf , depending on your current version. The exact syntax and options available within this configuration file also vary depending on the version.

Notes/Location

Essentially, this file is a text file that contains various command-line switches. A standard configuration file might appear as follows :

 -p 5432  Use TCP/IP port 5432  -D /usr/local/pgsql/data/  Path to data directory  -B 64  Start with 64 8KB buffers  -b /usr/local/pgsql/bin/postgres  Path to executable  -N 32  32 Max connections 

The exact syntax of the configuration file will depend on what version of PostgreSQL is running. The postgresql.conf file, which is the method used in 7.1, accepts the following options:

 CHECKPOINT_SEGMENTS (integer) 

The maximum distance between automatic WAL checkpoints.

 CHECKPOINT_TIMEOUT (integer) 

The maximum time between automatic WAL checkpoints, in seconds.

 CPU_INDEX_TUPLE_COST (floating point) 

Sets the estimated cost of processing each tuple when used in an index scan.

 CPU_OPERATOR_COST (floating point) 

Sets the estimated cost for processing each operator in a WHERE clause.

 CPU_TUPLE_COST (floating point) 

Sets the estimated cost of processing a tuple inside a sequential scan.

 DEADLOCK_TIMEOUT (integer) 

Specifies the amount of time, in milliseconds , to wait on a lock before checking to see if there is a deadlock condition or not.

 DEBUG_ASSERTIONS (boolean) 

The Boolean value to enable or disable various debugging assertion checks.

 DEBUG_LEVEL (integer) 

The value that determines how verbose the debugging output is. This option is by default, which means no debugging output. Values up to 4 are valid.

 DEBUG_PRINT_PARSE (boolean), DEBUG_PRINT_PLAN (boolean), DEBUG_PRINT_REWRITTEN  (boolean), DEBUG_PRINT_QUERY (boolean), DEBUG_PRETTY_PRINT (boolean) 

Specifies what to print in the debug information. Prints the query, the parse tree, the execution plan, or the query rewriter output to the server log.

 EFFECTIVE_CACHE_SIZE (floating point) 

Sets the assumed size of the disk cache. This is measured in disk pages, which are normally 8KB apiece.

 ENABLE_HASHJOIN (boolean) 

The Boolean value to enable or disable hash joins. The default is on.

 ENABLE_INDEXSCAN (boolean) 

The Boolean value to enable or disable the use of index scan plan types. The default is on.

 ENABLE_MERGEJOIN (boolean) 

The Boolean value to enable or disable the use of merge-join plan types. The default is on.

 ENABLE_NESTLOOP (boolean) 

The Boolean value to enable or disable the use of nested-loop join plans. It's not possible to suppress nested-loop joins entirely, but turning this variable off discourages the planner from using it.

 ENABLE_SEQSCAN (boolean) 

The Boolean value to enable or disable the use of sequential scan plan types. It's not possible to suppress sequential scans entirely, but turning this variable off discourages the planner from using it.

 ENABLE_SORT (boolean) 

The Boolean value to enable or disable the use of sort steps. It's not possible to suppress sorts entirely, but turning this variable off discourages the planner from using it.

 ENABLE_TIDSCAN (boolean) 

The Boolean value to enable or disable the use of TID scan plan types. The default is on.

 FSYNC (boolean) 

The Boolean value that enables or disables PostgreSQL use of the fsync() system call in several places to make sure that updates are physically written to disk and do not hang around in the kernel buffer cache. This increases the chance that a database installation will still be usable after an operating system or hardware crash by a large amount. However, use of this option will degrade system performance. The default is off.

 GEQO (boolean) 

The Boolean value to enable or disable genetic query optimization. This is on by default.

 GEQO_EFFORT (integer), GEQO_GENERATIONS (integer), GEQO_POOL_SIZE (integer),  GEQO_RANDOM_SEED (integer), GEQO_SELECTION_BIAS (floating point) 

Various tuning parameters for the genetic query optimization algorithm.

 GEQO_THRESHOLD (integer) 

Specifies how many FROM items until the GEQO optimization is used. The default is 11.

 HOSTNAME_LOOKUP (boolean) 

The Boolean value to specify whether to resolve IP addresses to hostnames. By default, connection logs show only the IP address.

 KRB_SERVER_KEYFILE (string) 

Specifies the location of the Kerberos server key file.

 KSQO (boolean) 

The Key Set Query Optimizer (KSQO) causes the query planner to convert queries whose WHERE clause contains many OR 'ed AND clauses. KSQO is commonly used when working with products like Microsoft Access that tend to generate queries of this form. The default is off.

 LOG_CONNECTIONS (boolean) 

The Boolean value to enable or disable logging of each successful connection.This is off by default.

 LOG_PID (boolean) 

The Boolean value that enables or disables the log entry to prefix each message with the process ID of the back-end process. The default is off.

 LOG_TIMESTAMP (boolean) 

The Boolean value to enable or disable each log message to include a timestamp.The default is off.

 MAX_CONNECTIONS (integer) 

Determines how many concurrent connections the database server will allow.The default is 32.

 MAX_EXPR_DEPTH (integer) 

Sets the maximum expression nesting depth that the parser will accept. The default value is high enough for any normal query, but you can raise it if you need to. (If you raise it too high, however, you run the risk of back-end crashes due to stack overflow.)

 PORT (integer) 

The TCP port on which the server listens. It is 5432 by default.

 RANDOM_PAGE_COST (floating point) 

Sets the estimated cost of performing random, nonsequential page retrievals.

 SHARED_BUFFERS (integer) 

Sets the number of 8KB shared memory buffers that the database server will use.The default is 64.

 SHOW_QUERY_STATS (boolean), SHOW_PARSER_STATS (boolean), SHOW_PLANNER_STATS  (boolean), SHOW_EXECUTOR_STATS (boolean) 

Various Boolean values to set options that write performance statistics of the respective module to the server log.

 SHOW_SOURCE_PORT (boolean) 

The Boolean value to enable or disable the showing of the outgoing port of the connected user . The default is off.

 SILENT_MODE (bool) 

The Boolean value that determines whether postmaster runs silently. If this option is set, postmaster will automatically run in the background, and any controlling ttys are disassociated; thus, no messages are written to stdout or stderr (the same effect as the postmaster 's -S option). Unless some logging system such as syslog is enabled, using this option is discouraged because it makes it impossible to see error messages.

 SORT_MEM (integer) 

Specifies the amount of memory to be used by internal sorts and hashes before resorting to temporary disk files. The value is specified in kilobytes and defaults to 512 kilobytes.

 SQL_INHERITANCE (bool) 

The Boolean value to determine whether subtables are included in queries by default. By default, 7.1 and above include this capability; however, this was not the case in prior versions. If you need the old behavior, you can set this variable to off.

 SSL (boolean) 

The Boolean value to enable or disable SSL connections. The default is off.

 SYSLOG (integer) 

The value that determines the postgres use of syslog for logging. If this option is set to 1, messages go both to syslog and the standard output. A setting of 2 sends output only to syslog . The default is 0, which means syslog is off. To use syslog , the build of postgres must be configured with the -- enable-syslog option.

 SYSLOG_FACILITY (string) 

This option determines the syslog "facility" to be used when syslog is enabled.You can choose from LOCAL0 , LOCAL1 , LOCAL2 , LOCAL3 , LOCAL4 , LOCAL5 , LOCAL6 , LOCAL7 ; the default is LOCAL0 .

 SYSLOG_IDENT (string) 

If logging to syslog is enabled, this option determines the program name used to identify PostgreSQL messages in syslog log messages. The default is postgres .

 TCPIP_SOCKET (boolean) 

The Boolean value to enable or disable TCP/IP connections. It is off by default.

 TRACE_NOTIFY (boolean) 

The Boolean value to enable or disable debugging output for the LISTEN and NOTIFY commands. The default is off.

 UNIX_SOCKET_DIRECTORY (string) 

Specifies the directory of the UNIX domain socket on which the postmaster is to listen for connections from client applications. The default is normally /tmp .

 UNIX_SOCKET_GROUP (string) 

Sets the group owner of the UNIX domain socket.

 UNIX_SOCKET_PERMISSIONS (integer) 

Sets the access permissions of the UNIX domain socket. The default permissions are 0777 , meaning anyone can connect.

 VIRTUAL_HOST (string) 

Specifies the TCP/IP hostname or address on which the postmaster is to listen for connections from client applications. Defaults to listening on all configured addresses (including localhost).

 WAL_BUFFERS (integer) 

The number of disk-page buffers in shared memory for the WAL log.

 WAL_DEBUG (integer) 

If nonzero, turn on WAL- related debugging output on standard error.

 WAL_FILES (integer) 

The number of log files created in advance at checkpoint time.

 WAL_SYNC_METHOD (string) 

The method used for forcing WAL updates out to disk. Possible values are FSYNC , FDATASYNC , OPEN_SYNC , and OPEN_DATASYNC . Not all of these choices are available on all platforms.

All the preceding Boolean values will accept the following values:

TRUE Values	FALSE Values
ON	OFF
TRUE	FALSE
YES	NO
1

Location of the file:

RPM ” /var/lib/pgsql/data/

Source ” /usr/local/pgsql/data

/etc/logrotate.d/postgres

Description

Responsible for rotating log files.

Notes/Location

Although not an official part of the PostgreSQL distribution, many systems include a file to manage and rotate log files produced by PostgreSQL.

These are usually cron jobs that are scheduled to run daily or weekly. These RPM additions are usually configured to run PostgreSQL logging with syslog .

It is generally not advisable to attempt to rotate log files not running as a syslog configured installation. PostgreSQL keeps its connections to log files open at all times; therefore, rotating a log file while postmaster is still active could result in unpredictable behavior.

If configuring PostgreSQL to run with syslog is not an option, the next best solution is to briefly stop the postmaster service, rotate your log files, and then restart the database system.

For more information on PostgreSQL log files and syslog , see Chapter 9, "Databases and Log Files."

Location of the file:

RPM ” /etc/logrotate.d/postgres

Note:

This file was subsequently dropped from the most recent RPM package due to the confusion resulting from the syslog versus PostgreSQL log issues identified here. However, older RPM packages that are specifically designed to work with syslog will include the preceding file in the specified location.

pg_hba.conf

Description

The pg_hba.conf file is a configuration file that is responsible for host-based access control. Essentially, this is a text file that details how users are permitted to connect to the PostgreSQL back end.

This file has separate areas that deal with local or remote (TCP/IP) users, databases allowed to connect, and authentication methods .

The format of a PostgreSQL access control file differs depending on whether a TCP/IP or a local UNIX connection is being specified. The basic formats are as follows:

TCP/IP:

 Host  DB IP Netmask Auth-Type  [  Auth-Args  ] 

Local:

 Local  DB Auth-Type  [  Auth-Args  ] 

Option	Description
Host	Either host , hostssl , or local , depending on whether access is being specified for a standard TCP/IP connection, secure SSL TCP/IP connection, or local UNIX connection.
DB	The name of the database for which this access control list is valid. Use all to specify all databases or sameuser to specify that the user can only connect to databases of the same username.
IP	For TCP/IP connections, this specifies valid client IP addresses that can connect to the PostgreSQL back end.
Netmask	The network mask for the valid client machine.
Auth-Type	The authentication method to employ before granting access. This can be one of the following:
	trust	No authentication; trust this user.
	password	Match password supplied by host. By default, pg_shadow is checked unless an alternative is supplied in the Auth-Args section.
	crypt	Same as preceding, but password is not sent as clear text; it is encrypted before being transmitted.
	ident	Uses the ident protocol (RFC 1413). Usually a file named pg_ident.conf exists that will map ident usernames to the corresponding PostgreSQL username. (Not supported by local connections.)
	krb4	Kerberos V4 is used. (Not supported by local connections.)
	krb5	Kerberos V5 is used. (Not supported by local connections.)
	reject	Deny connection attempt.
Auth-Args	Various arguments used by the authentication method specified.

Notes/Location

A sample pg_hba.conf file might appear as follows

 local       all         trust  host        web         192.168.0.0 255.255.255.0    trust  host        payroll     192.168.0.0 255.255.255.0    crypt 

In this case, all local connections are permitted. Similarly, any connection from the IP range 192.168.0.0 to 192.168.0.254 is permitted to the web database. However, a user from that address block trying to connect to the payroll database will need to provide authentication provided by the crypt method.

Location of the file:

RPM ” /usr/local/pgsql/data/

Source ” /var/lib/pgsql/data/

I l @ ve RuBoard