From d258ba01ec601f655137468bd484153eb369e92d Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 24 Mar 2003 14:32:51 +0000 Subject: Another big editing pass for consistent content and presentation. --- doc/src/sgml/backup.sgml | 37 +- doc/src/sgml/charset.sgml | 579 +++++++++----------------------- doc/src/sgml/diskusage.sgml | 67 ++-- doc/src/sgml/func.sgml | 6 +- doc/src/sgml/install-win32.sgml | 4 +- doc/src/sgml/maintenance.sgml | 75 ++--- doc/src/sgml/monitoring.sgml | 191 ++++++----- doc/src/sgml/ref/clusterdb.sgml | 14 +- doc/src/sgml/ref/createdb.sgml | 16 +- doc/src/sgml/ref/createlang.sgml | 19 +- doc/src/sgml/ref/createuser.sgml | 18 +- doc/src/sgml/ref/current_date.sgml | 148 -------- doc/src/sgml/ref/current_time.sgml | 158 --------- doc/src/sgml/ref/current_timestamp.sgml | 166 --------- doc/src/sgml/ref/current_user.sgml | 167 --------- doc/src/sgml/ref/dropdb.sgml | 16 +- doc/src/sgml/ref/droplang.sgml | 15 +- doc/src/sgml/ref/dropuser.sgml | 19 +- doc/src/sgml/ref/ecpg-ref.sgml | 14 +- doc/src/sgml/ref/initdb.sgml | 26 +- doc/src/sgml/ref/initlocation.sgml | 6 +- doc/src/sgml/ref/ipcclean.sgml | 16 +- doc/src/sgml/ref/pg_config-ref.sgml | 8 +- doc/src/sgml/ref/pg_controldata.sgml | 14 +- doc/src/sgml/ref/pg_ctl-ref.sgml | 125 +++---- doc/src/sgml/ref/pg_dump.sgml | 116 +++---- doc/src/sgml/ref/pg_dumpall.sgml | 37 +- doc/src/sgml/ref/pg_resetxlog.sgml | 39 ++- doc/src/sgml/ref/pg_restore.sgml | 183 ++++------ doc/src/sgml/ref/pgtclsh.sgml | 4 +- doc/src/sgml/ref/pgtksh.sgml | 4 +- doc/src/sgml/ref/postgres-ref.sgml | 77 +++-- doc/src/sgml/ref/postmaster.sgml | 157 +++++---- doc/src/sgml/ref/psql-ref.sgml | 350 +++++++++---------- doc/src/sgml/ref/vacuumdb.sgml | 39 ++- doc/src/sgml/reference.sgml | 27 +- doc/src/sgml/runtime.sgml | 524 ++++++++++++++--------------- doc/src/sgml/wal.sgml | 267 +++++++-------- 38 files changed, 1321 insertions(+), 2427 deletions(-) delete mode 100644 doc/src/sgml/ref/current_date.sgml delete mode 100644 doc/src/sgml/ref/current_time.sgml delete mode 100644 doc/src/sgml/ref/current_timestamp.sgml delete mode 100644 doc/src/sgml/ref/current_user.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index a58945f1ef0..24ea265f23d 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -1,5 +1,5 @@ Backup and Restore @@ -110,7 +110,7 @@ psql dbname < psql (e.g., with createdb -T template0 dbname). psql supports similar options to pg_dump - for controlling the database server location and the user names. See + for controlling the database server location and the user name. See its reference page for more information. @@ -135,7 +135,7 @@ psql dbname < The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database - directly from one server to another, for example + directly from one server to another; for example: pg_dump -h host1 dbname | psql -h host2 dbname @@ -179,27 +179,19 @@ pg_dumpall > outfile Large Databases - - Acknowledgement - - Originally written by Hannu Krosing - (hannu@trust.ee) on 1999-06-19 - - - Since PostgreSQL allows tables larger than the maximum file size on your system, it can be problematic - to dump the table to a file, since the resulting file will likely + to dump such a table to a file, since the resulting file will likely be larger than the maximum size allowed by your system. As - pg_dump writes to the standard output, you can - just use standard *nix tools to work around this possible problem. + pg_dump can write to the standard output, you can + just use standard Unix tools to work around this possible problem. Use compressed dumps. - Use your favorite compression program, for example + You can use your favorite compression program, for example gzip. @@ -222,9 +214,10 @@ cat filename.gz | gunzip | psql - Use <application>split</>. + Use <command>split</>. - This allows you to split the output into pieces that are + The split command + allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte: @@ -338,7 +331,7 @@ tar -cf backup.tar /usr/local/pgsql/data - If you have dug into the details of the file system layout you + If you have dug into the details of the file system layout of the data you may be tempted to try to back up or restore only certain individual tables or databases from their respective files or directories. This will not work because the @@ -348,7 +341,7 @@ tar -cf backup.tar /usr/local/pgsql/data all transactions. A table file is only usable with this information. Of course it is also impossible to restore only a table and the associated pg_clog data - because that will render all other tables in the database + because that would render all other tables in the database cluster useless. @@ -381,7 +374,7 @@ tar -cf backup.tar /usr/local/pgsql/data server, using pg_dump. (There are checks in place that prevent you from doing the wrong thing, so no harm can be done by confusing these things.) The precise installation procedure is - not subject of this section, these details are in . + not subject of this section; these details are in . @@ -393,7 +386,7 @@ tar -cf backup.tar /usr/local/pgsql/data pg_dumpall -p 5432 | psql -d template1 -p 6543 - to transfer your data, or use an intermediate file if you want. + to transfer your data. Or use an intermediate file if you want. Then you can shut down the old server and start the new server at the port the old one was running at. You should make sure that the database is not updated after you run pg_dumpall, @@ -413,7 +406,7 @@ pg_dumpall -p 5432 | psql -d template1 -p 6543 pg_dumpall > backup pg_ctl stop mv /usr/local/pgsql /usr/local/pgsql.old -cd /usr/src/postgresql-&version; +cd ~/postgresql-&version; gmake install initdb -D /usr/local/pgsql/data postmaster -D /usr/local/pgsql/data diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml index 9ccd8fa5e1e..5c2c2aee34c 100644 --- a/doc/src/sgml/charset.sgml +++ b/doc/src/sgml/charset.sgml @@ -1,18 +1,13 @@ - + Localization</> - <abstract> - <para> - Describes the available localization features from the point of - view of the administrator. - </para> - </abstract> - - <para> - <productname>PostgreSQL</productname> supports localization with - three approaches: + <para> + This chapter describes the available localization features from the + point of view of the administrator. + <productname>PostgreSQL</productname> supports localization with + three approaches: <itemizedlist> <listitem> @@ -25,20 +20,17 @@ <listitem> <para> - Using explicit multiple-byte character sets defined in the - <productname>PostgreSQL</productname> server to support languages - that require more characters than will fit into a single byte, - and to provide character set recoding between client and server. - The number of supported character sets is fixed at the time the - server is compiled, and internal operations such as string - comparisons require expansion of each character into a 32-bit - word. + Providing a number of different character sets defined in the + <productname>PostgreSQL</productname> server, including + multiple-byte character sets, to support storing text in all + kinds of languages, and providing character set recoding between + client and server. </para> </listitem> <listitem> <para> - Single byte character recoding provides a more light-weight + Single-byte character recoding provides a more light-weight solution for users of multiple, yet single-byte character sets. </para> </listitem> @@ -55,7 +47,7 @@ <firstterm>Locale</> support refers to an application respecting cultural preferences regarding alphabets, sorting, number formatting, etc. <productname>PostgreSQL</> uses the standard ISO - C and <acronym>POSIX</acronym>-like locale facilities provided by the server operating + C and <acronym>POSIX</acronym> locale facilities provided by the server operating system. For additional information refer to the documentation of your system. </para> @@ -92,7 +84,7 @@ initdb --locale=sv_SE <para> Occasionally it is useful to mix rules from several locales, e.g., - use U.S. collation rules but Spanish messages. To support that, a + use English collation rules but Spanish messages. To support that, a set of locale subcategories exist that control only a certain aspect of the localization rules. @@ -154,7 +146,7 @@ initdb --locale=sv_SE <para> The other locale categories can be changed as desired whenever the - server is started by setting the run-time configuration variables + server is running by setting the run-time configuration variables that have the same name as the locale categories (see <xref linkend="runtime-config"> for details). The defaults that are chosen by <command>initdb</command> are actually only written into @@ -190,16 +182,15 @@ initdb --locale=sv_SE variable <envar>LANGUAGE</envar> which overrides all other locale settings for the purpose of setting the language of messages. If in doubt, please refer to the documentation of your operating - system, in particular the - <citerefentry><refentrytitle>gettext</><manvolnum>3</></> manual - page, for more information. + system, in particular the documentation about + <application>gettext</>, for more information. </para> </note> <para> To enable messages translated to the user's preferred language, - the <option>--enable-nls</option> option must be used. This - option is independent of the other locale support. + <acronym>NLS</acronym> must have been enabled at build time. This + choice is independent of the other locale support. </para> </sect2> @@ -212,7 +203,7 @@ initdb --locale=sv_SE <itemizedlist> <listitem> <para> - Sort order in <command>ORDER BY</> queries. + Sort order in queries using <command>ORDER BY</> <indexterm><primary>ORDER BY</></> </para> </listitem> @@ -234,7 +225,7 @@ initdb --locale=sv_SE <para> The only severe drawback of using the locale support in - <productname>PostgreSQL</> is its speed. So use locale only if you + <productname>PostgreSQL</> is its speed. So use locales only if you actually need it. It should be noted in particular that selecting a non-C locale disables index optimizations for <literal>LIKE</> and <literal>~</> operators, which can make a huge difference in the @@ -247,49 +238,28 @@ initdb --locale=sv_SE <para> If locale support doesn't work in spite of the explanation above, - check that the locale support in your operating system is correctly configured. - To check whether a given locale is installed and functional you - can use <application>Perl</>, for example. Perl has also support - for locales and if a locale is broken <command>perl -v</> will - complain something like this: -<screen> -<prompt>$</> <userinput>export LC_CTYPE='not_exist'</> -<prompt>$</> <userinput>perl -v</> -<computeroutput> -perl: warning: Setting locale failed. -perl: warning: Please check that your locale settings: -LC_ALL = (unset), -LC_CTYPE = "not_exist", -LANG = (unset) -are supported and installed on your system. -perl: warning: Falling back to the standard locale ("C"). -</computeroutput> -</screen> + check that the locale support in your operating system is + correctly configured. To check what locales are installed on your + system, you may use the command <literal>locale -a</literal> if + your operating system provides it. </para> <para> - Check that your locale files are in the right location. Possible - locations include: <filename>/usr/lib/locale</filename> (<systemitem class="osname">Linux</>, - <systemitem class="osname">Solaris</>), <filename>/usr/share/locale</filename> (<systemitem class="osname">Linux</>), - <filename>/usr/lib/nls/loc</filename> (<systemitem class="osname">DUX 4.0</>). Check the locale - man page of your system if you are not sure. + Check that <productname>PostgreSQL</> is actually using the locale + that you think it is. <envar>LC_COLLATE</> and <envar>LC_CTYPE</> + settings are determined at <command>initdb</> time and cannot be + changed without repeating <command>initdb</>. Other locale + settings including <envar>LC_MESSAGES</> and <envar>LC_MONETARY</> + are initially determined by the environment the server is started + in. You can check the <envar>LC_COLLATE</> and <envar>LC_CTYPE</> + settings of a database with the utility program + <command>pg_controldata</>. </para> <para> - Check that <productname>PostgreSQL</> is actually using the locale that - you think it is. <envar>LC_COLLATE</> and <envar>LC_CTYPE</> settings are - determined at <application>initdb</> time and cannot be changed without - repeating <application>initdb</>. Other locale settings including - <envar>LC_MESSAGES</> and <envar>LC_MONETARY</> are determined by the - environment the postmaster is started in, and can be changed with a simple - postmaster restart. You can check the <envar>LC_COLLATE</> and - <envar>LC_CTYPE</> settings of - a database with the <filename>contrib/pg_controldata</> utility program. - </para> - - <para> - The directory <filename>src/test/locale</> contains a test suite - for <productname>PostgreSQL</>'s locale support. + The directory <filename>src/test/locale</> in the source + distribution contains a test suite for + <productname>PostgreSQL</>'s locale support. </para> <para> @@ -297,9 +267,9 @@ perl: warning: Falling back to the standard locale ("C"). text of the error message will obviously have problems when the server's messages are in a different language. If you create such an application you need to devise a plan to cope with this - situation. The embedded SQL interface (<application>ecpg</>) is + situation. The embedded SQL interface (<application>ECPG</>) is also affected by this problem. It is currently recommended that - servers interfacing with <application>ecpg</> applications be + servers interfacing with <application>ECPG</> applications be configured to send messages in English. </para> @@ -316,58 +286,41 @@ perl: warning: Falling back to the standard locale ("C"). </sect1> - <sect1 id="multibyte"> - <title>Multibyte Support - - multibyte - - - Author - - - Tatsuo Ishii (ishii@postgresql.org), - last updated 2002-07-24. - Check Tatsuo's - web site for more information. - - + + Character Set Support - - Multibyte (MB) support is intended to allow - PostgreSQL to handle - multiple-byte character sets such as EUC (Extended Unix Code), Unicode, and - Mule internal code. With MB enabled you can use multibyte - character sets in regular expressions (regexp), LIKE, and some - other functions. The default - encoding system is selected while initializing your - PostgreSQL installation using - initdb. Note that this can be - overridden when you create a database using - createdb or by using the SQL command - CREATE DATABASE. So you can have multiple databases each with - a different encoding system. Note that MB can - handle single byte characters sets such as ISO-8859-1. - + character set - - Multibyte support is enabled by default since - PostgreSQL version 7.3. - + + The character set support in PostgreSQL + allows you to store text in a variety of character sets, including + single-byte character sets such as the ISO 8859 series and + multiple-byte character sets such as EUC (Extended Unix + Code), Unicode, and Mule internal code. All character sets can be + used transparently throughout the server. (If you use extension + functions from other sources, it depends on whether they wrote + their code correctly.) The default character set is selected while + initializing your PostgreSQL database + cluster using initdb. It can be overridden when you + create a database using createdb or by using the + SQL command CREATE DATABASE. So you can have multiple + databases each with a different character set. + - Supported character set encodings + Supported Character Sets - Following encoding can be used as database encoding. + shows the character sets available + for use in the server. + - - Character Set Encodings - Encodings +
+ Server Character Sets - Encoding + Name Description @@ -406,59 +359,59 @@ perl: warning: Falling back to the standard locale ("C"). LATIN1 - ISO 8859-1 ECMA-94 Latin Alphabet No.1 + ISO 8859-1/ECMA 94 (Latin alphabet no.1) LATIN2 - ISO 8859-2 ECMA-94 Latin Alphabet No.2 + ISO 8859-2/ECMA 94 (Latin alphabet no.2) LATIN3 - ISO 8859-3 ECMA-94 Latin Alphabet No.3 + ISO 8859-3/ECMA 94 (Latin alphabet no.3) LATIN4 - ISO 8859-4 ECMA-94 Latin Alphabet No.4 + ISO 8859-4/ECMA 94 (Latin alphabet no.4) LATIN5 - ISO 8859-9 ECMA-128 Latin Alphabet No.5 + ISO 8859-9/ECMA 128 (Latin alphabet no.5) LATIN6 - ISO 8859-10 ECMA-144 Latin Alphabet No.6 + ISO 8859-10/ECMA 144 (Latin alphabet no.6) LATIN7 - ISO 8859-13 Latin Alphabet No.7 + ISO 8859-13 (Latin alphabet no.7) LATIN8 - ISO 8859-14 Latin Alphabet No.8 + ISO 8859-14 (Latin alphabet no.8) LATIN9 - ISO 8859-15 Latin Alphabet No.9 + ISO 8859-15 (Latin alphabet no.9) LATIN10 - ISO 8859-16 ASRO SR 14111 Latin Alphabet No.10 + ISO 8859-16/ASRO SR 14111 (Latin alphabet no.10) ISO-8859-5 - ECMA-113 Latin/Cyrillic + ISO 8859-5/ECMA 113 (Latin/Cyrillic) ISO-8859-6 - ECMA-114 Latin/Arabic + ISO 8859-6/ECMA 114 (Latin/Arabic) ISO-8859-7 - ECMA-118 Latin/Greek + ISO 8859-7/ECMA 118 (Latin/Greek) ISO-8859-8 - ECMA-121 Latin/Hebrew + ISO 8859-8/ECMA 121 (Latin/Hebrew) KOI8 @@ -474,78 +427,76 @@ perl: warning: Falling back to the standard locale ("C"). WIN1256 - Arabic Windows CP1256 + Windows CP1256 (Arabic) TCVN - Vietnamese TCVN-5712 (Windows CP1258) + TCVN-5712/Windows CP1258 (Vietnamese) WIN874 - Thai Windows CP874 + Windows CP874 (Thai)
- - Before PostgreSQL7.2, LATIN5 mistakenly - meant ISO 8859-5. From 7.2 on, - LATIN5 means ISO 8859-9. If you have a LATIN5 - database created on 7.1 or earlier and want to migrate to 7.2 (or - later), you should be very careful about this change. + Before PostgreSQL 7.2, LATIN5 + mistakenly meant ISO 8859-5. From 7.2 on, LATIN5 + means ISO 8859-9. If you have a LATIN5 database + created on 7.1 or earlier and want to migrate to 7.2 or later, + you should be very careful about this change. - - Not all APIs supports all the encodings listed above. For example, the + Not all APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, and LATIN10. - - Setting the Encoding + Setting the Character Set - initdb defines the default encoding - for a PostgreSQL installation. For example: + initdb defines the default character set + for a PostgreSQL cluster. For example, initdb -E EUC_JP - sets the default encoding to EUC_JP (Extended Unix Code for Japanese). - Note that you can use instead of if you prefer - to type longer option strings. + sets the default character set (encoding) to + EUC_JP (Extended Unix Code for Japanese). You + can use instead of + if you prefer to type longer option strings. If no option is - given, SQL_ASCII is used. + given, SQL_ASCII is used. - You can create a database with a different encoding: + You can create a database with a different character set: createdb -E EUC_KR korean - will create a database named korean with EUC_KR encoding. - Another way to accomplish this is to use a SQL command: + This will create a database named korean that + uses the character set EUC_KR. Another way to + accomplish this is to use this SQL command: -CREATE DATABASE korean WITH ENCODING = 'EUC_KR'; +CREATE DATABASE korean WITH ENCODING 'EUC_KR'; - The encoding for a database is represented as an - encoding column in the - pg_database system catalog. - You can see that by using the option or the - \l command of psql. + The encoding for a database is stored in the system catalog + pg_database. You can see that by using the + option or the \l command + of psql. $ psql -l @@ -567,27 +518,26 @@ $ psql -l - Automatic encoding conversion between server and - client + Automatic Character Set Conversion Between Server and Client - PostgreSQL supports an automatic - encoding conversion between server and client for some - encodings. The conversion info is stored in pg_conversion system - catalog. You can create a new conversion by using CREATE - CONVERSION. PostgreSQL comes with some predefined - conversions. They are listed in PostgreSQL supports automatic + character set conversion between server and client for certain + character sets. The conversion information is stored in the + pg_conversion system catalog. You can create a new + conversion by using the SQL command CREATE + CONVERSION. PostgreSQL comes with some + predefined conversions. They are listed in . - - Client/Server Character Set Encodings - Communication Encodings +
+ Client/Server Character Set Conversions - Server Encoding - Available Client Encodings + Server Character Set + Available Client Character Sets @@ -784,10 +734,10 @@ $ psql -l
- To enable the automatic encoding translation, you have to tell - PostgreSQL the encoding you would like - to use in the client. There are - several ways to accomplish this. + To enable the automatic character set conversion, you have to + tell PostgreSQL the character set + (encoding) you would like to use in the client. There are several + ways to accomplish this: @@ -811,17 +761,17 @@ $ psql -l PQsetClientEncoding() for its purpose. -int PQsetClientEncoding(PGconn *conn, const char *encoding) +int PQsetClientEncoding(PGconn *conn, const char *encoding); where conn is a connection to the server, - and encoding is an encoding you - want to use. If it successfully sets the encoding, it returns 0, - otherwise -1. The current encoding for this connection can be shown by + and encoding is the encoding you + want to use. If the function successfully sets the encoding, it returns 0, + otherwise -1. The current encoding for this connection can be determined by using: -int PQclientEncoding(const PGconn *conn) +int PQclientEncoding(const PGconn *conn); Note that it returns the encoding ID, not a symbolic string @@ -829,7 +779,7 @@ int PQclientEncoding(const PGconn *conn) can use: -char *pg_encoding_to_char(int encoding_id) +char *pg_encoding_to_char(int encoding_id); @@ -841,13 +791,13 @@ char *pg_encoding_to_char(int encoding_id) Setting the client encoding can be done with this SQL command: -SET CLIENT_ENCODING TO 'encoding'; +SET CLIENT_ENCODING TO 'value'; - Also you can use the SQL92 syntax SET NAMES for this purpose: + Also you can use the more standard SQL syntax SET NAMES for this purpose: -SET NAMES 'encoding'; +SET NAMES 'value'; To query the current client encoding: @@ -877,7 +827,7 @@ RESET CLIENT_ENCODING; - Using client_encoding variable. + Using the configuration variable client_encoding. If the client_encoding variable in postgresql.conf is set, that client encoding is automatically selected when a connection to the @@ -888,26 +838,19 @@ RESET CLIENT_ENCODING; - - - - What happens if the translation is not possible? - Suppose you choose EUC_JP for the server - and LATIN1 for the client, - then some Japanese characters cannot be translated into LATIN1. In - this case, a letter that cannot be represented in the LATIN1 character set - would be transformed as: - - -(HEXA DECIMAL) - + If the conversion of a particular character is not possible -- + suppose you chose EUC_JP for the server and + LATIN1 for the client, then some Japanese + characters cannot be converted to LATIN1 -- it + is transformed to its hexadecimal byte values in parentheses, + e.g., (826C). - References + Further Reading These are good sources to start learning about various kinds of encoding @@ -949,209 +892,11 @@ RESET CLIENT_ENCODING; - - History - - -Dec 7, 2000 - * An automatic encoding translation between Unicode and other - encodings are implemented - * Changes above will appear in 7.1 - -May 20, 2000 - * SJIS UDC (NEC selection IBM kanji) support contributed - by Eiji Tokuya - * Changes above will appear in 7.0.1 - -Mar 22, 2000 - * Add new libpq functions PQsetClientEncoding, PQclientEncoding - * ./configure --with-mb=EUC_JP - now deprecated. use - ./configure --enable-multibyte=EUC_JP - instead - * Add SQL_ASCII regression test case - * Add SJIS User Defined Character (UDC) support - * All of above will appear in 7.0 - -July 11, 1999 - * Add support for WIN1250 (Windows Czech) as a client encoding - (contributed by Pavel Behal) - * fix some compiler warnings (contributed by Tomoaki Nishiyama) - -Mar 23, 1999 - * Add support for KOI8(KOI8-R), WIN(CP1251), ALT(CP866) - (thanks Oleg Broytmann for testing) - * Fix problem with MB and locale - -Jan 26, 1999 - * Add support for Big5 for frontend encoding - (you need to create a database with EUC_TW to use Big5) - * Add regression test case for EUC_TW - (contributed by Jonah Kuo jonahkuo@mail.ttn.com.tw) - -Dec 15, 1998 - * Bugs related to SQL_ASCII support fixed - -Nov 5, 1998 - * 6.4 release. In this version, pg_database has "encoding" - column that represents the database encoding - -Jul 22, 1998 - * determine encoding at initdb/createdb rather than compile time - * support for PGCLIENTENCODING when issuing COPY command - * support for SQL92 syntax "SET NAMES" - * support for LATIN2-5 - * add UNICODE regression test case - * new test suite for MB - * clean up source files - -Jun 5, 1998 - * add support for the encoding translation between the backend - and the frontend - * new command SET CLIENT_ENCODING etc. added - * add support for LATIN1 character set - * enhance 8-bit cleanliness - -April 21, 1998 some enhancements/fixes - * character_length(), position(), substring() are now aware of - multi-byte characters - * add octet_length() - * add --with-mb option to configure - * new regression tests for EUC_KR - (contributed by Soonmyung Hong) - * add some test cases to the EUC_JP regression test - * fix problem in regress/regress.sh in case of System V - * fix toupper(), tolower() to handle 8bit chars - -Mar 25, 1998 MB PL2 is incorporated into PostgreSQL 6.3.1 - -Mar 10, 1998 PL2 released - * add regression test for EUC_JP, EUC_CN and MULE_INTERNAL - * add an English document (this file) - * fix problems concerning 8-bit single byte characters - -Mar 1, 1998 PL1 released - - - - - WIN1250 on Windows/ODBC - - - - - The WIN1250 character set on Windows client platforms can be used - with PostgreSQL with locale support - enabled. - - - - The following should be kept in mind: - - - - - Success depends on proper system locales. This has been tested - with Red Hat 6.0 and Slackware 3.6, with the - cs_CZ.iso8859-2 locale. - - - - - - Never try to set the server's database encoding to WIN1250. - Always use LATIN2 instead since there is no WIN1250 locale - in Unix. - - - - - - The WIN1250 encoding is usable only for Windows ODBC clients. The - characters are recoded on the fly, to be displayed and stored - back properly. - - - - - - - WIN1250 on Windows/ODBC - - - Compile PostgreSQL with locale enabled - and the server-side encoding set to LATIN2. - - - - - - Set up your installation. Do not forget to create locale - variables in your environment. For example (this may - not be correct for your environment): - - -LC_ALL=cs_CZ.ISO8859-2 - - - - - - - You have to start the server with locales set! - - - - - - Try it with the Czech language, and have it sort on a query. - - - - - - Install ODBC driver for PostgreSQL on your Windows machine. - - - - - - Set up your data source properly. Include this line in your ODBC - configuration dialog in the field Connect Settings: - - -SET CLIENT_ENCODING = 'WIN1250'; - - - - - - - Now try it again, but in Windows with ODBC. - - - - - Single-byte character set recoding</> -<!-- formerly in README.charsets, by Josef Balatka, <balatka@email.cz> --> + <title>Single-Byte Character Set Recoding</> <para> You can set up this feature with the <option>--enable-recode</> option @@ -1163,57 +908,57 @@ SET CLIENT_ENCODING = 'WIN1250'; <para> This method uses a file <filename>charset.conf</> file located in - the database directory (<envar>PGDATA</>). It's a typical - configuration text file where spaces and newlines separate items - and records and # specifies comments. Three keywords with the + the data directory for configuration. It's a typical + configuration text file where spaces and newlines separate fields + and records and <literal>#</> starts a comment. Three key words with the following syntax are recognized here: <synopsis> -BaseCharset <replaceable>server_charset</> -RecodeTable <replaceable>from_charset</> <replaceable>to_charset</> <replaceable>file_name</> -HostCharset <replaceable>host_spec</> <replaceable>host_charset</> +BaseCharset <replaceable>server_charset</> +RecodeTable <replaceable>from_charset</> <replaceable>to_charset</> <replaceable>file_name</> +HostCharset <replaceable>host_spec</> <replaceable>host_charset</> </synopsis> </para> <para> - <token>BaseCharset</> defines the encoding of the database server. + <token>BaseCharset</> defines the character set of the database server. All character set names are only used for mapping inside of <filename>charset.conf</> so you can freely use typing-friendly names. </para> <para> - <token>RecodeTable</> records specify translation tables between + <token>RecodeTable</> records specify conversion tables between server and client. The file name is relative to the - <envar>PGDATA</> directory. The table file format is very - simple. There are no keywords and characters are represented by a - pair of decimal or hexadecimal (0x prefixed) values on single + data directory. The table file format is very + simple. There are no key words, and character mappings are represented by a + pair of decimal or hexadecimal (prefixed by <literal>0x</>) values on single lines: <synopsis> -<replaceable>char_value</> <replaceable>translated_char_value</> +<replaceable>char_value</> <replaceable>converted_char_value</> </synopsis> + In the <filename>src/data/</> directory in the source distribution you can find an + example <filename>charset.conf</> and a few recoding tables. </para> <para> <token>HostCharset</> records define the client character set by IP address. You can use a single IP address, an IP mask range starting - from the given address or an IP interval (e.g., 127.0.0.1, - 192.168.1.100/24, 192.168.1.20-192.168.1.40). + from the given address or an IP interval (e.g., <literal>127.0.0.1</>, + <literal>192.168.1.100/24</>, <literal>192.168.1.20-192.168.1.40</>). </para> <para> - The <filename>charset.conf</> file is always processed up to the - end, so you can easily specify exceptions from the previous - rules. In the <filename>src/data/</> directory you will find an - example <filename>charset.conf</> and a few recoding tables. + The <filename>charset.conf</> file is always processed to the + end, so you can easily specify exceptions from preceding rules. </para> <para> - As this solution is based on the client's IP address and character - set mapping there are obviously some restrictions as well. You - cannot use different encodings on the same host at the same - time. It is also inconvenient when you boot your client hosts into - multiple operating systems. Nevertheless, when these restrictions are - not limiting and you do not need multibyte characters then it is a + As this solution is based on the client's IP address there are + obviously some restrictions as well. You cannot use different + character sets on the same host at the same time. It is also + inconvenient when you boot your client hosts into multiple + operating systems. Nevertheless, when these restrictions are not + limiting and you do not need multibyte characters then it is a simple and effective solution. </para> </sect1> diff --git a/doc/src/sgml/diskusage.sgml b/doc/src/sgml/diskusage.sgml index 0af7b4b3929..0660826d5d9 100644 --- a/doc/src/sgml/diskusage.sgml +++ b/doc/src/sgml/diskusage.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/diskusage.sgml,v 1.8 2002/11/15 03:11:16 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/diskusage.sgml,v 1.9 2003/03/24 14:32:50 petere Exp $ --> <chapter id="diskusage"> @@ -33,32 +33,32 @@ $Header: /cvsroot/pgsql/doc/src/sgml/diskusage.sgml,v 1.8 2002/11/15 03:11:16 mo <para> You can monitor disk space from three places: from <application>psql</> using <command>VACUUM</> information, from - <application>psql</> using <filename>contrib/dbsize</>, and from - the command line using <application>contrib/oid2name</>. Using - <application>psql</> on a recently vacuumed (or analyzed) database, + <application>psql</> using the tools in <filename>contrib/dbsize</>, and from + the command line using the tools in <filename>contrib/oid2name</>. Using + <application>psql</> on a recently vacuumed or analyzed database, you can issue queries to see the disk usage of any table: <programlisting> -play=# SELECT relfilenode, relpages -play-# FROM pg_class -play-# WHERE relname = 'customer'; +SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer'; + relfilenode | relpages -------------+---------- 16806 | 60 (1 row) </programlisting> + Each page is typically 8 kilobytes. (Remember, <literal>relpages</> + is only updated by <command>VACUUM</> and <command>ANALYZE</>.) </para> - <para> - Each page is typically 8 kilobytes. (Remember, <literal>relpages</> - is only updated by <command>VACUUM</> and <command>ANALYZE</>.) To - show the space used by <acronym>TOAST</> tables, use a query based on - the heap relfilenode shown above: + <para> + To show the space used by <acronym>TOAST</> tables, use a query + like the following, substituting the <literal>relfilenode</literal> + number of the heap (determined by the query above): <programlisting> -play=# SELECT relname, relpages -play-# FROM pg_class -play-# WHERE relname = 'pg_toast_16806' OR -play-# relname = 'pg_toast_16806_index' -play-# ORDER BY relname; +SELECT relname, relpages + FROM pg_class + WHERE relname = 'pg_toast_16806' OR relname = 'pg_toast_16806_index' + ORDER BY relname; + relname | relpages ----------------------+---------- pg_toast_16806 | 0 @@ -67,14 +67,15 @@ play-# ORDER BY relname; </para> <para> - You can easily display index usage too: + You can easily display index sizes, too: <programlisting> -play=# SELECT c2.relname, c2.relpages -play-# FROM pg_class c, pg_class c2, pg_index i -play-# WHERE c.relname = 'customer' AND -play-# c.oid = i.indrelid AND -play-# c2.oid = i.indexrelid -play-# ORDER BY c2.relname; +SELECT c2.relname, c2.relpages + FROM pg_class c, pg_class c2, pg_index i + WHERE c.relname = 'customer' + AND c.oid = i.indrelid + AND c2.oid = i.indexrelid + ORDER BY c2.relname; + relname | relpages ----------------------+---------- customer_id_indexdex | 26 @@ -82,11 +83,11 @@ play-# ORDER BY c2.relname; </para> <para> - It is easy to find your largest files using <application>psql</>: + It is easy to find your largest tables and indexes using this + information: <programlisting> -play=# SELECT relname, relpages -play-# FROM pg_class -play-# ORDER BY relpages DESC; +SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; + relname | relpages ----------------------+---------- bigtable | 3290 @@ -97,12 +98,12 @@ play-# ORDER BY relpages DESC; <para> <filename>contrib/dbsize</> loads functions into your database that allow you to find the size of a table or database from inside - <application>psql</> without the need for <command>VACUUM/ANALYZE.</> + <application>psql</> without the need for <command>VACUUM</> or <command>ANALYZE</>. </para> <para> You can also use <filename>contrib/oid2name</> to show disk usage. See - <filename>README.oid2name</> for examples. It includes a script that + <filename>README.oid2name</> in that directory for examples. It includes a script that shows disk usage for each database. </para> </sect1> @@ -114,7 +115,7 @@ play-# ORDER BY relpages DESC; The most important disk monitoring task of a database administrator is to make sure the disk doesn't grow full. A filled data disk may result in subsequent corruption of database indexes, but not of the - fundamental data tables. If the WAL files are on the same disk (as + tables themselves. If the WAL files are on the same disk (as is the case for a default configuration) then a filled disk during database initialization may result in corrupted or incomplete WAL files. This failure condition is detected and the database server @@ -129,8 +130,8 @@ play-# ORDER BY relpages DESC; information of such a setup; a restore would put everything back in one place. To avoid running out of disk space, you can place the WAL files or individual databases in other locations while creating - them. See the <application>initdb</> documentation and <xref - linkend="manage-ag-alternate-locs"> for more information. + them. See the <command>initdb</> documentation and <xref + linkend="manage-ag-alternate-locs"> for more information about that. </para> <tip> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 4e7631bd228..926a82a353e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.146 2003/03/21 21:54:29 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.147 2003/03/24 14:32:50 petere Exp $ PostgreSQL documentation --> @@ -5900,7 +5900,7 @@ SELECT TIMESTAMP 'now'; <entry><literal>255.255.255.0</literal></entry> </row> <row> - <entry><function>hostmask</function>(<type>inet</type>)</entry> + <entry><literal><function>hostmask</function>(<type>inet</type>)</literal></entry> <entry><type>inet</type></entry> <entry>construct hostmask for network</entry> <entry><literal>hostmask('192.168.23.20/30')</literal></entry> @@ -6477,7 +6477,7 @@ SET search_path TO <replaceable>schema</> <optional>, <replaceable>schema</>, .. <indexterm zone="functions-misc"> <primary>configuration</primary> - <secondary>run time</secondary> + <secondary>server</secondary> </indexterm> <para> diff --git a/doc/src/sgml/install-win32.sgml b/doc/src/sgml/install-win32.sgml index eb44c949ed3..96f85d315dd 100644 --- a/doc/src/sgml/install-win32.sgml +++ b/doc/src/sgml/install-win32.sgml @@ -54,7 +54,7 @@ <term><filename>interfaces\libpq\Release\libpqdll.lib</filename></term> <listitem> <para> - Import library to link your program to <filename>libpq.dll</filename> + Import library to link your programs to <filename>libpq.dll</filename> </para> </listitem> </varlistentry> @@ -98,7 +98,7 @@ </para> <para> - To use the libraries, you must add the + To use the library, you must add the <filename>libpqdll.lib</filename> file to your project. (In Visual C++, just right-click on the project and choose to add it.) </para> diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index daee3b11891..a2fb9f35996 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1,17 +1,14 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.21 2003/03/24 14:32:50 petere Exp $ --> <chapter id="maintenance"> <title>Routine Database Maintenance Tasks - - General Discussion - There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL - installation running smoothly. The tasks discussed here are repetitive + server running smoothly. The tasks discussed here are repetitive in nature and can easily be automated using standard Unix tools such as cron scripts. But it is the database administrator's responsibility to set up appropriate scripts, and to @@ -22,7 +19,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 One obvious maintenance task is creation of backup copies of the data on a regular schedule. Without a recent backup, you have no chance of recovery after a catastrophe (disk failure, fire, mistakenly dropping a critical - table, etc). The backup and recovery mechanisms available in + table, etc.). The backup and recovery mechanisms available in PostgreSQL are discussed at length in . @@ -45,8 +42,6 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 experience with the system. - - Routine Vacuuming @@ -75,8 +70,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 - The frequency and scope of VACUUMs performed for each of - these reasons will vary depending on the needs of each installation. + The frequency and scope of the VACUUM operations performed for each of + these reasons will vary depending on the needs of each site. Therefore, database administrators must understand these issues and develop an appropriate maintenance strategy. This section concentrates on explaining the high-level issues; for details about command syntax @@ -86,7 +81,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 Beginning in PostgreSQL 7.2, the standard form of VACUUM can run in parallel with normal database operations - (selects, inserts, updates, deletes, but not changes to table schemas). + (selects, inserts, updates, deletes, but not changes to table definitions). Routine vacuuming is therefore not nearly as intrusive as it was in prior releases, and it's not as critical to try to schedule it at low-usage times of day. @@ -131,8 +126,8 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 return disk space to the operating system. If you need to return disk space to the operating system you can use VACUUM FULL --- but what's the point of releasing disk space that will only have to be - allocated again soon? Moderately frequent standard VACUUMs - are a better approach than infrequent VACUUM FULLs for + allocated again soon? Moderately frequent standard VACUUM runs + are a better approach than infrequent VACUUM FULL runs for maintaining heavily-updated tables. @@ -140,7 +135,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 Recommended practice for most sites is to schedule a database-wide VACUUM once a day at a low-usage time of day, supplemented by more frequent vacuuming of heavily-updated tables if necessary. - (If you have multiple databases in an installation, don't forget to + (If you have multiple databases in a cluster, don't forget to vacuum each one; the vacuumdb script may be helpful.) Use plain VACUUM, not VACUUM FULL, for routine vacuuming for space recovery. @@ -233,11 +228,11 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 PostgreSQL's MVCC transaction semantics - depend on being able to compare transaction ID (XID) - numbers: a tuple with an insertion XID newer than the current + depend on being able to compare transaction ID (XID) + numbers: a tuple with an insertion XID greater than the current transaction's XID is in the future and should not be visible to the current transaction. But since transaction IDs have limited size - (32 bits at this writing) an installation that runs for a long time (more + (32 bits at this writing) a cluster that runs for a long time (more than 4 billion transactions) will suffer transaction ID wraparound: the XID counter wraps around to zero, and all of a sudden transactions that were in the past appear to be in the future --- which @@ -251,7 +246,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 against XID wraparound was to re-initdb at least every 4 billion transactions. This of course was not very satisfactory for high-traffic sites, so a better solution has been devised. The new - approach allows an installation to remain up indefinitely, without + approach allows a server to remain up indefinitely, without initdb or any sort of restart. The price is this maintenance requirement: every table in the database must be vacuumed at least once every billion transactions. @@ -293,9 +288,9 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 VACUUM's normal policy is to reassign FrozenXID to any tuple with a normal XID more than one billion transactions in the past. This policy preserves the original insertion XID until it is not - likely to be of interest anymore (in fact, most tuples will probably - live and die without ever being frozen). With this policy, - the maximum safe interval between VACUUMs of any table + likely to be of interest anymore. (In fact, most tuples will probably + live and die without ever being frozen.) With this policy, + the maximum safe interval between VACUUM runs on any table is exactly one billion transactions: if you wait longer, it's possible that a tuple that was not quite old enough to be reassigned last time is now more than two billion transactions old and has wrapped around @@ -304,13 +299,13 @@ $Header: /cvsroot/pgsql/doc/src/sgml/maintenance.sgml,v 1.20 2002/11/11 20:14:03 - Since periodic VACUUMs are needed anyway for the reasons + Since periodic VACUUM runs are needed anyway for the reasons described earlier, it's unlikely that any table would not be vacuumed for as long as a billion transactions. But to help administrators ensure this constraint is met, VACUUM stores transaction ID - statistics in the system table pg_database. In particular, - the datfrozenxid field of a database's - pg_database row is updated at the completion of any + statistics in the system table pg_database. In particular, + the datfrozenxid column of a database's + pg_database row is updated at the completion of any database-wide vacuum operation (i.e., VACUUM that does not name a specific table). The value stored in this field is the freeze cutoff XID that was used by that VACUUM command. All normal @@ -334,11 +329,11 @@ SELECT datname, age(datfrozenxid) FROM pg_database; database at least once every half-a-billion (500 million) transactions, so as to provide plenty of safety margin. To help meet this rule, each database-wide VACUUM automatically delivers a warning - if there are any pg_database entries showing an + if there are any pg_database entries showing an age of more than 1.5 billion transactions, for example: -play=# vacuum; +play=# VACUUM; WARNING: Some databases have not been vacuumed in 1613770184 transactions. Better vacuum them within 533713463 transactions, or you may have a wraparound failure. @@ -355,13 +350,13 @@ VACUUM database will be frozen. Hence, as long as the database is not modified in any way, it will not need subsequent vacuuming to avoid transaction ID wraparound problems. This technique is used by - initdb to prepare the template0 database. + initdb to prepare the template0 database. It should also be used to prepare any user-created databases that are to be marked datallowconn = false in - pg_database, since there isn't any convenient way to + pg_database, since there isn't any convenient way to vacuum a database that you can't connect to. Note that VACUUM's automatic warning message about - unvacuumed databases will ignore pg_database entries + unvacuumed databases will ignore pg_database entries with datallowconn = false, so as to avoid giving false warnings about these databases; therefore it's up to you to ensure that such databases are frozen correctly. @@ -415,9 +410,9 @@ VACUUM - If you simply direct the postmaster's stderr into a + If you simply direct the stderr of the postmaster into a file, the only way to truncate the log file is to stop and restart - the postmaster. This may be OK for development setups but you won't + the postmaster. This may be OK for development setups but you won't want to run a production server that way. @@ -425,7 +420,7 @@ VACUUM The simplest production-grade approach to managing log output is to send it all to syslog and let syslog deal with file rotation. To do this, set - syslog to 2 (log to syslog only) in + the configurations parameter syslog to 2 (to log to syslog only) in postgresql.conf. Then you can send a SIGHUP signal to the syslog daemon whenever you want to force it to start writing a new log @@ -436,18 +431,18 @@ VACUUM On many systems, however, syslog is not very reliable, particularly with large log messages; it may truncate or drop messages just when you need them the most. You may find it more useful to pipe the - postmaster's stderr to some type of - log rotation script. If you start the postmaster with - pg_ctl, then the postmaster's stderr + stderr of the postmaster to some type of + log rotation program. If you start the server with + pg_ctl, then the stderr of the postmaster is already redirected to stdout, so you just need a pipe command: - -pg_ctl start | logrotate - + +pg_ctl start | logrotate + The PostgreSQL distribution doesn't include a suitable - log rotation program, but there are many available on the net; + log rotation program, but there are many available on the Internet; one is included in the Apache distribution, for example. diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 48bbe9561bb..112cc5c35b4 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,5 +1,5 @@ @@ -14,7 +14,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.17 2003/03/20 18:51:16 Several tools are available for monitoring database activity and analyzing performance. Most of this chapter is devoted to describing - PostgreSQL's statistics collector, + PostgreSQL's statistics collector, but one should not neglect regular Unix monitoring programs such as ps and top. Also, once one has identified a poorly-performing query, further investigation may be needed using @@ -50,7 +50,7 @@ postgres 1016 0.1 2.4 6532 3080 pts/1 SN 13:19 0:00 postgres: tgl reg (The appropriate invocation of ps varies across different platforms, as do the details of what is shown. This example is from a recent Linux system.) The first process listed here is the - postmaster, the master server process. The command arguments + postmaster, the master server process. The command arguments shown for it are the same ones given when it was launched. The next two processes implement the statistics collector, which will be described in detail in the next section. (These will not be present if you have set @@ -67,7 +67,7 @@ postgres: user database host idle (i.e., waiting for a client command), idle in transaction (waiting for client inside a BEGIN block), or a command type name such as SELECT. Also, - waiting is attached if the server is presently waiting + waiting is attached if the server process is presently waiting on a lock held by another server process. In the above example we can infer that process 1003 is waiting for process 1016 to complete its transaction and thereby release some lock or other. @@ -77,22 +77,19 @@ postgres: user database host Solaris requires special handling. You must use /usr/ucb/ps, rather than - /bin/ps. You also must use two w + /bin/ps. You also must use two flags, not just one. In addition, your original invocation of the - postmaster must have a shorter + postmaster command must have a shorter ps status display than that provided by each - backend. If you fail to do all three things, the ps - output for each backend will be the original postmaster + server process. If you fail to do all three things, the ps + output for each server process will be the original postmaster command line. - - - - Statistics Collector + The Statistics Collector statistics @@ -103,7 +100,7 @@ postgres: user database host @@ -113,13 +110,13 @@ postgres: user database host Since collection of statistics adds some overhead to query execution, the system can be configured to collect or not collect information. - This is controlled by configuration variables that are normally set in - postgresql.conf (see for - details about setting configuration variables). + This is controlled by configuration parameters that are normally set in + postgresql.conf. (See for + details about setting configuration parameters.) - The variable STATS_START_COLLECTOR must be set to + The parameter stats_start_collector must be set to true for the statistics collector to be launched at all. This is the default and recommended setting, but it may be turned off if you have no interest in statistics and @@ -129,32 +126,32 @@ postgres: user database host - The variables STATS_COMMAND_STRING, - STATS_BLOCK_LEVEL, - and STATS_ROW_LEVEL control how much information is - actually sent to the collector, and thus determine how much run-time + The parameters stats_command_string, + stats_block_level, + and stats_row_level control how much information is + actually sent to the collector and thus determine how much run-time overhead occurs. These respectively determine whether a server process sends its current command string, disk-block-level access statistics, and - row-level access statistics to the collector. Normally these variables are + row-level access statistics to the collector. Normally these parameters are set in postgresql.conf so that they apply to all server - processes, but it is possible to turn them on or off in individual server - processes using the SET command. (To prevent ordinary users + processes, but it is possible to turn them on or off in individual sessions + using the SET command. (To prevent ordinary users from hiding their activity from the administrator, only superusers are - allowed to change these variables with SET.) + allowed to change these parameters with SET.) - + - Since the variables STATS_COMMAND_STRING, - STATS_BLOCK_LEVEL, and - STATS_ROW_LEVEL default to false, + Since the parameters stats_command_string, + stats_block_level, and + stats_row_level default to false, very few statistics are collected in the default configuration. Enabling one or more of these configuration variables will significantly enhance the amount of useful data produced by the statistics collector, at the expense of additional run-time overhead. - + @@ -181,7 +178,7 @@ postgres: user database host Another important point is that when a server process is asked to display any of these statistics, it first fetches the most recent totals emitted by - the collector process. It then continues to use this snapshot for all + the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction. So the statistics will appear not to change as long as you continue the current transaction. @@ -209,9 +206,9 @@ postgres: user database host One row per server process, showing process ID, database, user, current query, and the time at which the current query began execution. The columns that report - data on the current query are only available if the - STATS_COMMAND_STRING configuration option has - been enabled. Furthermore, these columns can only be accessed by + data on the current query are only available if the parameter + stats_command_string has been turned on. + Furthermore, these columns can only be accessed by superusers; or when the user examining the view is the same as the user in the row; for others it reads as null. (Note that because of the collector's reporting delay, current query will only be up-to-date for @@ -220,7 +217,7 @@ postgres: user database host pg_stat_database - One row per database, showing number of active backends, + One row per database, showing the number of active backend server processes, total transactions committed and total rolled back in that database, total disk blocks read, and total number of buffer hits (i.e., block read requests avoided by finding the block already in buffer cache). @@ -232,7 +229,7 @@ postgres: user database host For each table in the current database, total numbers of sequential and index scans, total numbers of tuples returned by each type of scan, and totals of tuple insertions, updates, - and deletes. + and deletions. @@ -360,12 +357,12 @@ postgres: user database host . The per-database access - functions accept a database OID to identify which database to - report on. The per-table and per-index functions accept a table or - index OID (note that only tables and indexes in the current - database can be seen with these functions). The per-backend access - functions accept a backend ID number, which ranges from one to the - number of currently active backends. + functions take a database OID as argument to identify which database to + report on. The per-table and per-index functions take a table or + index OID. (Note that only tables and indexes in the current + database can be seen with these functions.) The per-backend access + functions take a backend ID number, which ranges from one to the + number of currently active backend processes. @@ -382,15 +379,15 @@ postgres: user database host - pg_stat_get_db_numbackends(oid) + pg_stat_get_db_numbackends(oid) integer - Number of active backends in database + Number of active backend processes for database - pg_stat_get_db_xact_commit(oid) + pg_stat_get_db_xact_commit(oid) bigint Transactions committed in database @@ -398,7 +395,7 @@ postgres: user database host - pg_stat_get_db_xact_rollback(oid) + pg_stat_get_db_xact_rollback(oid) bigint Transactions rolled back in database @@ -406,7 +403,7 @@ postgres: user database host - pg_stat_get_db_blocks_fetched(oid) + pg_stat_get_db_blocks_fetched(oid) bigint Number of disk block fetch requests for database @@ -414,7 +411,7 @@ postgres: user database host - pg_stat_get_db_blocks_hit(oid) + pg_stat_get_db_blocks_hit(oid) bigint Number of disk block fetch requests found in cache for database @@ -422,7 +419,7 @@ postgres: user database host - pg_stat_get_numscans(oid) + pg_stat_get_numscans(oid) bigint Number of sequential scans done when argument is a table, @@ -431,7 +428,7 @@ postgres: user database host - pg_stat_get_tuples_returned(oid) + pg_stat_get_tuples_returned(oid) bigint Number of tuples read by sequential scans when argument is a table, @@ -440,7 +437,7 @@ postgres: user database host - pg_stat_get_tuples_fetched(oid) + pg_stat_get_tuples_fetched(oid) bigint Number of valid (unexpired) table tuples fetched by sequential scans @@ -450,7 +447,7 @@ postgres: user database host - pg_stat_get_tuples_inserted(oid) + pg_stat_get_tuples_inserted(oid) bigint Number of tuples inserted into table @@ -458,7 +455,7 @@ postgres: user database host - pg_stat_get_tuples_updated(oid) + pg_stat_get_tuples_updated(oid) bigint Number of tuples updated in table @@ -466,7 +463,7 @@ postgres: user database host - pg_stat_get_tuples_deleted(oid) + pg_stat_get_tuples_deleted(oid) bigint Number of tuples deleted from table @@ -474,7 +471,7 @@ postgres: user database host - pg_stat_get_blocks_fetched(oid) + pg_stat_get_blocks_fetched(oid) bigint Number of disk block fetch requests for table or index @@ -482,7 +479,7 @@ postgres: user database host - pg_stat_get_blocks_hit(oid) + pg_stat_get_blocks_hit(oid) bigint Number of disk block requests found in cache for table or index @@ -490,69 +487,71 @@ postgres: user database host - pg_stat_get_backend_idset() + pg_stat_get_backend_idset() set of integer - Set of currently active backend IDs (from 1 to N where N is the - number of active backends). See usage example below + Set of currently active backend process IDs (from 1 to the + number of active backend processes). See usage example in the text. - pg_backend_pid() + pg_backend_pid() integer - Process ID of the attached backend + Process ID of the backend process attached to the current session - pg_stat_get_backend_pid(integer) + pg_stat_get_backend_pid(integer) integer - Process ID of all backend processes + Process ID of the given backend process - pg_stat_get_backend_dbid(integer) + pg_stat_get_backend_dbid(integer) oid - Database ID of backend process + Database ID of the given backend process - pg_stat_get_backend_userid(integer) + pg_stat_get_backend_userid(integer) oid - User ID of backend process + User ID of the given backend process - pg_stat_get_backend_activity(integer) + pg_stat_get_backend_activity(integer) text - Current query of backend process (NULL if caller is not - superuser, or is the same user as that of the backend being queried, - or STATS_COMMAND_STRING is not enabled) + Active command of the given backend process (null if the + current user is not a superuser nor the same user as that of + the session being queried, or + stats_command_string is not on) - pg_stat_get_backend_activity_start(integer) + pg_stat_get_backend_activity_start(integer) text - The time at which the specified backend's currently executing query was - initiated (NULL if caller is not superuser, or - STATS_COMMAND_STRING is not enabled) + The time at which the specified backend process' currently + executing query was started (null if the current user is not a + superuser, or stats_command_string is not + on) - pg_stat_reset() + pg_stat_reset() boolean Reset all currently collected statistics @@ -564,8 +563,8 @@ postgres: user database host - blocks_fetched minus - blocks_hit gives the number of kernel + pg_stat_get_db_blocks_fetched minus + pg_stat_get_db_blocks_hit gives the number of kernel read() calls issued for the table, index, or database; but the actual number of physical reads is usually lower due to kernel-level buffering. @@ -574,13 +573,13 @@ postgres: user database host The function pg_stat_get_backend_idset provides - a convenient way to generate one row for each active backend. For - example, to show the PIDs and current queries of all backends: + a convenient way to generate one row for each active backend process. For + example, to show the PIDs and current queries of all backend processes: -SELECT pg_stat_get_backend_pid(S.backendid) AS procpid, - pg_stat_get_backend_activity(S.backendid) AS current_query -FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; +SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, + pg_stat_get_backend_activity(s.backendid) AS current_query + FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; @@ -592,7 +591,7 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; Another useful tool for monitoring database activity is the - pg_locks system catalog. This allows the + pg_locks system table. It allows the database administrator to view information about the outstanding locks in the lock manager. For example, this capability can be used to: @@ -609,7 +608,7 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; - View the relation in the current database with the most + Determine the relation in the current database with the most ungranted locks (which might be a source of contention among database clients). @@ -636,7 +635,7 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; view produces a consistent set of results, while not blocking normal lock manager operations longer than necessary. Nonetheless there could be some impact on database performance if this view is - examined often. + read often. @@ -646,7 +645,7 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; pg_locks view contains one row per lockable object and requested lock mode. Thus, the same lockable object may appear many times, if multiple transactions are holding or waiting - for locks on it. A lockable object is either a relation or a + for locks on it. A lockable object is either a relation (e.g., a table) or a transaction ID. (Note that this view includes only table-level locks, not row-level ones. If a transaction is waiting for a row-level lock, it will appear in the view as waiting for the @@ -654,13 +653,13 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S;
- Lock Status System View + <literal>pg_locks</literal> Columns Column Name - Type + Data Type Description @@ -671,7 +670,7 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; oid The OID of the locked relation, or null if the lockable object - is a transaction ID. This column can be joined with the + is a transaction ID. This column can be joined with the column oid of the pg_class system catalog to get more information on the locked relation. Note however that this will only work for relations in the current database (those for @@ -687,7 +686,7 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; The OID of the database in which the locked relation exists, or null if the lockable object is a transaction ID. If the lock is on a globally-shared table, this field will be zero. This - column can be joined with the pg_database + column can be joined with the column oid of the pg_database system catalog to get more information on the locked object's database. @@ -712,11 +711,11 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; integer The process ID of the PostgreSQL - backend belonging to the session that has acquired or is + server process belonging to the session that has acquired or is attempting to acquire the lock. If you have enabled the - statistics collector, this column can be joined with the + statistics collector, this column can be joined with the column pg_stat_activity view to get more - information on the backend holding or waiting to hold the + information on the session holding or waiting to hold the lock. @@ -740,8 +739,8 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; False indicates that this session is currently waiting to acquire this lock, which implies that some other session is holding a conflicting lock mode on the same lockable object. - This backend will sleep until the other lock is released (or a - deadlock situation is detected). A single backend can be + The waiting session will sleep until the other lock is released (or a + deadlock situation is detected). A single session can be waiting to acquire at most one lock at a time. diff --git a/doc/src/sgml/ref/clusterdb.sgml b/doc/src/sgml/ref/clusterdb.sgml index 5a7a27fcf63..4200481c1da 100644 --- a/doc/src/sgml/ref/clusterdb.sgml +++ b/doc/src/sgml/ref/clusterdb.sgml @@ -1,5 +1,5 @@ @@ -18,12 +18,12 @@ PostgreSQL documentation clusterdb - connection-options + connection-option --table | -t table dbname clusterdb - connection-options + connection-option --all-a @@ -114,7 +114,7 @@ PostgreSQL documentation - Clusters table only. + Cluster table only. @@ -134,7 +134,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the server - is running. If host begins with a slash, it is used + is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -145,7 +145,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file + Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. @@ -219,7 +219,7 @@ PostgreSQL documentation - Default connection parameters. + Default connection parameters diff --git a/doc/src/sgml/ref/createdb.sgml b/doc/src/sgml/ref/createdb.sgml index 43c7046fa8e..c202c52c12b 100644 --- a/doc/src/sgml/ref/createdb.sgml +++ b/doc/src/sgml/ref/createdb.sgml @@ -1,5 +1,5 @@ @@ -18,7 +18,7 @@ PostgreSQL documentation createdb - options + option dbname description @@ -47,7 +47,7 @@ PostgreSQL documentation endterm="SQL-CREATEDATABASE-title">. Thus, there is nothing special about creating databases via this or other methods. This means that a database server must be running at the targeted - port. Also, any default settings and environment variables used by + host. Also, any default settings and environment variables used by the libpq front-end library will apply. @@ -65,7 +65,7 @@ PostgreSQL documentation Specifies the name of the database to be created. The name must be - unique among all PostgreSQL databases in this installation. + unique among all PostgreSQL databases in this cluster. The default is to create a database with the same name as the current system user. @@ -98,7 +98,7 @@ PostgreSQL documentation - Echo the queries that createdb generates + Echo the commands that createdb generates and sends to the server. @@ -166,7 +166,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the - server is running. If host begins with a slash, it is used + server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -177,7 +177,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or the local Unix domain socket file + Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections. @@ -296,7 +296,7 @@ PostgreSQL documentation To create the database demo using the server on host eden, port 5000, using the LATIN1 encoding scheme with a look at the - underlying query: + underlying command: $ createdb -p 5000 -h eden -E LATIN1 -e demo CREATE DATABASE "demo" WITH ENCODING = 'LATIN1' diff --git a/doc/src/sgml/ref/createlang.sgml b/doc/src/sgml/ref/createlang.sgml index 72f82b74028..be86e7c1c79 100644 --- a/doc/src/sgml/ref/createlang.sgml +++ b/doc/src/sgml/ref/createlang.sgml @@ -1,5 +1,5 @@ @@ -18,12 +18,12 @@ PostgreSQL documentation createlang - connection-options + connection-option langname dbname createlang - connection-options + connection-option --list-l dbname @@ -85,7 +85,7 @@ PostgreSQL documentation - Displays SQL commands as they are executed. + Display SQL commands as they are executed. @@ -95,8 +95,7 @@ PostgreSQL documentation - Shows a list of already installed languages in the target database - (which must be specified). + Show a list of already installed languages in the target database. @@ -127,7 +126,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the server - is running. If host begins with a slash, it is used + is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -138,7 +137,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file + Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. @@ -182,7 +181,7 @@ PostgreSQL documentation - Default connection parameters. + Default connection parameters @@ -215,7 +214,7 @@ PostgreSQL documentation Examples - To install pltcl into the database + To install the language pltcl into the database template1: $ createlang pltcl template1 diff --git a/doc/src/sgml/ref/createuser.sgml b/doc/src/sgml/ref/createuser.sgml index faaaa683046..eeafdb944f4 100644 --- a/doc/src/sgml/ref/createuser.sgml +++ b/doc/src/sgml/ref/createuser.sgml @@ -1,5 +1,5 @@ @@ -18,7 +18,7 @@ PostgreSQL documentation createuser - options + option username @@ -78,7 +78,7 @@ PostgreSQL documentation The new user is allowed to create other users. - (Note: Actually, this makes the new user a superuser. + (Note: Actually, this makes the new user a superuser. The option is poorly named.) @@ -120,7 +120,7 @@ PostgreSQL documentation - Echo the queries that createuser generates + Echo the commands that createuser generates and sends to the server. @@ -138,8 +138,8 @@ PostgreSQL documentation - - + + Allows you to pick a non-default user ID for the new user. This is not @@ -200,7 +200,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the server - is running. If host begins with a slash, it is used + is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -211,7 +211,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file + Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. @@ -311,7 +311,7 @@ PostgreSQL documentation To create the same user joe using the server on host eden, port 5000, avoiding the prompts and - taking a look at the underlying query: + taking a look at the underlying command: $ createuser -p 5000 -h eden -D -A -e joe CREATE USER "joe" NOCREATEDB NOCREATEUSER diff --git a/doc/src/sgml/ref/current_date.sgml b/doc/src/sgml/ref/current_date.sgml deleted file mode 100644 index 5e06c4dd70d..00000000000 --- a/doc/src/sgml/ref/current_date.sgml +++ /dev/null @@ -1,148 +0,0 @@ - - - - - - - CURRENT_DATE - SQL - Functions - - - - CURRENT_DATE - - -Returns the current date - - - - 1998-04-15 - - - CURRENT_DATE - - - - - 1998-04-15 - - - Inputs - - -None. - - - - - 1998-04-15 - - - Outputs - - - - - -date - - - - - Returns "today". - - - - - - - 1998-04-15 - - - Description - - - The niladic CURRENT_DATE function has a data type of - DATE and returns the date at the time that it is run. - - - - - 1998-04-15 - - - Notes - - - Refer to SET DATESTYLE for further information about date format. - - - - - - Usage - - - Insert the date of insertion into a row: - - - INSERT INTO films - VALUES ('TM999','Ben Hur',105,CURRENT_DATE,'Action',NULL); - - - - Display CURRENT-DATE: - - - SELECT CURRENT_DATE AS today; - - today - ------------ - 1998-03-31 - - - - - - - Compatibility - - - - - - - 1998-04-15 - - - SQL92 - - - Full compatibility. - - - - - - diff --git a/doc/src/sgml/ref/current_time.sgml b/doc/src/sgml/ref/current_time.sgml deleted file mode 100644 index 85cd7e7da6f..00000000000 --- a/doc/src/sgml/ref/current_time.sgml +++ /dev/null @@ -1,158 +0,0 @@ - - - - - CURRENT_TIME - SQL - Functions - - - - CURRENT_TIME - - -Returns the current local time - - - - 1998-04-15 - - - CURRENT_TIME - - - - - 1998-04-15 - - - Inputs - - -None. - - - - - - 1998-04-15 - - - Outputs - - - - - - time - - - - Returns "now" - - - - - - - - 1998-04-15 - - - Description - - - The niladic CURRENT_TIME function has a data type of - TIME and returns the local time when it is run. - - - - - 1998-04-15 - - - Notes - - Refer to the SET TIME ZONE statement for a further description - of local time. - - - - - - Usage - - Display CURRENT_TIME: - - - SELECT CURRENT_TIME AS now; - - now - ----------- - 17:41:31+02 - - - - - - - Compatibility - - - - - - - 1998-04-15 - - - SQL92 - - - SQL92 specifies some additional capabilities for CURRENT_TIME: - - - - - - - - - - - - CURRENT_TIME [ (scale) ] - - - - The optional scale for CURRENT_TIME, if specified, is an - unsigned integer representing the number of digits in the - optional seconds fraction of the time value represented - by the function. - - - - - - - - - - diff --git a/doc/src/sgml/ref/current_timestamp.sgml b/doc/src/sgml/ref/current_timestamp.sgml deleted file mode 100644 index 75782dbc72f..00000000000 --- a/doc/src/sgml/ref/current_timestamp.sgml +++ /dev/null @@ -1,166 +0,0 @@ - - - - - CURRENT_TIMESTAMP - SQL - Functions - - - - CURRENT_TIMESTAMP - - - Returns the current date and time - - - - - - 1998-04-15 - - - CURRENT_TIMESTAMP - - - - - 1998-04-15 - - - Inputs - - -None. - - - - - - 1998-04-15 - - - Outputs - - - - - -timestamp - - - - Returns "today" and "now". - - - - - - - - 1998-04-15 - - - Description - - - The niladic CURRENT_TIMESTAMP function has a data type of - TIMESTAMP and returns the date and local time at which it is run. - - - - - 1998-04-15 - - - Notes - - - Refer to the SET TIME ZONE statement for a further description - of local time. - - - - - - - - Usage - - - Display CURRENT_TIMESTAMP: - - - SELECT CURRENT_TIMESTAMP AS date_and_time; - - date_and_time - ---------------------- - 1998-03-31 07:41:21-08 - - - - - - Compatibility - - - - - - - 1998-04-15 - - - SQL92 - - - SQL92 specifies some additional capabilities for CURRENT_TIMESTAMP: - - - - - - - - - - - - CURRENT_TIMESTAMP [ (scale) ] - - - - The optional scale for CURRENT_TIMESTAMP, if specified, is an - unsigned integer representing the number of digits in the - optional seconds fraction of the time value represented - by the function. - - - - - - - - - - - - - diff --git a/doc/src/sgml/ref/current_user.sgml b/doc/src/sgml/ref/current_user.sgml deleted file mode 100644 index 630728da9dd..00000000000 --- a/doc/src/sgml/ref/current_user.sgml +++ /dev/null @@ -1,167 +0,0 @@ - - - - - CURRENT_USER - SQL - Functions - - - - CURRENT_USER - - - Returns the current user name - - - - 1998-04-15 - - - CURRENT_USER - - - - - 1998-04-15 - - - Inputs - - - -None. - - - - - 1998-04-15 - - - Outputs - - - - - - - username - - - -The name of the current user. - - - - - - - - 1998-04-15 - - - Description - - - The niladic CURRENT_USER function returns a string of type "name" - whose value represents a user name identification. - - - - - 1998-04-15 - - - Notes - - - Data type "name" is a non-standard 63-character type for storing - system identifiers. - - - - - - Usage - - - Display CURRENT_USER - - - SELECT CURRENT_USER AS who_am_i; - - who_am_i - ------------ - jose - - - - - - Compatibility - - - - - - - 1998-04-15 - - - SQL92 - - - SQL92 specifies some additional niladic USER functions: - - - - CURRENT_USER / USER - - - USER is a synonym for CURRENT_USER. - - - - - SESSION_USER - - - The SESSION_USER function returns the SQL-session user name. - - - - - SYSTEM_USER - - - The SYSTEM_USER function returns the database's initial default user. - - - - - - A niladic USER function returns a SQL_TEXT character string whose - value represents a user name. - - - - - - diff --git a/doc/src/sgml/ref/dropdb.sgml b/doc/src/sgml/ref/dropdb.sgml index 302d5128259..107718d5aca 100644 --- a/doc/src/sgml/ref/dropdb.sgml +++ b/doc/src/sgml/ref/dropdb.sgml @@ -1,5 +1,5 @@ @@ -18,7 +18,7 @@ PostgreSQL documentation dropdb - options + option dbname @@ -57,9 +57,7 @@ PostgreSQL documentation dbname - Specifies the name of the database to be removed. The database - must be one of the existing PostgreSQL databases - in this installation. + Specifies the name of the database to be removed. @@ -69,7 +67,7 @@ PostgreSQL documentation - Echo the queries that dropdb generates + Echo the commands that dropdb generates and sends to the server. @@ -109,7 +107,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the server - is running. If host begins with a slash, it is used + is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -120,7 +118,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file + Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. @@ -212,7 +210,7 @@ PostgreSQL documentation To destroy the database demo using the server on host eden, port 5000, with verification and a peek - at the underlying query: + at the underlying command: $ dropdb -p 5000 -h eden -i -e demo Database "demo" will be permanently deleted. diff --git a/doc/src/sgml/ref/droplang.sgml b/doc/src/sgml/ref/droplang.sgml index a7163520bbe..6bddf1a5ba5 100644 --- a/doc/src/sgml/ref/droplang.sgml +++ b/doc/src/sgml/ref/droplang.sgml @@ -1,5 +1,5 @@ @@ -18,12 +18,12 @@ PostgreSQL documentation droplang - connection-options + connection-option langname dbname droplang - connection-options + connection-option --list-l dbname @@ -85,7 +85,7 @@ PostgreSQL documentation - Displays SQL commands as they are executed. + Display SQL commands as they are executed. @@ -95,8 +95,7 @@ PostgreSQL documentation - Shows a list of already installed languages in the target database - (which must be specified). + Show a list of already installed languages in the target database. @@ -171,7 +170,7 @@ PostgreSQL documentation - Default connection parameters. + Default connection parameters @@ -204,7 +203,7 @@ PostgreSQL documentation Examples - To remove pltcl: + To remove the language pltcl: $ droplang pltcl dbname diff --git a/doc/src/sgml/ref/dropuser.sgml b/doc/src/sgml/ref/dropuser.sgml index 53e371c439f..67536993e56 100644 --- a/doc/src/sgml/ref/dropuser.sgml +++ b/doc/src/sgml/ref/dropuser.sgml @@ -1,5 +1,5 @@ @@ -18,7 +18,7 @@ PostgreSQL documentation dropuser - options + option username @@ -31,8 +31,8 @@ PostgreSQL documentation dropuser removes an existing PostgreSQL user and the databases which that user owned. - Only users with usesuper set in - the pg_shadow table can destroy + Only superusers (users with usesuper set in + the pg_shadow table) can destroy PostgreSQL users. @@ -61,7 +61,6 @@ PostgreSQL documentation Specifies the name of the PostgreSQL user to be removed. - This name must exist in the PostgreSQL installation. You will be prompted for a name if none is specified on the command line. @@ -72,7 +71,7 @@ PostgreSQL documentation - Echo the queries that dropuser generates + Echo the commands that dropuser generates and sends to the server. @@ -112,7 +111,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the server - is running. If host begins with a slash, it is used + is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -123,7 +122,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file + Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. @@ -219,9 +218,9 @@ PostgreSQL documentation - To remove user joe using the postmaster on host + To remove user joe using the server on host eden, port 5000, with verification and a peek at the underlying - query: + command: $ dropuser -p 5000 -h eden -i -e joe User "joe" and any owned databases will be permanently deleted. diff --git a/doc/src/sgml/ref/ecpg-ref.sgml b/doc/src/sgml/ref/ecpg-ref.sgml index a38d731b944..995fe88aca0 100644 --- a/doc/src/sgml/ref/ecpg-ref.sgml +++ b/doc/src/sgml/ref/ecpg-ref.sgml @@ -1,5 +1,5 @@ @@ -66,7 +66,7 @@ PostgreSQL documentation - Automatically generate C code from SQL code. Currently, this + Automatically generate certain C code from SQL code. Currently, this works for EXEC SQL TYPE. @@ -101,7 +101,7 @@ PostgreSQL documentation - Specifies that ecpg should write all + Specifies that ecpg should write all its output to the given filename. @@ -111,9 +111,9 @@ PostgreSQL documentation - Turn on autocommit of transactions. In this mode, each query is + Turn on autocommit of transactions. In this mode, each SQL command is automatically committed unless it is inside an explicit - transaction block. In the default mode, queries are committed + transaction block. In the default mode, commands are committed only when EXEC SQL COMMIT is issued. @@ -130,7 +130,7 @@ PostgreSQL documentation - + Show a brief summary of the command usage, then exit. @@ -165,7 +165,7 @@ PostgreSQL documentation Programs using C code with embedded SQL have to be linked against the libecpg library, for example using the - flags -L/usr/local/pgsql/lib -lecpg. + linker options -L/usr/local/pgsql/lib -lecpg. diff --git a/doc/src/sgml/ref/initdb.sgml b/doc/src/sgml/ref/initdb.sgml index 7d63df874d9..5009fa1ff9e 100644 --- a/doc/src/sgml/ref/initdb.sgml +++ b/doc/src/sgml/ref/initdb.sgml @@ -1,5 +1,5 @@ @@ -18,7 +18,7 @@ PostgreSQL documentation initdb - options + option --pgdata -D @@ -33,17 +33,17 @@ PostgreSQL documentation initdb creates a new - PostgreSQL database cluster (or database - system). A database cluster is a collection of databases that are - managed by a single server instance. + PostgreSQL database cluster. A database + cluster is a collection of databases that are managed by a single + server instance. - Creating a database system consists of creating the directories in which + Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the template1 - database. When you create a new database, everything in the + database. When you later create a new database, everything in the template1 database is copied. It contains catalog tables filled in for things like the built-in types. @@ -99,7 +99,7 @@ PostgreSQL documentation - This option specifies the directory where the database system + This option specifies the directory where the database cluster should be stored. This is the only information required by initdb, but you can avoid writing it by setting the PGDATA environment variable, which @@ -117,9 +117,7 @@ PostgreSQL documentation Selects the encoding of the template database. This will also be the default encoding of any database you create later, unless you - override it there. To use the encoding feature, you must - have enabled it at build time, at which time you also select the default - for this option. + override it there. The default is SQL_ASCII. @@ -205,7 +203,7 @@ PostgreSQL documentation Specifies where initdb should find - its input files to initialize the database system. This is + its input files to initialize the database cluster. This is normally not necessary. You will be told if you need to specify their location explicitly. @@ -219,7 +217,7 @@ PostgreSQL documentation By default, when initdb determines that an error prevented it from completely creating the database - system, it removes any files it may have created before discovering + cluster, it removes any files it may have created before discovering that it can't finish the job. This option inhibits tidying-up and is thus useful for debugging. @@ -239,7 +237,7 @@ PostgreSQL documentation - Specifies the directory where the database system is to be + Specifies the directory where the database cluster is to be stored; may be overridden using the option. diff --git a/doc/src/sgml/ref/initlocation.sgml b/doc/src/sgml/ref/initlocation.sgml index 01c018a3b46..1b62aeeba05 100644 --- a/doc/src/sgml/ref/initlocation.sgml +++ b/doc/src/sgml/ref/initlocation.sgml @@ -1,5 +1,5 @@ @@ -47,9 +47,9 @@ PostgreSQL documentation $ export PGDATA2=/opt/postgres/data - Stop and start postmaster so it sees the PGDATA2 + Stop and start postmaster so it sees the PGDATA2 environment variable. The system must be configured so the - postmaster sees PGDATA2 every time it starts. Finally: + postmaster sees PGDATA2 every time it starts. Finally: $ initlocation PGDATA2 $ createdb -D PGDATA2 testdb diff --git a/doc/src/sgml/ref/ipcclean.sgml b/doc/src/sgml/ref/ipcclean.sgml index 76eee6f1077..85ca334c178 100644 --- a/doc/src/sgml/ref/ipcclean.sgml +++ b/doc/src/sgml/ref/ipcclean.sgml @@ -1,5 +1,5 @@ @@ -36,12 +36,10 @@ PostgreSQL documentation Only the database administrator should execute this program as it can cause bizarre behavior (i.e., crashes) if run during multiuser - execution. If this command is executed while a - postmaster is running, the shared memory - and semaphores allocated by the - postmaster will be deleted. This will - result in a general failure of the backend servers started by that - postmaster. + execution. If this command is executed while a server is running, + the shared memory and semaphores allocated by that server will be + deleted, which would have rather severe consequences for that + server. @@ -51,14 +49,14 @@ PostgreSQL documentation This script is a hack, but in the many years since it was written, no one has come up with an equally effective and portable solution. - Since the postmaster can now clean up by + Since the postmaster can now clean up by itself, it is unlikely that ipcclean will be improved upon in the future. The script makes assumption about the format of output of the - ipcs + ipcs utility which may not be true across different operating systems. Therefore, it may not work on your particular OS. diff --git a/doc/src/sgml/ref/pg_config-ref.sgml b/doc/src/sgml/ref/pg_config-ref.sgml index 9b26e2f5275..755faa429c5 100644 --- a/doc/src/sgml/ref/pg_config-ref.sgml +++ b/doc/src/sgml/ref/pg_config-ref.sgml @@ -1,4 +1,4 @@ - + @@ -50,7 +50,7 @@ Print the location of user executables. Use this, for example, to find - the psql program. This is normally also the location + the psql program. This is normally also the location where the pg_config program resides. @@ -130,10 +130,10 @@ Notes - The option is new in + The option was new in PostgreSQL 7.2. In prior releases, the server include files were installed in the same location as the client headers, which could - be queried with the . To make your + be queried with the option . To make your package handle both cases, try the newer option first and test the exit status to see whether it succeeded. diff --git a/doc/src/sgml/ref/pg_controldata.sgml b/doc/src/sgml/ref/pg_controldata.sgml index 2fa44e3c10e..314022eb925 100644 --- a/doc/src/sgml/ref/pg_controldata.sgml +++ b/doc/src/sgml/ref/pg_controldata.sgml @@ -1,5 +1,5 @@ @@ -12,7 +12,7 @@ PostgreSQL documentation pg_controldata - display server-wide control information + display control information of a PostgreSQL database cluster @@ -25,16 +25,16 @@ PostgreSQL documentation Description - pg_controldata returns information initialized during - initdb, such as the catalog version and server locale. + pg_controldata prints information initialized during + initdb, such as the catalog version and server locale. It also shows information about write-ahead logging and checkpoint - processing. This information is server-wide, and not specific to any one + processing. This information is cluster-wide, and not specific to any one database. - This utility may only be run by the user who installed the server because - it requires read access to the datadir. + This utility may only be run by the user who initialized the cluster because + it requires read access to the data directory. You can specify the data directory on the command line, or use the environment variable PGDATA. diff --git a/doc/src/sgml/ref/pg_ctl-ref.sgml b/doc/src/sgml/ref/pg_ctl-ref.sgml index f9043ff5025..c74f9d603b5 100644 --- a/doc/src/sgml/ref/pg_ctl-ref.sgml +++ b/doc/src/sgml/ref/pg_ctl-ref.sgml @@ -1,5 +1,5 @@ @@ -69,17 +69,17 @@ PostgreSQL documentation Description pg_ctl is a utility for starting, - stopping, or restarting , the - PostgreSQL backend server, or displaying - the status of a running postmaster. Although the postmaster can be - started manually, pg_ctl encapsulates - tasks such as redirecting log output and properly detaching from the - terminal and process group. It also provides convenient options for + stopping, or restarting the PostgreSQL + backend server (), or displaying the + status of a running server. Although the server can be started + manually, pg_ctl encapsulates tasks such + as redirecting log output and properly detaching from the terminal + and process group. It also provides convenient options for controlled shutdown. - In mode, a new postmaster is launched. The + In mode, a new server is launched. The server is started in the background, and standard input is attached to /dev/null. The standard output and standard error are either appended to a log file (if the @@ -87,42 +87,45 @@ PostgreSQL documentation standard output (not standard error). If no log file is chosen, the standard output of pg_ctl should be redirected to a file or piped to another process, for example a log rotating program, - otherwise the postmaster will write its output to the controlling + otherwise postmaster will write its output to the controlling terminal (from the background) and will not leave the shell's process group. - In mode, the postmaster that is running in + In mode, the server that is running in the specified data directory is shut down. Three different shutdown methods can be selected with the option: Smart mode waits for all the clients to disconnect. This is the default. Fast mode does not wait for clients to disconnect. All active transactions are rolled back and clients are forcibly disconnected, then the - database is shut down. Immediate mode will abort + server is shut down. Immediate mode will abort all server processes without a clean shutdown. This will lead to a recovery run on restart. mode effectively executes a stop followed - by a start. This allows the changing of postmaster command line - options. + by a start. This allows changing the postmaster + command-line options. - mode simply sends the postmaster a SIGHUP signal, - causing it to reread its configuration files - (postgresql.conf, pg_hba.conf, - etc.). This allows changing of configuration-file options that do not - require a complete restart to take effect. + mode simply sends the + postmaster process a SIGHUP + signal, causing it to reread its configuration files + (postgresql.conf, + pg_hba.conf, etc.). This allows changing of + configuration-file options that do not require a complete restart + to take effect. - mode checks whether a postmaster is running. - If it is, the PID and the command line - options that were used to invoke it are displayed. + mode checks whether a server is running in + the specified data directory. If it is, the PID + and the command line options that were used to invoke it are + displayed. @@ -133,7 +136,7 @@ PostgreSQL documentation - -D datadir + Specifies the file system location of the database files. If @@ -144,7 +147,7 @@ PostgreSQL documentation - -l filename + Append the server log output to @@ -156,7 +159,7 @@ PostgreSQL documentation - -m mode + Specifies the shutdown mode. mode @@ -168,35 +171,35 @@ PostgreSQL documentation - -o options + - Specifies options to be passed directly to - postmaster. + Specifies options to be passed directly to the + postmaster command. - The parameters are usually surrounded by single or double + The options are usually surrounded by single or double quotes to ensure that they are passed through as a group. - -p path + Specifies the location of the postmaster - executable. By default the postmaster is taken from the same + executable. By default the postmaster executable is taken from the same directory as pg_ctl, or failing that, the hard-wired installation directory. It is not necessary to use this option unless you are doing something unusual and get errors - that the postmaster was not found. + that the postmaster executable was not found. - -s + Only print errors, no informational messages. @@ -205,7 +208,7 @@ PostgreSQL documentation - -w + Wait for the start or shutdown to complete. Times out after @@ -224,7 +227,7 @@ PostgreSQL documentation - -W + Do not wait for start or shutdown to complete. This is the @@ -276,9 +279,10 @@ PostgreSQL documentation postmaster.pid - The existence of this file in the data directory is used to help - pg_ctl determine if the server is - currently running or not. + + The existence of this file in the data directory is used to help + pg_ctl determine if the server is + currently running or not. @@ -287,11 +291,12 @@ PostgreSQL documentation postmaster.opts.default - If this file exists in the data directory, - pg_ctl (in mode) - will pass the contents of the file as options to the - postmaster, unless overridden - by the option. + + If this file exists in the data directory, + pg_ctl (in + mode) will pass the contents of the file as options to the + postmaster command, unless overridden by the + option. @@ -314,10 +319,10 @@ PostgreSQL documentation postgresql.conf - This file, located in the data directory, is parsed to - find the proper port to send to the - psql when the - is given in mode. + + This file, located in the data directory, is parsed to find the + proper port to use with psql when the + is given in mode. @@ -332,7 +337,7 @@ PostgreSQL documentation Waiting for complete start is not a well-defined operation and may fail if access control is set up so that a local client cannot - connect without manual interaction (e.g. password authentication). + connect without manual interaction (e.g., password authentication). @@ -341,25 +346,25 @@ PostgreSQL documentation Examples - Starting the postmaster + Starting the Server - To start up a postmaster: + To start up a server: $ pg_ctl start - An example of starting the postmaster, - blocking until the postmaster comes up is: + An example of starting the server, blocking until the server has + come up is: $ pg_ctl -w start - For a postmaster using port 5433, and + For a server using port 5433, and running without fsync, use: $ pg_ctl -o "-F -p 5433" start @@ -368,32 +373,32 @@ PostgreSQL documentation - Stopping the postmaster + Stopping the Server $ pg_ctl stop - stops the postmaster. Using the switch allows one + stops the server. Using the switch allows one to control how the backend shuts down. - Restarting the postmaster + Restarting the Server - This is almost equivalent to stopping the - postmaster and starting it again + Restarting the server is almost equivalent to stopping the + server and starting it again except that pg_ctl saves and reuses the command line options that were passed to the previously running instance. To restart - the postmaster in the simplest form: + the server in the simplest form, use: $ pg_ctl restart - To restart postmaster, + To restart server, waiting for it to shut down and to come up: $ pg_ctl -w restart @@ -409,7 +414,7 @@ PostgreSQL documentation - Showing postmaster status + Showing the Server Status Here is a sample status output from diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 85fba6e812e..0c279db427c 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -1,5 +1,5 @@ @@ -21,7 +21,7 @@ PostgreSQL documentation pg_dump - options + option dbname @@ -54,14 +54,6 @@ PostgreSQL documentation architectures. - - pg_dump will save the information necessary to - re-generate all user-defined types, functions, tables, indexes, - aggregates, and operators. In addition, all the data is copied out - in text format so that it can be readily copied in again, as well - as imported into tools for editing. - - When used with one of the archive file formats and combined with pg_restore, pg_dump provides a @@ -73,14 +65,14 @@ PostgreSQL documentation The most flexible output file format is the custom format (). It allows for selection and reordering of all archived items, and is compressed by default. The - tar format () is not + tar format () is not compressed and it is not possible to reorder data when loading, but it is otherwise quite flexible; moreover, it can be manipulated with - other tools such as tar. + other tools such as tar. - While running pg_dump, one should examine the + While running pg_dump, one should examine the output for any warnings (printed on standard error), especially in light of the limitations listed below. @@ -198,7 +190,7 @@ PostgreSQL documentation table (column, ...) VALUES ...). This will make restoration very slow, - but it is necessary if you desire to rearrange column ordering. + but it is necessary if you desire to rearrange the column ordering. @@ -224,7 +216,7 @@ PostgreSQL documentation - p + p Output a plain-text SQL script file (default) @@ -233,10 +225,10 @@ PostgreSQL documentation - t + t - Output a tar archive suitable for input into + Output a tar archive suitable for input into pg_restore. Using this archive format allows reordering and/or exclusion of schema elements at the time the database is restored. It is also possible to limit @@ -246,7 +238,7 @@ PostgreSQL documentation - c + c Output a custom archive suitable for input into @@ -514,8 +506,9 @@ PostgreSQL documentation - Specify the compression level to use in archive formats that support - compression (currently only the custom archive format supports compression). + Specify the compression level to use in archive formats that + support compression. (Currently only the custom archive + format supports compression.) @@ -531,9 +524,11 @@ PostgreSQL documentation - Specifies the host name of the machine on which the server is - running. If the host name begins with a slash, it is used as the - directory for the Unix domain socket. + Specifies the host name of the machine on which the server is + running. If the value begins with a slash, it is used as the + directory for the Unix domain socket. The default is taken + from the PGHOST environment variable, if set, + else a Unix domain socket connection is attempted. @@ -543,11 +538,10 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file - extension on which the server - is listening for connections. The port number defaults to 5432, - or the value of the PGPORT - environment variable (if set). + Specifies the TCP port or local Unix domain socket file + extension on which the server is listening for connections. + Defaults to the PGPORT environment variable, if + set, or a compiled-in default. @@ -596,42 +590,13 @@ PostgreSQL documentation Diagnostics - - - - - - -Connection to database 'template1' failed. -connectDBStart() -- connect() failed: No such file or directory - Is the postmaster running locally - and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'? - - - - - - - - pg_dump could not connect to the - PostgreSQL server on the specified host and port. If you see this - message, ensure that the server is running on the proper host and - that you have specified the proper port. - - - - - - - - - pg_dump internally executes - SELECT statements. If you have problems running - pg_dump, - make sure you are able to select information from the database using, for - example, . - - + + pg_dump internally executes + SELECT statements. If you have problems running + pg_dump, make sure you are able to + select information from the database using, for example, . + @@ -639,11 +604,11 @@ connectDBStart() -- connect() failed: No such file or directory Notes - If your installation has any local additions to the template1 database, + If your database cluster has any local additions to the template1 database, be careful to restore the output of pg_dump into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database - without any local additions, copy from template0 not template1, + without any local additions, copy from template0 not template1, for example: CREATE DATABASE foo WITH TEMPLATE template0; @@ -657,17 +622,20 @@ CREATE DATABASE foo WITH TEMPLATE template0; When dumping a single table or as plain text, pg_dump - does not handle large objects. Large objects must be dumped in their - entirety using one of the binary archive formats. + does not handle large objects. Large objects must be dumped with the + entire database using one of the non-text archive formats. - When doing a data-only dump, pg_dump emits queries - to disable triggers on user tables before inserting the data and queries to - re-enable them after the data has been inserted. If the restore is stopped - in the middle, the system catalogs may be left in the wrong state. + When a data-only dump is chosen and the option + @@ -732,9 +700,9 @@ CREATE DATABASE foo WITH TEMPLATE template0; The pg_dump utility first appeared in - Postgres95 release 0.02. The + Postgres95 release 0.02. The non-plain-text output formats were introduced in - PostgreSQL release 7.1. + PostgreSQL release 7.1. diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index b584bb447ab..f33c81dd870 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -1,5 +1,5 @@ @@ -18,7 +18,7 @@ PostgreSQL documentation pg_dumpall - options + option @@ -63,8 +63,8 @@ PostgreSQL documentation - pg_dumpall might need to connect several - times to the PostgreSQL server, asking for + pg_dumpall needs to connect several + times to the PostgreSQL server and might be asking for a password each time. It is convenient to have a $HOME/.pgpass file in such cases. @@ -96,7 +96,7 @@ PostgreSQL documentation Dump data as INSERT commands (rather than COPY). This will make restoration very - slow, but it makes the output more portable to other RDBMS + slow, but it makes the output more portable to other SQL database packages. @@ -184,10 +184,10 @@ PostgreSQL documentation Specifies the host name of the machine on which the database - server is running. If host begins with a slash, it is used as - the directory for the Unix domain socket. The default is - taken from the PGHOST environment variable, if - set, else a Unix domain socket connection is attempted. + server is running. If the value begins with a slash, it is + used as the directory for the Unix domain socket. The default + is taken from the PGHOST environment variable, + if set, else a Unix domain socket connection is attempted. @@ -196,9 +196,10 @@ PostgreSQL documentation -p port - The port number on which the server is listening. Defaults to - the PGPORT environment variable, if set, or a - compiled-in default. + Specifies the TCP port or local Unix domain socket file + extension on which the server is listening for connections. + Defaults to the PGPORT environment variable, if + set, or a compiled-in default. @@ -237,7 +238,7 @@ PostgreSQL documentation - Default connection parameters. + Default connection parameters @@ -254,18 +255,10 @@ PostgreSQL documentation messages will refer to pg_dump. - - pg_dumpall will need to connect several - times to the PostgreSQL server. If password - authentication is configured, it will ask for a password each time. In - that case it would be convenient to set up a .pgpass - password file. - - Once restored, it is wise to run ANALYZE on each database so the optimizer has useful statistics. You - can also run vacuumdb -a -z to ANALYZE all + can also run vacuumdb -a -z to analyze all databases. diff --git a/doc/src/sgml/ref/pg_resetxlog.sgml b/doc/src/sgml/ref/pg_resetxlog.sgml index 61fce46a17d..a125b5f17a8 100644 --- a/doc/src/sgml/ref/pg_resetxlog.sgml +++ b/doc/src/sgml/ref/pg_resetxlog.sgml @@ -1,5 +1,5 @@ @@ -12,7 +12,7 @@ PostgreSQL documentation pg_resetxlog - reset write-ahead log and pg_control contents + reset the write-ahead log and other control information of a PostgreSQL database cluster @@ -30,25 +30,24 @@ PostgreSQL documentation Description - pg_resetxlog clears the write-ahead log and - optionally resets some fields in the pg_control file. This - function is sometimes - needed if these files have become corrupted. - It should be used only as a last resort, - when the server will not start due to such corruption. + pg_resetxlog clears the write-ahead log (WAL) and + optionally resets some other control information (stored in the + pg_control file). This function is sometimes needed + if these files have become corrupted. It should be used only as a + last resort, when the server will not start due to such corruption. After running this command, it should be possible to start the server, but bear in mind that the database may contain inconsistent data due to partially-committed transactions. You should immediately dump your data, - run initdb, and reload. After reload, check for + run initdb, and reload. After reload, check for inconsistencies and repair as needed. This utility can only be run by the user who installed the server, because - it requires read/write access to the datadir. + it requires read/write access to the data directory. For safety reasons, you must specify the data directory on the command line. pg_resetxlog does not use the environment variable PGDATA. @@ -64,12 +63,12 @@ PostgreSQL documentation The first three of these can be set using the switches discussed below. pg_resetxlog's own environment is the source for its guess at the locale fields; take care that LANG and so forth - match the environment that initdb was run in. + match the environment that initdb was run in. If you are not able to determine correct values for all these fields, -f can still be used, but the recovered database must be treated with even more suspicion than - usual --- an immediate dump and reload is imperative. Do not - execute any data-modifying operations in the database before you dump, + usual: an immediate dump and reload is imperative. Do not + execute any data-modifying operations in the database before you dump; as any such action is likely to make the corruption worse. @@ -79,8 +78,8 @@ PostgreSQL documentation be set manually. These are only needed when pg_resetxlog is unable to determine appropriate values by reading pg_control. A safe value for the - next transaction ID may be determined by looking for the largest - file name in $PGDATA/pg_clog, adding one, + next transaction ID may be determined by looking for the numerically largest + file name in the directory pg_clog under the data directory, adding one, and then multiplying by 1048576. Note that the file names are in hexadecimal. It is usually easiest to specify the switch value in hexadecimal too. For example, if 0011 is the largest entry @@ -88,7 +87,7 @@ PostgreSQL documentation zeroes provide the proper multiplier). The WAL starting address should be larger than any file number currently existing in - $PGDATA/pg_xlog. These also are in hex, and + the directory pg_xlog under the data directory. The addresses are also in hexadecimal and have two parts. For example, if 000000FF0000003A is the largest entry in pg_xlog, -l 0xFF,0x3B will work. There is no comparably easy way to determine a next OID that's beyond @@ -109,14 +108,14 @@ PostgreSQL documentation Notes - This command must not be used when the postmaster is + This command must not be used when the server is running. pg_resetxlog will refuse to start up if - it finds a postmaster lock file in the datadir. If the - postmaster crashed then a lock file may have been left + it finds a server lock file in the data directory. If the + server crashed then a lock file may have been left behind; in that case you can remove the lock file to allow pg_resetxlog to run. But before you do so, make doubly certain that there - is no postmaster nor any backend server process still alive. + is no postmaster nor any backend server process still alive. diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 427cbd45514..f1b90871d19 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -1,10 +1,6 @@ - + - - 2001-03-06 - - pg_restore 1 @@ -22,7 +18,8 @@ pg_restore - options + option + filename @@ -34,36 +31,31 @@ pg_restore is a utility for restoring a PostgreSQL database from an archive created by in one of the non-plain-text - formats. It - will issue the commands necessary to re-generate all user-defined - types, functions, tables, indexes, aggregates, and operators, as - well as the data in the tables. - - - - The archive files contain information for - pg_restore to rebuild the database, but also - allow pg_restore to be selective about what is - restored, or even to reorder the items prior to being restored. The - archive files are designed to be portable across architectures. + formats. It will issue the commands necessary to reconstruct the + database to the state it was in at the time it was saved. The + archive files also allow pg_restore to + be selective about what is restored, or even to reorder the items + prior to being restored. The archive files are designed to be + portable across architectures. - pg_restore can operate in two modes: If a - database name is specified, the archive is restored directly into - the database. Otherwise, a script containing the SQL commands - necessary to rebuild the database is created (and written to a file - or standard output), similar to the ones created by the - pg_dump plain text format. Some of the options - controlling the script output are therefore analogous to + pg_restore can operate in two modes: If + a database name is specified, the archive is restored directly into + the database. Large objects can only be restored by using a direct + database connection. Otherwise, a script containing the SQL + commands necessary to rebuild the database is created (and written + to a file or standard output), similar to the ones created by the + pg_dump plain text format. Some of the + options controlling the script output are therefore analogous to pg_dump options. Obviously, pg_restore cannot restore information - that is not present in the archive file; for instance, if the + that is not present in the archive file. For instance, if the archive was made using the dump data as - INSERTs option, + INSERT commands option, pg_restore will not be able to load the data using COPY statements. @@ -73,12 +65,12 @@ Options - pg_restore accepts the following command + pg_restore accepts the following command line arguments. - archive-name + filename Specifies the location of the archive file to be restored. @@ -92,7 +84,7 @@ - Restore only the data, no schema (definitions). + Restore only the data, not the schema (data definitions). @@ -113,7 +105,7 @@ Create the database before restoring into it. (When this - switch appears, the database named with is + option is used, the database named with is used only to issue the initial CREATE DATABASE command. All data is restored into the database name that appears in the archive.) @@ -128,8 +120,7 @@ Connect to database dbname and restore directly - into the database. Large objects can only be restored by using - a direct database connection. + into the database. @@ -155,14 +146,13 @@ the format, since pg_restore will determine the format automatically. If specified, it can be one of the following: - - t + t - Archive is a tar archive. Using this + The archive is a tar archive. Using this archive format allows reordering and/or exclusion of schema elements at the time the database is restored. It is also possible to limit which data is reloaded at restore time. @@ -171,10 +161,10 @@ - c + c - Archive is in the custom format of + The archive is in the custom format of pg_dump. This is the most flexible format in that it allows reordering of data load as well as schema elements. This format is also compressed @@ -183,6 +173,7 @@ + @@ -201,8 +192,7 @@ - Restore definition for named index only. + Restore definition of named index only. @@ -212,7 +202,7 @@ - List the contents of the archive. The output of this command + List the contents of the archive. The output of this operation can be used with the option to restrict and reorder the items that are restored. @@ -228,7 +218,7 @@ CLASS="PARAMETER">list-file only, and in the order they appear in the file. Lines can be moved and may also be commented out by placing a ; at the - start of the line. + start of the line. (See below for examples.) @@ -268,7 +258,8 @@ Prevent any attempt to restore original object ownership. Objects will be owned by the user name used to - attach to the database. + attach to the database. See also under and + . @@ -278,7 +269,7 @@ - Specify a procedure or function to be restored. + Restore the named function only. @@ -327,7 +318,8 @@ - Restore the schema (definitions), no data. Sequence values will be reset. + Restore only the schema (data defintions), not the data. + Sequence values will be reset. @@ -348,7 +340,7 @@ - Restore schema/data for table only. + Restore definition and/or data of named table only. @@ -358,7 +350,7 @@ - Restore definition of trigger only. + Restore named trigger only. @@ -431,7 +423,7 @@ - pg_restore also accepts + pg_restore also accepts the following command line arguments for connection parameters: @@ -441,8 +433,10 @@ Specifies the host name of the machine on which the server is - running. If host begins with a slash, it is used as the - directory for the Unix domain socket. + running. If the value begins with a slash, it is used as the + directory for the Unix domain socket. The default is taken + from the PGHOST environment variable, if set, + else a Unix domain socket connection is attempted. @@ -452,11 +446,11 @@ - Specifies the Internet TCP/IP port or local Unix domain socket - file extension on which the server is listening for - connections. The port number defaults to 5432, or the value - of the PGPORT environment variable (if set). - + Specifies the TCP port or local Unix domain socket file + extension on which the server is listening for connections. + Defaults to the PGPORT environment variable, if + set, or a compiled-in default. + @@ -494,7 +488,7 @@ - Default connection parameters. + Default connection parameters @@ -505,45 +499,14 @@ Diagnostics - - - - - - -Connection to database 'template1' failed. -connectDBStart() -- connect() failed: No such file or directory - Is the postmaster running locally - and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'? - - - - - - - - pg_restore could not attach to the - PostgreSQL server process on the specified - host and port. If you see this message, ensure that the - server is running on the proper host and that you have - specified the proper port. If your site uses an - authentication system, ensure that you have obtained the - required authentication credentials. - - - - - - - - When a direct database connection is specified using the -d - option, pg_restore internally executes - SQL statements. If you have problems running - pg_restore, make sure you are able to select - information from the database using, for example, - psql. - - + + When a direct database connection is specified using the + option, pg_restore + internally executes SQL statements. If you have + problems running pg_restore, make sure + you are able to select information from the database using, for + example, psql. + @@ -556,9 +519,9 @@ connectDBStart() -- connect() failed: No such file or directory pg_restore into a truly empty database; otherwise you are likely to get errors due to duplicate definitions of the added objects. To make an empty database without any local - additions, copy from template0 not template1, for example: + additions, copy from template0 not template1, for example: -CREATE DATABASE foo WITH TEMPLATE = template0; +CREATE DATABASE foo WITH TEMPLATE template0; @@ -568,8 +531,10 @@ CREATE DATABASE foo WITH TEMPLATE = template0; - When restoring data to a pre-existing table, pg_restore emits queries - to disable triggers on user tables before inserting the data then emits queries to + When restoring data to a pre-existing table and the option + @@ -601,20 +566,6 @@ CREATE DATABASE foo WITH TEMPLATE = template0; Examples - - To dump a database: - -$ pg_dump mydb > db.out - - - - - To reload this database: - -$ psql -d database -f db.out - - - To dump a database called mydb that contains large objects to a tar file: @@ -665,7 +616,7 @@ CREATE DATABASE foo WITH TEMPLATE = template0; 12; 145443 TABLE hs_old postgres 13; 145443 ACL hs_old - Semi-colons are comment delimiters, and the numbers at the start of lines refer to the + Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to each item. @@ -678,8 +629,8 @@ CREATE DATABASE foo WITH TEMPLATE = template0; 6; 145402 TABLE species_records postgres ;8; 145416 TABLE ss_old postgres - could be used as input to pg_restore and would only restore - items 10 and 6, in that order. + could be used as input to pg_restore and would only restore + items 10 and 6, in that order: $ pg_restore -L archive.list archive.file diff --git a/doc/src/sgml/ref/pgtclsh.sgml b/doc/src/sgml/ref/pgtclsh.sgml index 21ac402ff39..d915ffe00de 100644 --- a/doc/src/sgml/ref/pgtclsh.sgml +++ b/doc/src/sgml/ref/pgtclsh.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation pgtclsh - filename arguments + filename argument diff --git a/doc/src/sgml/ref/pgtksh.sgml b/doc/src/sgml/ref/pgtksh.sgml index b0689259788..5d2513afbaf 100644 --- a/doc/src/sgml/ref/pgtksh.sgml +++ b/doc/src/sgml/ref/pgtksh.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,7 @@ PostgreSQL documentation pgtksh - filename arguments + filename argument diff --git a/doc/src/sgml/ref/postgres-ref.sgml b/doc/src/sgml/ref/postgres-ref.sgml index 7b282eb295a..8d1bc512ebf 100644 --- a/doc/src/sgml/ref/postgres-ref.sgml +++ b/doc/src/sgml/ref/postgres-ref.sgml @@ -1,5 +1,5 @@ @@ -62,7 +62,7 @@ PostgreSQL documentation -tpaplex -S sort-mem - -v protocol-version + -v protocol -W seconds --name=value @@ -72,7 +72,7 @@ PostgreSQL documentation Description - The postgres executable is the actual + The postgres executable is the actual PostgreSQL server process that processes queries. It is normally not called directly; instead a multiuser server is started. @@ -80,7 +80,7 @@ PostgreSQL documentation The second form above is how - postgres is invoked by the postgres is invoked by the (only conceptually, since both postmaster and postgres are in fact the same program); it @@ -94,15 +94,15 @@ PostgreSQL documentation When invoked in interactive mode from the shell, the user can enter queries and the results will be printed to the screen, but in a form that is more useful for developers than end users. But note - that running a single-user backend is not truly suitable for + that running a single-user server is not truly suitable for debugging the server since no realistic interprocess communication and locking will happen. - When running a stand-alone backend, the session user will be set to + When running a stand-alone server, the session user will be set to the user with ID 1. This user does not actually have to exist, so - a stand-alone backend can be used to manually recover from certain + a stand-alone server can be used to manually recover from certain kinds of accidental damage to the system catalogs. Implicit superuser powers are granted to the user with ID 1 in stand-alone mode. @@ -113,11 +113,11 @@ PostgreSQL documentation Options - When postgres is started by a postgres is started by a then it inherits all options set by the latter. Additionally, - postgres-specific options can be passed - from the postmaster with the + postgres-specific options can be passed + from the postmaster with the switch. @@ -129,7 +129,7 @@ PostgreSQL documentation variable PGOPTIONS is set, then libpq-based clients will pass that string to the server, which will interpret it as - postgres command-line options. + postgres command-line options. @@ -138,10 +138,10 @@ PostgreSQL documentation The options , , , , , - , and , and have the same meanings as the except that - 0 prevents the debugging level of - the postmaster from being propagated to the backend. + -d 0 prevents the server log level of + the postmaster from being propagated to postgres. @@ -162,12 +162,12 @@ PostgreSQL documentation filename - Sends all debugging and error output to + Send all server log output to filename. - If the backend is running under the - postmaster, this option is ignored, + If postgres is running under the + postmaster, this option is ignored, and the stderr inherited from the - postmaster is used. + postmaster is used. @@ -176,7 +176,7 @@ PostgreSQL documentation - Ignore system indexes while scanning/updating system tuples. The + Ignore system indexes while scanning/updating system tables. The REINDEX command for system tables/indexes requires this option to be used. @@ -187,7 +187,7 @@ PostgreSQL documentation - Print time information and other statistics at the end of each query. + Print time information and other statistics at the end of each command. This is useful for benchmarking or for use in tuning the number of buffers. @@ -200,7 +200,7 @@ PostgreSQL documentation 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. Note that for a complex query, + kilobytes, and defaults to 1024. Note that for a complex query, several sorts and/or hashes might be running in parallel, and each one will be allowed to use as much as sort-mem kilobytes @@ -230,7 +230,7 @@ PostgreSQL documentation - Echo all queries. + Echo all commands. @@ -239,7 +239,7 @@ PostgreSQL documentation - Disables use of newline as a query delimiter. + Disables use of newline as a statement delimiter. @@ -268,6 +268,7 @@ PostgreSQL documentation disable sequential and index scans respectively, while n, m, and h disable nested-loop, merge and hash joins respectively. + @@ -277,7 +278,6 @@ PostgreSQL documentation plan types if it has any other alternative. - @@ -295,7 +295,7 @@ PostgreSQL documentation Allows the structure of system tables to be modified. This is - used by initdb. + used by initdb. @@ -304,9 +304,8 @@ PostgreSQL documentation database - Indicates that this server has been started by a - postmaster and makes different - assumptions about buffer pool management, file descriptors, + Indicates that this process has been started by a + postmaster and specifies the database to use. etc. @@ -339,7 +338,7 @@ PostgreSQL documentation As soon as this option is encountered, the process sleeps for the specified amount of seconds. This gives developers time - to attach a debugger to the backend process. + to attach a debugger to the server process. @@ -375,10 +374,10 @@ PostgreSQL documentation To stop a running query use the SIGINT signal. To - tell postgres to reread the config file, + tell postgres to reread the configuration file, use a SIGHUP signal. The - postmaster uses SIGTERM - to tell a postgres process to quit normally and + postmaster uses SIGTERM + to tell a postgres process to quit normally and SIGQUIT to terminate without the normal cleanup. These should not be used by users. @@ -389,17 +388,17 @@ PostgreSQL documentation Usage - Start a stand-alone backend with a command like + Start a stand-alone server with a command like -postgres -D $PGDATA other-options my_database +postgres -D /usr/local/pgsql/data other-options my_database - Provide the correct path to the database area with - Normally, the stand-alone backend treats newline as the command + Normally, the stand-alone server treats newline as the command entry terminator; there is no intelligence about semicolons, as there is in psql. To continue a command across multiple lines, you must type backslash just before each @@ -408,9 +407,9 @@ PostgreSQL documentation But if you use the @@ -422,7 +421,7 @@ PostgreSQL documentation - Note that the stand-alone backend does not provide sophisticated + Note that the stand-alone server does not provide sophisticated line-editing features (no command history, for example). diff --git a/doc/src/sgml/ref/postmaster.sgml b/doc/src/sgml/ref/postmaster.sgml index e22c5b9d16b..36bd38f8c1f 100644 --- a/doc/src/sgml/ref/postmaster.sgml +++ b/doc/src/sgml/ref/postmaster.sgml @@ -1,5 +1,5 @@ @@ -41,33 +41,33 @@ PostgreSQL documentation Description - postmaster is the + postmaster is the PostgreSQL multiuser database server. In order for a client application to access a database it connects (over a network or locally) to a running - postmaster. The - postmaster then starts a separate server + postmaster. The + postmaster then starts a separate server process () to handle - the connection. The postmaster also + the connection. The postmaster also manages the communication among server processes. - By default the postmaster starts in the - foreground and prints log messages to the standard output. In - practical applications the postmaster + By default the postmaster starts in the + foreground and prints log messages to the standard error stream. In + practical applications the postmaster should be started as a background process, perhaps at boot time. - One postmaster always manages the data + One postmaster always manages the data from exactly one database cluster. A database cluster is a collection of databases that is stored at a common file system - location. When the postmaster starts it needs to know the location + location. When the postmaster starts it needs to know the location of the database cluster files (data area). This is done with the invocation option or the PGDATA environment variable; there is no default. - More than one postmaster process can run on a system at one time, + More than one postmaster process can run on a system at one time, as long as they use different data areas and different communication ports (see below). A data area is created with . @@ -78,17 +78,17 @@ PostgreSQL documentation Options - postmaster accepts the following + postmaster accepts the following command line arguments. For a detailed discussion of the options consult the &cite-admin;. You can also save typing most of these options by setting up a configuration file. - -A 0|1 + - Enables run-time assert checks, which is a debugging aid to + Enables run-time assertion checks, which is a debugging aid to detect programming mistakes. This is only available if it was enabled during compilation. If so, the default is on. @@ -96,7 +96,7 @@ PostgreSQL documentation - -B nbuffers + Sets the number of shared buffers for use by the server @@ -107,7 +107,7 @@ PostgreSQL documentation - -c name=value + Sets a named run-time parameter. Consult the &cite-admin; for @@ -120,7 +120,7 @@ PostgreSQL documentation - -d debug-level + Sets the debug level. The higher this value is set, the more @@ -131,7 +131,7 @@ PostgreSQL documentation - -D datadir + Specifies the file system location of the data directory. See @@ -141,12 +141,12 @@ PostgreSQL documentation - -F + Disables fsync calls for performance improvement, at the risk of data corruption in event of a - system crash. This parameter corresponds to setting + system crash. This option corresponds to setting fsync=false in postgresql.conf. Read the detailed documentation before using this! @@ -158,11 +158,11 @@ PostgreSQL documentation - -h hostname + - Specifies the TCP/IP host name or address on which the - postmaster is to listen for + Specifies the IP host name or address on which the + postmaster is to listen for connections from client applications. Defaults to listening on all configured addresses (including localhost). @@ -171,7 +171,7 @@ PostgreSQL documentation - -i + Allows clients to connect via TCP/IP (Internet domain) @@ -180,18 +180,18 @@ PostgreSQL documentation to setting tcpip_socket=true in postgresql.conf. - has the opposite + has the opposite effect of this option. - -k directory + Specifies the directory of the Unix-domain socket on which the - postmaster is to listen for + postmaster is to listen for connections from client applications. The default is normally /tmp, but can be changed at build time. @@ -199,7 +199,7 @@ PostgreSQL documentation - -l + Enables secure connections using SSL. The @@ -210,11 +210,11 @@ PostgreSQL documentation - -N max-connections + Sets the maximum number of client connections that this - postmaster will accept. By + postmaster will accept. By default, this value is 32, but it can be set as high as your system will support. (Note that is required to be at least twice @@ -226,13 +226,13 @@ PostgreSQL documentation - -o extra-options + The command line-style options specified in extra-options are passed to - all backend server processes started by this - postmaster. See postmaster. See for possibilities. If the option string contains any spaces, the entire string must be quoted. @@ -240,11 +240,11 @@ PostgreSQL documentation - -p port + Specifies the TCP/IP port or local Unix domain socket file - extension on which the postmaster + extension on which the postmaster is to listen for connections from client applications. Defaults to the value of the PGPORT environment variable, or if PGPORT is not set, then @@ -257,10 +257,10 @@ PostgreSQL documentation - -S + - Specifies that the postmaster + Specifies that the postmaster process should start up in silent mode. That is, it will disassociate from the user's (controlling) terminal, start its own process group, and redirect its standard output and @@ -270,17 +270,17 @@ PostgreSQL documentation Using this switch discards all logging output, which is probably not what you want, since it makes it very difficult to troubleshoot problems. See below for a better way to start - the postmaster in the background. + the postmaster in the background. - has the opposite effect + has the opposite effect of this option. - --name=value + Sets a named run-time parameter; a shorter form of @@ -294,17 +294,18 @@ PostgreSQL documentation Two additional command line options are available for debugging - problems that cause a backend to die abnormally. These options - control the behavior of the postmaster - in this situation, and neither option is intended for - use in ordinary operation. + problems that cause a server process to die abnormally. The + ordinary strategy in this situation is to notify all other server + processes that they must terminate and then reinitialize the + shared memory and semaphores. This is because an errant server + process could have corrupted some shared state before terminating. + These options select alternative behaviors of the + postmaster in this situation. + Neither option is intended for use in ordinary + operation. - The ordinary strategy for this situation is to notify all other - backends that they must terminate and then reinitialize the shared - memory and semaphores. This is because an errant backend could - have corrupted some shared state before terminating. @@ -312,10 +313,10 @@ PostgreSQL documentation - -n + - postmaster + postmaster will not reinitialize shared data structures. A knowledgeable system programmer can then use a debugger to examine shared memory and semaphore state. @@ -324,14 +325,14 @@ PostgreSQL documentation - -s + - postmaster - will stop all other backend processes by sending the signal + postmaster + will stop all other server processes by sending the signal SIGSTOP, but will not cause them to terminate. This permits system programmers - to collect core dumps from all backend processes by hand. + to collect core dumps from all server processes by hand. @@ -367,7 +368,7 @@ PostgreSQL documentation - PGDATASTYLE + PGDATESTYLE @@ -418,22 +419,17 @@ PostgreSQL documentation - -semget: No space left on device - + semget: No space left on device - If you see this message, you should run the - ipcclean - command. After doing so, try starting - postmaster - again. If this still doesn't work, you probably need to configure - your kernel for shared memory and semaphores as described in the - installation notes. If you run multiple instances of - postmaster + If you see this message, you probably need to configure + your kernel for shared memory and semaphores as described in the &cite-admin;. + If you run multiple instances of + postmaster on a single host, or have a kernel with particularly small shared memory and/or semaphore limits, you may have to reconfigure your kernel to increase its shared memory or semaphore parameters. + @@ -444,18 +440,15 @@ semget: No space left on device consumption. - - -StreamServerPort: cannot bind to port - + StreamServerPort: cannot bind to port If you see this message, you should make certain that there is no - other postmaster + other postmaster process already running on the same port number. The easiest way to determine this is by using the command @@ -471,18 +464,18 @@ StreamServerPort: cannot bind to port If you are sure that no other - postmaster + postmaster processes are running and you still get this error, try specifying a different port using the -p option. You may also get this error if you terminate the - postmaster + postmaster and immediately restart it using the same port; in this case, you must simply wait a few seconds until the operating system closes the port before trying again. Finally, you may get this error if you specify a port number that your operating system considers to be reserved. For example, many versions of Unix consider port numbers under 1024 to - be trusted + be trusted and only permit the Unix superuser to access them. @@ -497,14 +490,14 @@ StreamServerPort: cannot bind to port If at all possible, do not use SIGKILL to kill the - postmaster. This will prevent - postmaster from freeing the system + postmaster. This will prevent + postmaster from freeing the system resources (e.g., shared memory and semaphores) that it holds before terminating. - To terminate the postmaster normally, + To terminate the postmaster normally, the signals SIGTERM, SIGINT, or SIGQUIT can be used. The first will wait for all clients to terminate before quitting, the second will @@ -515,7 +508,7 @@ StreamServerPort: cannot bind to port The utility command can be used to - start and shut down the postmaster + start and shut down the postmaster safely and comfortably. @@ -532,7 +525,7 @@ StreamServerPort: cannot bind to port Examples - To start postmaster in the background + To start postmaster in the background using default values, type: @@ -541,14 +534,14 @@ StreamServerPort: cannot bind to port - To start postmaster with a specific + To start postmaster with a specific port: $ postmaster -p 1234 - This command will start up postmaster + This command will start up postmaster communicating through the port 1234. In order to connect to this - postmaster using psql, you would need to + postmaster using psql, you would need to run it as $ psql -p 1234 diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 181d967cbb9..f2cf072edc7 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -20,9 +20,9 @@ PostgreSQL documentation psql - options + option dbname - user + username @@ -69,17 +69,17 @@ PostgreSQL documentation - - + + Specifies that psql is to execute one - query string, query, + command string, command, and then exit. This is useful in shell scripts. - query must be either - a query string that is completely parsable by the backend (i.e., + command must be either + a command string that is completely parsable by the server (i.e., it contains no psql specific features), or it is a single backslash command. Thus you cannot mix SQL and psql @@ -108,7 +108,7 @@ PostgreSQL documentation - Show all queries that are sent to the backend. This is equivalent + Show all commands that are sent to the server. This is equivalent to setting the variable ECHO to queries. @@ -120,7 +120,7 @@ PostgreSQL documentation - Echoes the actual queries generated by \d and other backslash + Echo the actual queries generated by \d and other backslash commands. You can use this if you wish to include similar functionality into your own programs. This is equivalent to setting the variable ECHO_HIDDEN from within @@ -135,7 +135,7 @@ PostgreSQL documentation Use the file filename - as the source of queries instead of reading queries interactively. + as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the internal command \i. @@ -179,7 +179,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the - postmaster is running. If host begins + server is running. If the value begins with a slash, it is used as the directory for the Unix-domain socket. @@ -191,7 +191,7 @@ PostgreSQL documentation - Turns on HTML tabular output. This is + Turn on HTML tabular output. This is equivalent to \pset format html or the \H command. @@ -203,7 +203,7 @@ PostgreSQL documentation - Lists all available databases, then exits. Other non-connection + List all available databases, then exits. Other non-connection options are ignored. This is similar to the internal command \list. @@ -227,9 +227,8 @@ PostgreSQL documentation - Specifies the TCP/IP port or, by omission, the local Unix domain - socket file extension on which the - postmaster is listening for + Specifies the TCP port or the local Unix domain + socket file extension on which the server is listening for connections. Defaults to the value of the PGPORT environment variable or, if not set, to the port specified at compile time, usually 5432. @@ -284,7 +283,7 @@ PostgreSQL documentation Run in single-step mode. That means the user is prompted before - each query is sent to the backend, with the option to cancel + each command is sent to the server, with the option to cancel execution as well. Use this to debug scripts. @@ -295,7 +294,7 @@ PostgreSQL documentation - Runs in single-line mode where a newline terminates a query, as a + Runs in single-line mode where a newline terminates an SQL command, as a semicolon does. @@ -345,7 +344,7 @@ PostgreSQL documentation This option is deprecated, as it is conceptually flawed. (Prompting for a non-default user name and prompting for a - password because the backend requires it are really two different + password because the server requires it are really two different things.) You are encouraged to look at the and options instead. @@ -357,7 +356,7 @@ PostgreSQL documentation - Connects to the database as the user username instead of the default. (You must have permission to do so, of course.) @@ -370,7 +369,7 @@ PostgreSQL documentation - Performs a variable assignment, like the \set + Perform a variable assignment, like the \set internal command. Note that you must separate name and value, if any, by an equal sign on the command line. To unset a variable, leave off the equal sign. To just set a variable without a value, @@ -386,7 +385,7 @@ PostgreSQL documentation - Shows the psql version. + Show the psql version. @@ -404,11 +403,11 @@ PostgreSQL documentation In the current version, psql - automatically issues a password prompt whenever the backend + automatically issues a password prompt whenever the server requests password authentication. Because this is currently based on a hack, the automatic recognition might mysteriously fail, hence this option to force a prompt. If no password prompt is - issued and the backend requires password authentication the + issued and the server requires password authentication the connection attempt will fail. @@ -419,7 +418,7 @@ PostgreSQL documentation - Turns on extended row format mode. This is equivalent to the + Turn on the extended table formatting mode. This is equivalent to the command \x. @@ -440,7 +439,7 @@ PostgreSQL documentation - Shows help about psql command line + Show help about psql command line arguments. @@ -455,8 +454,8 @@ PostgreSQL documentation psql returns 0 to the shell if it finished normally, 1 if a fatal error of its own (out of memory, - file not found) occurs, 2 if the connection to the backend went bad - and the session is not interactive, and 3 if an error occurred in a + file not found) occurs, 2 if the connection to the server went bad + and the session was not interactive, and 3 if an error occurred in a script and the variable ON_ERROR_STOP was set. @@ -495,13 +494,13 @@ PostgreSQL documentation If the connection could not be made for any reason (e.g., insufficient - privileges, postmaster is not running on the server, etc.), + privileges, server is not running on the targeted host, etc.), psql will return an error and terminate. - Entering Queries + Entering SQL Commands In normal operation, psql provides a @@ -523,16 +522,16 @@ testdb=> - At the prompt, the user may type in SQL queries. - Ordinarily, input lines are sent to the backend when a - query-terminating semicolon is reached. An end of line does not - terminate a query! Thus queries can be spread over several lines for - clarity. If the query was sent and without error, the query results + At the prompt, the user may type in SQL commands. + Ordinarily, input lines are sent to the server when a + command-terminating semicolon is reached. An end of line does not + terminate a command. Thus commands can be spread over several lines for + clarity. If the command was sent and without error, the results of the command are displayed on the screen. - Whenever a query is executed, psql also polls + Whenever a command is executed, psql also polls for asynchronous notification events generated by and . @@ -586,18 +585,23 @@ testdb=> - Some commands take an SQL identifier - (such as a table name) as argument. These arguments follow the - syntax rules of SQL regarding double quotes: an - identifier without double quotes is coerced to lower-case, while - whitespace within double quotes is included in the argument. + Some commands take an SQL identifier (such as a + table name) as argument. These arguments follow the syntax rules + of SQL: Unquoted letters are forced to + lowercase, while double quotes (") protect letters + from case conversion and allow incorporation of whitespace into + the identifier. Within double quotes, paired double quotes reduce + to a single double quote in the resulting name. For example, + FOO"BAR"BAZ is interpreted as fooBARbaz, + and "A weird"" name" becomes A weird" + name. Parsing for arguments stops when another unquoted backslash occurs. This is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of - arguments and continues parsing SQL queries, if + arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot @@ -612,8 +616,8 @@ testdb=> \a - If the current table output format is unaligned, switch to aligned. - If it is not unaligned, set it to unaligned. This command is + If the current table output format is unaligned, it is switched to aligned. + If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a general solution. @@ -624,8 +628,8 @@ testdb=> \cd directory - Change the current working directory to - directory. Without argument, change + Changes the current working directory to + directory. Without argument, changes to the current user's home directory. @@ -641,7 +645,7 @@ testdb=> \C [ title ] - Set the title of any tables being printed as the result of a + Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of @@ -701,10 +705,10 @@ testdb=> Performs a frontend (client) copy. This is an operation that runs an SQL command, but instead of the backend's + endterm="SQL-COPY-title"> command, but instead of the server reading or writing the specified file, psql reads or writes the file and - routes the data between the backend and the local file system. + routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required. @@ -712,8 +716,8 @@ testdb=> The syntax of the command is similar to that of the - SQL COPY command (see its - description for the details). Note that, because of this, + SQL COPY command. (See its + description for the details.) Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply. @@ -723,7 +727,7 @@ testdb=> This operation is not as efficient as the SQL COPY command because all data must pass - through the client/server IP or socket connection. For large + through the client/server connection. For large amounts of data the other technique may be preferable. @@ -732,9 +736,9 @@ testdb=> Note the difference in interpretation of stdin and stdout between - frontend and backend copies: in a frontend copy these always + client and server copies: in a client copy these always refer to psql's input and output - stream. On a backend copy stdin comes from + stream. On a server copy stdin comes from wherever the COPY itself came from (for example, a script run with the option), and stdout refers to the query output stream (see @@ -792,7 +796,7 @@ testdb=> Lists all available aggregate functions, together with the data type they operate on. If pattern (a regular expression) + class="parameter">pattern is specified, only matching aggregates are shown. @@ -845,16 +849,8 @@ testdb=> Descriptions for objects can be created with the - COMMENT ON SQL command. + COMMENT SQL command. - - - - PostgreSQL stores the object - descriptions in the pg_description system table. - - - @@ -863,7 +859,7 @@ testdb=> \dD [ pattern ] - Lists all available domains (derived types). If pattern is specified, only matching domains are shown. @@ -907,12 +903,12 @@ testdb=> order, to obtain a listing of all the matching objects. The letter S restricts the listing to system objects; without S, only non-system objects are shown. - If + is appended to the command name, each object is + If + is appended to the command name, each object is listed with its associated description, if any. - If a pattern is + If pattern is specified, only objects whose name matches the pattern are listed. @@ -948,7 +944,7 @@ testdb=> Lists available operators with their operand and return types. - If a pattern is + If pattern is specified, only operators whose name matches the pattern are listed. @@ -960,15 +956,15 @@ testdb=> Produces a list of all available tables with their - associated access permissions. - If a pattern is + associated access privileges. + If pattern is specified, only tables whose name matches the pattern are listed. The commands and - are used to set access permissions. See + are used to set access privileges. See for more information. @@ -991,7 +987,7 @@ testdb=> \du [ pattern ] - Lists all database users, or only those that match pattern. @@ -1063,13 +1059,13 @@ Tue Oct 26 21:40:57 CEST 1999 - Sets the client encoding. Without an argument, this command + Sets the client character set encoding. Without an argument, this command shows the current encoding. This command will not notice changes made directly by SET - CLIENT_ENCODING. If you use \encoding, + client_encoding. If you use \encoding, be sure to use it to set as well as examine the encoding. @@ -1083,7 +1079,7 @@ Tue Oct 26 21:40:57 CEST 1999 Sets the field separator for unaligned query output. The default - is pipe (|). See also + is the vertical bar (|). See also \pset for a generic way of setting output options. @@ -1096,7 +1092,7 @@ Tue Oct 26 21:40:57 CEST 1999 - Sends the current query input buffer to the backend and + Sends the current query input buffer to the server and optionally saves the output in filename or pipes the output into a separate Unix shell to execute \help (or \h) [ command ] - Give syntax help on the specified SQL + Gives syntax help on the specified SQL command. If command is not specified, then psql will list all the commands for which syntax help is available. If command is an - asterisk (*), then syntax help on all + asterisk (*), then syntax help on all SQL commands is shown. @@ -1169,8 +1165,8 @@ Tue Oct 26 21:40:57 CEST 1999 \l (or \list) - List the names, owners, and encodings of all the databases in - the server. Append a + to the command name to + List the names, owners, and character set encodings of all the databases in + the server. Append a + to the command name to see any descriptions for the databases as well. @@ -1214,13 +1210,13 @@ Tue Oct 26 21:40:57 CEST 1999 Stores the file into a PostgreSQL - large object. Optionally, it associates the given + large object. Optionally, it associates the given comment with the object. Example: foo=> \lo_import '/home/peter/pictures/photo.xcf' 'a picture of me' lo_import 152801 - The response indicates that the large object received object id + The response indicates that the large object received object ID 152801 which one ought to remember if one wants to access the object ever again. For that reason it is recommended to always associate a human-readable comment with every object. Those can @@ -1249,7 +1245,7 @@ lo_import 152801 Shows a list of all PostgreSQL - large objects currently stored in the database, + large objects currently stored in the database, along with any comments provided for them. @@ -1291,8 +1287,7 @@ lo_import 152801 class="parameter">filename or pipes future results into a separate Unix shell to execute command. If no arguments are - specified, the query output will be reset to - stdout. + specified, the query output will be reset to the standard output. @@ -1349,7 +1344,7 @@ lo_import 152801 - Unaligned writes all fields of a tuple on a + Unaligned writes all columns of a row on a line, separated by the currently active field separator. This is intended to create output that might be intended to be read in by other programs (tab-separated, comma-separated). @@ -1385,14 +1380,14 @@ lo_import 152801 Toggles between regular and expanded format. When expanded - format is enabled, all output has two columns with the field + format is enabled, all output has two columns with the column name on the left and the data on the right. This mode is useful if the data wouldn't fit on the screen in the normal horizontal mode. - Expanded mode is supported by all four output modes. + Expanded mode is supported by all four output formats. @@ -1402,7 +1397,7 @@ lo_import 152801 The second argument is a string that should be printed - whenever a field is null. The default is not to print + whenever a column is null. The default is not to print anything, which can easily be mistaken for, say, an empty string. Thus, one might choose to write \pset null '(null)'. @@ -1419,7 +1414,7 @@ lo_import 152801 comma-separated output, which other programs might prefer. To set a tab as field separator, type \pset fieldsep '\t'. The default field separator is - '|' (a pipe symbol). + '|' (a vertical bar). @@ -1464,13 +1459,6 @@ lo_import 152801 can be used to give your output descriptive tags. If no argument is given, the title is unset. - - - - This formerly only affected HTML mode. You - can now set titles in any output format. - - @@ -1512,6 +1500,9 @@ lo_import 152801 + + + Illustrations on how these different formats look can be seen in the section. @@ -1541,7 +1532,7 @@ lo_import 152801 \q - Quit the psql program. + Quits the psql program. @@ -1608,8 +1599,9 @@ lo_import 152801 Valid variable names can contain characters, digits, and - underscores. See the section about - psql variables for details. + underscores. See the section below for details. @@ -1644,7 +1636,7 @@ lo_import 152801 \T table_options - Allows you to specify options to be placed within the + Allows you to specify attributes to be placed within the table tag in HTML tabular output mode. This command is equivalent to \pset tableattr \x - Toggles extended row format mode. As such it is equivalent to + Toggles extended table formatting mode. As such it is equivalent to \pset expanded. @@ -1692,7 +1684,7 @@ lo_import 152801 Produces a list of all available tables with their - associated access permissions. + associated access privileges. If a pattern is specified, only tables whose name matches the pattern are listed. @@ -1700,13 +1692,13 @@ lo_import 152801 The commands and - are used to set access permissions. See + are used to set access privileges. See for more information. This is an alias for \dp (display - permissions). + privileges). @@ -1729,8 +1721,7 @@ lo_import 152801 \? - Get help information about the backslash (\) - commands. + Shows help information about the backslash commands. @@ -1741,31 +1732,20 @@ lo_import 152801 The various \d commands accept a pattern parameter to specify the - object name(s) to be displayed. Patterns are interpreted similarly - to SQL identifiers, in that unquoted letters are forced to lowercase, - while double quotes (") protect letters from case conversion - and allow incorporation of whitespace into the identifier. Within - double quotes, paired double quotes reduce to a single double quote in - the resulting name. For example, FOO"BAR"BAZ is interpreted - as fooBARbaz, and "A weird"" name" becomes - A weird" name. - - - - More interestingly, \d patterns allow the use of - * to mean any sequence of characters, and - ? to mean any single character. (This notation - is comparable to Unix shell filename patterns.) Advanced users can - also use regular-expression notations such as character classes, for - example [0-9] to match any digit. To make any of - these pattern-matching characters be interpreted literally, surround it + object name(s) to be displayed. * means any + sequence of characters and ? means any single + character. (This notation is comparable to Unix shell file name + patterns.) Advanced users can also use regular-expression + notations such as character classes, for example [0-9] + to match any digit. To make any of these + pattern-matching characters be interpreted literally, surround it with double quotes. A pattern that contains an (unquoted) dot is interpreted as a schema name pattern followed by an object name pattern. For example, - \dt foo*.bar* displays all tables in schemas whose name + \dt foo*.bar* displays all tables in schemas whose name starts with foo and whose table name starts with bar. If no dot appears, then the pattern matches only objects that are visible in the current schema search path. @@ -1787,17 +1767,16 @@ lo_import 152801 psql provides variable substitution - features similar to common Unix command shells. This feature is new - and not very sophisticated, yet, but there are plans to expand it in - the future. Variables are simply name/value pairs, where the value + features similar to common Unix command shells. + Variables are simply name/value pairs, where the value can be any string of any length. To set variables, use the psql meta-command \set: testdb=> \set foo bar - sets the variable foo to the value - bar. To retrieve the content of the variable, precede + sets the variable foo to the value + bar. To retrieve the content of the variable, precede the name with a colon and use it as the argument of any slash command: @@ -1840,6 +1819,8 @@ bar consist of all upper-case letters (and possibly numbers and underscores). To ensure maximum compatibility in the future, avoid such variables. A list of all specially treated variables follows. + + DBNAME @@ -1856,13 +1837,13 @@ bar ECHO - If set to all, all lines + If set to all, all lines entered or from a script are written to the standard output before they are parsed or executed. To specify this on program start-up, use the switch . If set to - queries, + queries, psql merely prints all queries as - they are sent to the backend. The option for this is + they are sent to the server. The option for this is . @@ -1877,7 +1858,7 @@ bar PostgreSQL internals and provide similar functionality in your own programs. If you set the variable to the value noexec, the queries are - just shown but are not actually sent to the backend and + just shown but are not actually sent to the server and executed. @@ -1887,9 +1868,7 @@ bar ENCODING - The current client multibyte encoding. If you are not set up to - use multibyte characters, this variable will always contain - SQL_ASCII. + The current client character set encoding. @@ -1909,7 +1888,7 @@ bar This feature was shamelessly plagiarized from - bash. + Bash. @@ -1925,7 +1904,7 @@ bar This feature was shamelessly plagiarized from - bash. + Bash. @@ -1957,7 +1936,7 @@ bar This feature was shamelessly plagiarized from - bash. + Bash. @@ -1982,7 +1961,7 @@ bar If you use the PostgreSQL large object interface to specially store data that does not fit into - one tuple, all the operations must be contained in a transaction + one row, all the operations must be contained in a transaction block. (See the documentation of the large object interface for more information.) Since psql has no way to tell if you already have a transaction in progress when @@ -1992,16 +1971,15 @@ bar action. This action could either be to roll back any transaction that might already be in progress, or to commit any such transaction, or to do nothing at all. In the last case you must - provide your own BEGIN - TRANSACTION/COMMIT block or the + provide your own BEGIN/COMMIT block or the results will be unpredictable (usually resulting in the desired action's not being performed in any case). To choose what you want to do you set this variable to one of - rollback, commit, or - nothing. The default is to roll back the + rollback, commit, or + nothing. The default is to roll back the transaction. If you just want to load one or a few objects this is fine. However, if you intend to transfer many large objects, it might be advisable to provide one explicit transaction block @@ -2015,7 +1993,7 @@ bar By default, if non-interactive scripts encounter an error, such - as a malformed SQL query or internal + as a malformed SQL command or internal meta-command, processing continues. This has been the traditional behavior of psql but it is sometimes not desirable. If this variable is set, script @@ -2048,9 +2026,9 @@ bar These specify what the prompt psql - issues is supposed to look like. See below. + endterm="APP-PSQL-prompting-title"> below. @@ -2099,8 +2077,6 @@ bar - - @@ -2127,7 +2103,7 @@ testdb=> SELECT * FROM :foo; A popular application of this facility is to refer to the last inserted OID in subsequent statements to build a foreign key scenario. Another possible use of this mechanism is to - copy the contents of a file into a field. First load the file into a + copy the contents of a file into a table column. First load the file into a variable and then proceed as above. testdb=> \set content '\'' `cat my_file.txt` '\'' @@ -2135,8 +2111,8 @@ testdb=> INSERT INTO my_table VALUES (:content); One possible problem with this approach is that my_file.txt might contain single quotes. These need to be escaped so that - they don't cause a syntax error when the third line is processed. This - could be done with the program sed: + they don't cause a syntax error when the second line is processed. This + could be done with the program sed: testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\'' @@ -2144,9 +2120,9 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' this way: After psql has parsed this line, it passes sed -e "s/'/\\\'/g" < my_file.txt to the shell. The shell will do its own thing inside the double - quotes and execute sed with the arguments + quotes and execute sed with the arguments -e and s/'/\\'/g. When - sed parses this it will replace the two + sed parses this it will replace the two backslashes with a single one and then do the substitution. Perhaps at one point you thought it was great that all Unix commands use the same escape character. And this is ignoring the fact that you might @@ -2157,12 +2133,12 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' - Since colons may legally appear in queries, the following rule + Since colons may legally appear in SQL commands, the following rule applies: If the variable is not set, the character sequence colon+name is not changed. In any case you can escape a colon with a backslash to protect it from interpretation. (The colon syntax for variables is standard SQL for - embedded query languages, such as ecpg. + embedded query languages, such as ECPG. The colon syntax for array slices and type casts are PostgreSQL extensions, hence the conflict.) @@ -2179,17 +2155,17 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt. Prompt 1 is the normal prompt that is issued when - psql requests a new query. Prompt 2 is - issued when more input is expected during query input because the - query was not terminated with a semicolon or a quote was not closed. + psql requests a new command. Prompt 2 is + issued when more input is expected during command input because the + command was not terminated with a semicolon or a quote was not closed. Prompt 3 is issued when you run an SQL COPY command and you are expected to type in the - tuples on the terminal. + row values on the terminal. The value of the respective prompt variable is printed literally, - except where a percent sign (%) is encountered. + except where a percent sign (%) is encountered. Depending on the next character, certain other text is substituted instead. Defined substitutions are: @@ -2212,7 +2188,7 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %m - The host name of the database server, truncated after the + The host name of the database server, truncated at the first dot, or [local] if the connection is over a Unix domain socket. @@ -2237,28 +2213,28 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %~ - Like %/, but the output is ~ + Like %/, but the output is ~ (tilde) if the database is your default database. %# If the current user is a database superuser, then a - #, otherwise a >. + #, otherwise a >. %R - In prompt 1 normally =, but ^ if - in single-line mode, and ! if the session is + In prompt 1 normally =, but ^ if + in single-line mode, and ! if the session is disconnected from the database (which can happen if \connect fails). In prompt 2 the sequence is - replaced by -, *, a single quote, + replaced by -, *, a single quote, or a double quote, depending on whether psql expects more input because the - query wasn't terminated yet, because you are inside a + command wasn't terminated yet, because you are inside a /* ... */ comment, or because you are inside a quote. In prompt 3 the sequence doesn't resolve to anything. @@ -2284,10 +2260,10 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' %:name: - The value of the psql, variable + The value of the psql variable name. See the - section for details. + section for details. @@ -2330,9 +2306,7 @@ testdb=> \set content '\'' `sed -e "s/'/\\\\\\'/g" < my_file.txt` '\' in your home directory and is reloaded when psql starts up. Tab-completion is also supported, although the completion logic makes no claim to be an - SQL parser. When available, - psql is automatically built to use these - features. If for some reason you do not like the tab completion, you + SQL parser. If for some reason you do not like the tab completion, you can turn if off by putting this in a file named .inputrc in your home directory: @@ -2341,7 +2315,7 @@ set disable-completion on $endif (This is not a psql but a - readline feature. Read its documentation + Readline feature. Read its documentation for further details.) @@ -2471,12 +2445,12 @@ $endif first argument of a single-letter backslash command to start directly after the command, without intervening whitespace. For compatibility this is still supported to some extent, - but I am not going to explain the details here as this use is + but were are not going to explain the details here as this use is discouraged. If you get strange messages, keep this in mind. For example testdb=> \foo -Field separator is "oo", +Field separator is "oo". which is perhaps not what one would expect. @@ -2494,10 +2468,11 @@ Field separator is "oo", - Pressing Control-C during a copy in (data sent to + Pressing ControlC + during a copy in (data sent to the server) doesn't show the most ideal of behaviors. If you get a - message such as COPY state must be terminated - first, simply reset the connection by entering \c + message such as COPY state must be terminated + first, simply reset the connection by entering \c - -. @@ -2515,19 +2490,19 @@ Field separator is "oo", psql. If you want to learn SQL or get familiar with PostgreSQL, you might wish to read the - Tutorial that is included in the distribution. + &cite-tutorial;. - The first example shows how to spread a query over several lines of + The first example shows how to spread a command over several lines of input. Notice the changing prompt: testdb=> CREATE TABLE my_table ( testdb(> first integer not null default 0, testdb(> second text testdb-> ); -CREATE +CREATE TABLE Now look at the table definition again: @@ -2539,8 +2514,7 @@ testdb=> \d my_table second | text | - At this point you decide to change the prompt to something more - interesting: + Now we change the prompt to something more interesting: testdb=> \set PROMPT1 '%n@%m %~%R%# ' peter@localhost testdb=> diff --git a/doc/src/sgml/ref/vacuumdb.sgml b/doc/src/sgml/ref/vacuumdb.sgml index 0eecf8f3c71..84583e9aea3 100644 --- a/doc/src/sgml/ref/vacuumdb.sgml +++ b/doc/src/sgml/ref/vacuumdb.sgml @@ -1,5 +1,5 @@ @@ -18,12 +18,12 @@ PostgreSQL documentation vacuumdb - connection-options + connection-option --full-f --verbose-v --analyze-z - --table | -t 'table - ( column [,...] )' + --table | -t table + ( column [,...] ) dbname @@ -78,6 +78,16 @@ PostgreSQL documentation vacuumdb accepts the following command-line arguments: + + + + + + Vacuum all databases. + + + + @@ -93,17 +103,6 @@ PostgreSQL documentation - - - - - - Vacuum all databases. - - - - - @@ -146,8 +145,8 @@ PostgreSQL documentation - If you specify columns to vacuum, you probably have to escape the parentheses - from the shell. + If you specify columns, you probably have to escape the parentheses + from the shell. (See examples below.) @@ -187,7 +186,7 @@ PostgreSQL documentation Specifies the host name of the machine on which the server - is running. If host begins with a slash, it is used + is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. @@ -198,7 +197,7 @@ PostgreSQL documentation - Specifies the Internet TCP/IP port or local Unix domain socket file + Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. @@ -272,7 +271,7 @@ PostgreSQL documentation - Default connection parameters. + Default connection parameters diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 3486ca41823..84221dc2157 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,5 +1,5 @@ @@ -135,27 +135,6 @@ PostgreSQL Reference Manual - - PostgreSQL Client Applications @@ -182,9 +161,9 @@ Disable this chapter until we have more functions documented. &pgDump; &pgDumpall; &pgRestore; - &psqlRef; &pgTclSh; &pgTkSh; + &psqlRef; &vacuumdb; @@ -205,8 +184,8 @@ Disable this chapter until we have more functions documented. &initdb; &initlocation; &ipcclean; - &pgCtl; &pgControldata; + &pgCtl; &pgResetxlog; &postgres; &postmaster; diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml index 0f53895fdcd..8a0c9915275 100644 --- a/doc/src/sgml/runtime.sgml +++ b/doc/src/sgml/runtime.sgml @@ -1,5 +1,5 @@ @@ -22,7 +22,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.172 2003/03/20 04:51:44 mo it is advisable to run PostgreSQL under a separate user account. This user account should only own the data that is managed by the server, and should not be shared with other - daemons. (For example, using the user nobody is a bad + daemons. (For example, using the user nobody is a bad idea.) It is not advisable to install executables owned by this user because compromised systems could then modify their own binaries. @@ -83,11 +83,9 @@ $Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.172 2003/03/20 04:51:44 mo - - PGDATA - As an alternative to the option, you can set the environment variable PGDATA. + PGDATA @@ -128,16 +126,14 @@ postgres$ initdb -D /usr/local/pgsql/data password to the database superuser. After initdb, modify the pg_hba.conf file to use md5 or password instead of trust authentication - before you start the server for the first time. (Other, + before you start the server for the first time. (Other approaches include using ident authentication or file system permissions to restrict connections. See for more information.) - locale - LC_COLLATE - initdb also initializes the default locale for + initdb also initializes the default localelocale for the database cluster. Normally, it will just take the locale settings in the environment and apply them to the initialized database. It is possible to specify a different locale for the @@ -168,12 +164,10 @@ set to "C". For more information see the Administrator's Guide. Starting the Database Server - - postmaster - Before anyone can access the database, you must start the database - server. The database server is called - postmaster. The postmaster must know where to + server. The database server program is called + postmaster.postmaster + The postmaster must know where to find the data it is supposed to use. This is done with the option. Thus, the simplest way to start the server is: @@ -188,7 +182,7 @@ $ postmaster -D /usr/local/pgsql/data - To start the postmaster in the + To start the postmaster in the background, use the usual shell syntax: $ postmaster -D /usr/local/pgsql/data > logfile 2>&1 & @@ -201,29 +195,26 @@ $ postmaster -D /usr/local/pgsql/data > logfile 2>&1 &< - - TCP/IP - - The postmaster also takes a number of other command line options. For - more information, see the reference page and below. In particular, in order for the - server to accept TCP/IP connections (rather than just Unix domain - socket ones), you must specify the option. + The postmaster also takes a number of other + command line options. For more information, see the reference page + and below. In particular, in order + for the server to accept + TCP/IPTCP/IP connections + (rather than just Unix-domain socket ones), you must specify the + option. - - pg_ctl - This shell syntax can get tedious quickly. Therefore the shell - script wrapper pg_ctl is provided to - simplify some tasks. For example: + script wrapper + pg_ctlpg_ctl + is provided to simplify some tasks. For example: pg_ctl start -l logfile will start the server in the background and put the output into the named log file. The option has the same meaning - here as in the postmaster. pg_ctl is also + here as in the postmaster. pg_ctl is also capable of stopping the server. @@ -232,7 +223,7 @@ pg_ctl start -l logfile computer boots. Autostart scripts are operating system-specific. There are a few distributed with PostgreSQL in the - /contrib/start-scripts directory. This may require root + contrib/start-scripts directory. This may require root privileges. @@ -305,14 +296,14 @@ fi On Solaris, create a file called - /etc/init.d/postgresql which should contain + /etc/init.d/postgresql that contains the following line: Solaris su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data" Then, create a symbolic link to it in /etc/rc3.d as - S99postgresql. + S99postgresql. @@ -320,44 +311,47 @@ su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgs - While the postmaster is running, its - PID is in the file + While the postmaster is running, its + PID is stored in the file postmaster.pid in the data directory. This is - used to prevent multiple postmasters running in the same data - directory, and can also be used for shutting down the postmaster. + used to prevent multiple postmaster processes + running in the same data directory and can also be used for + shutting down the postmaster process. Server Start-up Failures - There are several common reasons the postmaster might fail to - start. Check the postmaster's log file, or start it by hand - (without redirecting standard output or standard error) and see - what error messages appear. Some of the error messages are - self-explanatory, but some are not, as shown below: + There are several common reasons the server might fail to + start. Check the server's log file, or start it by hand (without + redirecting standard output or standard error) and see what error + messages appear. Below we explain some of the most common error + messages in more detail. FATAL: StreamServerPort: bind() failed: Address already in use - Is another postmaster already running on that port? + Is another postmaster already running on port 5432? + If not, wait a few seconds an retry. This usually means just what it suggests: you tried to start - another postmaster on the same port where one is already running. + another postmaster on the same port where one is already running. However, if the kernel error message is not Address already in use or some variant of that, there may - be a different problem. For example, trying to start a postmaster + be a different problem. For example, trying to start a postmaster on a reserved port number may draw something like: $ postmaster -i -p 666 FATAL: StreamServerPort: bind() failed: Permission denied - Is another postmaster already running on that port? + Is another postmaster already running on port 666? + If not, wait a few seconds an retry. - A message like: + A message like IpcMemoryCreate: shmget(key=5440001, size=83918612, 01600) failed: Invalid argument FATAL 1: ShmemCreate: cannot create region @@ -367,16 +361,16 @@ FATAL 1: ShmemCreate: cannot create region is trying to create (83918612 bytes in this example). Or it could mean that you do not have System-V-style shared memory support configured into your kernel at all. As a temporary workaround, you - can try starting the postmaster with a smaller-than-normal number + can try starting the server with a smaller-than-normal number of buffers ( switch). You will eventually want to reconfigure your kernel to increase the allowed shared memory - size. You may see this message when trying to start multiple - postmasters on the same machine if their total space requested + size. You may also see this message when trying to start multiple + servers on the same machine if their total space requested exceeds the kernel limit. - An error like: + An error like IpcSemaphoreCreate: semget(key=5440026, num=16, 01600) failed: No space left on device @@ -385,7 +379,7 @@ IpcSemaphoreCreate: semget(key=5440026, num=16, 01600) failed: No space left on class="osname">System V semaphores is smaller than the number PostgreSQL wants to create. As above, you may be able to work around the problem by starting the - postmaster with a reduced number of allowed connections + server with a reduced number of allowed connections ( switch), but you'll eventually want to increase the kernel limit. @@ -422,14 +416,13 @@ psql: could not connect to server: Connection refused This is the generic I couldn't find a server to talk to failure. It looks like the above when TCP/IP - communication is attempted. A common mistake is to forget the - option to allow the postmaster to accept TCP/IP - connections. + communication is attempted. A common mistake is to forget to + configure the server to allow TCP/IP connections. - Alternatively, you'll get this when attempting Unix-socket - communication to a local postmaster: + Alternatively, you'll get this when attempting Unix-domain socket + communication to a local server: psql: could not connect to server: Connection refused Is the server running locally and accepting @@ -439,14 +432,14 @@ psql: could not connect to server: Connection refused The last line is useful in verifying that the client is trying to - connect to the right place. If there is in fact no postmaster + connect to the right place. If there is in fact no server running there, the kernel error message will typically be either Connection refused or No such file or directory, as illustrated. (It is important to realize that Connection refused in this context - does not mean that the postmaster got your - connection request and rejected it -- that case will produce a + does not mean that the server got your + connection request and rejected it. That case will produce a different message, as shown in .) Other error messages such as Connection timed out may @@ -493,7 +486,7 @@ search_path = '$user, public' As you see, options are one per line. The equal sign between name and value is optional. Whitespace is insignificant and blank lines - are ignored. Hash marks (#) introduce comments + are ignored. Hash marks (#) introduce comments anywhere. Parameter values that are not simple identifiers or numbers should be single-quoted. @@ -502,28 +495,27 @@ search_path = '$user, public' SIGHUP - The configuration file is reread whenever the postmaster receives a + The configuration file is reread whenever the postmaster process receives a SIGHUP signal (which is most easily sent by means of - pg_ctl reload). The postmaster also propagates this - signal to all currently running backend processes so that existing + pg_ctl reload). The postmaster also propagates this + signal to all currently running server processes so that existing sessions also get the new value. Alternatively, you can send the - signal to a single backend process directly. + signal to a single server process directly. A second way to set these configuration parameters is to give them - as a command line option to the postmaster, such as: + as a command line option to the postmaster, such as: postmaster -c log_connections=yes -c syslog=2 - which would have the same effect as the previous example. Command-line options override any conflicting settings in postgresql.conf. Occasionally it is also useful to give a command line option to - one particular backend session only. The environment variable + one particular session only. The environment variable PGOPTIONS can be used for this purpose on the client side: @@ -539,7 +531,7 @@ env PGOPTIONS='-c geqo=off' psql Some options can be changed in individual SQL sessions with the SET command, for example: -=> SET ENABLE_SEQSCAN TO OFF; +SET ENABLE_SEQSCAN TO OFF; See the SQL command language reference for details on the syntax. @@ -549,22 +541,21 @@ env PGOPTIONS='-c geqo=off' psql a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The commands ALTER DATABASE and ALTER - USER, respectively, are used to configure these settings. - Such per-database settings override anything received from the postmaster - or the configuration file, and in turn are overridden by per-user + USER, respectively, are used to configure these + settings. Such per-database settings override anything received + from the postmaster command-line or the + configuration file, and in turn are overridden by per-user settings. - - pg_settings - - - The pg_settings virtual table allows display and update - of current session run-time parameters. There is one entry for each of the - available parameters provided by SHOW ALL. But it is - in a form that allows it to be joined with other relations and have a - selection criteria applied. - + + The virtual table pg_settings allows + displaying and updating session run-time parameters. It contains one + row for each configuration parameter; the columns are shown in + . This form allows the + configuration data to be joined with other tables and have a + selection criteria applied. + An UPDATE performed on pg_settings @@ -577,14 +568,14 @@ env PGOPTIONS='-c geqo=off' psql overridden by another UPDATE or SET. -
- pg_settings Columns +
+ <literal>pg_settings</> Columns Name - Type + Data Type Description @@ -593,30 +584,27 @@ env PGOPTIONS='-c geqo=off' psql name text - The name of a current session run-time parameter + The name of the run-time configuration parameter setting text - The value of a current session run-time parameter + The current value of the run-time configuration parameter
- - Planner and Optimizer Tuning - CPU_INDEX_TUPLE_COST (floating point) - Sets the query optimizer's estimate of the cost of processing + Sets the query planner's estimate of the cost of processing each index tuple during an index scan. This is measured as a fraction of the cost of a sequential page fetch. @@ -627,7 +615,7 @@ env PGOPTIONS='-c geqo=off' psql CPU_OPERATOR_COST (floating point) - Sets the optimizer's estimate of the cost of processing each + Sets the planner's estimate of the cost of processing each operator in a WHERE clause. This is measured as a fraction of the cost of a sequential page fetch. @@ -638,7 +626,7 @@ env PGOPTIONS='-c geqo=off' psql CPU_TUPLE_COST (floating point) - Sets the query optimizer's estimate of the cost of processing + Sets the query planner's estimate of the cost of processing each tuple during a query. This is measured as a fraction of the cost of a sequential page fetch. @@ -662,7 +650,7 @@ env PGOPTIONS='-c geqo=off' psql EFFECTIVE_CACHE_SIZE (floating point) - Sets the optimizer's assumption about the effective size of the + Sets the planner's assumption about the effective size of the disk cache (that is, the portion of the kernel's disk cache that will be used for PostgreSQL data files). This is measured in disk pages, which are normally 8 kB @@ -818,7 +806,7 @@ env PGOPTIONS='-c geqo=off' psql algorithm: The pool size is the number of individuals in one population. Valid values are between 128 and 1024. If it is set to 0 (the default) a pool size of 2^(QS+1), where QS is the - number of FROM items in the query, is taken. The effort is used + number of FROM items in the query, is taken. The effort is used to calculate a default for generations. Valid values are between 1 and 80, 40 being the default. Generations specifies the number of iterations in the algorithm. The number must be a positive @@ -868,7 +856,7 @@ env PGOPTIONS='-c geqo=off' psql RANDOM_PAGE_COST (floating point) - Sets the query optimizer's estimate of the cost of a + Sets the query planner's estimate of the cost of a nonsequentially fetched disk page. This is measured as a multiple of the cost of a sequential page fetch. A higher value makes it more likely a sequential scan will be used, @@ -877,12 +865,11 @@ env PGOPTIONS='-c geqo=off' psql - Unfortunately, there is no well-defined method for determining - ideal values for the family of COST variables that + ideal values for the family of cost variables that were just described. You are encouraged to experiment and share your findings. @@ -893,24 +880,24 @@ env PGOPTIONS='-c geqo=off' psql Logging and Debugging - CLIENT_MIN_MESSAGES (string) - This controls how much message detail is written to the + This controls which message levels are send to the client. client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, - WARNING, and ERROR. Later values send - less information to the client. The default is + WARNING, and ERROR. Each level + includes all the levels that follow it. The later the level, + the fewer messages are sent. The default is NOTICE. Note that LOG has a different - precedence here than in LOG_MIN_MESSAGES. + rank here than in LOG_MIN_MESSAGES. - Here is a summary of the various message types: + Here is a list of the various message types: DEBUG[1-5] @@ -936,7 +923,7 @@ env PGOPTIONS='-c geqo=off' psql Provides information that may be helpful to users, e.g., - truncation of long identifiers and index creation as part + truncation of long identifiers and the creation of indexes as part of primary keys. @@ -947,7 +934,7 @@ env PGOPTIONS='-c geqo=off' psql Provides warnings to the user, e.g., COMMIT - outside a transaction. + outside a transaction block. @@ -956,7 +943,7 @@ env PGOPTIONS='-c geqo=off' psql ERROR - Reports the error that caused a transaction to abort. + Reports an error that caused the current transaction to abort. @@ -975,7 +962,7 @@ env PGOPTIONS='-c geqo=off' psql FATAL - Reports why a backend session terminated. + Reports an error that caused the current session to abort. @@ -984,7 +971,7 @@ env PGOPTIONS='-c geqo=off' psql PANIC - Reports why all backend sessions restarted. + Reports an error that caused all sessions to abort. @@ -1018,14 +1005,15 @@ env PGOPTIONS='-c geqo=off' psql DEBUG_PRETTY_PRINT (boolean) - These flags enable various debugging output to be sent to the - server log. For each executed query, print the resulting parse + These options enable various debugging output to be sent to the + server log. For each executed query, they print the resulting parse tree, the query rewriter output, or the execution plan. indents these displays to produce a more readable but much longer output format. + EXPLAIN_PRETTY_PRINT (boolean) @@ -1106,16 +1094,17 @@ env PGOPTIONS='-c geqo=off' psql LOG_MIN_MESSAGES (string) - Controls how much message detail is written to the server - logs. Valid values are DEBUG5, - DEBUG4, DEBUG3, DEBUG2, - DEBUG1, INFO, NOTICE, - WARNING, ERROR, LOG, - FATAL, and PANIC. Later values send - less detail to the logs. The default is NOTICE. - Note that LOG has a different precedence here than - in CLIENT_MIN_MESSAGES. Also see that section for - an explanation of the various values. + This controls which message levels are written to the server + log. Valid values are DEBUG5, DEBUG4, + DEBUG3, DEBUG2, DEBUG1, + INFO, NOTICE, WARNING, + ERROR, LOG, FATAL, and + PANIC. Each level includes all the levels that + follow it. The later the level, the fewer messages are sent + to the log. The default is NOTICE. Note that + LOG has a different rank here than in + CLIENT_MIN_MESSAGES. Also see that section for an + explanation of the various values. @@ -1125,8 +1114,8 @@ env PGOPTIONS='-c geqo=off' psql LOG_PID (boolean) - Prefixes each server message in the log file with the process ID of - the backend process. This is useful to sort out which messages + Prefixes each message in the server log file with the process ID of + the server process. This is useful to sort out which messages pertain to which connection. The default is off. This parameter does not affect messages logged via syslog, which always contain the process ID. @@ -1185,8 +1174,8 @@ env PGOPTIONS='-c geqo=off' psql Enables the collection of statistics on the currently - executing command of each backend, along with the time at - which that query began execution. This option is off by + executing command of each session, along with the time at + which that command began execution. This option is off by default. Note that even when enabled, this information is only visible to the superuser, so it should not represent a security risk. This data can be accessed via the @@ -1201,7 +1190,7 @@ env PGOPTIONS='-c geqo=off' psql STATS_ROW_LEVEL (boolean) - Enables the collection of block-level and row-level statistics + These enable the collection of block-level and row-level statistics on database activity, respectively. These options are off by default. This data can be accessed via the pg_stat and @@ -1293,13 +1282,11 @@ env PGOPTIONS='-c geqo=off' psql - General Operation - AUTOCOMMIT (boolean) @@ -1356,10 +1343,11 @@ env PGOPTIONS='-c geqo=off' psql Australian time zones - If set to true, CST, EST, - and SAT are interpreted as Australian - time zones rather than as North American Central/Eastern - time zones and Saturday. The default is false. + If set to true, ACST, + CST, EST, and + SAT are interpreted as Australian time + zones rather than as North/South American time zones and + Saturday. The default is false. @@ -1384,7 +1372,7 @@ env PGOPTIONS='-c geqo=off' psql character set encoding - Sets the client-side encoding for multibyte character sets. + Sets the client-side encoding (character set). The default is to use the database encoding. @@ -1395,8 +1383,8 @@ env PGOPTIONS='-c geqo=off' psql date style - Sets the display format for dates, as well as the rules for - interpreting ambiguous input dates. + Sets the display format for date and time values, as well as the rules for + interpreting ambiguous date input values. The default is ISO, US. @@ -1410,7 +1398,7 @@ env PGOPTIONS='-c geqo=off' psql - If this is on, create users as username@dbname. + If this is on, you should create users as username@dbname. When username is passed by a connecting client, @ and the database name is appended to the user name and that database-specific user name is looked up by the @@ -1454,14 +1442,14 @@ env PGOPTIONS='-c geqo=off' psql check for deadlock is relatively slow, so the server doesn't run it every time it waits for a lock. We (optimistically?) assume that deadlocks are not common in production applications and - just wait on the lock for a while before starting check for a + just wait on the lock for a while before starting the check for a deadlock. Increasing this value reduces the amount of time wasted in needless deadlock checks, but slows down reporting of real deadlock errors. The default is 1000 (i.e., one second), which is probably about the smallest value you would want in practice. On a heavily loaded server you might want to raise it. Ideally the setting should exceed your typical transaction time, - so as to improve the odds that the lock will be released before + so as to improve the odds that a lock will be released before the waiter decides to check for deadlock. @@ -1504,7 +1492,7 @@ env PGOPTIONS='-c geqo=off' psql - The value for dynamic_library_path has to be a colon-separated + The value for DYNAMIC_LIBRARY_PATH has to be a colon-separated list of absolute directory names. If a directory name starts with the special value $libdir, the compiled-in PostgreSQL package @@ -1577,10 +1565,10 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' FSYNC (boolean) - If this option is on, the PostgreSQL backend + If this option is on, the PostgreSQL server will use the fsync() system call in several places to make sure that updates are physically written to disk. This - insures that a database installation will recover to a + insures that a database cluster will recover to a consistent state after an operating system or hardware crash. (Crashes of the database server itself are not related to this.) @@ -1599,20 +1587,22 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' - For the above reasons, some administrators always leave it off, - some turn it off only for bulk loads, where there is a clear - restart point if something goes wrong, and some leave it on just - to be on the safe side. Because it is always safe, the default - is on. If you trust your operating system, your hardware, and - your utility company (or better your UPS), you might want to - disable fsync. + For the above reasons, everyone can decide for himself what to + do with the fsync option. Some administrators + always leave it off, some turn it off only for bulk loads, + where there is a clear restart point if something goes wrong, + and some leave it on just to be on the safe side. The default + is on so that you are on the safe side. If you trust your + operating system, your hardware, and your utility company (or + better your battery backup), you can consider disabling + fsync. - It should be noted that the performance penalty of doing - fsyncs is considerably less in + It should be noted that the performance penalty of having + fsync on considerably less in PostgreSQL version 7.1 and later. If you - previously suppressed fsyncs for performance + previously suppressed fsync for performance reasons, you may wish to reconsider your choice. @@ -1649,7 +1639,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' Sets the locale to use for formatting monetary amounts, for - example with the to_char() family of + example with the to_char family of functions. Acceptable values are system-dependent; see for more information. If this variable is set to the empty string (which is the default) then the value @@ -1708,7 +1698,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' Sets the maximum expression nesting depth of the parser. The default value is high enough for any normal query, but you can raise it if needed. (But if you raise it too high, you run - the risk of backend crashes due to stack overflow.) + the risk of server crashes due to stack overflow.) @@ -1717,7 +1707,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' MAX_FILES_PER_PROCESS (integer) - Sets the maximum number of simultaneously open files in each + Sets the maximum number of simultaneously open files allowed to each server subprocess. The default is 1000. The limit actually used by the code is the smaller of this setting and the result of sysconf(_SC_OPEN_MAX). Therefore, on systems @@ -1782,7 +1772,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' When a password is specified in CREATE USER or ALTER USER without writing either ENCRYPTED or - UNENCRYPTED, this flag determines whether the password is to be + UNENCRYPTED, this option determines whether the password is to be encrypted. The default is on (encrypt the password). @@ -1845,7 +1835,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' SEARCH_PATH (string) search_path - namespaces + pathfor schemas This variable specifies the order in which schemas are searched @@ -1861,7 +1851,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' The value for search_path has to be a comma-separated list of schema names. If one of the list items is the special value $user, then the schema - having the same name as the SESSION_USER is substituted, if there + having the name returned by SESSION_USER is substituted, if there is such a schema. (If not, $user is ignored.) @@ -1895,10 +1885,6 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' - - schemas - current schema - The current effective value of the search path can be examined via the SQL function current_schemas(). This is not quite the same as examining the value of @@ -1948,12 +1934,12 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' - SILENT_MODE (bool) + SILENT_MODE (boolean) Runs the server silently. If this option is set, the server - will automatically run in background and any controlling ttys - are disassociated, thus no messages are written to standard + will automatically run in background and any controlling terminals + are disassociated. Thus, no messages are written to standard output or standard error (same effect as postmaster's option). Unless some logging system such as syslog is enabled, using this option is @@ -1966,24 +1952,24 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' SORT_MEM (integer) - Specifies the amount of memory to be used by internal sorts and + Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files. The value is specified in kilobytes, and defaults to 1024 kilobytes (1 MB). - Note that for a complex query, several sorts or hashes might be + Note that for a complex query, several sort or hash operations might be running in parallel; each one will be allowed to use as much memory as this value specifies before it starts to put data into temporary - files. Also, each running backend could be doing one or more - sorts simultaneously, so the total memory used could be many - times the value of SORT_MEM. Sorts are used + files. Also, several running sessions could be doing + sort operations simultaneously. So the total memory used could be many + times the value of SORT_MEM. Sort operations are used by ORDER BY, merge joins, and CREATE INDEX. Hash tables are used in hash joins, hash-based aggregation, and - hash-based processing of IN sub-selects. + hash-based processing of IN subqueries. - SQL_INHERITANCE (bool) + SQL_INHERITANCE (boolean) inheritance @@ -1992,7 +1978,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' not included in versions prior to 7.1. If you need the old behavior you can set this variable to off, but in the long run you are encouraged to change your applications to use the - ONLY keyword to exclude subtables. See the + ONLY key word to exclude subtables. See the SQL language reference and the &cite-user; for more information about inheritance. @@ -2024,7 +2010,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' ever be active simultaneously. Whenever the number of active concurrent connections is at least max_connections minus superuser_reserved_connections, new connections - will be accepted only from superuser accounts. + will be accepted only for superusers. @@ -2052,7 +2038,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' time zone - Sets the time zone for displaying and interpreting timestamps. + Sets the time zone for displaying and interpreting time stamps. The default is to use whatever the system environment specifies as the time zone. @@ -2121,7 +2107,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' Sets the group owner of the Unix domain socket. (The owning user of the socket is always the user that starts the server.) In combination with the option - this can be used as + UNIX_SOCKET_PERMISSIONS this can be used as an additional access control mechanism for this socket type. By default this is the empty string, which uses the default group for the current user. This option can only be set at @@ -2147,7 +2133,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' The default permissions are 0777, meaning anyone can connect. Reasonable alternatives are 0770 (only user and group, see also under - ) and 0700 + UNIX_SOCKET_GROUP) and 0700 (only user). (Note that actually for a Unix domain socket, only write permission matters and there is no point in setting or revoking read or execute permissions.) @@ -2181,17 +2167,15 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' VIRTUAL_HOST (string) - Specifies the TCP/IP host name or address on which the - postmaster is to listen for - connections from client applications. Defaults to listening on - all configured addresses (including localhost). + Specifies the host name or IP address on which the server is + to listen for connections from client applications. The + default is to listening on all configured addresses (including + localhost). - @@ -2248,7 +2232,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' the given interval. But the delay is just wasted if no other transactions become ready to commit. Therefore, the delay is only performed if at least COMMIT_SIBLINGS other transactions - are active at the instant that a backend process has written its commit + are active at the instant that a server process has written its commit record. @@ -2281,8 +2265,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' WAL_DEBUG (integer) - If nonzero, turn on WAL-related debugging output on standard - error. + If nonzero, turn on WAL-related debugging output to the server log. @@ -2295,7 +2278,7 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' values are FSYNC (call fsync() at each commit), FDATASYNC (call fdatasync() at each commit), - OPEN_SYNC (write WAL files with open() option O_SYNC), or + OPEN_SYNC (write WAL files with open() option O_SYNC), and OPEN_DATASYNC (write WAL files with open() option O_DSYNC). Not all of these choices are available on all platforms. This option can only be set at server start or in the @@ -2312,8 +2295,8 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' Short Options - For convenience there are also single letter option switches - available for many parameters. They are described in . @@ -2373,8 +2356,8 @@ dynamic_library_path = '/usr/local/lib/postgresql:/home/my_project/lib:$libdir' id="fn.runtime-config-short"> For historical reasons, these options must be passed to - the individual backend process via the - postmaster option, for example, + the individual server process via the + postmaster option, for example, $ postmaster -o '-S 1024 -s' @@ -2454,14 +2437,14 @@ $ postmaster -o '-S 1024 -s' The complete lack of these facilities is usually manifested by an - Illegal system call error upon postmaster start. In + Illegal system call error upon server start. In that case there's nothing left to do but to reconfigure your - kernel -- PostgreSQL won't work without them. + kernel. PostgreSQL won't work without them. When PostgreSQL exceeds one of the various hard - IPC limits, the postmaster will refuse to start and + IPC limits, the server will refuse to start and should leave an instructive error message describing the problem encountered and what to do about it. (See also .) The relevant kernel @@ -2489,7 +2472,7 @@ $ postmaster -o '-S 1024 -s' SHMMAX Maximum size of shared memory segment (bytes) - 250kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections or infinity + 250 kB + 8.2 kB * shared_buffers + 14.2 kB * max_connections up to infinity @@ -2519,19 +2502,19 @@ $ postmaster -o '-S 1024 -s' SEMMNI Maximum number of semaphore identifiers (i.e., sets) - >= ceil(max_connections / 16) + at least ceil(max_connections / 16) SEMMNS Maximum number of semaphores system-wide - ceil(max_connections / 16) * 17 + room for other applications + ceil(max_connections / 16) * 17 plus room for other applications SEMMSL Maximum number of semaphores per set - >= 17 + at least 17 @@ -2543,7 +2526,7 @@ $ postmaster -o '-S 1024 -s' SEMVMX Maximum value of semaphore - >= 255 (The default is often 32767, don't change unless asked to.) + at least 255 (The default is often 32767, don't change unless asked to.) @@ -2593,8 +2576,8 @@ $ postmaster -o '-S 1024 -s' exist on the system at one time. Hence this parameter must be at least ceil(max_connections / 16). Lowering the number of allowed connections is a temporary workaround for failures, - which are usually confusingly worded No space - left on device, from the function semget(). + which are usually confusingly worded No space + left on device, from the function semget. @@ -2622,8 +2605,6 @@ $ postmaster -o '-S 1024 -s' for PostgreSQL. - - @@ -2635,8 +2616,8 @@ $ postmaster -o '-S 1024 -s' By default, only 4 MB of shared memory is supported. Keep in mind that shared memory is not pageable; it is locked in RAM. - To increase the number of shared buffers supported by the - postmaster, add the following to your kernel configuration + To increase the amount of shared memory supported by your + system, add the following to your kernel configuration file. A SHMALL value of 1024 represents 4 MB of shared memory. The following increases the maximum shared memory area to 32 MB: @@ -2644,22 +2625,22 @@ $ postmaster -o '-S 1024 -s' options "SHMALL=8192" options "SHMMAX=\(SHMALL*PAGE_SIZE\)" + For those running 4.1 or later, just make the above changes, + recompile the kernel, and reboot. - For those running 4.1 or later, just make the above changes, - recompile the kernel, and reboot. For those running earlier - releases, use bpatch to find the - sysptsize value in the current kernel. This is - computed dynamically at boot time. + For those running earlier releases, use bpatch to + find the sysptsize value in the current + kernel. This is computed dynamically at boot time. $ bpatch -r sysptsize 0x9 = 9 Next, add SYSPTSIZE as a hard-coded value in the kernel configuration file. Increase the value you found using - bpatch. Add 1 for every additional 4 MB of + bpatch. Add 1 for every additional 4 MB of shared memory you desire. options "SYSPTSIZE=16" @@ -2670,21 +2651,16 @@ options "SYSPTSIZE=16" Semaphores</> <para> - You may need to increase the number of semaphores. By default, - <productname>PostgreSQL</> allocates 34 semaphores, which is - over half the default system total of 60. - </para> - </formalpara> - - <para> - Set the values you want in your kernel configuration file, e.g.: + You may need to increase the number of semaphores. By + default, <productname>PostgreSQL</> allocates 34 semaphores, + which is over half the default system total of 60. Set the + values you want in your kernel configuration file, e.g.: <programlisting> options "SEMMNI=40" options "SEMMNS=240" -options "SEMUME=40" -options "SEMMNU=120" </programlisting> - </para> + </para> + </formalpara> </listitem> </varlistentry> @@ -2719,7 +2695,7 @@ options SEMMAP=256 <literal>option</literal> singular.) </para> <para> - You might also want to use the <application>sysctl</> setting to + You might also want to use the <command>sysctl</> setting to lock shared memory into RAM and prevent it from being paged out to swap, e.g. <literal>kern.ipc.shm_use_phys</>. </para> @@ -2766,18 +2742,16 @@ options SEMMAP=256 </para> <para> - Alternatively, you can use - <citerefentry><refentrytitle>sysctl</refentrytitle> - <manvolnum>8</manvolnum></citerefentry>, if available, to - control these parameters. Look for a file called - <filename>/etc/sysctl.conf</filename> and add lines like the - following to it: + Alternatively, you can use <command>sysctl</command>, if + available, to control these parameters. Look for a file + called <filename>/etc/sysctl.conf</filename> and add lines + like the following to it: <programlisting> kernel.shmall = 134217728 kernel.shmmax = 134217728 </programlisting> This file is usually processed at boot time, but - <application>sysctl</application> can also be called + <command>sysctl</command> can also be called explicitly later. </para> @@ -2806,8 +2780,6 @@ sysctl -w kern.sysv.shmmni sysctl -w kern.sysv.shmseg sysctl -w kern.sysv.shmall </programlisting> - These values have the same meanings on <productname>MacOS</> X - as those listed for previous operating systems. </para> </listitem> </varlistentry> @@ -2820,18 +2792,18 @@ sysctl -w kern.sysv.shmall <para> In the default configuration, only 512 kB of shared memory per segment is allowed, which is about enough for <option>-B 24 -N - 12</>. To increase the setting, first change directory to + 12</>. To increase the setting, first change to the directory <filename>/etc/conf/cf.d</>. To display the current value of - <varname>SHMMAX</>, in bytes, run + <varname>SHMMAX</>, run <programlisting> ./configure -y SHMMAX </programlisting> - To set a new value for <varname>SHMMAX</>, run: + To set a new value for <varname>SHMMAX</>, run <programlisting> ./configure SHMMAX=<replaceable>value</> </programlisting> where <replaceable>value</> is the new value you want to use - (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel + (in bytes). After setting <varname>SHMMAX</>, rebuild the kernel: <programlisting> ./link_unix </programlisting> @@ -2887,14 +2859,14 @@ set semsys:seminfo_semmsl=32 /etc/conf/bin/idtune -g SHMMAX </programlisting> which displays the current, default, minimum, and maximum - values, in bytes. To set a new value for <varname>SHMMAX</>, - run: + values. To set a new value for <varname>SHMMAX</>, + run <programlisting> /etc/conf/bin/idtune SHMMAX <replaceable>value</> </programlisting> where <replaceable>value</> is the new value you want to use (in bytes). After setting <varname>SHMMAX</>, rebuild the - kernel + kernel: <programlisting> /etc/conf/bin/idbuild -B </programlisting> @@ -2905,7 +2877,6 @@ set semsys:seminfo_semmsl=32 </variablelist> - </para> </sect2> @@ -2927,9 +2898,8 @@ set semsys:seminfo_semmsl=32 (Bourne shells) or <command>limit</command> (<application>csh</>) is used to control the resource limits from the command line. On BSD-derived systems the file <filename>/etc/login.conf</filename> - controls the various resource limits set during login. See - <citerefentry><refentrytitle>login.conf</refentrytitle> - <manvolnum>5</manvolnum></citerefentry> for details. The relevant + controls the various resource limits set during login. See the + operating system documentation for details. The relevant parameters are <varname>maxproc</varname>, <varname>openfiles</varname>, and <varname>datasize</varname>. For example: @@ -3007,10 +2977,10 @@ default:\ <term><systemitem>SIGTERM</systemitem></term> <listitem> <para> - After receiving <systemitem>SIGTERM</systemitem>, the postmaster - disallows new connections, but lets existing backends end their - work normally. It shuts down only after all of the backends - terminate normally. This is <firstterm>Smart + After receiving <systemitem>SIGTERM</systemitem>, the server + disallows new connections, but lets existing sessions end their + work normally. It shuts down only after all of the sessions + terminate normally. This is the <firstterm>Smart Shutdown</firstterm>. </para> </listitem> @@ -3020,10 +2990,10 @@ default:\ <term><systemitem>SIGINT</systemitem></term> <listitem> <para> - The postmaster disallows new connections and sends all existing - backends <systemitem>SIGTERM</systemitem>, which will cause them + The server disallows new connections and sends all existing + server processes <systemitem>SIGTERM</systemitem>, which will cause them to abort their current transactions and exit promptly. It then - waits for the backends to exit and finally shuts down. This is + waits for the server processes to exit and finally shuts down. This is the <firstterm>Fast Shutdown</firstterm>. </para> </listitem> @@ -3032,10 +3002,11 @@ default:\ <varlistentry> <term><systemitem>SIGQUIT</systemitem></term> <listitem> - <para> This is <firstterm>Immediate Shutdown</firstterm>, which - will cause the postmaster to send a - <systemitem>SIGQUIT</systemitem> to all backends and exit - immediately (without properly shutting itself down). The backends + <para> + This is the <firstterm>Immediate Shutdown</firstterm>, which + will cause the <command>postmaster</command> process to send a + <systemitem>SIGQUIT</systemitem> to all child processes and exit + immediately (without properly shutting itself down). The child processes likewise exit immediately upon receiving <systemitem>SIGQUIT</systemitem>. This will lead to recovery (by replaying the WAL log) upon next start-up. This is recommended @@ -3044,18 +3015,20 @@ default:\ </listitem> </varlistentry> </variablelist> + </para> <important> <para> It is best not to use <systemitem>SIGKILL</systemitem> to shut down - the postmaster. This will prevent the postmaster from releasing + the server. This will prevent the server from releasing shared memory and semaphores, which may then have to be done by manually. </para> </important> - The <acronym>PID</> of the postmaster process can be found using the - <application>ps</application> program, or from the file + <para> + The <acronym>PID</> of the <command>postmaster</command> process can be found using the + <command>ps</command> program, or from the file <filename>postmaster.pid</filename> in the data directory. So for example, to do a fast shutdown: <screen> @@ -3063,9 +3036,9 @@ $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput </screen> </para> <para> - The program <application>pg_ctl</application> is a shell script + The program <command>pg_ctl</command> is a shell script that provides a more convenient interface for shutting down the - postmaster. + server. </para> </sect1> @@ -3079,21 +3052,21 @@ $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput <para> <productname>PostgreSQL</> has native support for using <acronym>SSL</> connections to encrypt client/server communications - for increased security. This requires - <productname>OpenSSL</productname> be installed on both client and - server systems and support enabled at build time (see <xref - linkend="installation">). + for increased security. This requires that + <productname>OpenSSL</productname> is installed on both client and + server systems and that support in <productname>PostgreSQL</> is + enabled at build time (see <xref linkend="installation">). </para> <para> With <acronym>SSL</> support compiled in, the <productname>PostgreSQL</> server can be started with - <acronym>SSL</> support by setting the parameter + <acronym>SSL</> enabled by setting the parameter <varname>ssl</varname> to on in <filename>postgresql.conf</>. When starting in <acronym>SSL</> mode, the server will look for the files <filename>server.key</> and <filename>server.crt</> in the - data directory. These files should contain the server private key - and certificate respectively. These files must be set up correctly + data directory, which should contain the server private key + and certificate, respectively. These files must be set up correctly before an <acronym>SSL</>-enabled server can start. If the private key is protected with a passphrase, the server will prompt for the passphrase and will not start until it has been entered. @@ -3101,7 +3074,7 @@ $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput <para> The server will listen for both standard and <acronym>SSL</> - connections on the same TCP/IP port, and will negotiate with any + connections on the same TCP port, and will negotiate with any connecting client on whether to use <acronym>SSL</>. See <xref linkend="client-authentication"> about how to force the server to require use of <acronym>SSL</> for certain connections. @@ -3120,8 +3093,8 @@ $ <userinput>kill -INT `head -1 /usr/local/pgsql/data/postmaster.pid`</userinput openssl req -new -text -out server.req </programlisting> Fill out the information that <command>openssl</> asks for. Make sure - that you enter the local host name as Common Name; the challenge - password can be left blank. The script will generate a key that is + that you enter the local host name as <quote>Common Name</>; the challenge + password can be left blank. The programm will generate a key that is passphrase protected; it will not accept a passphrase that is less than four characters long. To remove the passphrase (as you must if you want automatic start-up of the server), run the commands @@ -3146,15 +3119,6 @@ chmod og-rwx server.key <primary>ssh</primary> </indexterm> - <note> - <title>Acknowledgement - - Idea taken from an email by Gene Selkov, Jr. - (selkovjr@mcs.anl.gov) written on 1999-09-08 in response - to a question from Eric Marsden. - - - One can use SSH to encrypt the network connection between clients and a @@ -3164,8 +3128,8 @@ chmod og-rwx server.key First make sure that an SSH server is - running properly on the same machine as - PostgreSQL and that you can log in using + 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: @@ -3173,7 +3137,7 @@ ssh -L 3333:foo.com:5432 joe@foo.com The first number in the argument, 3333, is the port number of your end of the tunnel; it can be chosen freely. The - second number, 5432, is the remote end of the tunnel -- the port + second number, 5432, is the remote end of the tunnel: the port number your server is using. The name or the address in between the port numbers is the host with the database server you are going to connect to. In order to connect to the database server using @@ -3185,7 +3149,7 @@ psql -h localhost -p 3333 template1 user joe@foo.com and it will use whatever authentication procedure was set up for this user. In order for the tunnel setup to succeed you must be allowed to connect via - ssh as joe@foo.com, just + ssh as joe@foo.com, just as if you had attempted to use ssh to set up a terminal session. diff --git a/doc/src/sgml/wal.sgml b/doc/src/sgml/wal.sgml index 2589ab8163b..279364a65d8 100644 --- a/doc/src/sgml/wal.sgml +++ b/doc/src/sgml/wal.sgml @@ -1,38 +1,28 @@ - + Write-Ahead Logging (<acronym>WAL</acronym>) - - Author - Vadim Mikheev and Oliver Elphick - - - - - General Description - - - Write Ahead Logging (WAL) + Write-Ahead Logging (WAL) is a standard approach to transaction logging. Its detailed description may be found in most (if not all) books about transaction processing. Briefly, WAL's central concept is that changes to data files (where tables and indexes - reside) must be written only after those changes have been logged - + reside) must be written only after those changes have been logged, that is, when log records have been flushed to permanent - storage. When we follow this procedure, we do not need to flush + storage. If we follow this procedure, we do not need to flush data pages to disk on every transaction commit, because we know that in the event of a crash we will be able to recover the database using the log: any changes that have not been applied to the data pages will first be redone from the log records (this is roll-forward recovery, also known as REDO) and then changes made by uncommitted transactions will be removed from the data pages - (roll-backward recovery - UNDO). + (roll-backward recovery, UNDO). - - Immediate Benefits of <acronym>WAL</acronym> + + Benefits of <acronym>WAL</acronym> The first obvious benefit of using WAL is a @@ -54,11 +44,11 @@ - index tuples pointing to nonexistent table rows + index rows pointing to nonexistent table rows - index tuples lost in split operations + index rows lost in split operations @@ -74,22 +64,22 @@ page content in the log if that is required to ensure page consistency for after-crash recovery. - + - + Future Benefits - UNDO operation is not implemented. This means that changes + The UNDO operation is not implemented. This means that changes made by aborted transactions will still occupy disk space and that - we still need a permanent pg_clog file to hold - the status of transactions, since we are not able to re-use - transaction identifiers. Once UNDO is implemented, + a permanent pg_clog file to hold + the status of transactions is still needed, since + transaction identifiers cannot be reused. Once UNDO is implemented, pg_clog will no longer be required to be permanent; it will be possible to remove pg_clog at shutdown. (However, the urgency of this concern has decreased greatly with the adoption of a segmented - storage method for pg_clog --- it is no longer + storage method for pg_clog: it is no longer necessary to keep old pg_clog entries around forever.) @@ -122,7 +112,7 @@ A difficulty standing in the way of realizing these benefits is that they require saving WAL entries for considerable - periods of time (eg, as long as the longest possible transaction if + periods of time (e.g., as long as the longest possible transaction if transaction UNDO is wanted). The present WAL format is extremely bulky since it includes many disk page snapshots. This is not a serious concern at present, since the @@ -130,93 +120,13 @@ but to achieve these future benefits some sort of compressed WAL format will be needed. - - - - - Implementation - - - WAL is automatically enabled from release 7.1 - onwards. No action is required from the administrator with the - exception of ensuring that the additional disk-space requirements - of the WAL logs are met, and that any necessary - tuning is done (see ). - - - - WAL logs are stored in the directory - $PGDATA/pg_xlog, as - a set of segment files, each 16 MB in size. Each segment is - divided into 8 kB pages. The log record headers are described in - access/xlog.h; record content is dependent on - the type of event that is being logged. Segment files are given - ever-increasing numbers as names, starting at - 0000000000000000. The numbers do not wrap, at - present, but it should take a very long time to exhaust the - available stock of numbers. - - - - The WAL buffers and control structure are in - shared memory, and are handled by the backends; they are protected - by lightweight locks. The demand on shared memory is dependent on the - number of buffers. The default size of the WAL - buffers is 8 buffers of 8 kB each, or 64 kB total. - - - - It is of advantage if the log is located on another disk than the - main database files. This may be achieved by moving the directory, - pg_xlog, to another location (while the - postmaster is shut down, of course) and creating a symbolic link - from the original location in $PGDATA to - the new location. - - - - The aim of WAL, to ensure that the log is - written before database records are altered, may be subverted by - disk drives that falsely report a successful write to the kernel, - when, in fact, they have only cached the data and not yet stored it - on the disk. A power failure in such a situation may still lead to - irrecoverable data corruption. Administrators should try to ensure - that disks holding PostgreSQL's - log files do not make such false reports. - - - - Database Recovery with <acronym>WAL</acronym> - - - After a checkpoint has been made and the log flushed, the - checkpoint's position is saved in the file - pg_control. Therefore, when recovery is to be - done, the backend first reads pg_control and - then the checkpoint record; then it performs the REDO operation by - scanning forward from the log position indicated in the checkpoint - record. - Because the entire content of data pages is saved in the log on the - first page modification after a checkpoint, all pages changed since - the checkpoint will be restored to a consistent state. - - - - Using pg_control to get the checkpoint - position speeds up the recovery process, but to handle possible - corruption of pg_control, we should actually - implement the reading of existing log segments in reverse order -- - newest to oldest -- in order to find the last checkpoint. This has - not been implemented, yet. - - - + <acronym>WAL</acronym> Configuration - There are several WAL-related parameters that + There are several WAL-related configuration parameters that affect database performance. This section explains their use. Consult for details about setting configuration parameters. @@ -232,25 +142,25 @@ log (known as the redo record) it should start the REDO operation, since any changes made to data files before that record are already on disk. After a checkpoint has been made, any log segments written - before the undo records are no longer needed and can be recycled or + before the redo records are no longer needed and can be recycled or removed. (When WAL-based BAR is implemented, the log segments would be archived before being recycled or removed.) - The postmaster spawns a special backend process every so often + The server spawns a special process every so often to create the next checkpoint. A checkpoint is created every - CHECKPOINT_SEGMENTS log segments, or every - CHECKPOINT_TIMEOUT seconds, whichever comes first. + checkpoint_segments log segments, or every + checkpoint_timeout seconds, whichever comes first. The default settings are 3 segments and 300 seconds respectively. It is also possible to force a checkpoint by using the SQL command CHECKPOINT. - Reducing CHECKPOINT_SEGMENTS and/or - CHECKPOINT_TIMEOUT causes checkpoints to be done + Reducing checkpoint_segments and/or + checkpoint_timeout causes checkpoints to be done more often. This allows faster after-crash recovery (since less work will need to be redone). However, one must balance this against the increased cost of flushing dirty data pages more often. In addition, @@ -262,15 +172,15 @@ - There will be at least one 16MB segment file, and will normally - not be more than 2 * CHECKPOINT_SEGMENTS - + 1 files. You can use this to estimate space requirements for - WAL. Ordinarily, when old log segment files are no longer needed, - they are recycled (renamed to become the next sequential future - segments). If, due to a short-term peak of log output rate, there - are more than 2 * CHECKPOINT_SEGMENTS + 1 segment files, - the unneeded segment files will be deleted instead of recycled until the - system gets back under this limit. + There will be at least one 16 MB segment file, and will normally + not be more than 2 * checkpoint_segments + 1 + files. You can use this to estimate space requirements for WAL. + Ordinarily, when old log segment files are no longer needed, they + are recycled (renamed to become the next segments in the numbered + sequence). If, due to a short-term peak of log output rate, there + are more than 2 * checkpoint_segments + 1 + segment files, the unneeded segment files will be deleted instead + of recycled until the system gets back under this limit. @@ -282,7 +192,7 @@ to write (move to kernel cache) a few filled WAL buffers. This is undesirable because LogInsert is used on every database low level modification (for example, - tuple insertion) at a time when an exclusive lock is held on + row insertion) at a time when an exclusive lock is held on affected data pages, so the operation needs to be as fast as possible. What is worse, writing WAL buffers may also force the creation of a new log segment, which takes even more @@ -294,8 +204,8 @@ not occur often enough to prevent WAL buffers being written by LogInsert. On such systems one should increase the number of WAL buffers by - modifying the postgresql.conf - WAL_BUFFERS parameter. The default number of + modifying the configuration parameter wal_buffers. + The default number of WAL buffers is 8. Increasing this value will correspondingly increase shared memory usage. @@ -305,47 +215,122 @@ buffers to disk using the operating system sync() call. Busy servers may fill checkpoint segment files too quickly, causing excessive checkpointing. If such forced checkpoints happen - more frequently than CHECKPOINT_WARNING seconds, + more frequently than checkpoint_warning seconds, a message, will be output to the server logs recommending increasing - CHECKPOINT_SEGMENTS. + checkpoint_segments. - The COMMIT_DELAY parameter defines for how many - microseconds the backend will sleep after writing a commit + The commit_delay parameter defines for how many + microseconds the server process will sleep after writing a commit record to the log with LogInsert but before performing a LogFlush. This delay allows other - backends to add their commit records to the log so as to have all + server processes to add their commit records to the log so as to have all of them flushed with a single log sync. No sleep will occur if fsync - is not enabled or if fewer than COMMIT_SIBLINGS - other backends are not currently in active transactions; this avoids - sleeping when it's unlikely that any other backend will commit soon. + is not enabled or if fewer than commit_siblings + other sessons are currently in active transactions; this avoids + sleeping when it's unlikely that any other session will commit soon. Note that on most platforms, the resolution of a sleep request is - ten milliseconds, so that any nonzero COMMIT_DELAY - setting between 1 and 10000 microseconds will have the same effect. + ten milliseconds, so that any nonzero commit_delay + setting between 1 and 10000 microseconds would have the same effect. Good values for these parameters are not yet clear; experimentation is encouraged. - The WAL_SYNC_METHOD parameter determines how + The wal_sync_method parameter determines how PostgreSQL will ask the kernel to force WAL updates out to disk. All the options should be the same as far as reliability goes, but it's quite platform-specific which one will be the fastest. - Note that this parameter is irrelevant if FSYNC + Note that this parameter is irrelevant if fsync has been turned off. - Setting the WAL_DEBUG parameter to any nonzero + Setting the wal_debug parameter to any nonzero value will result in each LogInsert and LogFlush WAL call being - logged to standard error. At present, it makes no difference what + logged to the server log. At present, it makes no difference what the nonzero value is. This option may be replaced by a more general mechanism in the future. + + + Internals + + + WAL is automatically enabled; no action is + required from the administrator except ensuring that the additional + disk-space requirements of the WAL logs are met, + and that any necessary tuning is done (see ). + + + + WAL logs are stored in the directory + pg_xlog under the data directory, as a set of + segment files, each 16 MB in size. Each segment is divided into 8 + kB pages. The log record headers are described in + access/xlog.h; the record content is dependent + on the type of event that is being logged. Segment files are given + ever-increasing numbers as names, starting at + 0000000000000000. The numbers do not wrap, at + present, but it should take a very long time to exhaust the + available stock of numbers. + + + + The WAL buffers and control structure are in + shared memory and are handled by the server child processes; they + are protected by lightweight locks. The demand on shared memory is + dependent on the number of buffers. The default size of the + WAL buffers is 8 buffers of 8 kB each, or 64 kB + total. + + + + It is of advantage if the log is located on another disk than the + main database files. This may be achieved by moving the directory + pg_xlog to another location (while the server + is shut down, of course) and creating a symbolic link from the + original location in the main data directory to the new location. + + + + The aim of WAL, to ensure that the log is + written before database records are altered, may be subverted by + disk drives that falsely report a successful write to the kernel, + when, in fact, they have only cached the data and not yet stored it + on the disk. A power failure in such a situation may still lead to + irrecoverable data corruption. Administrators should try to ensure + that disks holding PostgreSQL's + WAL log files do not make such false reports. + + + + After a checkpoint has been made and the log flushed, the + checkpoint's position is saved in the file + pg_control. Therefore, when recovery is to be + done, the server first reads pg_control and + then the checkpoint record; then it performs the REDO operation by + scanning forward from the log position indicated in the checkpoint + record. Because the entire content of data pages is saved in the + log on the first page modification after a checkpoint, all pages + changed since the checkpoint will be restored to a consistent + state. + + + + Using pg_control to get the checkpoint + position speeds up the recovery process, but to handle possible + corruption of pg_control, we should actually + implement the reading of existing log segments in reverse order -- + newest to oldest -- in order to find the last checkpoint. This has + not been implemented, yet. + +