Looping eterno

Dados das tabelas;
spi.operacao --4352044 Registros
spi.auditoria --1014656 Registros
spi.datalegado --0
spi.dataoperacao --14793986 Registros
spi.detalheoperacao--182374 Registros
spi.extrato --3001929 Registros Registros
spi.extrato_cam --28017 Registros
spi.legado --41461940 Registros
spi.log--0 Registros

--Script
DO $$
DECLARE
    start_time timestamp := clock_timestamp(); 
    schema_orig text := 'public';
    schema_dest text := 'teste';
    table_pai text := 'operacao';
    tables_ text[] := ARRAY['operacao', 'auditoria', 'datalegado','dataoperacao','detalheoperacao','extrato', 'legado','log','extrato_cam'];
    cut_off_date TIMESTAMP := '2021-03-01';
    total_rows_ins integer := 0;
    total_rows_del integer := 0;
    table_name_var text;
    msgid_value integer;
    ispb_value integer;
    rows_affected integer := 0;
BEGIN
    PERFORM set_config('max_parallel_workers_per_gather', '12', true);
    FOREACH table_name_var IN ARRAY tables_ LOOP
        total_rows_ins := 0;
        IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = schema_orig AND table_name = table_name_var) THEN
            FOR msgid_value, ispb_value IN (SELECT msgid, ispb FROM public.operacao WHERE ts_inclusao < cut_off_date) LOOP
                BEGIN
                    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = schema_dest AND table_name = table_name_var) THEN
                        RAISE NOTICE 'Inserindo na tabela %.%',schema_dest, table_name_var;
                        IF table_name_var = 'operacao' THEN
                            EXECUTE format('INSERT INTO %I.%I (msgid, nuop, ispb, codmsg, lgnoperad, mqid, codmsgerro, lgnautoriz, lgnpiloto, sitlanc, cod, coa, identddestinatario, identdemissor, statusop, statusmsg, ts_inclusao, domsist, prioridade, dt_movto, authid) 
                                SELECT msgid, nuop, ispb, codmsg, lgnoperad, mqid, codmsgerro, lgnautoriz, lgnpiloto, sitlanc, cod, coa, identddestinatario, identdemissor, statusop, statusmsg, ts_inclusao, domsist, prioridade, dt_movto, authid 
                                FROM %I.%I WHERE ts_inclusao < $1 ON CONFLICT DO NOTHING',
                                schema_dest, table_name_var, schema_orig, table_name_var) USING cut_off_date;
                            GET DIAGNOSTICS rows_affected = ROW_COUNT;
                            total_rows_ins := total_rows_ins + rows_affected
                        ELSIF table_name_var = 'extrato_cam' THEN
                            EXECUTE format('INSERT INTO %I.%I (numctrl, tsop, ispb, flagacao, vlrmsg, numctrl_cam, dt_movto, codser, codmsg) 
                                                SELECT numctrl, tsop, ispb, flagacao, vlrmsg, numctrl_cam, dt_movto, codser, codmsg
                                                FROM %I.%I WHERE ispb = $1 ON CONFLICT DO NOTHING',
                                                schema_dest, table_name_var, schema_orig, table_name_var) USING ispb_value;
                            GET DIAGNOSTICS rows_affected = ROW_COUNT;
                            total_rows_ins := total_rows_ins + rows_affected;
                        END IF; 
                    ELSE
                        RAISE NOTICE 'A tabela %.% não existe no esquema de destino.', schema_dest, table_name_var;
                    END IF;
                EXCEPTION
                    WHEN unique_violation THEN
                        RAISE NOTICE 'Dados duplicados encontrados na tabela %.%, pulando para a próxima linha.', schema_orig ,table_name_var;
                    WHEN foreign_key_violation THEN
                        RAISE NOTICE 'Violada restrição FOREIGN KEY na tabela %.%, pulando para a próxima linha.', schema_orig, table_name_var;
                    WHEN others THEN
                        RAISE NOTICE 'Erro ao inserir a linha na tabela %.%:', schema_dest, table_name_var;
                        RAISE NOTICE 'Detalhes do erro: SQL STATE: % | SQLSTATE: %', SQLERRM, SQLSTATE;
                        ROLLBACK;  -- Executa o rollback de todas as operações
                        RAISE NOTICE 'ROLLBACK concluído.';
                END;
            END LOOP;
            RAISE NOTICE 'Total de linhas inseridas na tabela %.%: %', schema_dest, table_name_var, total_rows_ins;
        ELSE
            RAISE NOTICE 'A tabela %.% não existe no esquema de origem.', schema_orig, table_name_var;
        END IF;	
    END LOOP;
    FOR i IN REVERSE array_length(tables_, 1) .. 1 LOOP
        table_name_var := tables_[i];
        total_rows_del := 0;  -- Reinicia o contador de linhas deletadas para esta tabela
        
        IF table_name_var = 'extrato_cam' THEN
            query_text := format('DELETE FROM %I.%I WHERE ispb IN (SELECT ispb FROM %I.%I WHERE ts_inclusao < $1)', schema_orig, table_name_var, schema_orig, table_pai);
        ELSE
            query_text := format('DELETE FROM %I.%I WHERE msgid IN (SELECT msgid FROM %I.%I WHERE ts_inclusao < $1)', schema_orig, table_name_var, schema_orig, table_pai);
        END IF;
        
        EXECUTE query_text USING cut_off_date;
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        total_rows_del := total_rows_del + rows_affected; 
        RAISE NOTICE 'Total de linhas deletadas na tabela %.%: %', schema_orig, table_name_var, total_rows_del;		
    END LOOP;
    RAISE NOTICE 'Tempo total de execução: %', clock_timestamp() - start_time;
END $$;

Oi, Aulino! Como vai?

Puxa, 24 horas é bastante tempo para esperar uma transferência de dados, mesmo!

Como seu script é bastante longo, dividir em partes menores pode ser uma opção viável. O PostgreSQL tem a opção de paralelização de consultas, e essa estratégia pode ser considerada, também.

Uma forma eficaz de descobrir como otimizar suas consultas é utilizando a função EXPLAIN. Ela retorna o plano de execução por trás da consulta.

Certifique-se de monitorar o desempenho de cada otimização da consulta para garantir que os resultados desejados sejam atingidos.

Fico na torcida para que dê tudo certo por aí, Aulino!

Abraços.