"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

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
`\d` or `\d+`	Show extended table description
`\d $table_name`	Show full definition for the given table
`\df`	List functions
`\di`	List indexes
`\c`	Create new connection. There are various syntaxes to accomplish this. Here is one: `\c "host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=disable"`
`\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`.

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}"