Log Export Examples Using Crunchy Postgres for...

Bring a Friend - Sidecar containers

Many applications running on Kubernetes will send their output to standard error (stderr) and standard out (stdout). This is a convenient location for log aggregators to collect and consolidate logs. However, some applications like Postgres write their logs to disk so getting the Postgres logs to the log aggregator requires a few special tricks.

Crunchy Postgres for Kubernetes recently introduced support for sidecar containers for both Postgres and PgBouncer pods. A sidecar is a container that runs alongside the main container in the pod. It can share resources such as storage and network interfaces while performing operations not provided by the main container using the shared resources. This new feature opens up a world of possibilities when deploying your Postgres clusters on Kubernetes.

Let’s take a look at how introducing a sidecar container can help you manage your Postgres logs with minimal effort and complexity. In the forthcoming examples I will be working with an open source container called Vector by DataDog. I chose Vector because it is lightweight, easy to use, and well documented.

Getting Started

The first thing we will need to do is deploy the latest version of Crunchy Postgres for Kubernetes. Sample files and instructions are in the Crunchy Data Developer Portal. Make a fork and clone the repo locally. Change directory to the postgres-operator-examples/kustomize directory of the cloned directory. Here is mine:

$ pwd
crunchydata/pgo/version/5_2_1/postgres-operator-examples/kustomize

To enable the sidecar functionality you will need to add the following to the Deployment.spec.template.spec.containers.env section of the manager.yaml file located in the postgres-operator-examples/kustomize/install/manager directory.

- name: PGO_FEATURE_GATES
  value: 'InstanceSidecars=true'

Modified manager.yaml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgo
  labels:
    postgres-operator.crunchydata.com/control-plane: postgres-operator
spec:
  replicas: 1
  strategy: { type: Recreate }
  selector:
    matchLabels:
      postgres-operator.crunchydata.com/control-plane: postgres-operator
  template:
    metadata:
      labels:
        postgres-operator.crunchydata.com/control-plane: postgres-operator
    spec:
      containers:
        - name: operator
          image: postgres-operator
          env:
            - name: PGO_NAMESPACE
              valueFrom:
                fieldRef:
                  fieldPath: metadata.namespace
            - name: PGO_FEATURE_GATES
              value: 'InstanceSidecars=true'
            - name: CRUNCHY_DEBUG
              value: 'true'
            - name: RELATED_IMAGE_POSTGRES_13
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.9-0'
            - name: RELATED_IMAGE_POSTGRES_13_GIS_3.0
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-13.9-3.0-0'
            - name: RELATED_IMAGE_POSTGRES_13_GIS_3.1
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-13.9-3.1-0'
            - name: RELATED_IMAGE_POSTGRES_14
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.6-0'
            - name: RELATED_IMAGE_POSTGRES_14_GIS_3.0
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-14.6-3.1-0'
            - name: RELATED_IMAGE_POSTGRES_14_GIS_3.2
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis:ubi8-14.6-3.2-0'
            - name: RELATED_IMAGE_PGADMIN
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-pgadmin4:ubi8-4.30-6'
            - name: RELATED_IMAGE_PGBACKREST
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-0'
            - name: RELATED_IMAGE_PGBOUNCER
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.17-3'
            - name: RELATED_IMAGE_PGEXPORTER
              value: 'registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.2.1-0'
          securityContext:
            allowPrivilegeEscalation: false
            capabilities: { drop: [ALL] }
            readOnlyRootFilesystem: true
            runAsNonRoot: true
      serviceAccountName: pgo

Create the postgres-operator namespace for CPK:

kubectl apply -k install/namespace

Deploy CPK onto your Kubernetes cluster:

kubectl apply -k install/default --server-side

You will see that two pods get created:

$ kubectl -n postgres-operator get pods
NAME                           READY   STATUS    RESTARTS   AGE
pgo-75c5985b5b-nlrw6           1/1     Running   0          24s
pgo-upgrade-756f656f5b-s688x   1/1     Running   0          24s

You are now ready to introduce the sidecar to the PostgresCluster custom resource.

Three Common Logging Scenarios

For this blog we will review three common scenarios:

Example 1 - Postgres errors to stderr.
Example 2 - pgAudit logs to stdout.
Example 3 - pgAudit logs to an Amazon S3 bucket.

Spec This Out

For these examples we are going to add a sidecar to the Postgres instance. More information about how to configure your cluster to use sidecars is on our docs.

The initial PostgresCluster spec looks like this. We have added some logging configuration to control what gets logged and additional information to include in the log prefix. This will give us something to look at.

Initial Spec

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-ha
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.6-0
  postgresVersion: 14
  instances:
    - name: pgha1
      replicas: 2
      dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 1
              podAffinityTerm:
                topologyKey: kubernetes.io/hostname
                labelSelector:
                  matchLabels:
                    postgres-operator.crunchydata.com/cluster: hippo-ha
                    postgres-operator.crunchydata.com/instance-set: pgha1
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi
  proxy:
    pgBouncer:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.17-3
      replicas: 2
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 1
              podAffinityTerm:
                topologyKey: kubernetes.io/hostname
                labelSelector:
                  matchLabels:
                    postgres-operator.crunchydata.com/cluster: hippo-ha
                    postgres-operator.crunchydata.com/role: pgbouncer
  patroni:
    dynamicConfiguration:
      synchronous_mode: false
      postgresql:
        parameters:
          log_checkpoints: 'on'
          log_statement: 'mod'
          log_line_prefix: '%t [%p]: host=%h user=%u,db=%d '
          log_lock_waits: 'on'
          logging_collector: 'on'

We are going to add a ConfigMap with the desired Vector configuration and add the sidecar container to the PostgresCluster.

The Sidecar

We will add the sidecar definition to the PostgresCluster.spec.instances as a member of each instance. In this example we only have one instance listed. We define some resources, environment variables, mounts and an image. We also define other values that the Vector sidecar will need to run.

PostgresCluster.spec.instances

containers:
  - resources:
      limits:
        cpu: 200m
        memory: 1Gi
      requests:
        cpu: 200m
        memory: 1Gi
    terminationMessagePath: /dev/termination-log
    name: vector
    env:
      - name: VECTOR_SELF_NODE_NAME
        valueFrom:
          fieldRef:
            apiVersion: v1
            fieldPath: spec.nodeName
      - name: VECTOR_SELF_POD_NAME
        valueFrom:
          fieldRef:
            apiVersion: v1
            fieldPath: metadata.name
      - name: VECTOR_SELF_POD_NAMESPACE
        valueFrom:
          fieldRef:
            apiVersion: v1
            fieldPath: metadata.namespace
    imagePullPolicy: IfNotPresent
    volumeMounts:
      - name: postgres-data
        readOnly: false
        mountPath: /pgdata
      - name: postgres-config
        readOnly: false
        mountPath: /etc/vector
    terminationMessagePolicy: File
    image: timberio/vector:nightly-distroless-libc
    args:
      - '--config-dir'
      - /etc/vector/

We also add a config section at the PostgresCluster.spec level:

config:
  files:
    - configMap:
        name: vector-config

Here you can see the modified spec:

Modified Spec

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo-ha
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.6-0
  postgresVersion: 14
  instances:
    - name: pgha1
      replicas: 2
      containers:
        - resources:
            limits:
              cpu: 200m
              memory: 1Gi
            requests:
              cpu: 200m
              memory: 1Gi
          terminationMessagePath: /dev/termination-log
          name: vector
          env:
            - name: VECTOR_SELF_NODE_NAME
              valueFrom:
                fieldRef:
                  apiVersion: v1
                  fieldPath: spec.nodeName
            - name: VECTOR_SELF_POD_NAME
              valueFrom:
                fieldRef:
                  apiVersion: v1
                  fieldPath: metadata.name
            - name: VECTOR_SELF_POD_NAMESPACE
              valueFrom:
                fieldRef:
                  apiVersion: v1
                  fieldPath: metadata.namespace
          imagePullPolicy: IfNotPresent
          volumeMounts:
            - name: postgres-data
              readOnly: false
              mountPath: /pgdata
            - name: postgres-config
              readOnly: false
              mountPath: /etc/vector
          terminationMessagePolicy: File
          image: timberio/vector:nightly-distroless-libc
          args:
            - '--config-dir'
            - /etc/vector/
      dataVolumeClaimSpec:
        accessModes:
          - 'ReadWriteOnce'
        resources:
          requests:
            storage: 1Gi
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 1
              podAffinityTerm:
                topologyKey: kubernetes.io/hostname
                labelSelector:
                  matchLabels:
                    postgres-operator.crunchydata.com/cluster: hippo-ha
                    postgres-operator.crunchydata.com/instance-set: pgha1
  config:
    files:
      - configMap:
          name: vector-config
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-0
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - 'ReadWriteOnce'
              resources:
                requests:
                  storage: 1Gi
  proxy:
    pgBouncer:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbouncer:ubi8-1.17-3
      replicas: 2
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
            - weight: 1
              podAffinityTerm:
                topologyKey: kubernetes.io/hostname
                labelSelector:
                  matchLabels:
                    postgres-operator.crunchydata.com/cluster: hippo-ha
                    postgres-operator.crunchydata.com/role: pgbouncer
  patroni:
    dynamicConfiguration:
      synchronous_mode: false
      postgresql:
        parameters:
          log_checkpoints: 'on'
          log_statement: 'mod'
          log_line_prefix: '%t [%p]: host=%h user=%u,db=%d '
          log_lock_waits: 'on'
          logging_collector: 'on'

The ConfigMap

Vector uses a config map to get its configuration. Configuration consists of sources, transforms and sinks:

Sources - The source of the data you want to work with.
Transforms - Manipulating the data in transport. Parsing, filtering and aggregating are examples of transforms.
Sinks - The destination you select for your transformed data.

The ConfigMap in our examples will use the Postgres logs and vector logs as the data source. Each will have different combinations of transforms and sinks. Vector parses the Postgres logs based on the declared transformations and sends them to the desired location.

Example 1 - Logging Postgres errors to stderr

In this example, we are collecting the Postgres logs, filtering for errors and sending them to stderr using the vector sidecar container. This ConfigMap will use the Postgres logs as a datasource. It transforms the data by parsing for errors and then routes copies of that error to stderr.

Vector ConfigMap

kind: ConfigMap
apiVersion: v1
metadata:
  name: vector-config
  labels:
    app.kubernetes.io/component: Agent
    app.kubernetes.io/instance: vector
    app.kubernetes.io/name: vector
    app.kubernetes.io/version: 0.26.0-distroless-libc
data:
  agent.yaml: |
    data_dir: /pgdata
    api:
      enabled: true
      address: 127.0.0.1:8686
      playground: false
    sources:
      postgres_logs:
        type: file
        ignore_older_secs: 600
        include: ["/pgdata/*/log/postgresql*.log"]
        read_from: "beginning"
        multiline:
          start_pattern: '^[^\s]'
          mode: "continue_through"
          condition_pattern: '^[\s]+'
          timeout_ms: 1000
      vector_logs:
        type: "internal_logs"
    transforms:
      error_logs:
        type: "filter"
        inputs: ["postgres_logs"]
        condition: 'contains(string!(.message), "ERROR:", case_sensitive: false)'
    sinks:
      stderr:
        type: "console"
        inputs: ["error_logs"]
        encoding:
          codec: "text"
        target: "stderr"
      stdout:
        type: "console"
        inputs: ["vector_logs"]
        encoding:
          codec: "text"
        target: "stdout"

I have used kustomization.yaml to deploy the ConfigMap and the Postgres cluster.

namespace: <your_namespace>

resources:
  - configmap-vector.yaml
  - postgres.yaml

I have my deployment artifacts in a directory called vectordemo. They consist of:

postgres.yaml
pgo-s3-creds.yaml
configmap-vector.yaml
kustomization.yaml

Let’s create the cluster:

$ kubectl apply -k vectordemo
configmap/vector-config created
secret/pgo-s3-creds created

Show Me The Logs

The Postgres logs are in the volume claimed by the pod. If we exec into the primary database container in the Postgres pod we can see them here:

