· 6 years ago · Oct 16, 2019, 06:54 PM
1DO
2$$
3DECLARE
4 temprow information_schema.schemata%rowtype;
5 t timestamptz := clock_timestamp();
6 tenant_t timestamptz := clock_timestamp();
7 global_t timestamptz := clock_timestamp();
8 createdAtLimit timestamptz := now() - interval '3 months';
9 startingAt timestamptz;
10 txCount INTEGER;
11BEGIN
12FOR temprow IN
13 SELECT * FROM information_schema.schemata WHERE schema_name LIKE '\_%'
14
15 LOOP
16 RAISE NOTICE 'Restoring %, starting at %', temprow.schema_name, tenant_t;
17 EXECUTE FORMAT ('SET SEARCH_PATH TO %I', temprow.schema_name);
18
19 CREATE INDEX IF NOT EXISTS archived_transaction_createdat_idx ON archived_transaction (createdAt DESC NULLS LAST);
20 SELECT createdAt FROM transaction WHERE createdAt IS NOT NULL ORDER BY createdAt DESC LIMIT 1 INTO startingAt;
21 RAISE NOTICE 'Transactions will be restored from 3 months prior to %', COALESCE(startingAt, now());
22
23 CREATE TABLE IF NOT EXISTS temp_transaction AS WITH RECURSIVE rec_transaction AS (SELECT t.* FROM archived_transaction t WHERE parenttransactionid IS NULL AND createdAt > createdAtLimit UNION ALL SELECT c.* FROM archived_transaction c JOIN rec_transaction p ON c.parenttransactionid = p.id AND c.environmentid = p.environmentid WHERE c.createdAt > createdAtLimit) SELECT * FROM rec_transaction;
24 INSERT INTO temp_transaction WITH RECURSIVE rec_transaction AS (SELECT t.* FROM archived_transaction t WHERE originaltransactionid IS NULL AND createdAt > createdAtLimit UNION ALL SELECT c.* FROM archived_transaction c JOIN rec_transaction p ON c.originaltransactionid = p.id AND c.environmentid = p.environmentid WHERE c.createdAt > createdAtLimit) SELECT * FROM rec_transaction;
25
26 SELECT count(id) FROM temp_transaction INTO txCount;
27 RAISE NOTICE '- Totalling % transactions', txCount;
28
29 t = clock_timestamp();
30 INSERT INTO transaction SELECT * FROM archived_transaction WHERE id IN (SELECT id FROM temp_transaction) ON CONFLICT DO NOTHING;
31 RAISE NOTICE '-- spent % restoring % transactions', clock_timestamp() - t, txCount;
32
33 t = clock_timestamp();
34 INSERT INTO transmission SELECT * FROM archived_transmission WHERE createdAt > createdAtLimit AND transactionid IN (SELECT id FROM temp_transaction) ON CONFLICT DO NOTHING;
35 RAISE NOTICE '-- spent % restoring transmission', clock_timestamp() - t;
36
37 t = clock_timestamp();
38 INSERT INTO as2_transmission_detail SELECT * FROM archived_as2_transmission_detail WHERE createdAt > createdAtLimit AND transmissionid IN (SELECT id FROM transmission) ON CONFLICT DO NOTHING;
39 RAISE NOTICE '-- spent % restoring as2_transmission_detail', clock_timestamp() - t;
40
41 t = clock_timestamp();
42 INSERT INTO ftp_transmission_detail SELECT * FROM archived_ftp_transmission_detail WHERE createdAt > createdAtLimit AND transmissionid IN (SELECT id FROM transmission) ON CONFLICT DO NOTHING;
43 RAISE NOTICE '-- spent % restoring ftp_transmission_detail', clock_timestamp() - t;
44
45 t = clock_timestamp();
46 INSERT INTO error SELECT * FROM archived_error WHERE transactionid IN (SELECT id FROM temp_transaction) ON CONFLICT DO NOTHING;
47 RAISE NOTICE '-- spent % restoring error', clock_timestamp() - t;
48
49 t = clock_timestamp();
50 INSERT INTO business_document SELECT * FROM archived_business_document WHERE createdAt > createdAtLimit AND transactionid IN (SELECT id FROM temp_transaction) ON CONFLICT DO NOTHING;
51 RAISE NOTICE '-- spent % restoring business_document', clock_timestamp() - t;
52
53 t = clock_timestamp();
54 INSERT INTO document_property SELECT * FROM archived_document_property WHERE businessdocumentid IN (SELECT id FROM business_document) ON CONFLICT DO NOTHING;
55 RAISE NOTICE '-- spent % restoring document_property', clock_timestamp() - t;
56
57 t = clock_timestamp();
58 INSERT INTO process_instance SELECT * FROM archived_process_instance ON CONFLICT DO NOTHING;
59 RAISE NOTICE '-- spent % restoring process_instance', clock_timestamp() - t;
60
61 t = clock_timestamp();
62 INSERT INTO process_step_instance SELECT * FROM archived_process_step_instance WHERE transactionid IN (SELECT id FROM temp_transaction) ON CONFLICT DO NOTHING;
63 RAISE NOTICE '-- spent % restoring process_step_instance', clock_timestamp() - t;
64
65 RAISE NOTICE '% finished at % (%)', temprow.schema_name, tenant_t, clock_timestamp() - tenant_t;
66 DROP TABLE temp_transaction;
67
68 END LOOP;
69
70 RAISE NOTICE 'Total time: %', clock_timestamp() - global_t;
71
72END$$;