Chapter 6. Database Administration

The syntax of the pg_options file is extremely simple, a shown in the following:

verbose=2
query=2
syslog=0

First verbose is set to two which makes PostgreSQL display some runtime information on the screen. The seond line tells PostgreSQL to print information about queries. Syslog is turned off so that all information is sent to stdout and stderr . Adding an option can easily be done by adding it to the file. The following is a complete list of all currently supported options:

• all all is the global trace flag. It can be set to (trace messages enabled individually), 1 (enable all trace messages), or -1 (disable all trace messages). Tracing means that the internals of the database can be monitored .

• verbose The verbose flag is used to define the amount of message the database provides. means that no messages are displayed. 1 provides more messages, and 2 displays as many messages as possible.

• query This flag is used to define the way queries are traced. means that no query is displayed, 1 writes one line per query, and 4 prints the full query.

• plan Prints the query plan.

• parse Prints the output of the parser.

• rewritten Prints rewritten queries.

• pretty_plan Pretty-print query plan.

• pretty_parse Prints pretty-print parser output.

• pretty_rewritten Prints pretty-print rewritten queries.

• parsestats Prints the statistics of the parser.

• plannerstats Prints the statistics of the planner.

• executorstats Prints the statistics of the executor.

• locks Used to trace locking.

• userlocks Used to trace user locks.

• spinlocks Used to trace spin locks.

• notify Used to trace notify functions.

• lock_debug_oidmin Minimum relation oid traced by locks.

• lock_debug_relid oid , if not zero, of relation traced by locks.

• deadlock_timeout Deadlock check timer.

• syslog Defines the behavior of syslog . prints all messages to standard output or standard error, 1 prints all messages to standard output, standard error, and syslog . 2 sends the messages to syslog only.

• hostlookup Enables hostname lookups in ps_status .

• showportnumber Shows port numbers in ps_status .

• nofsync Disables fsync on a per-backend basis.

The nofsync flag may be especially interesting for you, and it is worth going into a little more into detail. int fsync(int fd) can be found in the unistd.h C library and is used to flush all data to disk. This is useful because it protects your data from being lost when the machine crashes. But fsync can also cost a lot of performance when dealing with huge amounts of data. We will focus on the performance aspect of fsync in Chapter 8, called "Performance Tuning."

PostgreSQL 7.1 ” postgreSQL.conf

Since PostgreSQL 7.1, all runtime parameters can be found in postgresql.conf . Like its predecessor, the file can be found in $PGDATA/data . Let's take a look at the default config file:

#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form
#
#   name = value
#
# (The `=' is optional.) White space is collapsed, comments are
# introduced by `#' anywhere on a line. The complete list of option
# names and allowed values can be found in the PostgreSQL
# documentation. Examples are:

#log_connections = on
#fsync = off
#max_connections = 64

# Any option can also be given as a command line switch to the
# postmaster, e.g., 'postmaster -c log_connections=on'. Some options
# can be set at run-time with the 'SET' SQL command.


#========================================================================


#
#    Connection Parameters
#
#tcpip_socket = false
#ssl = false

#max_connections = 32 # 1-1024

#port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777

#virtual_host = ''

#krb_server_keyfile = ''

#
#    Performance
#
#sort_mem = 512
#shared_buffers = 2*max_connections # min 16
#fsync = true

#
#    Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#ksqo = false
#geqo = true

#effective_cache_size = 1000  # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025
#geqo_selection_bias = 2.0 # range 1.5-2.0

#
#    GEQO Optimizer Parameters
#
#geqo_threshold = 11
#geqo_pool_size = 0  #default based in tables, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed

#
#    Inheritance
#
#sql_inheritance = true

#
#    Deadlock
#
#deadlock_timeout = 1000

#
#    Expression Depth Limitation
#
#max_expr_depth = 10000 # min 10

#
#    Write-ahead log (WAL)
#
#wal_buffers = 8 # min 4
#wal_files = 0 # range 0-64
#wal_sync_method = fsync # fsync or fdatasync or open_sync or open_datasync
# Note: default wal_sync_method varies across platforms
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600

#
#    Debug display
#
#silent_mode = false

#log_connections = false
#log_timestamp = false
#log_pid = false

#debug_level = 0 # range 0-16
#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

#ifdef USE_ASSERT_CHECKING
#debug_assertions = true
#endif

#
#    Syslog
#
#ifdef ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#endif

#
#    Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
#ifdef BTREE_BUILD_STATS
#show_btree_build_stats = false
#endif

#
#    Lock Tracing
#
#trace_notify = false
#ifdef LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_spinlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif

We will take a look at the most important parameters that affect your daily work with PostgreSQL.

• log_connections Prints one line for every connection established to the server.

• fsync Tells the database whether to turn fsync() on or off. If fsync() is on, the database makes sure that the data is physically written to disk.

• max_connections By default, PostgreSQL allows 32 simultaneous connections. This value can be changed by setting max_connections to the desired value. By default, there is a compile limit of 1024 connections, which can only be changed by recompiling the server.

