17:57 <gchristensen> I started working on a job to load the hydra database snapshot and do a pgdump on it, thinking that by verifying the pg_dump output is about 30G+, we'll have a fairly well tested backup

17:58 <gchristensen> anyway, after a few minor tweaks today, it is working: https://buildkite.com/grahamc/postgres-load-dump/builds/41#0408ca3d-5ac4-49b9-b5aa-0166a447fda3 it is dumping with "pg_dump hydra --create --format=directory --exclude-table users --verbose -U hydra" -- excluding the user table with the idea of making the dumps publicn somehow

17:59 <gchristensen> here is the repo: https://github.com/grahamc/hydra-pg-load-dump

17:59 <gchristensen> I'm thinking people could add batch queries they'd like run, too, and have those results uploaded

18:01 <gchristensen> any opinions?

18:04 <gchristensen> it'd be nice for it to be on infra managed by the foundation and not my home system, but there are a few of requirements we can't easily meet at the moment: an always-on system (precludes Packet spot market) with a significant amount of disk space for receiving the snapshots. I'd also like it if it continued using buildkite or something similar where the logs were public and easily viewable,

18:04 <gchristensen> but I wish that were true about a bunch of the scheduled tasks we have :)

18:30 <niksnut> what do you mean with 'add batch queries they'd like run'?

18:32 <gchristensen> like LnL would like to have a query similar to this run on a semi-regular basis, and be able to fetch the results: COPY (SELECT * FROM builds as b JOIN jobsetevalmembers as m ON b.id = m.build JOIN jobsetevals as e ON e.id = m.eval WHERE e.project = 'nixpkgs' AND e.jobset = 'trunk' AND e.id > 1515735) TO '/tmp/builds.csv';

18:33 <LnL> that's mostly just a one time thing

18:33 <gchristensen> ah

18:33 <MichaelRaskin> Periodic would be «date_part('epoch',now())-e.timestamp < 3.3e7» at least…

18:34 <LnL> the actual meta queries should probably run against all the data

18:35 <gchristensen> we don't need to do that part :P (that said, if there were queries people'd want to run, it would be a lot easier for them to be able to submit the query and fetch results than have ~300G of disk space to download and import the db dump. but again, don't need to do this part)

18:36 <LnL> yeah so for package metadata these are a bunch of relatively small queries, but for each package in nixpkgs which adds up