Skip to content

PostgreSQL

"PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance." - https://www.postgresql.org

Usage

Connect to a database

psql "postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOSTNAME}:${POSTGRES_PORT:-5432}/${POSTGRES_DB}"

Meta Commands

postgres shell has a lot of meta commands. See https://www.postgresql.org/docs/11/app-psql.html#APP-PSQL-META-COMMANDS for full descriptions.

meta-command behavior
\l List databases. Filter like \l foo*
\c Connect to database. There are various syntaxes to accomplish this. Here is one: \c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"
\d "$table_name" Show full definition for the given table
\d or \d+ Show extended table description. Show all with \d+ *
\df List functions
\di List indexes
\x Toggle expanded display. This is the same as \G in MySQL, separating each record and showing each column as a row formatted as column_name \| row value.

Show postgres version

astronomer_houston=> SELECT version();
                                          version
--------------------------------------------------------------------------------------------
 PostgreSQL 9.6.18 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1 , 64-bit
(1 row)

Show processes

Each process is one connection to the db. (See How Connections are Establisted)

select * from pg_stat_activity ;

or

select count(*) from pg_stat_activity where usename = 'airflow' ; -- note this is usename, not useRname

or a more refined view

select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

Postgres in Docker

Some of these syntaxes apply to non-docker interactions too, so long as you remove the docker-isms from them.

Example docker-compose file for local development

## https://docs.docker.com/compose/compose-file/
version: "3.7"
services:
  postgres:
    # https://hub.docker.com/_/postgres
    image: "postgres:latest"
    restart: "always"
    env_file: .env # Should contain POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD
    # Uncomment the 'command' line to enable postgres query logging to the terminal
    # https://www.postgresql.org/docs/11/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN
    # command: [ "postgres", "-c", "log_destination=stderr", "-c", "log_min_messages=debug", "-c", "log_min_error_statement=debug" ]

    expose:
      - "5432"
    ports:
      - "5432:5432"

Dump a database

https://www.postgresql.org/docs/11/app-pgdump.html

docker exec "${POSTGRES_CONTAINER}" pg_dump -U "${POSTGRES_USER}" "${POSTGRES_DB}"

Full backups should be performed with pg_dumpall.

Load local data into a db

https://www.postgresql.org/docs/11/app-psql.html

cat foo.sql |
docker exec -i "${POSTGRES_CONTAINER}" \
  psql -U "${POSTGRES_USER}" "${POSTGRES_DB}"

See Also