kubectl exec -c database -it $(kubectl get pod -l \
postgres-operator.crunchydata.com/role=master -o name -n postgres-operator) \ -n
postgres-operator -- bash bash-4.4$ ls /pgdata/pg14/log postgresql-Tue.log

Now let's create an error. While logged into the primary database issue the following commands:

bash-4.4$ psql
psql (14.6)
Type "help" for help.

postgres=# CREATE TABLE t_random AS SELECT s, md5(random()::text) FROM generate_Series(1,100) s;
SELECT 100
postgres=# CREATE TABLE t_random AS SELECT s, md5(random()::text) FROM generate_Series(1,100) s;
ERROR:  relation "t_random" already exists
postgres=#

The second attempt to run the same command results in an error. When we look at the container logs, we can see a logged error when we attempt to create a table that already exists. The original Postgres logs are still on disk. The error message in the log was copied and sent to stderr by the Vector sidecar. The pod displays the error message in its log.

$ kubectl logs -n postgres-operator hippo-ha-pgha1-2pns-0 -c vector
2022-11-16 20:41:04 UTC [281]: host=[local] user=postgres,db=postgres ERROR:  relation "t_random" already exists

Example 2 - PGAudit logs to stdout

For example 2, we enable the pgAudit extension by adding the following to the postgresql.parameters in the PostgresCluster spec:

shared_preload_libraries: pgaudit.so
pgaudit.log: 'write, ddl, role'
pgaudit.log_level: 'notice'

We will create a Postgres cluster using an updated PostgresCluster custom resource and an updated configmap as listed below. Then we can deploy the cluster using the same kustomization file we used before.

Vector ConfigMap

kind: ConfigMap
apiVersion: v1
metadata:
  name: vector-config
  labels:
    app.kubernetes.io/component: Agent
    app.kubernetes.io/instance: vector
    app.kubernetes.io/name: vector
    app.kubernetes.io/version: 0.26.0-distroless-libc
data:
  agent.yaml: |
    data_dir: /pgdata
    api:
      enabled: true
      address: 127.0.0.1:8686
      playground: false
    sources:
      postgres_logs:
        type: file
        ignore_older_secs: 600
        include: ["/pgdata/*/log/postgresql*.log"]
        read_from: "beginning"
        multiline:
          start_pattern: '^[^\s]'
          mode: "continue_through"
          condition_pattern: '^[\s]+'
          timeout_ms: 1000
      vector_logs:
        type: "internal_logs"
    transforms:
      audit_logs:
        type: "filter"
        inputs: ["postgres_logs"]
        condition: '!contains(string!(.message), "AUDIT:", case_sensitive: false)'
    sinks:
      stdout:
        type: "console"
        inputs: ["audit_logs","vector_logs"]
        encoding:
          codec: "text"
        target: "stdout"

Using the same commands we used in the previous example we can exec into the primary database container and create a table. Pgaudit generates a log entry in the Postgres log. When we get the pod log we see the Pgaudit entry that was sent to stdout by the vector sidecar container.

$ kubectl logs -n postgres-operator hippo-ha-pgha1-9k9k-0 -c vector
2022-11-16 20:18:04 UTC [874]: host=[local] user=postgres,db=postgres LOG:  AUDIT: SESSION,1,1,DDL,CREATE TABLE AS,TABLE,public.t_random,"CREATE TABLE t_random AS SELECT s, md5(random()::text) FROM generate_Series(1,100) s;",<not logged>

Example 3 - PGAudit logs to Amazon S3

In this final example, we will send the pgAudit logs to an S3 bucket instead of stdout. I created some AWS objects and policies. These are examples only. Work with your cloud administrator to ensure your AWS objects and policies adhere to your security standards.

AWS Objects and Policies

IAM User Group - I created an IAM user group on my AWS account with the following policy:

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "VisualEditor0",
			"Effect": "Allow",
			"Action": "s3:ListAllMyBuckets",
			"Resource": "*"
		}
	]
}

IAM User - I created an IAM user on my AWS account with the following policy and assigned it to the user group. This allows the user to list buckets I have granted access to and place objects or perform operations on objects in them:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:ListBucket",
                "s3:DeleteObject",
                "s3:GetObjectVersion"
            ],
            "Resource": [
                "arn:aws:s3:::<bucket_name>"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "s3:ListAllMyBuckets",
                "s3:ListAccessPoints"
            ],
            "Resource": "arn:aws:s3:::<bucket_name>"
        }
    ]
}
    c

{ xv gvfd
r gvdv   "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Statement1",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<aws_account_id>:user/<IAM_user_name>"
            },
            "Action": "s3:*",
            "Resource": "arn:aws:s3:::<bucket_name>"
        }
    ]
}

Access Point - You will need an S3 access point to your bucket. I created a policy for this access point to allow s3.* actions on the access point resource.

{
	"Version": "2012-10-17",
	"Statement": [
		{
			"Sid": "Statement1",
			"Effect": "Allow",
			"Principal": {
				"AWS": "arn:aws:iam::<aws_account_id>:user/<IAM_user_name>"
			},
			"Action": "s3:*",
			"Resource": "arn:aws:s3:us-east-1:<aws_account_id>:accesspoint/<access_point_name>"
		}
	]
}

Destination Folder - Create the folder in the bucket that you want the pgAudit logs to go to. You can do this using the AWS Management Console or command line using the AWS CLI:
```
aws s3api put-object --bucket vectordemo --key pgaudit/
```

Deployment Artifacts

S3 Credentials Secret - Create a secret that has the AWS S3 Key and AWS S3 Key Secret of the IAM user you created:

apiVersion: v1
kind: Secret
metadata:
  name: pgo-s3-creds
type: Opaque
stringData:
  vector-s3-key: <your_key>
  vector-s3-key-secret: <your_key_secret>

Postgres.yaml - Add two more environment variables to the env section of the side car section of the PostgresCluster spec. This will load the S3 credentials from the secret into environment variables in the vector container so it can connect to your bucket:
```
- name: AWS_ACCESS_KEY_ID
  valueFrom:
    secretKeyRef:
      key: vector-s3-key
      name: pgo-s3-creds
- name: AWS_SECRET_ACCESS_KEY
  valueFrom:
    secretKeyRef:
      key: vector-s3-key-secret
      name: pgo-s3-creds
```

Vector Configmap - The Configmap settings to work with your S3 bucket. Note that the bucket property in the aws_s3 sink must match the folder you created in your S3 bucket. In this example I used pgAudit.

ConfigMap

kind: ConfigMap
apiVersion: v1
metadata:
  name: vector-config
  labels:
    app.kubernetes.io/component: Agent
    app.kubernetes.io/instance: vector
    app.kubernetes.io/name: vector
    app.kubernetes.io/version: 0.26.0-distroless-libc
data:
  agent.yaml: |
    data_dir: /pgdata
    api:
      enabled: true
      address: 127.0.0.1:8686
      playground: false
    sources:
      postgres_logs:
        type: file
        ignore_older_secs: 600
        include: ["/pgdata/*/log/postgresql*.log"]
        read_from: "beginning"
        multiline:
          start_pattern: '^[^\s]'
          mode: "continue_through"
          condition_pattern: '^[\s]+'
          timeout_ms: 60000
      vector_logs:
        type: "internal_logs"
    transforms:
      audit_logs:
        type: "filter"
        inputs: ["postgres_logs"]
        condition: 'contains(string!(.message), "AUDIT:", case_sensitive: false)'
    sinks:
      stdout:
        type: "console"
        inputs: ["vector_logs"]
        encoding:
          codec: "text"
        target: "stdout"
      aws_s3:
        type: aws_s3
        endpoint: "https://<access_point_name>-<aws_account_id>.s3-accesspoint.<region>.amazonaws.com"
        inputs: ["audit_logs"]
        acl: bucket-owner-full-control
        bucket: "pgaudit"
        content_encoding: gzip
        encoding:
          codec: text
        content_type: text/x-log
        filename_append_uuid: false
        filename_extension: gzip
        filename_time_format: "%+"
        key_prefix: pg-audit-logs-%F/
        storage_class: STANDARD
        compression: gzip
        region: <your_bucket_region>

kustomization.yaml - I am using kustomize to deploy my cluster with the sidecar:

namespace: <your_namespace>

resources:
  - pgo-s3-creds.yaml
  - configmap-vector.yaml
  - postgres.yaml

Apply the changes and determine which Postgres pod is primary:

$ kubectl get pod -l postgres-operator.crunchydata.com/role=master -o name -n <namespace>
pod/hippo-ha-pgha1-t4zj-0

Exec into the primary Postgres pod, initialize pgbench, and run it for 30 seconds

$ kubectl exec hippo-ha-pgha1-t4zj-0 -n postgres-operator -it -- bash

bash-4.4$ pgbench -i
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
100000 of 100000 tuples (100%) done (elapsed 0.10 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 0.42 s (drop tables 0.00 s, create tables 0.09 s, client-side generate 0.15 s, vacuum 0.10 s, primary keys 0.09 s).*

bash-4.4$ pgbench -T 30
pgbench (14.6)
starting vacuum...end.
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
duration: 30 s
number of transactions actually processed: 10638
latency average = 2.820 ms
initial connection time = 5.468 ms
tps = 354.559025 (without initial connection time)*
bash-4.4$

You now have a sub-directory in your pgaudit folder:

$ aws s3 ls s3://vectordemo/pgaudit/
PRE pg-audit-logs-2022-12-05/

In that sub-directory you have the .gzip files containing your audit logs:

$ aws s3 ls s3://vectordemo/pgaudit/pg-audit-logs-2022-12-05/
2022-12-05 17:31:57      93438 2022-12-05T22:31:56.288982454+00:00.gzip
2022-12-05 17:32:04      93550 2022-12-05T22:32:03.081461533+00:00.gzip
2022-12-05 17:32:11      93735 2022-12-05T22:32:09.988534108+00:00.gzip
2022-12-05 17:32:18      93682 2022-12-05T22:32:17.197508864+00:00.gzip
2022-12-05 17:37:17      29768 2022-12-05T22:37:16.885495554+00:00.gzip

Opening one of the .gzip files in the S3 bucket you will see that only PgAudit log entries were sent from the sidecar.

2022-12-05 22:32:19 UTC [851]: host=[local] user=postgres,db=postgres LOG:  AUDIT: SESSION,42548,1,WRITE,INSERT,,,"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (9, 1, 61457, 3463, CURRENT_TIMESTAMP);",<not logged>
2022-12-05 22:32:19 UTC [851]: host=[local] user=postgres,db=postgres LOG:  AUDIT: SESSION,42549,1,WRITE,UPDATE,,,UPDATE pgbench_accounts SET abalance = abalance + -1301 WHERE aid = 64317;,<not logged>
2022-12-05 22:32:19 UTC [851]: host=[local] user=postgres,db=postgres LOG:  AUDIT: SESSION,42550,1,WRITE,UPDATE,,,UPDATE pgbench_tellers SET tbalance = tbalance + -1301 WHERE tid = 7;,<not logged>
2022-12-05 22:32:19 UTC [851]: host=[local] user=postgres,db=postgres LOG:  AUDIT: SESSION,42551,1,WRITE,UPDATE,,,UPDATE pgbench_branches SET bbalance = bbalance + -1301 WHERE bid = 1;,<not logged>
2022-12-05 22:32:19 UTC [851]: host=[local] user=postgres,db=postgres LOG:  AUDIT: SESSION,42552,1,WRITE,INSERT,,,"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (7, 1, 64317, -1301, CURRENT_TIMESTAMP);",<not logged>

Note: Vector pushes logs to S3 when they hit a specified size or after a timeout of 5 minutes. The Postgres activity stopped before the specified size limit causing a flush to happen at the 5 minute timeout. This is why the final .gzip file is smaller than the previous. More information on vector configuration can be found in their documentation: https://vector.dev/docs/.

Summary

With the addition of the new sidecar functionality in Crunchy Postgres for Kubernetes 5.2 and above, managing your Postgres logs has never been easier. It also gives you the ultimate flexibility, just select the sidecar of your choice and configure it to copy the logs where you want them. The use of sidecars is not limited to log management, there's lot of other use cases out there. I'm excited to see what other uses will appear as more PGO users adopt this new feature.

Latest Articles

Log Export Examples Using Crunchy Postgres for Kubernetes