Postgres: psql and basic queries

Why DevOps needs the database

Almost every service has a database behind it, and very often that's PostgreSQL. Developers write the complex queries. A DevOps engineer is expected to do something else: connect, look at what's inside, check whether the database is alive, and figure out why the application can't connect to it. That minimum covers most of the day-to-day work around a database.

Connecting through psql

psql is PostgreSQL's console client. A connection is defined by four parameters: host, port, user, and database.

psql -h db.example.com -p 5432 -U appuser -d appdb

The same parameters fit into one connection string. An application usually gets it this way, through an environment variable, for example DATABASE_URL:

psql "postgres://appuser:secret@db.example.com:5432/appdb"

The scheme can be postgres:// or postgresql://, psql understands both.

A password right in the command is fine only as an example. In real work it ends up in the shell history and in the process list. The safest option is a ~/.pgpass file with 0600 permissions: psql picks up the password from there on its own. The PGPASSWORD variable also works, but the PostgreSQL documentation advises against it: on some systems a process's environment is visible to other users through ps. Keep the connection string with the password just as secret as the password itself.

If PostgreSQL is running in a container and you don't have psql on your own machine, the client is already inside the image:

docker exec -it pgb-shop psql -U appuser -d appdb

Inside psql there are backslash commands that help you look around:

\l           list databases
\dt          tables in the current database
\d users     structure of the users table
\du          roles (users)
\q           quit

Here's what \dt looks like in a database with two tables:

         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+---------
 public | orders | table | appuser
 public | users  | table | appuser
(2 rows)

Basic queries

A SQL query in psql ends with a semicolon: until there is one, psql waits for more input. Four main actions:

-- read
SELECT id, email FROM users WHERE active = true LIMIT 10;

-- add
INSERT INTO users (email, active) VALUES ('a@b.com', true);

-- change
UPDATE users SET active = false WHERE id = 42;

-- delete
DELETE FROM users WHERE id = 42;

After UPDATE and DELETE, psql prints how many rows were affected: UPDATE 1, DELETE 3. Read that number every time.

The most dangerous case here is UPDATE and DELETE without WHERE: they touch the whole table. On a table with 12 rows, such an UPDATE responds with UPDATE 12, and there's no undo. A habit that saves you: run a SELECT with the same WHERE first, make sure it returns exactly the rows you want, and only then change them.

SELECT id, email FROM users WHERE id = 42;
UPDATE users SET active = false WHERE id = 42;

Useful for ops work

A few queries that DevOps specifically needs:

-- how many clients are connected
SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend';

-- database size
SELECT pg_size_pretty(pg_database_size('appdb'));

-- what's running right now, besides this query
SELECT pid, datname, state, query_start, query
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid();

pg_stat_activity is a system view: one row per server process. The columns you'll use most:

• pid the process number serving the connection;
• datname the database the client is connected to;
• state what the process is doing: active is running a query, idle is waiting for the next command;
• query_start when the current query started, which shows you the long-running ones;
• query the query text.

This view shows you a stuck long-running query, or that connections are close to the limit. The lesson on connection pools covers the connection limit and how to avoid hitting it.

Common mistakes

• UPDATE or DELETE without WHERE, or with a loose condition. WHERE email LIKE 'petr%' will catch every Petrov, not just one. Check the condition with a SELECT and look at the number in the response.
• Counting connections with SELECT count(*) FROM pg_stat_activity and no filter. Starting with PostgreSQL 10, the view also shows the server's own background processes (checkpointer, walwriter, autovacuum launcher, and others). On an empty server such a query returns around five rows, even though only one client is connected. The filter backend_type = 'client backend' leaves only clients.
• A state = 'active' selection always includes your own query to pg_stat_activity. It's filtered out by pid <> pg_backend_pid().
• A regular user without superuser rights or the pg_read_all_stats role can see other connections, but instead of the query text gets <insufficient privilege>, and state is empty. Connect with a user that has the right permissions when you're troubleshooting.
• A forgotten semicolon. The psql prompt changes from appdb=# to appdb-#: the query hasn't been sent, psql is waiting for more. Add ; and press Enter.

What you'll need for the tasks

• docker exec -it <container> psql -U <user> -d <database> and quitting with \q.
• \dt and \d <table> to look around.
• SELECT with WHERE to check a condition, then UPDATE, INSERT, DELETE.
• pg_stat_activity and the columns pid, datname, state, backend_type, query_start, query.

The next lesson covers migrations: how to change the database schema alongside the code.