initdb Description The initdb command is used to prepare a directory location for a new PostgreSQL system. The initdb command is usually performed with several other steps, as briefly outlined here: -
Create the directory to hold the data as root. -
Use chown to change ownership of the directory to the DBA user . -
Use login (or su ) to log in to the DBA user account. -
Execute initdb with appropriate options. -
initdb will generate the shared catalog tables. -
initdb will generate the template1 database. (Each time you create a new database, it is generated from template1 .) Usage/Options initdb -D path [ options ] Option | Description | -D, --pgdata path | The path to the PostgreSQL database. | -i, --sysid= id | Specifies the UID of the DBA. | -W, --pwprompt | Forces password prompt. | -E, --encoding= type | Specifies the encoding type to use (the system must have been built with the multibyte encoding flag set to true ). | -d, --debug | Prints debugging information from the back end. | -n, --noclean | By default, if | -L, path | Specifies the path for initdb to find its input files. (This is a special-case option and is rarely needed.) | Examples $initdb -D /usr/local/pgsql/data Notes/Location The initdb command should not be run as root. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin initlocation Description The initlocation utility is used to initialize a secondary data storage area. In some ways, this command is similar to initdb , except that many internal catalog operations do not occur with initlocation . Additionally, this command can be run as often as necessary, as opposed to initdb , which is generally run only once per installation. Usage/Options initlocation path path ” The path where the new database will reside. Examples $initlocation /usr/local/pgsql/data2 Notes/Location This should be run as the DBA account, not as root. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin ipcclean Description The ipcclean command is a shell script designed to clean up orphaned semaphores and shared memory after a back-end server crash. Usage/Options ipcclean Notes/Location This command makes certain assumptions about the naming convention used with output from the ipcs utility. Therefore, this shell script might not be portable across all operating systems. Warning! Running the ipcclean command while a database server is currently operational will result in a general failure of the database system. pg_ctl Description pg_ctl is used to control various aspects of the PostgreSQL postmaster server. Usage/Options pg_ctl [-w] [-D path ] [-p path ] [-o " options "] start pg_ctl [-w] [-D path ] [-m mode ] stop pg_ctl [-w] [-D path ] [-m mode ] [-o " options "] restart pg_ctl [-D path ] status Option | Description | -w | Watches for creation/destruction of the pid file ( $PGDATA/ postmaster.pid ). Times out after 60 seconds. | -D path | The path to the database. | -p path | Specifies the path to the postmaster file. | -m mode | Specifies one of the following shutdown modes: s, smart | Waits for clients to log out (default). | f, fast | Sends SIGTERM to the back end. Active transactions are issued an immediate ROLLBACK. | i, immediate | Sends SIGUSR1 to all back ends; in this mode, database recovery will be needed on the next system start. | | -o " options " | Sends specified options to the postmaster . Options are usually quoted in order to ensure proper execution. | start | Starts the postmaster. | stop | Stops the postmaster . | restart | Restarts the postmaster (automatic stop/start). | status | Shows the status of the postmaster . | Examples $pg_ctl start $pg_ctl -m smart stop Notes/Location Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pg_passwd Description The pg_passwd utility is used to create and manipulate the password file needed if authentication is enabled in PostgreSQL. Usage/Options pg_passwd filename filename ”The path and filename of the password file to create or manipulate. Examples $pg_passwd /usr/local/pgsql/data/pg_pword File "/usr/local/pgsql/data/pg_pword" does not exist,, Create? (y/n): Y Username: barry Password: Re-enter password: Notes/Location The file must be in the path of the PostgreSQL database to be used for client authentication. Additionally, the authentication method might need to be entered in the pg_hba.conf configuration file. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin postgres Description The postgres file is the actual server process for processing queries in PostgreSQL. It is usually called by the multiprocess postmaster wrapper. (Both are actually the same file; postmaster is a symlink to the postgres process.) The postgres server is usually not invoked directly; rather, many of these options are passed to the postgres process upon execution. Although generally not invoked directly, the postgres process can be executed in an interactive mode that will allow queries to be entered and executed. However, such execution should not be attempted if the postmaster process is running; data corruption could result. Usage/Options postgres [ options ] database Option | Description | -A 01 | Specifies whether assert checking should be enabled. (This debugging tool is available only if enabled at compile time. If it was enabled, the default is on.) | -B val | The number of 8KB shared buffers to use. The default is 64. | -c var =val | Sets various runtime options. See the Advanced Option list later in this chapter for these options. | -d level | Sets the debug level. The higher the value, the more entries are output to the log. The default is 0; the range is usually valid up to 4. | -D path | The path to the data directory. | -F | Disables fsync system calls. Can result in improved performance, but there is a risk of data corruption. Generally, use this option only if there is a specific reason to do so; it is not intended for standard operation. | -e | Sets the date style to European (that is, dd-mm-yyyy). | -o file | Sends all debugging information to the specified file. | -P | Disables use of system indexes of scan/update tuples. (Note: The REINDEX command requires use of this option.) | -s | Sends timing statistics to stdout for each processed query. Useful for performance tuning. | -S val | Specifies the amount of kilobytes to be used for internal sorts and hashes before the system calls on temporary files. This memory amount indicates that every system sort and/or hash is capable of using up to this much memory. When the system is processing complex sorts, multiple sort /hash instances will be used, and each one will use up to this much memory. The default value is 512 kilobytes. | -E | Echoes all queries to stdout . | -N | Prohibits use of a new line as a query delimiter . | Not for Everyone These advanced options are not recommended for general use. They typically are used only for advanced debugging scenarios or by PostgreSQL developers. Moreover, the inclusion of these advanced options might change from release to release. There is no guarantee that these options will exist in any specific version of PostgreSQL. | Advanced Option | Description | -fi | Disables index scans . | -fs | Disables sequential scans. | -fn | Disables nested loop joins. | -fm | Disables merge joins. | -fh | Disables hash joins. | -i | Prevents query execution but shows plan. | -L | Prohibits use of the locking system. | -O | Enables modification of system tables. | -p database | Indicates that the specified database has been started by postmaster . Impacts buffer sizes, file descriptors, and so on. | -tpa | Prints timing information for the system parser. (Cannot be used with the -s option.) | -tpl | Prints timing information for the system planner. (Cannot be used with the -s option.) | -te | Prints timing information for the system executor. (Cannot be used with the -s option.) | -v val | Specifies the protocol version to use. | -W sec | Sleeps for the specified number of seconds before starting. Useful for developers who need to start debugging programs in the interim. | Notes/Location When starting the postgres process, the current OS username is selected as the PostgreSQL username. If the current username is not a valid PostgreSQL user, the process will not continue. postgres and postmaster are the same file (actually postmaster is a symbolic link to the postgres executable). However, you cannot substitute one command for the other and expect the same results. The postgres executable registers what name it was invoked by, and if it is called as postmaster , certain options and assumptions are enabled. Many of these options can be and are passed to the postgres process by using a configuration file. (See the "pg_options/postgresql.conf" section in Chapter 8, "System Configuration Files and Libraries.") Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin postmaster Description The postmaster is the multiuser implementation of the postgres application. In most cases, this process is started at boot time, and log files are redirected to an appropriate file. One postmaster instance is required to manage each database cluster. Starting multiple instances can be achieved by specifying separate data locations and connection ports. Usage/Options Option | Description | -A 01 | Specifies whether assert checking should be enabled. (This debugging tool is only available if enabled at compile time. If it was enabled, the default is on.) | -B val | The number of 8KB shared buffers to use. The default is 64. | -b path | Specifies the path to the back-end executable (usually postgres ). | -c var=val | Sets various runtime options. See the following listing: shared_buffers = integer debug_level = integer fsync = BOOLEAN virtual_host = integer tcpip_socket = BOOLEAN unix_socket_directory = integer ssl = BOOLEAN max_connections = integer port = integer enable_indexscan = BOOLEAN enable_hashjoin = BOOLEAN enable_mergejoin = BOOLEAN enable_nestloop = BOOLEAN enable_seqscan = BOOLEAN enable_tidscan = BOOLEAN sort_mem = integer show_query_stats = BOOLEAN show_parser_stats = BOOLEAN show_planner_stats = BOOLEAN show_executor_stats = BOOLEAN (Note: See the configuration options in Chapter 8 for more information about these settings.) | -d level | Sets the debug level. The higher the value, the more entries are output to the log. The default is 0; the range is usually valid up to 4. | -D path | The path to the data directory. | -F | Disables fsync system calls. Can result in improved performance, but there is a risk of data corruption. Generally, only use this option if there is a specific reason to do so; it is not intended for standard operation. | -h host | Specifies the host for which the server is to respond to queries. This defaults to listening on all configured interfaces. | -i | Enables clients to connect via TCP/IP. By default, on UNIX, domain sockets are permitted. | -k path | Specifies the directory the postmaster is to use for listening to UNIX domain sockets. (The default is /tmp .) | -1 | Enables use of SSL connections. (Note: This option requires that SSL was enabled at compile time and that the -i option has also been used.) | -N val | Specifies the maximum connections permitted to this database back end. The default value is 32, but this can be set as high as 1,024 if your system will support that many processes. (Note: The -B option must be set with at least twice the number of -N to operate correctly.) | -o options | Command-line options to pass to the postgres back end. If the option string contains any whitespace, quotes must be used. (Note: See postgres for valid command-line switches.) | -p port | The TCP/IP port on which to start listening for connections. The default port is either 5432 or the port set during compile time. (Note: If set to a nondefault port, all client applications will need to specify this port number to connect successfully.) | -S | Starts postmaster as a separate process from the current terminal (like a daemon). However, all error messages will be redirected to /dev/null instead of stdout . (Note: Use of this option will make debugging problems nearly impossible . It is better to start postmaster as an explicit background process and then redirect error messages to a specific file. See the following section for an example.) | Examples Start postmaster as a foreground process: $postmaster -D /usr/local/pgsql/data Start postmaster as a background process with a specified data directory and direct all error messages to a specified log file: $postmaster -D /usr/local/pgsql/data >pglog 2>&1 & Notes/Location When starting the postmaster process, the current OS username is selected as the PostgreSQL username. If the current username is not a valid PostgreSQL user, the process will not continue. postgres and postmaster are the same file (actually postmaster is a symbolic link to the postgres executable). However, you cannot substitute one command for the other and expect the same results. The postgres executable registers the name it was invoked by, and if it is called as postmaster , certain options and assumptions are enabled. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin |