Solving PostgreSQL's pg_basebackup 'ERROR: temporary file size exceeds temp_file_limit (1KB)'

When performing a pg_basebackup in PostgreSQL, you might run into this frustrating error:

pg_basebackup: error: could not get COPY data stream: ERROR: temporary file size exceeds temp_file_limit (1KB)

This error occurs because PostgreSQL enforces a strict limit on the size of temporary files created during operations like backups. Here, the limit (temp_file_limit) is set to 1 KB, which is far too small for most tasks—especially for a backup operation.

What causes this error?

The parameter temp_file_limit controls the maximum size of temporary files that a session can create. PostgreSQL often creates temporary files during complex operations, such as sorting, hashing, and backups. In this case, the pg_basebackup process exceeded the tiny 1KB limit set for temporary files, triggering the error.

How to fix the error

The solution is simple: increase the temp_file_limit to a more reasonable value. Here’s a quick step-by-step guide to fix the issue:

1. Edit postgresql.conf

Locate your PostgreSQL configuration file (postgresql.conf) and increase the temp_file_limit value. Here’s an example:

temp_file_limit = '1GB'

A value like 1GB or higher is recommended, depending on your system’s resources and workload.

2. Reload or restart PostgreSQL

Once you've made the change, reload or restart PostgreSQL to apply the new configuration:

# Reload the configuration
pg_ctl reload

# Reload the configuration
systemctl reload postgresql

3. Retry pg_basebackup

Now, try running pg_basebackup again, and it should proceed without any issues.

Conclusion

This error is typically caused by a misconfigured temp_file_limit set to a very low value (like 1KB). Increasing this limit to a reasonable size allows PostgreSQL to handle temporary files properly during complex operations like backups.

By setting temp_file_limit to a higher value and ensuring sufficient disk space, you can avoid this issue and ensure smooth backup operations.