Contents:

• Creating new database and user
• Running in a Docker container
• Backup and Restore
• Documentation

Creating new database and user #

Open the client:

psql -U postgres

Execute:

CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
ALTER DATABASE yourdbname OWNER TO youruser;

Running in a Docker container #

Setup for docker-compose.yaml:

services:
  postgresdb:
    container_name: postgresdb
    image: 'postgres:15-alpine'
    volumes:
      - 'postgres-db:/var/lib/postgresql/data'
    restart: unless-stopped
    env_file:
      - ./envs/postgres.env
    networks:
      - main

networks:
  main:
    name: main

volumes:
  postgres-db:

Where ./envs/postgres.env:

POSTGRES_PASSWORD="your-admin-password"

Backup and Restore #

Assuming a Docker container, to create a backup:

docker exec -i postgresdb pg_dumpall -U postgres | gzip > /tmp/dump.sql.gz

To restore from that backup:

gunzip -c /tmp/dump.sql.gz | docker exec -i postgresdb psql -U postgres

Documentation #

• PostgreSQL Lock Conflicts;