createdb Description The createdb utility is a command-line alternative to the CREATE DATABASE SQL clause. Usage/Options createdb [ options ] name [ comment ] | Option | Description | | -e, --echo | Echoes backend messages to stdout . | | -E, --encoding type | Character encoding scheme. | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -q, --quiet | Do not return any responses from the back end. | | -U, --username user | Connects as this username. | | -W, --password | Prompts for the password. | | -D, --location path | Alternate path to the database location. | | name | The name of the database to create. | | comment | Description or explanation of the database. | Examples $ createdb mydatabase 'My database for holding records' $ createdb -h db.somewebsite.com -p 9333 mydatabase $ createdb -D /usr/local/mydb mydatabase Notes/Location createdb relies on the psql command to actually perform the database creation. Therefore, psql must be present and able to be executed in order for createdb to function correctly. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin createlang Description The createlang utility registers a new language to the specified PostgreSQL database. Usage/Options createlang [ options ] [ language [ dbname ]] | Option | Description | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -U, --username user | Connects as this username. | | -W, --password | Prompts for the password. | | -l, --list | Lists the languages currently registered for the specified database. | | language | The language name to register with the database. | | dbname | The database name to create a language in. | Examples $ createlang pltcl mydatabase $ createlang -h db.someserver.com -p 9999 plsql mydatabase $ createlang -l mydatabase Notes/Location Currently the createlang command accepts plsql or pltcl . This command is a wrapper for the CREATE LANGUAGE SQL command; however, this is the preferred method for adding languages because of certain system checks it automatically performs . Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin createuser Description The createuser command adds a new user to PostgreSQL. Usage/Options $ createuser [ options ] username | Option | Description | | -e, --echo | Echoes back-end messages to stdout . | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -q, --quiet | Do not return any responses from the back end. | | -d, --createdb | Enables the user to create new databases. | | -D, --no-createdb | Prohibits the user from creating new databases. | | -a, --adduser | Enables the user to create additional users. | | -A, --no-adduser | Prohibits the user from creating new users. | | -P, --pwprompt | If using authentication, prompts for the new user's password. | | -i, --sysid id | Enables specification of the UID of the user. | | username | Unique username to create. | Examples $ createuser joe $ createuser -h db.someserver.com -p 9999 joe $ createuser -a -d joe Notes/Location The createuser utility is a wrapper for the psql command. Therefore, the psql file must be present and able to be executed by the user. To create users, the flag in pg_shadow for the executing user must be set as such to succeed. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin dropdb The dropdb utility is a command-line alternative to the DROP DATABASE SQL clause. Usage/Options dropdb [ options ] name | Option | Description | | -e, --echo | Echoes back-end messages to stdout . | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -q, --quiet | Do not return any responses from the back end. | | -U, --username user | Connects as this username. | | -W, --password | Prompts for the password. | | -i, --interactive | Interactive verification of the delete process. | | name | The name of the database to remove. | Examples $ dropdb mydatabase $ dropdb -h db.somewebsite.com -p 9333 mydatabase Notes/Location dropdb relies on the psql command to actually perform the database deletion. Therefore, psql must be present and able to be executed for dropdb to function correctly. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin droplang Description The droplang utility removes a language from the specified PostgreSQL database. Usage/Options droplang [ options ] [ language [ dbname ]] | Option | Description | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -U, --username user | Connects as this username. | | -W, --password | Prompts for the password. | | -l, --list | Lists the languages currently registered for the specified database. | | language | The language name to remove. | | dbname | Removes the language specified in this database. | Examples $ droplang pltcl mydatabase $ droplang -h db.someserver.com -p 9999 plsql mydatabase $ droplang -l mydatabase Notes/Location This command is a wrapper for the DROP LANGUAGE SQL command; however, this is the preferred method for removing languages because of the system checks it automatically performs. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin dropuser Description The dropuser command removes a user from PostgreSQL. Usage/Options $ dropuser [ options ] username | Option | Description | | -e, --echo | Echoes back-end messages to stdout . | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -q, --quiet | Do not return any responses from the back end. | | -i, --interactive | Prompts for confirmation before deletion. | | username | Unique username to delete. | Examples $ dropuser joe $ dropuser -h db.someserver.com -p 9999 joe Notes/Location The dropuser utility is a wrapper for the psql command. Therefore, the psql file must be present and able to be executed by the user. To remove users, the flag in pg_shadow for the executing user must be set as such to succeed. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin ecpg Description The ecpg command is a SQL preprocessor that is used to embed SQL commands within C programs. Using SQL commands within a C program is essentially a two-step process. First, the file of SQL commands is passed through the ecpg utility; then it can be linked and compiled with a standard C compiler. Usage/Options ecpg [ options ] file [,el]] | Option | Description | | -v | Prints version information of ecpg . | | -t | Turns off autotransaction mode. | | -I path | Specifies an alternate include path. | | -d | Turns on debugging info . | | -o filename | Specifies the output filename; if this is omitted, file.c is the default. | | file | The file(s) to process. | Examples $ ecpg myfile.pgc Notes/Location A discussion concerning the actual syntax of the ecpg command is outside the scope of this entry. For a more complete discussion of using embedded SQL in C programs, see Chapter 13,"Client-Side Programming," and the section "ecpg." Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pgaccess Description The pgaccess utility is a GUI front end that makes many common administration tasks easier. Usage/Options pgaccess [ dbname ] dbname ”Start pgaccess connected to this database. Notes/Location pgaccess provides the following functionality: -
Opens any database. -
Specifies username and password on login. -
Specifies hostname and/or port for connection. -
Saves preferences locally ( ~./pgaccessrc ). -
Executes VACUUM command on database. -
Provides edit-in-place modification of table data. -
Deletes selected table rows. -
Appends records to tables. -
Filters rows based on supplied criteria. -
Specifies sort order of rows. -
Imports/exports table data. -
Renames tables. -
Drops tables. -
Defines and edits user-specified queries. -
Stores queries as views. -
Stores view layouts. -
Constructs queries using drag-and-drop support. -
Builds queries with table aliasing. -
Prompts the user for supplied parameters for dynamic queries (such as " SELECT * FROM authors WHERE name=[parameter 'Authors Name']" ). -
Defines, inspects, and deletes sequences. -
Designs, views, sorts, and drops views. -
Defines, views, and drops functions. -
Defines and generates simple reports . -
Changes fonts, size , and style of reports. -
Loads and saves reports. -
Designs custom forms. -
Saves and views forms. -
Defines, edits, and executes user-defined scripts. pgaccess depends on the Tcl/Tk language, so it will need to be installed to work properly. Additionally, the PostgreSQL-Tcl packages will need to be installed, or the source will need to be compiled with the - -with-tcl option enabled. Location of the file: RPM ” /usr/bin Source ” /usr/bin or /usr/local/pgaccess pgadmin Description The pgadmin tool is a Windows 95/98/NT tool for performing basic PostgreSQL administration. (This tool is not included in the base PostgreSQL package; it is a third-party tool specifically for Windows users.) Notes/Location The tool's features include the following: -
Executing arbitrary SQL commands. -
Creating databases, tables, indexes, sequences, views, triggers, functions, and languages. -
Granting user and group privileges. -
Data import and export tools. -
Predefined reports on databases, tables, indexes, sequences, languages, and views. -
Revision tracking. The pgadmin tool is not distributed as part of the standard PostgreSQL system. Please visit http://www.pgadmin.freeserve.co.uk for more information on obtaining, installing, and using pgadmin. pg_dump Description The pg_dump utility is a very important tool in the PostgreSQL administrator's arsenal. It enables database schema and/or data to be dumped out to standard text. By default, it writes the data to stdout , which can easily be redirected to a file using the appropriate piping symbol. Combined with psql or pg_restore , this is the preferred method for performing database backups and restores (see the next section, "pg_dumpall"). Version 7.1 of PostgreSQL adds some important features to pg_dump and pg_dumpall . There is a new option that enables dumps to be made in a specified format. Some of these new format types enable advanced features like dump and restore of user-defined objects, selective restores, and so on (see the section "pg_restore" for more information). Usage/Options pg_dump [ options ] database | Option | Description | | -h host | Starts the host where the server is running. | | -p port | Specifies the port where the server is running. | | -u | Prompts for user/password authentication. | | -v | Verbose mode. | | -a | Dumps only the data; no schema. | | -b, --blobs | Dumps data and BLOBs (v7.1 feature). | | -c | Drops schema before creating. | | -C, --create | Includes commands to create the database (v7.1 feature). | | -d | Dumps data in a proper INSERT format. | | -D | Dumps data as INSERTs with attribute names . | | -f, --file name | Sends output messages to the specified file (v7.1 feature). | | -Fp | Uses a plain SQL text format. This is the default (v7.1 feature). | | -Ft | Outputs archive in a tar format (v7.1 feature). | | -Fc | Outputs the archive in new custom format. This is the most flexible option (v7.1 feature). | | -i | Ignores version mismatch with server back end ( pg_dump is only designed to work with the correct version; this is for experimental use only). | | -n | Suppresses double quotes in dump. | | -N | Includes double quotes in dump (default). | | -o | Dumps the OIDs for every table. | | -O, --no-owner | Does not set ownership objects to match the original database (v7.1 feature). | | -R, --no- reconnect | Does not attempt to connect to the database (v7.1 feature). | | -S, --superuser name | Specifies the superuser name (DBA) to use when disabling triggers and setting ownership information (v7.1 feature). | | -s | Dumps schema only; no data. | | -t table | Dumps info for this table only. | | -x | Does not dump ACL (Grant/Revoke) information. | | -Z, --compress [0..9] | Specifies compression level (0 “9); currently, only the custom format supports this feature (v7.1 feature). | Examples $ pg_dump authors $ pg_dump -a authors $ pg_dump -t payroll authors Notes/Location pg_dump cannot handle large objects (LOs). pg_dump cannot correctly handle extracting all system catalog metadata. For instance, partial indexes are not supported. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pg_dumpall Description The pg_dumpall command is very similar to pg_dump . However, pg_dumpall extracts all databases to a script file. In addition to the standard items extracted in a pg_dump , pg_dumpall also includes the contents of the pg_shadow file. Version 7.1 of PostgreSQL adds some important features to pg_dump and pg_dumpall . There is a new option that enables dumps to be made in a specified format. Some of these new format types enable advanced features like dump and restore user-defined objects, selective restores, and so on (see the section "pg_restore" for more information). Usage/Options pg_dumpall [ options ] | Option | Description | | -h host | Starts the host where the server is running. | | -p port | Specifies the port where the server is running. | | -u | Prompts for user/password authentication. | | -v | Verbose mode. | | -a | Dumps only the data; no schema. | | -b, --blobs | Dump data and BLOBs (v7.1 feature). | | -c | Drops schema before creating. | | -C, --create | Includes commands to create the database (v7.1 feature). | | -d | Dumps data in a proper INSERT format. | | -D | Dumps data as INSERTs with attribute names. | | -f, --file name | Sends output messages to the specified file (v7.1 feature). | | -Fp | Uses a plain SQL text format. This is the default (v7.1 feature). | | -Ft | Outputs the archive in a tar format (v7.1 feature). | | -Fc | Outputs the archive in new custom format. This is the most flexible option (v7.1 feature). | | -i | Ignores version mismatch with the server back end ( pg_dump is only designed to work with the correct version; this is for experimental use only). | | -n | Suppresses double quotes in dump . | | -N | Includes double quotes in dump (default). | | -o | Dumps the OIDs for every table. | | -O, --no-owner | Does not set ownership objects to match the original database (v7.1 feature). | | -R, --no-reconnect | Does not attempt to connect to the database (v7.1 feature). | | -S, --superuser name | Specifies the superuser name (DBA) to use when disabling triggers and setting ownership information (v7.1 feature). | | -s | Dumps schema only; no data. | | -x | Does not dump ACL (Grant/Revoke) information. | | -Z, --compress [0..9] | Specifies compression level (0 “9); currently, only the custom format supports this feature (v7.1 feature). | Examples $ pg_dumpall $ pg_dumpall -a $ pg_dumpall -o Notes/Location pg_dumpall has many of the same limitations that pg_dump has with regard to system metadata. See the section "pg_dump" for more information. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pg_restore Description This is a new tool included with the PostgreSQL 7.1 release. It is designed to restore data dumped by the pg_dump or pg_dumpall database utilities. The new version of pg_dump includes the capability to dump data in a nontext format that has many advantages over traditional data dumps: -
Selective restores are possible using the new pg_dump format and pg_restore . -
The new archive format produced by pg_dump is designed to be portable among platforms. -
The new pg_dump format will produce queries to enable the regeneration of all user-defined types, functions, tables, indexes, aggregates, and operators. Usage/Options pg_restore [ options ] archive-file | Option | Description | | -a, --data-only | Restores data only; no schema. | | -c, --clean | Drops schema before invoking createdb . | | -C, --create | Includes SQL to create schema. | | -d, --dbname name | The name of the database to connect to. | | -f, --file= filename | Specifies the file to hold generated output. | | -Ft, --format=t | Specifies that the format of the archive file is in tar . | | -Fc, --format=c | Specifies that the format of the archive file is in the custom format of pg_dump . This is the most flexible format to restore from. | | -i, --index= name | Restores information for the named index only. | | -l, --list | Lists the contents of the archive only. | | -L, --use-list file | Restores the elements contained in the specified file only. Restored in the order they appear, comments begin with a semicolon ( ; ) at the start of the line. | | -N, --orig-order | Restores items to the original dump order. | | -o, --oid-order | Restores items to the original OID order. | | -O, --no-owner | Does not restore ownership information; objects will be owned by the current user. | | -P, --function name | Restores named functions only. | | -r, -rearrange | Restores items in modified OID order. (This is the default.) | | -R, --no-reconnect | Prohibits pg_restore from attempting any database connections. Useful if already directly connected to the database. | | -s, --schema-only | Restores the schema only; no data. | | -S, --superuser= name | Specifies the superuser name to use when disabling triggers and applying ownership information. By default, pg_restore uses the current username, if that user is considered a DBA. | | -t, --table= name | Restores schema/data for the specified table only. | | -T, --trigger= name | Restores the specified trigger only. | | -v, --verbose | Produces verbose output. | | -x, --no-acl | Prevents restoration of the Access Control List (that is, Grant/Revoke information). | | -h, --host name | Specifies the hostname where the server process is running. | | -p, --port port | Specifies the port to connect to. | | -u | Forces prompts for authentication. | Examples Dump a database and restore using custom format: $ pg_dump -Fc newriders $ newriders.cust_fmt $ pg_restore -d newriders newriders.cust_fmt Only restore the payroll table: $ pg_restore -d newriders -t payroll newriders.cust_fmt Notes/Location See also the sections discussing pg_dump, pg_dumpall, and pg_upgrade. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pg_upgrade Description The pg_upgrade utility can be used to upgrade to a new version of the database system without having to reload all the data in the current database. This command currently will not function on PostgreSQLVersion 7.1 and above; see the section on pg_restore if you are using one of the newer versions. Usage/Options pg_upgrade [ -f file ] old_data_dir -f file ”This specifies a file containing the schema for the old database. old_data_dir ”This represents the path to the old data directory. Examples The usual method of upgrading a database while using the pg_upgrade utility is as follows : -
Back up existing data (that is, pg_dumpall ). -
Dump out schema to a file (that is, pg_dumpall -s db.out ). -
Stop the current postmaster . -
Rename the old data directory (that is, data.old ). -
Build the new binaries with the make tools. -
Install the new binaries with make install . -
Run initdb in the new system to create a new database structure. -
Start the postmaster . -
Upgrade the old database to the new system using $ pg_upgrade -f db.out/usr/local/pgsql/data.old . -
Copy old pg_hba.conf files and pg_options to their new location (that is, /usr/local/pgsql/data ). -
Stop and start postmaster again. -
Verify that connections are working correctly. -
Connect to the restored database and examine its contents carefully . -
If the database is not valid, restore from your full dump file created in step 1. -
If the database is valid, issue a vacuum command to update query-planning statistics (that is, vacuumdb -z mydb ). Notes/Location Not all upgrades can be accomplished with this tool. Check the release notes of the new database version to see if pg_upgrade is supported. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pgtclsh Description The pgtclsh command is a wrapper program, which essentially is a standard Tcl shell with the libpgtcl library loaded. Usage/Options pgtclsh [script arg1 [,]] script ”The optional Tcl script file to process. arg1 ”The optional arguments to pass to the specified script file. Examples $ pgtclsh myfile.tcl Notes/Location If pgtclsh is launched with no specified script file, it automatically enters into the interactive Tcl interface. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin pgtksh Description The pgtksh command is essentially a Tk (wish) shell with the libpgtcl libraries loaded. This is what the pgaccess program is based on. Usage/Options pgtksh [script arg1 [,]] script ”The optional Tcl script file to process. arg1 ”The optional arguments to pass to the specified script file. Examples $ pgtksh myfile.tcl Notes/Location If pgtksh is launched with no specified script file, it automatically enters into the interactive Tcl interface. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin psql Description psql is an interactive front end to the PostgreSQL system. It is a very versatile interface and has numerous options that can control nearly every aspect of a PostgreSQL system. Once psql is started and connected to the specified database, the user is entered into an interactive shell. In this mode, commands can be issued to the PostgreSQL back end, and the responses can be seen in real time. Usage/Options psql [ options ] [ database [ user ]] The psql options fall into two categories: command-line options, which are issued while starting psql , and shell options, which can be issued inside the psql shell. | Command-Line Options | Description | | -a, --echo-all | Echoes all processed lines to the screen. This is useful when running a script to monitor progress or for debugging purposes. | | -A, --no-align | Switches the output to an unaligned layout. | | -c, --command query | Specifies that psql is to execute a single SQL command or a single interactive shell command.The query must either be pure SQL or a psql shell command. Mixing command types is not permitted. | | -d, --dbname database | Specifies the database to connect to. | | -e, --echo-queries | Echoes all queries sent to the back end. | | -E, --echo-hidden | Echoes all queries, even hidden queries as a result of shell commands (that is, \dt and so on). | | -f, --file file | Reads the specified file and executes the contained SQL queries. After finished, terminates. | | -F, --field-separator sep | Uses the specified field separator. | | -h, --host hostname | Connects to the specified host (where the server is running). | | -H, --html | Produces table output in HTML format. | | -l, --list | Lists all available databases and then exits. | | -o, --output file | Captures all query output in the specified file. | | -p, --port port | Connects using the specified port. | | -P, --pset val | Allows setting the default print (output) style (for example, aligned , unaligned , html , or latex ). | | -q | Quiet mode. | | -R, --record-separator sep | Uses the specified separator for record demarcation . | | -s, --single-step | Prompts the user before each query is executed. Useful for debugging or controlling execution of SQL scripts. | | -S, --single-line | Runs PostgreSQL in a single-line mode, where carriage returns terminate a query (the default is a semicolon). | | -t, --tuples-only | Turns off the printing of column names and result totals. Only prints data (tuples) returned. | | -T, --table-attr options | Specifies the options to be included in HTML table output. | | -u | Forces username and password prompts. | | -U, --username name | Connects to the database as a specified user. | | -v, --variable, --set var= val | Assigns a value to a variable. To unset a variable, include it with no equal sign or value following. | | -V, --version | Displays the psql version information. | | -W, --password | Forces PostgreSQL to prompt the user for a password. | | -x, --expanded | Turns on extended row format mode. | | -X, --no-psqlrc | Does not read the startup file ~/.psqlrc . | | -?, --help | Displays a help screen showing psql options. | Inside the psql shell, most options are prefaced with a backslash ( \ ). | Shell Options | Description | | \a | Toggles alignment mode. | | \C title | Sets the specified title to print atop each query result set. | | \c, \connect db [user] | Closes the current connection and connects to the specified database. Optionally, will connect as the specified user. | | \copy table [with oids] {from to} filename stdin stdout [using delimiters char ] [with null as nullstr ] | Performs a front-end version of the SQL COPY command. Specifies direction of copy and whether to route to a file or use stdin or stdout . Additionally, null string and delimiters can be specified. | | \copyright | Displays the PostgreSQL copyright. | | \d | Same as \dtvs . Displays information about the specified relations. | | \dt | Displays information about the tables in the current database. | | \dv | Displays information about the views in the current database. | | \ds | Displays information about the sequences in the current database. | | \di | Displays information about the indexes in the current database. | | \da [ pattern ] | Displays information about the aggregates in the current database. Optionally, only shows those that match the pattern specified (such as max ). | | \dd [ obj ] | Shows the comments associated with all objects in the current database. Optionally, only shows comments attached to specified object. | | \df [ pattern ] | Displays information about the functions in the current database. Optionally, only shows functions that match the specified pattern. | | \dl | Lists all large objects in the current database (same as \lo_list ). | | \dp [ pattern ] | Displays information concerning permissions on objects within the current database. Optionally, only displays information on objects that match the specified pattern (same as \z ). | | \dS | Displays information concerning the system tables in the current database. | | \dT [ pattern ] | Displays information on the data types included in the current database. Optionally, only displays information on objects that match the specified pattern. | | \e, \edit file | Launches an external editor (vi is default) to edit the file specified. | | \echo text | Echoes the text specified or performs command substitution. | | \encoding type | Sets encoding to the specified type or, with no parameter, lists the current encoding type. | | \f str | Sets the field separator to the specified string. Default is the piping symbol ( ) (see also \pset ). | | \g [ file command ] | Sends output from the entered query to the specified file or pipes it through the specified command (similar to \o ). | | \h, \help [ command ] | Displays a list of all the valid SQL commands. Optionally, displays more detailed help on the specified command. | | \i file | Reads input from the specified file and executes it. | | \l, \list | Lists all known databases and their owners (including a "+" will also display comments). | | \lo_export oid file | Exports the large object with the specified OID to the filename specified. | | \lo_import file [ comment ] | Imports the large object from the filename specified. Optionally, provides a descriptive comment to be associated with the LO. | | \lo_list | Lists all known large objects in the current database. | | \lo_unlink oid | Deletes the large object with the specified OID from the current database. | | \o [ file command ] | Sends all future results of queries to the filename specified or pipes through the command given. | | \p | Prints the current query buffer. | | \pset parameter | Allows the user to manually set one of several options that affect the current database. See this listing of parameters: | format | Sets the output mode for tables to the specified format: unaligned , aligned , html , or latex (that is, \pset format= latex ). | | border | Sets the border width or type. In HTML, 0 = no border, 1 = dividing lines, and 2 = new frame (that is, \pset border=0 ). | | expanded | Toggles between regular and expanded format. | | null | Specifies how to display a NULL field value (that is, \pset null 'N/A'). | | fieldsep | Specifies the character(s) to use as a field separator (that is, \pset fieldsep '#' ). | | recordset | Specifies the character(s) to use as a record separator (that is, \pset recordsep '%' ). | | tuples_only | Suppresses header and footer information from being displayed with query output. Returns data only from queries. | | title | Specifies the title to use for following tables (that is, \pset title 'Our Bank Account' ). | | tableattr | Specifies attributes to include in HTML output (that is, \pset tableattr bgcolor ='#FFFF00' ). | | pager | Toggles the use of page-by-page displays. By default, it uses more to handle page displays, but the PAGER variable can be defined to any appropriate handler. | | | \q | Quits the current psql shell. | | \qecho text | Echoes the specified text to wherever \o output is currently being directed. Useful for adding comments to redirected output files. | | \r | Clears (resets) the current query buffer. | | \s file | Saves the current psql command history to the specified filename. (Note: PostgreSQL v7 and greater do this automatically upon exit.) | | \set var value | Sets the psql environmental variable to the specified value. (Note: This is not the same as the SQL SET command.) Here is a list of valid environmental variables : | DBNAME | The name of the currently connected database. | | ECHO | The echo mode to which psql is currently set. all means all output is echoed ; queries means only query output is echoed. | | ECHO_HIDDEN | Specifies whether hidden queries are echoed to stdout (that is, hidden queries like \dt ). | | ENCODING | Specifies the encoding scheme to use. If multibyte encoding is not available, this will be set to SQL_ASCII . | | HISTCONTROL | Controls what is entered into the command history buffer. The value ignorespace ignores any commands beginning with whitespace. The value ignoredups will refuse to enter duplicate entries, and ignore-both combines both. | | HISTSIZE | The number of commands to store in the history buffer (default is 500). | | HOST | The host where the current connection is operating. | | IGNOREEOF | If not set, sending an EOF (Ctrl+D) will terminate the current psql session. Otherwise , if set to a numeric variable, it will ignore this many EOF insertions before termination (default is 10). | | LASTOID | The value of the last affected OID. | | LO_TRANSACTION | Specifies the default action to take when executing a large object event (for example, \lo_export , \lo_import , and so on). A rollback value will force a rollback to any in-progress transactions. commit will force a commit, and nothing will specify that no action is to take place. The latter case is usually reserved for those cases in which the user will be providing explicit BEGIN COMMIT encapsulation to LO events. | | ON_ERROR_STOP | Specifies that if noninteractive scripts encounter an error, processing is to stop. By default, psql will continue to process statements even if a malformed SQL statement has been encountered . | | PORT | The port where the current session is connected. | | PROMPT1 | Specifies what the normal prompt is to look like (default is %/%R%#) . | | PROMPT2 | This prompt is issued when psql is expecting more data (default is %/%R%# ). | | PROMPT3 | This prompt is issued when a SQL COPY has been called and the interface requires tuple input (default is >> ). Prompt types: %M ”Full hostname. %m ”Truncated hostname. %> ”Port number. %n ”Username connected as. %/ ”Current database. %~ ” Like %/ but prefixes a ~ if this is default database. %# ” Prefixes # if DBA; otherwise > . %R ”Sets the following: PROMPT1 "=" (default), PROMPT1 "^" (single-line mode), PROMPT1 "!" (if session disconnected), or PROMPT2 "-", "*", "'", """ (depending on what continuation condition exists). % digits ” Sets to the digit specified. % :name ” The value of the psql variable NAME . % :command ” The output of the given command. | | QUIET | Sets quiet mode. | | SINGLELINE | Sets single-line mode. If set, then a new line indicates query termination (default is ; ). | | SINGLESTEP | Sets single-step mode. If set, the user is prompted for confirmation before any query execution takes place. | | USER | The user you are currently connected as. | | | \t | Toggles whether to suppress header and footer information from being displayed with query output. Returns data only from queries (same as \pset tuples_only ). | | \T options | Specifies options to be placed in HTML table output (same as the \pset tableattr command). | | \w file command | Outputs the current query buffer to the specified filename or pipes it through the provided command. | | \x | Toggles extended row format mode. | | \z pattern | Displays permission information on current database objects. Optionally, displays information only on those objects that match the specified pattern. | | \! command | Escapes to a separate UNIX shell and executes the provided command. | | \? | Displays help on psql backslash commands. | Examples To start psql , execute a query and immediately exit: $ psql -c 'SELECT * FROM authors' newriders name age ---------- Sam 25 Bill 67 Alternatively, to run an entire script called mydb.sql from the command line (executing into the newriders database): $ psql -f mydb.sql newriders To perform the same example but this time display it in HTML mode (useful for CGI programming): $ psql -H -c 'SELECT * FROM authors' newriders <table border=1> <tr> <th align=center>name</th> <th align=center>age</th> </tr> <tr valign=top> <td align=left>Sam</td> <td align=left>25</td> </tr> <tr valign=top> <td align=left>Bill</td> <td align=left>67</td> </tr> </table> To redirect queries to an output file from inside a psql shell and to include descriptive titles to each data dump: psql=>\o mycapture.txt psql=>\qecho Listing of all authors psql=>\qecho ********************** psql=>SELECT * FROM authors; psql=>\qecho And their payroll info psql=>\qecho ********************** psql=>SELECT * FROM payroll; To list all files that end in .sql in the current directory from a psql shell interface (notice the use of backticks, not single quotes): psql=>\echo 'ls *.sql' authors.table.sql payroll.table.sql Notes/Location The psql shell environment also supports variable substitution. The most basic form associates a variable name with a value. For instance: psql=>\set myvar name='Sam' psql=>\echo :myvar psql=>name='Sam' psql=>SELECT * FROM authors WHERE :myvar; name age Sam 44 As you can see, variable names are referenced by prefixing the name with a colon (:). To change the default editor when using the \e command, specify the correct value to the PSQL_EDITOR variable. Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin vacuumdb Description The vacuumdb command is a wrapper program for the VACUUM SQL statements. Although there is no real difference in how they operate , the vacuumdb command is most widely used for running via a cron job. Usage/Options vacuumdb [connection-options] [analyze options] | Connection Option | Description | | -h, --host host | The hostname where the server resides. | | -p, --port port | The port or socket file of the listening server. | | -U, --username user | Connects as specified user. | | -W, --password | Forces prompt for password. | | -e, --echo | Echoes back-end messages to stdout . | | -q, --quiet | Do not return any responses from the back end. | | Analyze options: | | -d, --dbname name | The name of the database to vacuum. | | -z, --analyze | Calculates statistics for query optimizer. | | -a, --alldb | Vacuums all databases. | | -v, --verbose | Verbose output. | | -t, --table table | Cleans or analyzes table only. | | -t, --table table(col) | Analyzes column only (must be used with -z ). | Examples Clean the newriders database (both are equivalent): $ vacuumdb -d newriders $ $ vacuumdb newriders Clean all databases and then analyze a specific table: $ vacuumdb -a $ $ vacuumdb -z -d newriders -t authors Notes/Location Location of the file: RPM ” /usr/bin Source ” /usr/local/pgsql/bin |
