https://progress.opensuse.org/https://progress.opensuse.org/themes/openSUSE/favicon/favicon.ico?15829177842019-12-11T22:19:57ZopenSUSE Project Management ToolopenQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2640022019-12-11T22:19:57Zokurzokurz@suse.com
<ul><li><strong>Copied to</strong> <i><a class="issue tracker-4 status-3 priority-5 priority-high3 closed" href="/issues/60926">action #60926</a>: postgres error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"</i> added</li></ul> openQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2640082019-12-11T22:20:36Zokurzokurz@suse.com
<ul></ul><p>I have deleted <code>postgresql-2019-11-*</code> which gives us a bit of time to solve the issue. This ticket shall focus on the admin impact. I created <a class="issue tracker-4 status-3 priority-5 priority-high3 closed" title="action: postgres error "duplicate key value violates unique constraint "screenshots_filename", Key (filen... (Resolved)" href="https://progress.opensuse.org/issues/60926">#60926</a> for the openQA bug itself.</p>
openQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2643082019-12-12T16:37:06Zokurzokurz@suse.com
<ul><li><strong>Priority</strong> changed from <i>Urgent</i> to <i>High</i></li></ul><p>I could resolve <a class="issue tracker-4 status-3 priority-5 priority-high3 closed" title="action: postgres error "duplicate key value violates unique constraint "screenshots_filename", Key (filen... (Resolved)" href="https://progress.opensuse.org/issues/60926">#60926</a> already and deploy the change to o3 so while there are also many logs there I do not even think we need to delete any. For osd next week's deployment should suffice or we do it in the middle. What I would still like to do is learn a bit about how postgres manages the log rotation and also if we have a possibility to monitor for errors in it's according logfiles.</p>
<p>EDIT: 2019-12-14: So what I learned by now is that postgres has logrotation and we seem to use the default, do daily rotations plus limit file size by 10M. On OSD we have an additional /etc/logrotate.d/postgresql that ensures deletion of old files with</p>
<pre><code>/var/lib/pgsql/data/pg_log/postgresql-*.log {
daily
minage 15
rotate 0
missingok
}
</code></pre>
<p>on o3 we do not have that.</p>
<p><a href="https://www.endpoint.com/blog/2010/01/01/monitoring-postgres-log-files-with" class="external">https://www.endpoint.com/blog/2010/01/01/monitoring-postgres-log-files-with</a> mentions a program "tail_and_mail" monitoring postgres logs and sending email alerts depending on configuration, e.g. alerts and aggregated error reports. Sounds useful.</p>
<p>After the original problem what seems to be the second-most likely error in logs:</p>
<pre><code>2019-12-15 12:42:25.201 UTC openqa geekotest [23773]STATEMENT: INSERT INTO job_modules ( always_rollback, category, fatal, important, job_id, milestone, name, script, t_created, t_updated) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10 ) RETURNING id
2019-12-15 12:42:25.204 UTC openqa geekotest [23773]ERROR: duplicate key value violates unique constraint "job_modules_job_id_name_category_script"
2019-12-15 12:42:25.204 UTC openqa geekotest [23773]DETAIL: Key (job_id, name, category, script)=(1114160, cleanup_before_shutdown, shutdown, tests/shutdown/cleanup_before_shutdown.pm) already exists.
</code></pre> openQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2655182019-12-18T09:28:17Zcoolocoolo@suse.com
<ul></ul><p>please fix <a href="https://gitlab.suse.de/openqa/salt-states-openqa/merge_requests/43" class="external">https://gitlab.suse.de/openqa/salt-states-openqa/merge_requests/43</a> - postgresql logs aren't supposed to be stored for months</p>
<p><a href="https://www.postgresql.org/docs/current/logfile-maintenance.html" class="external">https://www.postgresql.org/docs/current/logfile-maintenance.html</a></p>
openQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2657012019-12-18T21:11:37Zokurzokurz@suse.com
<ul></ul><p><a href="https://gitlab.suse.de/openqa/salt-states-openqa/merge_requests/239" class="external">https://gitlab.suse.de/openqa/salt-states-openqa/merge_requests/239</a></p>
openQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2674502019-12-27T20:59:12Zokurzokurz@suse.com
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Resolved</i></li><li><strong>Target version</strong> changed from <i>Current Sprint</i> to <i>Done</i></li></ul><p>merged, deployed and active on osd. /srv is at 47% space usage.</p>
openQA Infrastructure - action #60923: [alert] /srv about to run full, postgres logs very big due to repeated error "duplicate key value violates unique constraint "screenshots_filename", Key (filename)=(8ca/3c9/98a00d8bb2ccba5a2de1d403b5.png) already exists. INSERT INTO screenshots …"https://progress.opensuse.org/issues/60923?journal_id=2839792020-03-08T09:27:07Zokurzokurz@suse.com
<ul><li><strong>Related to</strong> <i><a class="issue tracker-4 status-3 priority-3 priority-lowest closed behind-schedule" href="/issues/64096">action #64096</a>: partition /srv was nearly depleted but now fixed (itself?)</i> added</li></ul>