So, as we discussed during meeting, I have offloaded the data (for stg) older than half a year to another database. This is how I did it (probably could have been done more efficiently, but hey, this worked, and I'm not postgres expert...):

$ pg_dump -Fc resultsdb_stg > resultsdb_stg.dump # dump the resultsdb_stg to file
$ createdb -T template0 resultsdb_stg_archive # create new empty database callend resultsdb_stg_archive
$ pg_restore -d resultsdb_stg_archive resultsdb_stg.dump # load data from the dump to the resultsbd_stg_archive db
$ psql resultsdb_stg_archive
=# -- Get the newest result we want to keep in archives
=# select id, job_id from result where submit_time<'2016-06-01' order by submit_time desc limit 1;
   id    | job_id
---------+--------
 7857664 | 308901

=# -- Since jobs can contain multiple results, let's select the first result with the 'next' job_id (could be done as 'select id, job_id from result where job_id = 308902 order by id limit 1;' too, but this would automagically catch a hole in the job sequence)
=# select id, job_id from result where job_id > 308901 order by id limit 1;
   id    | job_id
---------+--------
 7857665 | 308902

=# -- delete all the result_data, results, and jobs, starting from what we got in the previous query
=# delete from result_data where result_id >= 7857665;
=# delete from result where id >= 7857665;
=# delete from job where id >= 308902;

$ psql resultsdb_stg
=# -- since the db's were 'cloned' at the beginning, perform deletion of the inverse set of data than we did in archive
=# delete from result_data where result_id < 7857665;
=# delete from result where id < 7857665;
=# delete from job where id < 308902;



On Wed, Dec 7, 2016 at 2:19 PM, Josef Skladanka <jskladan@redhat.com> wrote:


On Mon, Dec 5, 2016 at 4:25 PM, Tim Flink <tflink@redhat.com> wrote:
Is there a way we could export the results as a json file or something
similar? If there is (or if it could be added without too much
trouble), we would have multiple options:

Sure, adding some kind of export should be doable
 

1. Dump the contents of the current db, do a partial offline migration
   and finish it during the upgrade outage by export/importing the
   newest data, deleting the production db and importing the offline
   upgraded db. If that still takes too long, create a second postgres
   db containing the offline upgrade, switchover during the outage and
   import the new results since the db was copied.


I slept just two hours, so this is a bit entangled for me. So - my initial idea was, that we
 - dump the database
 - delete most of the results
 - do migration on the small data set

In paralel (or later on), we would
 - create a second database (let's call it 'archive')
 - import the un-migrated dump
 - remove data that is in the production db
 - run the lenghty migration

This way, we have minimal downtime, and the data are available in the 'archive' db,

With the archive db, we could either
1) dump the data and then import it to the prod db (again no down-time)
2) just spawn another resultsdb (archives.resultsdb?) instance, that would operate on top of the archives

I'd rather do the second, since it also has the benefit of being able to offload old data
to the 'archive' database (which would/could be 'slow by definition'), while keeping the 'active' dataset
small enough, that it could all be in memory for fast queries,.

What do you think? I guess we wanted to do something pretty similar, I just got lost a bit in what you wrote :)

 
2. If the import/export process is fast enough, might be able to do
   instead of the inplace migration

My gut feeling is that it would be pretty slow, but I have no relevant experience.

Joza