PART 2 · PSQL CONSOLE CLIENT
Inspecting the live database in psql: metacommands and strict mode
When connecting to an unfamiliar server, an engineer can learn everything about the database in 30 seconds using five psql metacommands
# 1. Интерактивный вход в контейнер PostgreSQL
docker exec -it itmo_pg_dba psql -U postgres -d gamedb
# 2. Главные метакоманды ревизии внутри psql:
\dn -- список схем (увидим схему game)
\dt game.* -- список всех таблиц в схеме game
\dv game.* -- список представлений (VIEW) в схеме game
\d+ game.purchases -- колонки, типы, PK, FK, CHECK и ссылки!
\du -- список ролей (game_owner, game_app, game_readonly)
\dp game.* -- матрица прав доступа (Access privileges)
# 3. Неинтерактивный запуск со строгой остановкой на первой ошибке:
docker exec -i itmo_pg_dba \
psql -v ON_ERROR_STOP=1 -U postgres -d gamedb < live_admin_pg.sql
Why is the -v ON_ERROR_STOP=1 option needed?
• By default, when psql reads an SQL file, it prints an error and continues executing the following statements!
• If `CREATE TABLE players` fails due to a typo, the script will continue, fail on the foreign key constraint, and still return a status code of 0.
• The -v ON_ERROR_STOP=1 flag immediately terminates execution with a non-zero error code.
How to Interpret the \dp Output (Access Rights)
In the "Access privileges" column, an entry such as `game_app=arwd/game_owner` means:
• a (append) — INSERT · r (read) — SELECT
• w (write) — UPDATE · d (delete) — DELETE
If the purchases table has game_app=ar/game_owner, the service can only read and add records!
A psql life hack: The \x auto command enables vertical output mode for wide tables so that lines don't wrap.