EnterpriseDB, a company devoted to popularizing PostgreSQL technology, builds installers for Windows, Mac OS X, and desktop versions of Linux. For Windows users, this is the preferred installer to use. Mac OS X and Linux opinions vary depending on what you are doing. For example, the EnterpriseDb PostGIS installers for Mac OS X and Linux aren’t always kept up to date with the latest releases of PostGIS, so PostGIS Mac OS X and Linux users, tend to prefer other distributions. EnterpriseDb also distribute binaries for beta versions of coming PostgreSQL versions. The installers are super easy to use. They come packaged with PgAdmin GUI Administration tool and a stack builder that allows you to install additional add-ons like JDBC, .NET drivers, Ruby, PostGIS, phpPgAdmin, pgAgent, WaveMaker, and others.
EnterpriseDB has two offerings: the official, open source PostgreSQL, which EnterpriseDB calls the Community Edition, and their proprietary edition called Advanced Plus. The proprietary fork offers Oracle compatibility and enhanced management features. Don’t get confused between the two when you download. In this book, we will focus on the official PostgreSQL, not Advanced Plus; however, much of the material apply more or less equally to Advanced Plus.
Note
If you want to try out different versions of PostgreSQL on the same machine or want to run it from a USB device. EnterpriseDB also offers binaries in addition to installers. Read this article on our site at PostgreSQL in Windows without Install for further guidance.
Most Unix/Linux distributions come packaged with some version of PostgreSQL, though the version they come with is usually not the latest and greatest. To compensate for this, many people use backports.
For adventurous Linux users, you can always download the latest and greatest PostgreSQL, including the developmental versions by going to the PostgreSQL Yum repository. Not only will you find the core server, but you can also retrieve popular extensions like PL, PostGIS, and many more. At the time of this writing, Yum is available for Fedora 14-16, Red Hat Enterprise 4-6, CentOS 4-6, Scientific Linux 5-6. If you have older versions of the OS or still use PostgreSQL 8.3, you should check the documentations for what’s maintained. If you use Yum for the install, we prefer this Yum distro because it is managed by PostgreSQL group; it is actively maintained by PostgreSQL developers and always releases patches and updates as soon as they are available. We have instructions for installing using Yum in the Yum section of our PostgresOnLine journal site.
Ubuntu is generally good about staying up to date with latest versions of PostgreSQL. Debian tends to be a bit slower. You can usually get the latest PostgreSQL on most recent versions of Ubuntu/Debian using a command along the lines of:
sudo apt-get install postgresql-server-9.1
If you plan to be compiling any of the other additional add-ons not generally packaged with PostgreSQL, such as the PostGIS or R, then you’ll want to also install the development libraries:
sudo apt-get install postgresql-server-dev-9.1
If you want to try the latest and greatest of PostgreSQL and not have to compile yourself, or the version of Ubuntu/Debian you have doesn’t have the latest version of PostgreSQL, then you’ll want to go with a backport. Here are some that people use:
OpenSCG Red Hat, Debian, Ubuntu, and OpenSuse PostgreSQL packages have PostgreSQL for latest stable and beta releases of PostgreSQL.
Martin Pitt backports usually keeps Ubuntu installs for PostgreSQL two versions plus latest beta release of PostgreSQL. It also has releases currently for lucid, natty, and oneiric for core PostgreSQL and postgresql extensions.
If you are interested in PostgreSQL for the GIS offerings, then UbuntuGIS may be something to check out for the additional add-ons like PostGIS and pgRouting, in addition to some other non-PostgreSQL-related GIS toolkits it offers.
FreeBSD is a popular choice for PostgreSQL install. However, many people who use FreeBSD tend to compile their own directly from source rather than using a Port or package distribution. You can find even the latest beta versions of PostgreSQL on the FreeBSD database section of FreeBSD ports site.
There are several ways of installing PostgreSQL on Mac OS X that we’ve seen people use. There is the EnterpriseDb desktop install, which we already mentioned. Many have complained since it installs in a non-standard location, and it doesn’t play well when it wants to receive other add-ons. There is also HomeBrew, which seems to be gaining a lot of popularity; and there’s KyngChaos, for people who want a relatively smooth, but very up to date GIS experience. Lastly, there is the standard MacPorts.
Installing PostgreSQL 9.0 using Homebrew gives step-by-step instructions of using HomeBrew to install PostgreSQL 9. Similar steps can be done for newer PostgreSQL.
KyngChaos PostgreSQL + GIS has the latest release package of PostgreSQL and PostGIS 2.0, as well as pgRouting. However, the packages distributed by KyngChaos are incompatible with the EnterpriseDb ones, so if you want to use KyngChaos, you’ll need to use the PostgreSQL 9.1 packaged with it as well.
You can always install and use PostgreSQL on your own server and your own LAN, but for applications running on the Internet, you may want to look for a hosting company with scalable servers and reliable bandwidth. You should avoid shared hosting environments. Though they are ridiculously cheap, you’re relegated to having little or no control over the server itself. The database that usually comes stock is MySQL or an antiquated version of PostgreSQL. Unless you’re running a fly-by-night website, we don’t recommend shared hosting.
Before the advent of virtualization and cloud computing, the only alternative to shared hosting was to have your own dedicated server. This can be a server that you lease from the hosting company or your own server placed at a hosting facility. This tried-and-true arrangement is still the way to go for large operations requiring many powerful servers and a thirst for bandwidth. With your own server, you dictate the OS and the PostgreSQL version. The drawbacks are that it does take a few days for the hosting company to build your leased rig and expect little support from the hosting company should you have a software problem. Placing your own server at a secure hosting facility tends to give you the best reliability in terms of connectivity since these facilities are built with redundancy in mind, but you’ll have to maintain your own server. This means having to dispatch your own technician to the hosting site, or even placing your own IT personnel permanently at the hosting facility.
With rampant virtualization of servers, cloud hosting gained popularity in the last few years. It fills a nice gap between restrictive shared hosting and dedicated hosting which required a high level of technical savvy.
For a list of hosts that claim PostgreSQL experience and support, check out PostgreSQL Hosting Providers. We’ll be covering hosts that we have heard positive reviews about from PostgreSQL users, or that we have direct experience with.
Dedicated Server. This is the oldest and most common kind of hosting offered suitable for PostgreSQL. It is the most responsive, but also takes the most time to set up. It is being quickly replaced by cheaper options. We won’t provide any examples of these since there are too many good ones to itemize. It tends to be the most expensive, but it provides you with the greatest control for disks you can use, as well as disk configuration. For low profile servers, the dedicated server is almost always more expensive. For high-end servers getting into the 8 CPU/terabyte disks, dedicated is on par or cheaper than Cloud and VPS. This is designed more for experienced Sys Admins and Db Admins with a tremendous need for power.Virtual Private Server (VPS)/Virtual Dedicated Serveris like a physical server in that you can treat it like any other, but it is not a physical device—instead, it’s a host on a host server. It is much like a cloud server except you usually can’t save images of it or build them yourself. The ISP builds it and charges you a fixed monthly fee based on configuration of the virtual. You are, however, usually allowed to install any additional things you want via remoting in or a control panel of options. There are more VPS providers than there are of cloud server providers, but this may change in the future.Cloud Server. A cloud server is like a VPS; in fact, in many cases, they use the same underlying technology. The main difference between cloud and standard virtual is you have more control and the billing is hour metered. As you would with a virtual dedicated server, you can install anything you want on it, manage permissions, remote into it, and sometimes add more disks or disk space. They also often come packaged with cloud storage. Where it differs from dedicated server or the conventional VPS is that you can usually save images of it and restore these images using a web console. You can delete servers, add new servers, scale up servers. Therefore, they are ideal if you are not sure how much power you will need, or even what OS you want to deploy on, or if your needs fluctuate frequently on a daily basis. They vary in offerings from cloud host to cloud host. so you’ll want to closely analyze the fine print. As far as pricing goes for an always on server with similar configuration, they are about the same price or slightly more expensive than VPS/Virtual Dedicated one.DbaaSis an upcoming type called Database as a Service (DbaaS).
You can get a Virtual for as little as $5 USD per month, but if you have higher bandwidth needs and want to do something serious with PostgreSQL (something more than hosting your blog or consulting website), we recommend spending at least $50 USD per month. These do charge you extra for bandwidth above what is packaged with the plan, so you’ll want to consider the cost of that depending on traffic you have.
Hub.org has been providing PostgreSQL and open source specific hosting services for longer than any we can think of and was founded by a PostgreSQL core team member. They offer FreeBSD VPS servers with the latest versions of PostgreSQL installed. The DBMS is dedicated for higher end plans and shared for lower plans. They also offer PostGIS hosting in the plan. Their VPS hosting starts at $5.00 USD/month, with higher ends being $60 USD per month. The disk space availability is pretty low (around 20 GB for their highest plan), so probably not suitable for large PostgreSQL databases, but fine for those with low traffic or databases under 15 GB or so.
A2Hosting offers Virtual Private Server hosting with quick installers for PostgreSQL 9.0. They also offer 24/7 support. Their VPS plans range from approximately $10 to $60 with quick installers. They offer various Linux options (CentOS, Fedora, Slackware, and Ubuntu). It also offers shared hosting with PostgreSQL 9.0.
GoDaddy Virtual Dedicated - Although GoDaddy doesn’t offer PostgreSQL and are more well known for their shared hosting SQL Server and MySQL, they are the biggest host and offer fairly cheap Virtual Dedicated hosting packages. They also offer Windows 2008+ virtual dedicated servers, in addition to CentOS and Fedora offerings—a better option for people who want to run a DBMS+ASP.NET on the same Windows box. The disk sizes are on the low end, approximately 20 GB to 120 GB. You should probably go with at least the 2 GB RAM plan, which is priced around $40 USD per month. We must say their tech support isn’t that great, but it isn’t horrible either, and is available 24/7.
Pricing usually starts around $0.02 USD per 1 GB of RAM per hour, depending on if you go with a contract or a pay-as-you go plan; keep in mind that Windows servers tend to be more pricey than the Linux/Unix Cloud server offerings. Each has their own specialty perks, so it’s hard to say one is absolutely better than another. You’ll probably want to go with at least a 2 GB motherboard RAM plan if you plan to do anything remotely serious with PostgreSQL.
As a general rule, cloud disk speeds tend to be slower than the physical disks and not optimized for databases with Amazon EC having one of the worst reputations. This may change in the future, but that’s where the technology is right now. A lot of people use cloud servers despite concerns with speed and robustness because of the sheer convenience of being able to create an image to your liking and cloning it many times.
Lots of cloud server offerings are cropping up these days. They are generally fairly
priced, easy to use with wizards, and more importantly you can have a server up an running
in less than 30 minutes. Many come with OS images that come pre-installed with PostgreSQL.
We generally prefer installing our own using the aforementioned Yum
repository, or Ubuntu apt-get, or EnterpriseDb installers, but if you
want to get up and running with PostgreSQL with additional add-ons such as GIS add-ons, a
pre-made image might better suit your needs.
Below are a couple of cloud hosts we’ve heard general good things about from PostgreSQL users or have personal experience with. As they say: your mileage may vary.
Linode is a Linux-only XEN VPS host with a fairly good reputation among PostgreSQL users. Plans offered accomodate for various flavors of Linux, including OpenSUSE, as well as automated backup options at fairly cheap prices. Linode also have various plan tiers, starting at $20 per month for 20 GB storage, going up to $160 per month for 160 GB storage and 4 GB motherboard RAM. The host is a bit of a hybrid Cloud/VPS, in that it offers similar features of standard cloud hosting, like deploying your own server with a panel, saving images but using VPS technology. Unlike standard cloud hosts, Linode doesn’t charge by the hour but instead, by the month, based on whatever plan you purchased.
GoGrid is not specifically designed for PostgreSQL, but generally performs well. These plans offer dedicated servers on the same network as your cloud servers so connectivity speeds are fast between the two. From our personal experience, GoGrid’s 24/7 tech support is superb. If you are not sure cloud server will be fast enough for your needs, GoGrid’s hybrid approach might be just what you are looking for. It offers Linux and Windows standard images at the same price, as well as some community contributed ones; you can also make your own. It is also generous with ips starting with 8 ips per account, which you can assign to the same server if you choose, allowing you to add more for a small additional cost. This is important if you plan to host several different domains each requiring their own SSL certificate on the same server. They, however, start at a higher tier than the others with their entry-level Professional Cloud, starting at $200 per month, which gets you a 4 GB/4core server with 200 GB disk and 100 GB of cloud storage. For Windows hosting, GoGrid is comparable to Amazon’s prices. Many of its options are geared toward enterprises requiring virtual private clouds.
Amazon EC (AWS) is probably the most popular choice in general for cloud servers. It is relatively cheap and you can turn off an instance if you don’t need it and not get charged for the downtime. For databases, it generally has a bad reputation, though that reputation is improving. (Here’s an interesting read on Amazon by Christophe Pettus (PostgreSQL on Amazon) that has tips and tricks of getting the most out of AWS.) If you don’t need to do super heavy lifting and don’t have terabytes of data to swing around, it’s not a bad choice. They also allow you to add new EC disks on demand for any size you want so easy to scale, disk-wise. Granted, their disks are kinda slow.
Amazon provides images for both Windows and various Linux/Free BSD distros, so there are likely more choices available than any other cloud server offering. Amazon also allows you to save the full snapshot image of a server, regardless its size, whereas other cloud offerings have limits on the maximum size you can save. However, they do not offer 24/7 tech support like many of the others.
Many Amazon EC community images come pre-installed with PostgreSQL, and they come and go. It is best to run a search in the image list, or use one specially made for what you are doing.
Generally speaking, Amazon is more hands-off than the others, so most issues are opened and closed with a form. You rarely get personalized emails. This is not to say the service is poor, since most issues involve systemwide issues that it promptly addresses. If you feel uncomftable with this arrangement or are a non-techie with lots of basic OS hand-holding needs, Amazon is probably not the best host for you.
RackSpace is not specifically designed for PostgreSQL, but we know several PostgreSQL users using it for PostgreSQL and web application, and are happy with the performance and Rackspace support team. It offers both Linux and Windows.
SoftLayer is not specifically designed for PostgreSQL but similar to GoGrid, it provides both dedicated as well as cloud hosting offerings and Private network setups. It provides hosting for both Linux and Windows. Pricing is similar to the others with hourly and monthly options.
Fairly recently, there have been database cloud offerings that focus on giving you optimized PostgreSQL installs. We’ll refer to these as database-as-a-service (DbaaS). These tend to be pricier than cloud server offerings, but in return are optimized for more database heavy load and often take care of the System DBA tasks for you.
In theory, they don’t suffer the same disk speed issues many have complained about with Server cloud offerings like Amazon EC.
These are similar to Amazon RDS (Amazon’s MySQL database service offering), SQL Server Azure (Microsoft’s SQL Server in the cloud), and even Oracle’s Public Cloud.
There are a few downsides: you are usually stuck with one version of PostgreSQL, which is usually a version behind the latest, and you may not be able to install all the extensions you want to. In other words, many of these don’t support PostGIS, which we personally can’t live without. Their starting costs tend to be pricier than server cloud offerings, but promise you better speed and optimized for Postgres.
Heroku Postgres. Heroku offers a lot of application appliances in the cloud. One of these is Heroku Postgres. which gives you up to 2 TB database storage and various pricing offerings for number of cores and hot memory (equivalent to motherboard ram). The main downside we see with Heroku is that many modules are disabled, and a user is stuck with whatever version of PostgreSQL Heroku supports (which is currently one version behind latest). For example, you can’t run PostGIS or any untrusted languages like plpythonu on this. This may change in the future. For more of a critique, read: Heroku a really easy way to get a database in a hurry.
EnterpriseDb Cloud Database. This is a PostgreSQL and PostgreSQL Plus advanced servers cloud database hosting on Amazon EC2. It just came out of beta at the time of this writing. It comes ready with elastic scale out and auto provisioning, along with the self-healing tool kits built by EnterpriseDb. In addition, you have the option to manage it yourself or have EnterpriseDb do the managing. This one does use the latest version of PostgreSQL (9.1) and does allow for installation of PostGIS, unlike the others mentioned.
CartoDB PostGIS in the Cloud is a PostgreSQL offering targeted at providing an easy interface for performing spatial queries and maps with PostGIS. It has an intro free offering that comes with canned data and CartoDb pricing tiers based on database size that allow you to load up your own spatial data in various formats. It also provides slick map interfaces for displaying spatial queries. This is the first DaaS to provide PostGIS 2.0.
VMWare vFabric Postgres is not a hosted service, but instead an engine to make a DbaaS; it’s more suited for Enterprises or ISPs looking to replicate a stampede of PostgreSQL elephants.
In this section, we list help commands of command line tools discussed in this book.
pg_dump is the command-line executable packaged with
PostgreSQL for doing individual database and selective parts of a database. It can backup to
tar, custom compressed backup, and plain text. Plain-text backups need to be restored with
psql. If you choose --inserts or --column-inserts
option, it will backup using standard SQL inserts that can be run with a tool like pgAdmin.
For examples of pg_dump usage, refer to Selective Backup Using pg_dump.
Example A-1. pg_dump help
pg_dump --help
pg_dump dumps a database as a text file or to other formats. Usage: pg_dump [OPTION]... [DBNAME] General options: -f, --file=FILENAME output file or directory name -F, --format=c|d|t|p output file format (custom, directory, tar, plain text) -v, --verbose verbose mode -Z, --compress=0-9 compression level for compressed formats --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock --help show this help, then exit --version output version information, then exit Options controlling the output content: -a, --data-only dump only the data, not the schema -b, --blobs include large objects in dump -c, --clean clean (drop) database objects before recreating -C, --create include commands to create database in dump -E, --encoding=ENCODING dump the data in encoding ENCODING -n, --schema=SCHEMA dump the named schema(s) only -N, --exclude-schema=SCHEMA do NOT dump the named schema(s) -o, --oids include OIDs in dump -O, --no-owner skip restoration of object ownership in plain-text format -s, --schema-only dump only the schema, no data -S, --superuser=NAME superuser user name to use in plain-text format -t, --table=TABLE dump the named table(s) only -T, --exclude-table=TABLE do NOT dump the named table(s) -x, --no-privileges do not dump privileges (grant/revoke) --binary-upgrade for use by upgrade utilities only --column-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore --exclude-table-data=TABLE do NOT dump data for the named table(s)--inserts dump data as INSERT commands, rather than COPY --no-security-labels do not dump security label assignments --no-tablespaces do not dump tablespace assignments --no-unlogged-table-data do not dump unlogged table data --quote-all-identifiers quote all identifiers, even if not key words --section=SECTION dump named section (pre-data, data, or post-data)
--serializable-deferrable wait until the dump can run without anomalies --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) --role=ROLENAME do SET ROLE before dump
pg_dump_all is used for doing complete plain text
server cluster backup as well as server level objects like roles and
table spaces. This feature is discussed in Systemwide Backup Using pg_dumpall.
Example A-2. pg_dumpall help
pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file. Usage: pg_dumpall [OPTION]... General options: -f, --file=FILENAME output file name --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock --help show this help, then exit --version output version information, then exit Options controlling the output content: -a, --data-only dump only the data, not the schema -c, --clean clean (drop) databases before recreating -g, --globals-only dump only global objects, no databases -o, --oids include OIDs in dump -O, --no-owner skip restoration of object ownership -r, --roles-only dump only roles, no databases or tablespaces -s, --schema-only dump only the schema, no data -S, --superuser=NAME superuser user name to use in the dump -t, --tablespaces-only dump only tablespaces, no databases or roles -x, --no-privileges do not dump privileges (grant/revoke) --binary-upgrade for use by upgrade utilities only --column-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore --inserts dump data as INSERT commands, rather than COPY --no-security-labels do not dump security label assignments --no-tablespaces do not dump tablespace assignments --no-unlogged-table-data do not dump unlogged table data --quote-all-identifiers quote all identifiers, even if not key words --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead o ALTER OWNER commands to set ownership Connection options: -h, --host=HOSTNAME database server host or socket directory -l, --database=DBNAME alternative default database -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) --role=ROLENAME do SET ROLE before dump If -f/--file is not used, then the SQL script will be written to the standard output.
pg_restore is the command-line tool packaged with PostgreSQL for doing database restores of compressed, tar, and directory backups created by pg_dump. Examples of its use are available in Restore.
Example A-3. pg_restore help
pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.
Usage:
pg_restore [OPTION]... [FILE]
General options:
-d, --dbname=NAME connect to database name
-f, --file=FILENAME output file name
-F, --format=c|d|t backup file format (should be automatic)
-l, --list print summarized TOC of the archive
-v, --verbose verbose mode
--help show this help, then exit
--version output version information, then exit
Options controlling the restore:
-a, --data-only restore only the data, no schema
-c, --clean clean (drop) database objects before recreating
-C, --create create the target database
-e, --exit-on-error exit on error, default is to continue
-I, --index=NAME restore named index
-j, --jobs=NUM use this many parallel jobs to restore
-L, --use-list=FILENAME use table of contents from this file for
selecting/ordering output
-n, --schema=NAME restore only objects in this schema
-O, --no-owner skip restoration of object ownership
-P, --function=NAME(args)
restore named function
-s, --schema-only restore only the schema, no data
-S, --superuser=NAME superuser user name to use for disabling triggers
-t, --table=NAME restore named table
-T, --trigger=NAME restore named trigger
-x, --no-privileges skip restoration of access privileges (grant/revoke)
-1, --single-transaction
restore as a single transaction
--disable-triggers disable triggers during data-only restore
--no-data-for-failed-tables
do not restore data of tables that could not be
created
--no-security-labels do not restore security labels
--no-tablespaces do not restore tablespace assignments
--section=SECTION restore named section (pre-data, data, or post-data)
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before restorepsql is a tool for doing interactive querying as well as running command-line scripted tasks. In this section, we’ll list both the command line and interactive commands of psql.
This section lists commands available in psql when you launch an interactive session. For examples of usage, refer to Interactive psql and Non-Interactive psql.
Example A-4. Getting list of interactive help commands
psql ?
General
copyright show PostgreSQL usage and distribution terms
g [FILE] or ; execute query (and send results to file or |pipe)
h [NAME] help on syntax of SQL commands, * for all commands
q quit psql
Query Buffer
e [FILE] [LINE] edit the query buffer (or file) with external editor
ef [FUNCNAME [LINE]] edit function definition with external editor
p show the contents of the query buffer
reset (clear) the query buffer
w FILE write query buffer to file
Input/Output
copy ... perform SQL COPY with data stream to the client host
echo [STRING] write string to standard output
i FILE execute commands from file
ir FILE as i, but relative to location of current script
o [FILE] send all query results to file or |pipe
qecho [STRING] write string to query output stream (see o)
Informational
(options: S = show system objects, + = additional detail)
d[S+] list tables, views, and sequences
d[S+] NAME describe table, view, sequence, or index
da[S] [PATTERN] list aggregates
db[+] [PATTERN] list tablespaces
dc[S] [PATTERN] list conversions
dC [PATTERN] list casts
dd[S] [PATTERN] show comments on objects
ddp [PATTERN] list default privileges
dD[S] [PATTERN] list domains
det[+] [PATTERN] list foreign tables
des[+] [PATTERN] list foreign servers
deu[+] [PATTERN] list user mappings
dew[+] [PATTERN] list foreign-data wrappers
df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
dF[+] [PATTERN] list text search configurations
dFd[+] [PATTERN] list text search dictionaries
dFp[+] [PATTERN] list text search parsers
dFt[+] [PATTERN] list text search templates
dg[+] [PATTERN] list roles
di[S+] [PATTERN] list indexes
dl list large objects, same as lo_list
dL[S+] [PATTERN] list procedural languages
dn[S+] [PATTERN] list schemas
do[S] [PATTERN] list operators
dO[S+] [PATTERN] list collations
dp [PATTERN] list table, view, and sequence access privileges
drds [PATRN1 [PATRN2]] list per-database role settings
ds[S+] [PATTERN] list sequences
dt[S+] [PATTERN] list tables
dT[S+] [PATTERN] list data types
du[+] [PATTERN] list roles
dv[S+] [PATTERN] list views
dE[S+] [PATTERN] list foreign tables
dx[+] [PATTERN] list extensions
l[+] list all databases
sf[+] FUNCNAME show a function's definition
z [PATTERN] same as dp
Formatting
a toggle between unaligned and aligned output mode
C [STRING] set table title, or unset if none
f [STRING] show or set field separator for unaligned query output
H toggle HTML output mode (currently off)
pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|fieldsep_zero | footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
[on|off] show only rows (currently off)
T [STRING] set HTML <table> tag attributes, or unset if none
x [on|off] toggle expanded output (currently off)
Connection
c[onnect] [DBNAME|- USER|- HOST|- PORT|-]
connect to new database (currently "postgres")
encoding [ENCODING] show or set client encoding
password [USERNAME] securely change the password for a user
conninfo display information about current connection
Operating System
cd [DIR] change the current working directory
setenv NAME [VALUE] set or unset environment variable 
iming [on|off] toggle timing of commands (currently off)
! [COMMAND] execute command in shell or start interactive shellExample A-5 shows the non-interactive command helps screen. Examples of its usage are covered in Non-Interactive psql.
Example A-5. psql Basic Help screen
psql --help
psql is the PostgreSQL interactive terminal.
Usage:
psql [OPTION]... [DBNAME [USERNAME]]
General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 ("one"), --single-transaction
execute command file as a single transaction
--help show this help, then exit
--version output version information, then exit
Input and output options:
-a, --echo-all echo all input from script
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)
Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
set field separator (default: "|")
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see pset command)
-R, --record-separator=STRING
set record separator (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator to zero byte
-0, --record-separator-zero
set record separator to zero byte
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port (default: "5432")
-U, --username=USERNAME database user name
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
For more information, type "?" (for internal commands) or "help" (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL
documentation.