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