PR#1492: bundle db maintenance script to hub - koji

		`@@ -115,6 +115,8 @@`
		is a proposal of the :doc:`Content Generator
		Metadata <content_generator_metadata>` format available for review.

		`+ .. _cg_api:`
		`+`
		`API`
		`===`

docs/source/server_howto.rst

file modified

+34

		`@@ -497,6 +497,40 @@`

		`The sql commands you need to use vary by authentication mechanism.`

		`+ Maintaining database`
		`+ --------------------`
		`+`
		`+ For now, there is one table which needs periodical cleanup. As postgres doesn't`
		`+ have any mechanism for this, we need to do it via some other mechanism. Default`
		`+ handling is done by cron, but can be substituted by anything else (Ansible`
		`+ tower, etc.)`
		`+`
		+ Script is by default installed on hub as `/usr/sbin/koji-sweepd-db`. On systemd
		+ systems it also has corresponding `koji-sweep-db` service and timer. Note, that
		+ timer is not enabled by default, so you need to run usual `systemctl` commands:
		`+`
		`+ ::`
		`+`
		`+ systemctl enable --now koji-sweep-db.timer`
		`+`
		`+ If you don't want to use this script, be sure to run following SQL with`
		`+ appropriate age setting. Default value of one day should be ok for most`
		`+ deployments. As there will be tons of freed records, additional VACUUM can be`
		`+ handy.`
		`+`
		`+ .. code-block:: sql`
		`+`
		`+ DELETE FROM sessions WHERE update_time < NOW() - '1 day'::interval;`
		`+ VACUUM ANALYZE sessions;`
		`+`
		+ Optionally (if you're using :ref:`reservation API <cg_api>` for
		`+ content generators), you could want to run also reservation cleanup:`
		`+`
		`+ .. code-block:: sql`
		`+`
		`+ DELETE FROM build_reservations WHERE update_time < NOW() - '1 day'::interval;`
		`+ VACUUM ANALYZE build_reservations;`
		`+`
		`Set User/Password Authentication`
		`^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^`

koji.spec

file modified

+6 -1

		`@@ -450,7 +450,7 @@`
		`make DESTDIR=$RPM_BUILD_ROOT PYTHON=%{__python3} %{?install_opt} install`
		`# alter python interpreter in koji CLI`
		`scripts='%{_bindir}/koji %{_sbindir}/kojid %{_sbindir}/kojira %{_sbindir}/koji-shadow`
		`- %{_sbindir}/koji-gc %{_sbindir}/kojivmd'`
		`+ %{_sbindir}/koji-gc %{_sbindir}/kojivmd %{_sbindir}/koji-sweep-db'`
		`for fn in $scripts ; do`
		`sed -i 's\|#!/usr/bin/python2\|#!/usr/bin/python3\|' $RPM_BUILD_ROOT$fn`
		`done`
		`@@ -534,6 +534,11 @@`
		`%dir /etc/koji-hub`
		`%config(noreplace) /etc/koji-hub/hub.conf`
		`%dir /etc/koji-hub/hub.conf.d`
		`+ %{_sbindir}/koji-sweep-db`
		`+ %if %{use_systemd}`
		`+ %{_unitdir}/koji-sweep-db.service`
		`+ %{_unitdir}/koji-sweep-db.timer`
		`+ %endif`

		`%if 0%{py2_support} > 1`
		`%files -n python2-%{name}-hub`

util/Makefile

file modified

+3 -1

		`@@ -1,4 +1,4 @@`
		`- BINFILES = kojira koji-gc koji-shadow`
		`+ BINFILES = kojira koji-gc koji-shadow koji-sweep-db`
		`SYSTEMDSYSTEMUNITDIR = $(shell pkg-config systemd --variable=systemdsystemunitdir)`
		`TYPE = systemd`

		`@@ -29,6 +29,8 @@`
		`install-systemd: _install`
		`mkdir -p $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)`
		`install -p -m 644 kojira.service $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)`
		`+ install -p -m 644 koji-sweep-db.service $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)`
		`+ install -p -m 644 koji-sweep-db.timer $(DESTDIR)$(SYSTEMDSYSTEMUNITDIR)`

		`install-sysv: _install`
		`mkdir -p $(DESTDIR)/etc/rc.d/init.d`

util/koji-sweep-db

file added

+88

		`@@ -0,0 +1,88 @@`
		`+ #!/usr/bin/python2`
		`+`
		`+ import os`
		`+`
		`+ from optparse import OptionParser`
		`+ from six.moves.configparser import RawConfigParser`
		`+`
		`+ import koji.db`
		`+`
		`+ def clean_sessions(cursor, vacuum):`
		`+ q = " FROM sessions WHERE update_time < now() - '1 day'::interval"`
		`+ if options.verbose:`
		`+ cursor.execute("SELECT COUNT(*) " + q)`
		`+ rows = cursor.fetchall()[0][0]`
		`+ print("Deleting %d sessions" % rows)`
		`+`
		`+ cursor.execute("DELETE " + q)`
		`+ if vacuum:`
		`+ cursor.execute("VACUUM ANALYZE sessions")`
		`+`
		`+`
		`+ def clean_reservations(cursor, vacuum):`
		`+ q = " FROM build_reservations WHERE create_time < now() - '1 day'::interval"`
		`+ if options.verbose:`
		`+ cursor.execute("SELECT COUNT(*) " + q)`
		`+ rows = cursor.fetchall()[0][0]`
		`+ print("Deleting %d build reservations" % rows)`
		`+`
		`+ cursor.execute("DELETE " + q)`
		`+ if vacuum:`
		`+ cursor.execute("VACUUM ANALYZE build_reservations")`
		`+`
		`+`
		`+ if __name__ == "__main__":`
		`+ global options`
		`+ parser = OptionParser("%prog cleans koji database")`
		`+ parser.add_option('-v', '--verbose', action="store_true", help="Be verbose")`
		`+ parser.add_option('-c', '--conf', default='/etc/koji-hub/hub.conf',`
		`+ action='store', help="Path to koji's hub.conf")`
		`+ parser.add_option('--no-vacuum', action="store_false", dest="vacuum",`
		`+ default=True,`
		`+ help="Don't run vacuum on affected tables")`
		`+ options, args = parser.parse_args()`
		`+`
		`+ if not os.path.exists(options.conf):`
		`+ parser.error("Config file doesn't exist")`
		`+`
		`+ config = RawConfigParser()`
		`+ config.read(options.conf)`
		`+`
		`+ cfgmap = [`
		`+ #option, type, default`
		`+ ['DBName', 'string', None],`
		`+ ['DBUser', 'string', None],`
		`+ ['DBHost', 'string', None],`
		`+ ['DBhost', 'string', None], # alias for backwards compatibility`
		`+ ['DBPort', 'integer', None],`
		`+ ['DBPass', 'string', None],`
		`+ ]`
		`+`
		`+ opts = {}`
		`+ for name, dtype, default in cfgmap:`
		`+ key = ('hub', name)`
		`+ if config and config.has_option(*key):`
		`+ if dtype == 'integer':`
		`+ opts[name] = config.getint(*key)`
		`+ elif dtype == 'boolean':`
		`+ opts[name] = config.getboolean(*key)`
		`+ else:`
		`+ opts[name] = config.get(*key)`
		`+ continue`
		`+ opts[name] = default`
		`+ if opts['DBHost'] is None:`
		`+ opts['DBHost'] = opts['DBhost']`
		`+`
		`+`
		`+ koji.db.provideDBopts(database=opts["DBName"],`
		`+ user=opts["DBUser"],`
		`+ password=opts.get("DBPass", None),`
		`+ host=opts.get("DBHost", None),`
		`+ port=opts.get("DBPort", None))`
		`+`
		`+ conn = koji.db.connect()`
		`+ cursor = conn.cursor()`
		`+`
		`+ clean_sessions(cursor, options.vacuum)`
		`+ clean_reservations(cursor, options.vacuum)`
		`+ conn.commit()`

util/koji-sweep-db.service

file added

		`@@ -0,0 +1,7 @@`
		`+ [Unit]`
		`+ Description=Daily maintenance script for koji db`
		`+ Documentation=https://pagure.io/docs/koji/`
		`+`
		`+ [Service]`
		`+ Type=simple`
		`+ ExecStart=/usr/sbin/koji-sweep-db`

util/koji-sweep-db.timer

file added

+11

		`@@ -0,0 +1,11 @@`
		`+ [Unit]`
		`+ Description=Daily maintenance script for koji db`
		`+ Documentation=https://pagure.io/docs/koji/`
		`+`
		`+ [Timer]`
		`+ OnCalendar=daily`
		`+ Accuracy=1h`
		`+ Persistent=true`
		`+`
		`+ [Install]`
		`+ WantedBy=timers.target`

tkopecek commented 4 years ago

Fixes: https://pagure.io/koji/issue/1478

ngompa commented 4 years ago

@tkopecek Why not install this into some place like /usr/libexec/koji-hub and have a small wrapper to call it in cron or systemd timers or whatever? That way people can easily call it, or it can be done from Ansible, or whatever easily.

tkopecek commented 4 years ago

Yep, maybe libexec is better location. What about default installation? I still hesitate between implicit installation (as in current PR) and just documentation and explicit based on (installation) docs.

pretty please pagure-ci rebuild

4 years ago

ngompa commented 4 years ago

for a cron job, I'd probably suggest documentation. For systemd timers, we should install, since they don't get enabled by default.

rebased onto 5d07c8e60b511a1d7907647f14b6ecbf88b66541

4 years ago

tkopecek commented 4 years ago

updated

ktdreyer commented on line 15 of docs/source/server_howto.rst 4 years ago

We assume systemd in other places in our documentation, so I think we could remove the references to cron and just give the exact systemd steps here.

ngompa commented 4 years ago

Code-wise, LGTM :thumbsup:

amessina commented on line 10 of util/koji-sweep-db.service 4 years ago

This part isn't strictly necessary--if enabled, it will run every time multi-user.target is reached. Hit the "comment" icon on the source. "This part" refers to the [Install] section of the service unit (not the timer unit).

Edited 4 years ago by amessina

2 new commits added