When it comes to administering Postgres databases, there’s a wide variety of third party tools available such as SQL Workbench/J or pgAdmin III. However, Postgres itself comes bundled with a powerful command line tool called
psql which is great for those familiar with the terminal or looking to script administration tasks. One of the most common tasks a database administrator performs is simply getting familiar with the environment. This involves asking questions like “Which databases reside on this server?” or “What tables are stored in a particular database on this server?”. In this tutorial, we will learn how to answer these key questions from the command line using
In addition to being able to submit raw SQL queries to the server via
psql you can also take advantage of the
psql meta-commands to obtain information from the server. Meta-commands are commands that are evaluated by
psql and often translated into SQL that is issued against the system tables on the server, saving administrators time when performing routine tasks. They are denoted by a backslash and then followed by the command and its arguments. We will see some examples of this below.
A single Postgres server process can manage multiple databases at the same time. Each database is stored as a separate set of files in its own directory within the server’s data directory. To view all of the defined databases on the server you can use the
\list meta-command or its shortcut
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | sales | ubuntu | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows)
Most Postgres servers have three databases defined by default:
template1 are skeleton databases that are or can be used by the
CREATE DATABASE command.
postgres is the default database you will connect to before you have created any other databases. Once you have created another database you will want to switch to it in order to create tables and insert data. Often, when working with servers that manage multiple databases, you’ll find the need to jump between databases frequently. This can be done with the
\connect meta-command or its shortcut
postgres=# \c sales You are now connected to database "sales" as user "ubuntu". sales=#
Once you’ve connected to a database, you will want to inspect which tables have been created there. This can be done with the
\dt meta-command. However, if there are no tables you will get no output.
sales=# \dt No relations found. sales=#
After creating a table, it will be returned in a tabular list of created tables.
sales=# CREATE TABLE leads (id INTEGER PRIMARY KEY, name VARCHAR); CREATE TABLE sales=# \dt List of relations Schema | Name | Type | Owner --------+-------+-------+-------- public | leads | table | ubuntu (1 row) sales=#