Re: stale WAL files? - Mailing list pgsql-general

On 3/25/19 5:10 PM, Rob Sargent wrote:
> PG10.7, Centos7
> 
> On Mar15 we filled our default tablespace/WAL partition.  Cleaned up 
> some old dumps and restarted.  pg_wal had apparently exploded but 
> cleaned itself up by the next day. On Mar16 I ran CHECKPOINT in all 
> databases on that server (except template0).  All seems fine except for 
> 271 WALs from MAR16 going nowhere fast.  Of course we see transient WALS 
> come and go every day.  We did a restart of the server last Friday 
> (Mar22) without issue.
> 
> -rw-------. 1 postgres postgres 16777216 Mar 16 16:32 
> 0000000100000CE9000000DD
>   ...(269 others)
> -rw-------. 1 postgres postgres 16777216 Mar 16 17:01 
> 0000000100000CEA000000E9
> 
> What’s keeping these alive.  Or can they be deleted? Any normal admin 
> routine likely to clean these up?

wal_keep_segments?

Do you have replication set up and replication slots in use?

> 
> 
> One of our crew was apparently trying to create a function: he had a 
> run-away json query which caused memory grief but I don’t see that 
> reflected anywhere in the log. The log file for that day goes south as 
> follows.
> 
>     [1]2019-03-15 12:09:15.230 MDT [29189] STATEMENT:  select count(*)
>     from sui.probancset_group_member where group_id = '4ce6a94d-bb2d-\
>     43c2-a1f9-7b68f1618cd4';
>     [2]2019-03-15 17:07:30.818 MDT [35020] ERROR:  could not write to
>     hash-join temporary file: No space left on device
>     [3]2019-03-15 17:07:30.818 MDT [35020] STATEMENT:  update segment as
>     s set events_less = s.events_less + b.events_less, events_equal\
>       = s.events_equal + b.events_equal, events_greater =
>     s.events_greater + b.events_greater, threshold_events =
>     s.threshold_events +\
>       b.threshold_events from
>     bulk."bc_1819_17_fd6dbc1e_57e5_4d49_b896_59d6687c8ee6" as b where
>     s.markerset_id = '8d723d2f-1281-48c5-9\
>     016-2dab3f4d242b' and s.probandset_id = b.probandset_id and
>     s.markerset_id = b.markerset_id and s.startbase = b.startbase and s.e\
>     ndbase = b.endbase and  s.probandset_id >=
>     '90000000-0000-0000-0000-000000000000' and s.probandset_id <
>     'a0000000-0000-0000-0000-\
>     000000000000'
>     2019-03-15 17:07:30.818 MDT [317161] PANIC:  could not write to file
>     "pg_wal/xlogtemp.317161": No space left on device
>     [4]2019-03-15 17:08:19.231 MDT [35166] ERROR:  unterminated
>     dollar-quoted string at or near "$$
> 
>              begin
>                    --new probandset_group record
>                    insert into probandset_group(id,name) select
>     uuid_generate_v4(),'1808_p3c2n4';
> 
>                    --create necessary new probandset and
>     probandset_group_member records
>                    select addprobandset(a.name, people, groupname) from
>                           (select powerset((select array_agg(name order
>     by name) lst from base.person
>                                   where name in (superset))) as name
>                                   except (select
>     regexp_split_to_array(p.name,',') from  probandset p  )) a
>                           where array_length(a.name,1)>1;
> 
>                    --update superset field for new probandset_group
>                    update probandset_group set proband_superset_id =
>     (select id from probandset where name = superset);
>              end;
> 
>     [1] Last reported normal operation
>     [2] death knell
>     [3] First failed operation from our application
>     [4] continued effort from create function work
> 
> Thanks in advance.
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com