5

I have some problem with PostgreSQL and PostGIS.

I received an error after running the below query with some coordinates on special raster file.

It worked fine until last week.

The query is:

select (ST_ValueCount(ST_Union(ST_Clip(r.rast,r.geom,true),1,'area_sum'))).* from (select * ,(SELECT (ST_SetSRID(ST_GeomFromGeoJSON('{ "type": "Polygon", "coordinates": [[[47.48034200000001,39.13347600000003],[47.49611600000003,39.114525000000015],[47.510418000000016,39.10534899999999],[47.52599799999996,39.08644900000013],[47.54254200000014,39.05803700000013],[47.53256200000004,39.0290940000001],[47.52131200000002,39.00986000000012],[47.49699400000003,38.99015700000001],[47.498607000000106,38.97137000000009],[47.52537600000005,38.96289400000006],[47.52689400000003,38.94405700000004],[47.54134800000014,38.92563300000012],[47.529907000000094,38.906041000000016],[47.519521,38.87627100000009],[47.521041000000025,38.85683000000006],[47.54817600000001,38.83865400000002],[47.58924400000001,38.80162500000006],[47.59110200000009,38.762773000000095],......]]}'), 4326)))as geom from public.layer where filename in ( 'myrasterfile.tif') ) as r where ST_Intersects(r.rast,r.geom))

The error is:

SQLSTATE[08006] [7] FATAL:  the database system is in recovery mode

Why does my PostgreSQL instance go into recovery mode on this query process?

What should I do?

2
  • 1
    the database system is in recovery mode - this error is possible at the time of connecting to the database. It is not possible to send any queries yet. Look in the database logs for the real reason for switching to recovery mode, it will be somewhere before that.
    – Melkij
    Commented Apr 27, 2022 at 9:05
  • Your database crashed, most likely because it ran out of memory. Look at the log file for details and adjust your configuration. Disable memory overcommit on the operating system. Commented Apr 27, 2022 at 16:35

1 Answer 1

2

My case: RDS PostgreSql DB status was Storage-full

So I couldn't connect to it from my client w/ the fatal error in the original post.

Solution:

  1. connect to the AWS web console: RDS > Databases > db-name
  2. Modify > Allocated storage >> increase the storage size
  3. (optional) Enable storage autoscaling
  4. Continue (preview changes) >> Save

Extra: you may want to check why the storage has exceeded, maybe safely delete some unwanted rows and review the storage size again according to your needs vs budget.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.