Running a postgres development environment in macOS

postgres-dev-env.md

Running a postgres development environment in macOS

The challenge is to set up a straightforward development environment for experimenting and conducting basic backend development tasks on my Mac. The underlying concept is to run a PostgreSQL instance as needed, without getting bogged down in the intricacies, and to simultaneously accommodate multiple versions or configurations.

Requirements:

• Homebrew. You can install everything without Hombrew, but it's easier to maintain this way.
• Docker, to run posgres images

How to

1. Install pgAdmin via homebrew

brew install --cask pgadmin4

2. Run the docker container. In this example the password is "development". It's obvious, but i prefer to make it extremely clear...

docker run --name my_database -p 5432:5432 -e POSTGRES_PASSWORD=development postgres:14

3. Connect to 127.0.0.1:5432 using pgAdmin using the password "development" (the one we used in the docker run command)

Basic docker commands I always forget

# list containers
docker ps
docker ps -a

# list images
docker images

# kill container
docker kill <pid>

# run in container
docker exec -it <container name> <command>

# remove container
docker rm <container_id>

# remove container
docker rmi <image_id>

# run command in a new container
# Official docs: https://docs.docker.com/engine/reference/commandline/run/
docker run [OPTIONS] IMAGE [COMMAND] [ARG...]

# Bash into a container (always useful to know). You get the container name using "docker ps"
docker exec -it <container name> /bin/bash

To re-start a previously stopped container look for it's ID, and start it.

~/ » docker ps -a                             felipemallea@M-FMALLEAS
CONTAINER ID   IMAGE         COMMAND                  CREATED        STATUS                    PORTS     NAMES
e0631e67db9c   postgres:14   "docker-entrypoint.s…"   41 hours ago   Exited (0) 41 hours ago             my_database
--------------------------------------------------------------------------------------------------------------------------
~/ » docker start e0631e67db9c
e0631e67db9c

Basic SQL commands I always forget

Create database Ref: https://www.postgresqltutorial.com/postgresql-administration/postgresql-create-database/

CREATE DATABASE database_name
WITH
   [OWNER =  role_name]
   [TEMPLATE = template]
   [ENCODING = encoding]
   [LC_COLLATE = collate]
   [LC_CTYPE = ctype]
   [TABLESPACE = tablespace_name]
   [ALLOW_CONNECTIONS = true | false]
   [CONNECTION LIMIT = max_concurrent_connection]
   [IS_TEMPLATE = true | false ]

Create user Docs: https://www.postgresql.org/docs/8.0/sql-createuser.html

CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:
    
      SYSID uid 
    | CREATEDB | NOCREATEDB
    | CREATEUSER | NOCREATEUSER
    | IN GROUP groupname [, ...]
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'abstime' 

Grant user permissions on database ...