Termination of PostgreSQL Pod when Copying Files to /tmp Directory

[Shahid Hussain]

Dear PostgreSQL Community,
I am using PGO v5.3.0, which I installed using Helm.

I am writing to seek your assistance regarding an issue I have encountered while copying large files to the /tmp directory of a PostgreSQL pod. 

I found during investigation that when attempting to copy a file larger than 16MB to the /tmp directory using the following command:

    kubectl -n <namespace> cp sample_file.tar.gz pgdb-instance1-b5kq-0:/tmp/

I observed that the PostgreSQL pod is terminated after approximately two minutes, accompanied by the following error logs:

2023-07-12 14:52:09.493 UTC,,,94,,64aebd8f.5e,10,,2023-07-12 14:49:51 UTC,,0,LOG,00000,"received fast shutdown request"
2023-07-12 14:52:09.495 UTC,,,94,,64aebd8f.5e,11,,2023-07-12 14:49:51 UTC,,0,LOG,00000,"aborting any active transactions"
2023-07-12 14:52:09.495 UTC,"postgres","postgres",111,"[local]",64aebd90.6f,39,"idle",2023-07-12 14:49:52 UTC,2/0,0,FATAL,57P01,"terminating connection due to administrator command"
2023-07-12 14:52:09.498 UTC,,,94,,64aebd8f.5e,12,,2023-07-12 14:49:51 UTC,,0,LOG,00000,"background worker ""logical replication launcher"" (PID 123) exited with exit code 1"
2023-07-12 14:52:09.521 UTC,,,101,,64aebd8f.65,1,,2023-07-12 14:49:51 UTC,,0,LOG,00000,"shutting down"
2023-07-12 14:52:09.683 UTC,,,94,,64aebd8f.5e,13,,2023-07-12 14:49:51 UTC,,0,LOG,00000,"database system is shut down"
command terminated with exit code 137

Based on this information, it appears that the pod receives a fast shutdown request, leading to the termination of the PostgreSQL process.

If anyone has encountered a similar situation or has any insights into why this behavior occurs, I would greatly appreciate your expertise and suggestions for resolving this issue.

I tried setting different values for work_mem and shared_buffer but that could not help.

This pod we have allocated 2GB RAM and 10GB storage.

Following are few patroni settings.
max_connections = '1000'
max_locks_per_transaction = '64'
max_prepared_transactions = '0'
max_replication_slots = '10'
max_wal_senders = '10'
max_worker_processes = '8'
password_encryption = 'scram-sha-256'
port = '5432'
shared_buffers = '2048MB'
shared_preload_libraries = 'pgaudit'
ssl = 'on'
ssl_ca_file = '/pgconf/tls/ca.crt'
ssl_cert_file = '/pgconf/tls/tls.crt'
ssl_key_file = '/pgconf/tls/tls.key'
temp_buffers = '160MB'
track_commit_timestamp = 'off'
unix_socket_directories = '/tmp/postgres'
wal_keep_size = '128MB'
wal_level = 'logical'
wal_log_hints = 'on'
work_mem = '160MB'
hba_file = '/pgdata/pg13/pg_hba.conf'
ident_file = '/pgdata/pg13/pg_ident.conf'

[Shahid Hussain]

Hi team,

Looking for some input on this item.

[Shahid Hussain]

Hi!. Looking for some input here.

[drew.s...@crunchydata.com]

Hello Shahid,

An exit code 137 indicates that the container or pod is attempting to use more memory than it is allowed. Can you please send me your postgrescluster spec? Where are you running this kube cluster? Are you certain that the node has enough resources?

Regards,

Drew

[drew.s...@crunchydata.com]

Hey Shahid,

I looked into this a bit more and I found that we create the `/tmp` directory as an EmptyDir volume with a 16MiB limit which does explain why you are seeing what you're seeing.

May I ask why you are attempting to move large files into the `/tmp` directory?

Regards,

Drew

[Shahid Hussain]

Hi Drew,

Currently, we handle our own backup and restore mechanism. In this manual procedure, we copy the backup file to the /tmp directory and perform the restoration from there, as part of our automation process.

However, we faced an issue when our backed-up file exceeded 26MB in size. After investigating the problem, we discovered that there is a hard-coded 16MB limitation, and unfortunately, we don't have the ability to adjust this limit through configuration settings. As a temporary solution, we have now shifted to using the pgdata directory to store our temporary backup files.

It's worth mentioning that there is a build B & R mechanism by PGO using PgBackRest, but at present, we are relying on the existing framework we have in place.

Thanks,

Shahid