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

Is there any way to reduce the temporary files which are having huge size .

On Mon, Feb 1, 2021 at 7:25 PM dbatoCloud Solution <dbatocloud17(at)gmail(dot)com>
wrote:

> 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
>>>
>>