Re: could not write to log file: No space left on device -> AWS RDS Aurora postgreql DB

Dear All
@Michael - Thanks
@RB - I checked and found the temp files are having huge size

[image: image.png]

Thanks & Best Wishes,

Ashok

---------------------------------------------------------------------------------------------------------------------

Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)*

*Dubai** , UAE* *| BLR , INDIA*

M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922*

W: https://dbatocloudsolution.blogspot.in/
<http://dbatocloudsolution.blogspot.in/> | E: dbatocloud17(at)gmail(dot)com

On Mon, Feb 1, 2021 at 6:59 PM Bk B <rbbalakumaran(at)gmail(dot)com> wrote:

> Check your performance insight or cloudwatch logs for temp files
> generation trend. A select query or any part of query which is selecting
> large data set would break the memory since Aurora is using it where in
> native Postgresql uses user defined tablespace.
>
> I would advise to enable temp files logging and use pgbadger or similar
> tool to see what is causing this.
>
> On Mon, Feb 1, 2021, 6:49 PM dbatoCloud Solution <dbatocloud17(at)gmail(dot)com>
> wrote:
>
>> Dear All,
>>
>> I am facing an issue in AWS Aurora PostgreSQL DB where it shows,
>>
>> Environment: - AWS RDS PostgreSQL 10.11
>>
>> ##
>> ERROR: could not write block 1726445 of temporary file: No space left on
>> device
>> ##
>>
>> Error#
>> could not write to log file: No space left on device
>>
>>
>>
>> 2021-01-12 01:40:03 UTC::@:[29941]:LOG: database system is shut down
>> Postgres Shared Memory Value: 46679678976 bytes
>> 2021-01-12 01:40:04.250 GMT [30047] LOG: skipping missing configuration
>> file "/rdsdbdata/db/postgresql.auto.conf"
>> 2021-01-12 01:40:04 UTC::@:[30047]:WARNING: unrecognized configuration
>> parameter "rds.adaptive_autovacuum"
>> 2021-01-12 01:40:04 UTC::@:[30047]:WARNING: unrecognized configuration
>> parameter "rds.enable_plan_management"
>> 2021-01-12 01:40:04 UTC::@:[30047]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_calls"
>> 2021-01-12 01:40:04 UTC::@:[30047]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_mean_time_ms"
>> 2021-01-12 01:40:04 UTC::@:[30047]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_stddev_time_ms"
>> 2021-01-12 01:40:04 UTC::@:[30047]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_total_time_ms"
>> 2021-01-12 01:40:04 UTC::@:[30047]:LOG: listening on IPv4 address
>> "0.0.0.0", port 5432
>> 2021-01-12 01:40:04 UTC::@:[30047]:LOG: listening on IPv6 address "::",
>> port 5432
>> 2021-01-12 01:40:04 UTC::@:[30047]:LOG: listening on Unix socket
>> "/tmp/.s.PGSQL.5432"
>> 2021-01-12 01:40:04 UTC::@:[30047]:LOG: redirecting log output to
>> logging collector process
>> 2021-01-12 01:40:04 UTC::@:[30047]:HINT: Future log output will appear
>> in directory "/rdsdbdata/log/error".
>> 2021-01-12 02:27:07.156 GMT [1499] LOG: skipping missing configuration
>> file "/rdsdbdata/db/postgresql.auto.conf"
>> 2021-01-12 02:27:07 UTC::@:[1499]:WARNING: unrecognized configuration
>> parameter "rds.adaptive_autovacuum"
>> 2021-01-12 02:27:07 UTC::@:[1499]:WARNING: unrecognized configuration
>> parameter "rds.enable_plan_management"
>> 2021-01-12 02:27:07 UTC::@:[1499]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_calls"
>> 2021-01-12 02:27:07 UTC::@:[1499]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_mean_time_ms"
>> 2021-01-12 02:27:07 UTC::@:[1499]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_stddev_time_ms"
>> 2021-01-12 02:27:07 UTC::@:[1499]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_total_time_ms"
>> 2021-01-12 02:27:07 UTC::@:[1499]:LOG: database system is shut down
>> Postgres Shared Memory Value: 46679678976 bytes
>> 2021-01-12 02:27:08.128 GMT [1608] LOG: skipping missing configuration
>> file "/rdsdbdata/db/postgresql.auto.conf"
>> 2021-01-12 02:27:08 UTC::@:[1608]:WARNING: unrecognized configuration
>> parameter "rds.adaptive_autovacuum"
>> 2021-01-12 02:27:08 UTC::@:[1608]:WARNING: unrecognized configuration
>> parameter "rds.enable_plan_management"
>> 2021-01-12 02:27:08 UTC::@:[1608]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_calls"
>> 2021-01-12 02:27:08 UTC::@:[1608]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_mean_time_ms"
>> 2021-01-12 02:27:08 UTC::@:[1608]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_stddev_time_ms"
>> 2021-01-12 02:27:08 UTC::@:[1608]:WARNING: unrecognized configuration
>> parameter "apg_plan_mgmt.pgss_min_total_time_ms"
>> 2021-01-12 02:27:08 UTC::@:[1608]:LOG: listening on IPv4 address
>> "0.0.0.0", port 5432
>> 2021-01-12 02:27:08 UTC::@:[1608]:LOG: listening on IPv6 address "::",
>> port 5432
>> 2021-01-12 02:27:08 UTC::@:[1608]:LOG: listening on Unix socket
>> "/tmp/.s.PGSQL.5432"
>> 2021-01-12 02:27:08 UTC::@:[1608]:LOG: redirecting log output to
>> logging collector process
>> 2021-01-12 02:27:08 UTC::@:[1608]:HINT: Future log output will appear
>> in directory "/rdsdbdata/log/error".
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>> could not write to log file: No space left on device
>>
>> could not write to log file: No space left on device
>>
>>
>>
>> Thanks & Best Wishes,
>>
>> Ashok
>>
>>
>>
>>
>> ---------------------------------------------------------------------------------------------------------------------
>>
>> Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)*
>>
>> *Dubai** , UAE* *| BLR , INDIA*
>>
>> M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922*
>>
>> W: https://dbatocloudsolution.blogspot.in/
>> <http://dbatocloudsolution.blogspot.in/> | E: dbatocloud17(at)gmail(dot)com
>>
>