Postgres

pg_stat_statements is useful to gather performance information about queries so lets add it to our dockerized postgres database. Using docker-compose Using docker-compose we just need to add the following docker-compose.yaml: docker-compose.yml version: '3.5' services: postgres: container_name: postgres_container image: postgres ports: - "5432:5432" volumes: - /var/lib/postgresql/data # used for query profiling, deactivate for enhanced performance command: postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all -c max_connections=200 environment: POSTGRES_USER: postgres POSTGRES_PASSWORD: thepassword We simply start our Postgres database with docker-compose: ...

Steps Pull image and run with ports exposed: docker run --name "nifi" -p 8443:8443 -d apache/nifi:latest Fetch the generated username and password from the logs: docker logs nifi | grep -A1 "Generated Username" Generated Username [8f6d91f7-733e-40cf-b900-059ea9dccbf2] Generated Password [v7KGiiRYLJL2+HzhKOqz1rbgiPOaWz0B] Now we may enter the https://localhost:8443/nifi/login in our browser, accept the security exemption and login with the credentials from above, voila! Installing additional connectors I have found a nice summary on the following GitHub repository: ...

A simple setup when two Postgres databases prefilled with schema/data needed. docker-compose.yml version: '3.6' services: postgres1: image: postgres restart: always environment: - DATABASE_HOST=127.0.0.1 - POSTGRES_USER=root - POSTGRES_PASSWORD=root - POSTGRES_DB=root ports: - "15432:15432" volumes: - ./postgres1-init.sql:/docker-entrypoint-initdb.d/docker_postgres_init.sql postgres2: image: postgres restart: always environment: - DATABASE_HOST=127.0.0.1 - POSTGRES_USER=root - POSTGRES_PASSWORD=root - POSTGRES_DB=root ports: - "25432:25432" volumes: - ./postgres2-init.sql:/docker-entrypoint-initdb.d/docker_postgres_init.sql And our sample init scripts: CREATE USER tester WITH PASSWORD 'tester' CREATEDB; CREATE DATABASE testdb WITH OWNER = tester ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TABLESPACE = pg_default CONNECTION LIMIT = -1; ...

A lot of boilerplate code is written when developers need to test their applications with different connected systems like databases, stream platforms and other collaborators. Docker allows to handle those dependencies but there is still some glue code required to bind the container’s lifecycle and the configuration to the concrete integration test. Testcontainers is a testing library that offers lightweight throwaway instances of anything able to run in a Docker container, with bindings to configure the specific containers and also provides wrappers to manage our own custom containers. ...

Inspect Docker Image with dive Install dive brew install dive Now we can run dive against any Docker image we wish to inspect…​ Run dive dive confluentinc/cp-kafka:5.4.3 Figure 1. Screenshot of dive analyzing the Kafka Docker image Resources: dive on GitHub Introspect Private Docker Registry List images: curl -s https://the-registry-url/v2/_catalog Get tags for an image curl -s https://the-registry-url/v2/the-image-name/tags/list An example: curl -s https://registry.local/v2/alpine/rabbitmq/tags/list {"name":"alpine/rabbitmq","tags":["3.9.17"]} Run Trivy Scan for Docker Image docker run aquasec/trivy image IMAGE:TAG ...

Precondition: Postgres Schema exists Using this precondition should run a migration only if the schema my_schema exists. <preConditions onFail="CONTINUE"> <sqlCheck expectedResult="1"> SELECT COUNT(1) FROM information_schema.schemata WHERE schema_name = 'my_schema'; </sqlCheck> </preConditions> Resources: https://docs.liquibase.com/concepts/changelogs/preconditions.html

Get size of a table SELECT pg_size_pretty(pg_total_relation_size('schemaname.tablename')); Select unique combination of fields / tuples SELECT DISTINCT ON(field1, field2) field1, field2 FROM thetable Select rows where a combination of fields is not unique SELECT columnA, columnB, count(*) AS count FROM thetable GROUP BY columnA, columnB HAVING count(*) > 1 Search for rows with array containing value Assuming, the field appointments has the type date[] SELECT * FROM mtable WHERE appointments @> ARRAY['2023-09-19'::date] ...

Count unique / all values SELECT COUNT(DISTINCT <FIELDNAME>), COUNT(ALL <FIELDNAME>) FROM <TABLE>; Partially anonymize e-mail addresses UPDATE <TABLE_NAME> SET <EMAIL_FIELD>= INSERT( <EMAIL_FIELD>, POSITION('@' IN <EMAIL_FIELD>), 100, CONCAT(FLOOR(1 + (RAND() * 100)),'@hascode.com')) WHERE POSITION('@' IN <EMAIL_FIELD>)>0; Find duplicate entries SELECT COUNT(*), <FIELDNAME> FROM <TABLENAME> GROUP BY <FIELDNAME> HAVING COUNT(*)>1; MySQL Fix Zero Dates for Data Imports Error: ERROR 1067 (42000) at line 1234: Invalid default value for ‘datefield’ In newer MySQL Versions zero in date or zero dates are forbidden .. check this with: ...