INDEX
########################################################### 2024-01-16 11:05 ########################################################### That Devops Guy... Learning Postgres https://www.youtube.com/watch?v=5q-mXycaPBs Running postgres is easy - but is essetial to getting things working # Mininmal info you need is a password docker run --name some-postgres -e POSTGRES_PASSWORD=password -d postres # Alternatively use Docker compose # docker-compose.yml version: '3.1' services: db: # The actual postres database image: postgres restart: always environment: POSTGRES_PASSWORD: example adminer: # Admin utility for viewing DB on localhost:8080 image: adminer restart: always ports: - 8080:8080 docker compose up # Now log onto website with db=db, username=postgres password=... database=postgres If container is stopped or removed, all the data is lost Postgres stores data in "/var/lib/postgresql/data" so you want to mount this externally docker run -it --rm --name postgres -e POSTGRES_PASSWORD=admin123 \ -v ${PWD}/pgdata:/var/lib/postgresql/data postgres:15.0 # Mount and set postgres version ` docker exec -it postgres bash psql -h localhost -U postgres # Interact with database in server - trusts without password CREATE TABLE customers (firstname text, lastname text, customer_id serial); INSERT INTO customers (firstname, lastname) VALUES ("Bob", "Smith") \dt # Print the list of tables SELECT * FROM customers; # View contents of table "customers" docker rm -f postgres # Data is persisted in ./pgdata folder # Rerun container and database will work fully as before Can now make a basic docker compose for exposing the database # docker-compose.yml version: '3.1' services: db: image: postgres:15.0 # Set a specific version restart: always environment: POSTGRES_PASSWORD: admin123 ports: - 5000:5432 # Map postgres port (5432) externally as 5000 volumes: - ./pgdata:/var/lib/postgresql/data # Store database persistently adminer: image: adminer restart: always ports: - 8080:8080 docker compose up # Runs with mounts and port mapping
That Devops Guy... How to Configure PostgreSQL https://www.youtube.com/watch?v=WyWCa6WVx60 Main variables are POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB and PGDATA (storage folder) PG_IDENT.conf maps linux system users to postgres PG_HBA.conf is like the postgres firewall to allow certain IPs access POSTGRESQL.conf where you control everything postgres has All of these config files are inside /var/lib/postgresql/data docker run -it --rm --name postgres -e POSTGRES_PASSWORD=admin123 \ -v ${PWD}/pgdata:/var/lib/postgresql/data -p 5000:5432 postgres:15.0 # Store all of these config files in ./config locally # pg_dba.conf (firewall) ## "local" is for Unix domain socket connections only - allow all local local all all trust ## IPv4 local connections: host all all 127.0.0.1/32 ## IPv6 local connections: host all all ::1/128 ## Allow replication connections from localhost, by a user with replication privilege local replication all trust host replication all 127.0.0.1 trust host replication all ::1/128 trust # For anything untrusted, require sha-256 password host all all all scram-sha-256 # pg_ident.conf (users) ## Can use regex to match system users to db users ## mymap /^(.*)@mydomain\.com$ guest # postgresql.conf (main config) ## File locations - set where config files are stored - be explicit) data_directory = '/data' hba_file = '/config/pb_hba.conf' ident_file = '/config/pg_ident.conf' ## Connections and auth - configure network, locales, resource usage etc. port = 5432 listen_address = '*' max_connections 100 shared_buffers = 128MB dynamic_shared_memory_type = posix max_wal_size = 1GB # Write ahead log - transaction logs for replications min_wal_size = 80MB log_timezone = 'Etc/UTC' datestyle = 'iso, mdy' timezone = 'Etc/UTC' lc_messages = 'en_US.utf8' lc_monetary = 'en_US.utf8' lc_numeric = 'en_US.utf8' lc_time = 'en_US.utf8' default_text_search_config = 'pg_catalo.english' Now you want to mount these config files and force postgres to use them sudo chown 999:999 config/{poregresql,pb_hba,pb_ident}.conf # 999 = postgres user # PBDATA variable has to be set before postgres runs, even though it gets it from config docker run -it --rm --name postgres -e POSTGRES_USER=postgresadmin \ -e POSTGRES_PASSWORD=admin123 -e POSTGRES_DB=postgresdb -e PBDATA="/data" \ -v ${PWD}/pbdata:/data -v ${PWD}/config:/config -p 5000:5432 \ postgres:15.0 -c 'config_file=/config/postgresql.conf' # Need to pass in where initial config file is # docker-compose.yaml version: '3.1' services: db: container_name: postgres image: postgres:15.0 command: "postgres -c config_file=/config/postgresql.conf" # Pass command to run environment: POSTGRES_USER: "postgresadmin" POSTGRES_PASSWORD: "admin123" POSTGRES_DB: "postgresdb" PGDATA: "/data" ports: - 5000:5432 volumes: - ./pgdata:/data - ./config:/config adminer: image: adminer restart: always ports: - 8080:8080 docker compose up
That Devops Guy... How to Replicate PostgreSQL https://www.youtube.com/watch?v=FC2JMBYDcJE Launch postgres instance 1 then launch a container to take a backup and launch instance 2 We now label postgres containers (and their config/volumes/networks) uniquely docker network create postgres docker run -it --rm --name postgres-1 --net postgres -e ... \ # SET variables -v ${PWD}/postgres-1/pgdata:/data -v ${PWD}/postgres-1/config:/config \ -v ${PWD}/postgres-1/archive:/mnt/server/archive -p 5000:5432 \ postgres:15.0 -c 'config_file=/config/postgresql.conf' # Need folder structure [postgres-1/config/...] and [postgres-2/config/...] # Once run it also creates [postgres-N/archive] and [postgres-N/pgdata] # Need to create a replication user that can copy data docker exec -it postres-1 bash createuser -U postgresadmin -P -c 5 --replication replicationUser # Allow up to 5 connections # (extending) postgres-1/config/pg_hba.conf host replication replicationUser 0.0.0.0/9 md5 ... Write-ahead logging is where the db writes a transaction logs an action before it is run This is key to replication as these logs are what tells postgres to write data Also means that if there is a crash the database can be recovered # Set these in config to enable replication wal_level = replica archive_mode = on archive_command = 'test ! -f /mnt/server/archive/%f && cp %p /mnt/server/archive/%f' max_wal_senders = 3 # Now if you kill the database the archive folder will have a new file Postgres is not great for high availability but has pgbase_backup for replicating db So you copy a database, copy its transaction logs and replicate as read-only docker run -it --rm --net postgres -v ${PWD}/postgres-2/pgdata:/data \ --entrypoint /bin/bash postgres:15.0 # Open shell in postgres network # Take backup of postgres-1 (fills up postgres-1/archive and postgres-2/pgdata) pg_basebackup -h postgres-1 -p 5432 -U replicationUser -D /data/ -Fp -Xs -R # Run 2nd postgres instance same as the 1st one (but using N=2 and port 5001) # Starts in standby mode, waiting for signal from postgres-1 Simulate failover and use pg_ctl to elevate postgres-2 from secondary(ro) to primary(rw) docker rm -f postgres-1 # Kill database docker exec -it postgres-2 bash; psql --username=postgresadmin postgresdb # Connect to db2 runuser -u postgres -- pg_ctl promote # Now primary and writeable
That Devops Guy... How to run PostgreSQL in Kubernetes https://www.youtube.com/watch?v=iYwE3h0p7Zs Postgres is not designed to work in kubernetes - but it can work Main question is how you manage cluster upgrades, as it will delete pods kind create cluster --name postgresql --image kindest/node:v1.28.0 kubectl create ns postgresql # Store environment variables as secrets (POSTGRES_... and REPLICATION_USER/PASSWORD) kubectl -n postgresql create secret generic postgresql \ --from-literal POSTGRES_USER="postgresadmin" ... # Repeat for all variables # Can't use a pod or deployment as these will remove data - need a StatefulSet # statefulset.yaml --- [ConfigMap] data: pg_hba.conf: | # Paste in pg_hba.conf file postgresql.conf: | # Paste in postgresql.conf file --- [StatefulSet] spec: selector: matchLabels: app: postgres serviceName: "postgres" replicas: 1 template: metadata: labels: app: postgres spec: terminationGracePeriodSeconds: 30 initContainers: - name: init # Have to run code to setup archive directory image: postgres:15.0 comand: [ "bash", "-c" ] args: - | # create archive directory on startup mkdir -p /data/archive && chown -R 999:999 /data/archive volumeMounts: - name: data mountPath: /data containers: - name: postgres image: postgres:15.0 args: ["-c", "config_file=/config/postgresql.conf"] ports: - containerPort: 5432 name: database env: - name: PGDATA value: "/data/pgdata" - name: POSTGRES_USER valueFrom: secretKeyRef: name: postgresql key: POSTGRES_PASSWORD optional: false - name: POSTGRES_DB valueFrom: secretKeyRef: name: postgresql key: POSTGRES_DB optional: false volumeMounts: - name: config mountPath: /config readOnly: false - name: data mountPath: /data readOnly: false volumes: - name: config configMap: name: postgres defaultMode: 0755 volumeClaimTemplate: - metadata: name: data spec: accessModes: [ "ReadWriteOnce" ] storageClassName: "standard" # Important you set this correctly resources: requests: storage: 100Mi --- [Service] spec: ports: - port: 5432 targetPort: 5432 name: postgres clusterIP: None # Will get a predictable, consistent hostname selector: app: postgres kubectl -n postgresql apply -f statefulset.yaml kubectl -n postgresql logs postgres-0 kubectl -n postgresql delete pod postgres-0 # Deletes pod but new one gets recreated # As data is stored externally, no data is lost