Re: stale WAL files?

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(dot)klaver(at)aklaver(dot)com