How to run pgbench from a pod without having to type the password every time [Kubernetes] [CloudNativePG]

Question

I am a user of Kubernetes, and I am using the CloudNativePG operator version 1.20.1 with PostgreSQL. Currently, I am conducting tests on clusters using the pgbench tool. However, when I execute the tool from the console where Kubernetes is located, it experiences significant latency issues.

To address this problem, I had the idea of executing pgbench from a neighboring cluster, which yields much better results. However, I encounter a challenge when attempting to execute the benchmark without manually typing the password. This becomes crucial because I need to perform the same benchmark on multiple clusters, and I would like to automate the process using a bash file or any other suitable method.

The command I would like to use to run the bench is the following:

kubectl exec <pod-where I launch the bench> -- bash -c "pgbench -h <Host> -p <port> -U pgbench_user -j 1 -c 20 -T 20 pgbench_db"

The intention behind this command is to use kubectl exec to run the pgbench tool within a specific pod. However, since I haven't yet resolved the password authentication issue, I haven't been able to execute this command successfully.

I attempted to investigate the usage of the .pgpass file to automate the execution of pgbench without manually typing the password. However, I encountered an issue when trying to create the .pgpass file in the home directory. The system indicated that I couldn't create files there.

I'm currently exploring alternative methods or workarounds to automate the execution of pgbench without manual password input.

Thank you

Answer 1

The recommended approach is to use the cnpg plugin for kubectl to generate a Job object that runs pgbench using secrets, even using the --dry-run option. That's all you need to automate them, including customizing databases and users.

See https://cloudnative-pg.io/documentation/current/benchmarking/ for details.