Developer's Guide to PostgreSQL on Linux: psql shell
By
Introduction
This is the second article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I introduce the psql interactive shell using the default postgres user.
Series Contents
- Installation
- psql interactive shell (you're here)
- Creating Databases
- Creating Accounts and Making Connections
- Changing the Default Data Directory
- Using Explain and Analyze (coming soon)
- Utilitizing Indexes (coming soon)
- Creating Backups (coming soon)
- Restoring From Backups (coming soon)
Connecting to the psql Interactive Shell with postgres Linux User
Once the PostgreSQL service has been installed and the cluster has been initialized as described in Developer's Guide to PostgreSQL on Linux: Installation a Linux system user account is created named postgres. The postgres user serves as the database cluster's superuser.
# id postgres
uid=111(postgres) gid=116(postgres) groups=116(postgres),115(ssl-cert)The postgres superuser is usually used to do administrative tasks like creating applications databases and users (also referred to as user roles).
Once logged into either the Ubuntu or CentOS server with PostgreSQL installed and initialized I can switch to the postgres user to interact with the default database cluster using the handy psql interactive shell client program.
# su - postgres
$ psql
psql (11.5 (Ubuntu 11.5-1.pgdg18.04+1))
Type "help" for help.
postgres=# When you execute the psql command as the postgres user the default behavior is to connect to the postgres database. In a fashion similar to how standard Linux terminals display a prompt symbol of # for the root user or $ for all other users, the psql shell displays a $ prompt symbol for the postgres superuser and a $ for all other users.
The psql program has a number of useful commands for inspecting and interacting with the contents of the database cluster.
Listing Databases
By default a PostgreSQL customer comes with the three databases postgres, template1 and template0. To list the databases present use the \l command.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+----------+----------+---------+---------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)Listing the User Roles of the PostgreSQL Cluster
To list the user roles available in the cluster use the \du command.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}Executing SQL Statements From a File
Often times it is useful to execute a set of previously generated SQL commands from a file. With the psql program this is accomplished with \i filename as I show below to build a movies database. You can download the movies_db.sql file used in this tutorial series if you would like to follow along.
postgres=# \i movies_db.sqlNow when I list the contents of the cluster I see a new database named movies.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+---------+-----------------------
movies | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)Switching Connections to Another Database
To switch connections to another database within the PostgreSQL cluster use \c dbname
postgres=# \c movies
You are now connected to database "movies" as user "postgres".
movies=# Listing Tables, View and Sequences
To list the tables views or sequences in the database you are connected to issue the \d command
movies=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------+----------+----------
public | genres | table | postgres
public | genres_id_seq | sequence | postgres
public | movies | table | postgres
public | movies_id_seq | sequence | postgres
(4 rows)To list the contents of a table issue the command \d tablename
movies=# \d genres
Table "public.genres"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('genres_id_seq'::regclass)
name | character varying(100) | | |
Indexes:
"genres_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "movies" CONSTRAINT "movies_genre_fx" FOREIGN KEY (genre_id) REFERENCES genres(id)
movies=# \d movies
Table "public.movies"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('movies_id_seq'::regclass)
title | character varying(200) | | not null |
year | integer | | |
runtime | integer | | |
genre_id | integer | | |
Indexes:
"movies_pkey" PRIMARY KEY, btree (id)
"movies_title_key" UNIQUE CONSTRAINT, btree (title)
Foreign-key constraints:
"movies_genre_fx" FOREIGN KEY (genre_id) REFERENCES genres(id)Listing Functions and Stored Procedures
To list available functions or stored procedures in a database use \df
movies=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------------+------------------+---------------------------------------------------------------------------------+------
public | save_movie | | title character varying, year integer, runtime integer, genre character varying | proc
(1 row)Showing the Definition of a Function or Stored Procedure
To display the definition of functions or stored procedures issue the command \sf function_name
movies=# \sf save_movie
CREATE OR REPLACE PROCEDURE public.save_movie(title character varying, year integer, runtime integer, genre character varying)
LANGUAGE plpgsql
AS $procedure$
DECLARE
genre_id INTEGER;
movie_id INTEGER;
BEGIN
SELECT id INTO genre_id FROM genres WHERE name = genre;
IF genre_id IS NULL THEN
INSERT INTO genres (name) VALUES (genre) RETURNING id INTO genre_id;
RAISE NOTICE 'Created: genres(id=%, genre=%)', genre_id, genre;
END IF;
INSERT INTO movies ("title", "year", "runtime", "genre_id")
VALUES (title, year, runtime, genre_id)
RETURNING id INTO movie_id;
RAISE NOTICE 'Created: movies(id=%, title=%, year=%, runtime=%, genre_id=%)',
movie_id, title, year, runtime, genre_id;
COMMIT;
END $procedure$Quitting the psql Shell
To quit the psql interactive shell type \q in the shell.
movies=# \q
$Resources for Learning More About PostgreSQL
- A great, example driven, reference book on robust management of PostgreSQL is PostgreSQL High Availability Cookbook - Second Edition
- A good companion book that is again example driven but focusing on performance is PostgreSQL High Performance Cookbook
thecodinginterface.com earns commision from sales of linked products such as the books above. This enables providing continued free tutorials and content so, thank you for supporting the authors of these resources as well as thecodinginterface.com
Conclusion
In this article I demonstrated how to use the psql shell to display and interact with the contents of the PostgreSQL cluster such as databases, tables, and functions or stored procedures. The psql program is a very powerful and full featured tool that does much more than I introduced in this article. I invite the reader to explore the docs and experiment with it further.