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
- Official images: https://hub.docker.com/_/postgres
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
- pgcli - "Pgcli is a command line interface for Postgres with auto-completion and syntax highlighting." https://github.com/dbcli/pgcli
- http://www.pgadmin.org - Graphical UI for postgres
- MySQL - Another relational database
- SQLite - File based local database that does not require a server.