• tcpip_socket If this flag is turned on, PostgreSQL accepts TCP/IP connections and not only Unix domain socket connections.

• ssl Enables secure connections via SSL.

• port Defines the TCP-port to which the server is listening. This value can only be set when the server starts; you can't change it for individual backend processes.

• hostname_lookup If you want hostnames instead of IP addresses to be logged, hostname_lookup has to be turned on. Don't forget that DNS lookups can take some time.

• show_source_port With this flag enabled, the database shows the outgoing port of the host connecting to PostgreSQL.

• unix_socket_directory Defines the directory of the Unix domain socket the postmaster is listening to for connections from client applications. The default value is usually set to /tmp .

• unix_socket_group Defines the group owner of the Unix domain socket. By default, it is an empty string, which means that the default group of the current user is taken.

• unix_socket_permissions The value must be defined by using the default Unix syntax for setting file system permissions. For example, 0777 means that everybody can connect.

• virtual_host Works pretty much the same like Apache's virtual_host directly. By default, PostgreSQL listens to all hostnames assigned to the machine.

• krb_server_keyfile Defines the location of the Kerberos key file.

• sort_mem If PostgreSQL has to sort data, it is usually done in memory as long as the size of the sort buffer is not exceeded. If that happens, the database uses temporary files for sorting. Compared to sorting in memory, this is ghastly slow, but if the amount of data that has to be processed is extremely huge, it cannot be done in any other way. Keep in mind that if many concurrent sorts are running, the amount of memory defined by sort_mem is used multiple times. The size of the sort buffer is defined in kilobytes.

• shared_buffers Sets the shared memory buffers the database will use. A buffer is usually 8KB; the default value is set to 32.

• enable_seqscan If you want to avoid the planner using sequential scans , you can unset the enable_seqscan flag. It is not possible to turn sequential scans off completely, but their use can be reduced to the minimum.

• enable_indexscan Defines whether the database use indexscans.

• enable_tidscan Defines whether the planner uses TID scan plan types.

• enable_sort If you want to influence the number of sort operations, you can unset the enable_sort flag. Sorting can be turned off completely because the planner cannot always find a workaround.

• enable_nestloop Enables the use of nested loops .

• enable_mergejoin Enables the use of merge joins.

• enable_hashjoins Enables the use of has joins.

• ksqo The Key Set Query Optimizer (KSQO) makes the planner convert queries with many ANDs and ORs in the WHERE clause to queries using UNION . This is usually done when working with Microsoft Access.

• geqo Defines whether generic query optimization is used. Generic query optimization means that query planning is done without exhaustive searching. The database uses on by default.

• effective_cache_size Defines the value the optimizer assumes for the size of the disk cache.

• random_page_cost In many cases, PostgreSQL cannot do sequential reading. Jumping from block to block takes longer than sequential reading. random_page_cost can be used to tell the query optimizer how much longer random reading takes (for example, 4 times). This is a floating point value and allows very fine tuning.

• cpu_tuple_cost Defines the value the optimizer will assume to process a tuple. It is measured as a fraction of the cost of a sequential page fetch.

• cpu_index_tuple_cost Similar to cpu_tuple_cost but sets the time for processing a tuple using an index.

• cpu_operator_cost Sets the time to process an operator in a WHERE clause. It is also measured as a fraction of the cost of a sequential scan.

• sql_inheritance Defines the behavior of the database when dealing with inheritance. As described in the section called "Inheritance" in Chapter 8), PostgreSQL treats inherited tables slightly different. If you find the old way better, set this value to off .

• deadlock_timeout In a few cases, the database has to face a deadlock. deadlock_timeout defines the time to wait when waiting for a lock before checking whether the conditions for a deadlock are fulfilled. The time is measured in milliseconds .

• max_expr_depth Tells the database how many expressions can be nested within each other. If this is set to a very high value, the backend can crash because of buffer overflows.

• wal_buffers Defines the number of disk page buffers stored in shared memory for WAL log. WAL is short for Write Ahead Log.

• wal_files PostgreSQL creates log files in advance. wal_files defines how many of these files are created.

• silent_mode This has actually the same effect as the -S flag of the postmaster. No standard error and no standard output is produced.

• log_timestamp Adds a timestamp to each entry in the logfile. The default value is set to off , but we strongly recommend that you turn it on , because it allows you to find out what kind of action was performed by the database.

• log_pid The process ID can be added to every entry in the logfile by using the log_pid flag.

• debug_level The higher this value is, the more debugging information will be displayed. The default value is set to . During development, it is useful to turn the debug level up and then down again for production.

• syslog syslog is a powerful logging tool supported by all current Linux distributions. If this value is set to , syslog is turned off completely. 1 means that messages are written to standard output and syslog . If you use 2 , syslog only is used.

postgresql.conf is simply a wonderful thing. All major configuration tasks can be done within just one file, and this makes PostgreSQL administration even more simple.