Welcome to pgcopydb Discussions!

[dimitri]

👋 Welcome!

We’re using Discussions as a place to connect with other members of our community. We hope that you:

• Ask questions you’re wondering about.
• Share ideas.
• Engage with other community members.
• Welcome others and are open-minded. Remember that this is a community we
  build together 💪.

To get started, comment below with an introduction of yourself and tell us about what you do with this community.

[herwigg]

Hi, First of all I think it is a wonderful tool because it makes migration of databases way easier and more performant. Thank you for that. I have two remarks:

• as I understand it you still have to create a target db manually, the tool does not do that (even not when you connect with superuser) I think it would be a very nice addition to the tool to be able to create the db as well.
• and second: for the role creation: the tool has a role migration but this copies all roles (or is it possible with the option --filters)
• it would be nice if you could do above two things in simulation mode, so show what the tool would do without actually doing it and/or output the actions to a file instead of directly to a target instance/db. A simple grep on the output file to select the roles that you want to create would make it complete. As you know roles are global so all roles are per default migrated to the target instance right now using the tool, but when migrating a single database that is not what we want.

[shane-borden]

Is there any way to use this tool to move "data only"?

[aborschev]

Hi there!
I've created code to monitor pgcopydb copy operation in progress.
Maybe someone find this useful, or propose some enhancements.
The similar scripts can be done for INDEX and VACUUM operations, based on pg_stat_progess_vacuum and pg_stat_progress_index.

-- This DO block shows statistics of COPY processes in database based on pg_stat_progress_copy.
-- Loops every 5 sec, shows list of pocesses with speed in MB/s and tuples/s, and total.
-- Used to monitor pgcopydb copy operations. 
do $$
declare
    -- Variables to adjust:
    refresh_interval double precision := 5; -- 5 sec
    total_only boolean := False;            -- Hide stats for individual copy jobs
    show_idle_msg boolean := true           -- shows 'No COPY processes' messages when pg_stat_progress_copy empty  
    -- Variables to store previous values
    prev_stats RECORD := NULL;
    current_stats RECORD;
    prev_time TIMESTAMPTZ;
    curr_time TIMESTAMPTZ;   
    -- Temporary variables for calculations
    elapsed_interval INTERVAL;
    elapsed_interval_sec FLOAT;     
    -- Rate variables
    current_bytes_rate numeric;
    current_tuples_rate numeric;
    avg_bytes_rate numeric;
    avg_tuples_rate numeric;
    total_current_bytes_rate numeric;
    total_current_tuples_rate numeric;    
    -- Counters
    active_workers INT;
    first_iteration BOOLEAN := TRUE;    
BEGIN
    -- Create temporary table to store process rates between iterations
    CREATE TEMPORARY TABLE IF NOT EXISTS copy_process_stats (
        pid INT PRIMARY KEY,
        bytes_processed BIGINT,
        tuples_processed BIGINT,
        query_start TIMESTAMPTZ,
        last_update TIMESTAMPTZ
    );
    -- In case the table left from prev. run.
    TRUNCATE copy_process_stats;
    -- Initialize time
    prev_time := clock_timestamp();
    curr_time := prev_time;   
    -- Print header
    RAISE NOTICE '=== Monitoring COPY progress start at % ===', curr_time;
    if total_only = false then
    	RAISE NOTICE '|Time    |PID     | Table (Schema)     |Run Time| MBytes | Tuples     | Avg MB/s |AvgTuple/s|Curr MB/s|CurrTuple/s| Command';
    	RAISE NOTICE '|--------|--------|--------------------|--------|--------|------------|----------|----------|---------|-----------|---------';   
    end if;
    -- Main monitoring loop
    LOOP
        -- Get current time
        curr_time := clock_timestamp();
        elapsed_interval := curr_time - prev_time;
        elapsed_interval_sec := EXTRACT(EPOCH FROM elapsed_interval);        
        -- Reset aggregates
        total_current_bytes_rate := 0;
        total_current_tuples_rate := 0;
        active_workers := 0;        
        -- Process each COPY operation
        FOR current_stats IN 
            SELECT 
                pcp.pid,
                left(regexp_replace(psa.query, E'[\\n\\r]+', ' ', 'g' ), 64) as query_part,
                psa.query_start,
                pcp.relid::regclass AS table_name,
                pcp.bytes_processed,
                pcp.tuples_processed
            FROM pg_stat_progress_copy pcp
            JOIN pg_stat_activity psa ON pcp.pid = psa.pid
            WHERE psa.query ~* 'COPY'
        loop
            active_workers := active_workers + 1;   
           -- RAISE NOTICE 'active_workers % pid %',active_workers, current_stats.pid;
            -- Get previous stats from our temp table
            SELECT * INTO prev_stats 
            FROM copy_process_stats 
            WHERE pid = current_stats.pid;            
            -- Calculate rates
            IF prev_stats IS NOT NULL AND elapsed_interval_sec > 0 THEN
                -- Current rate (last interval)
                IF current_stats.bytes_processed < prev_stats.bytes_processed  -- cilent process starts NEW TABLE copy 
                THEN prev_stats.bytes_processed := 0;
                END IF; 
                IF current_stats.tuples_processed < prev_stats.tuples_processed
                THEN prev_stats.tuples_processed := 0;
                END IF;
                current_bytes_rate := (current_stats.bytes_processed - prev_stats.bytes_processed) / 
                                    (1024.0 * 1024.0 * elapsed_interval_sec);
                current_tuples_rate := (current_stats.tuples_processed - prev_stats.tuples_processed) / 
                                    elapsed_interval_sec;
                
                -- Average rate (since start)
                avg_bytes_rate := current_stats.bytes_processed / 
                                (1024.0 * 1024.0 * EXTRACT(EPOCH FROM (curr_time - current_stats.query_start)));
                avg_tuples_rate := current_stats.tuples_processed / 
                                EXTRACT(EPOCH FROM (curr_time - current_stats.query_start));
            ELSE
                -- First measurement for this process
                current_bytes_rate := 0;
                current_tuples_rate := 0;
                avg_bytes_rate := 0;
                avg_tuples_rate := 0;
            END IF;            
            -- Update temp table with current stats
            INSERT INTO copy_process_stats (pid, bytes_processed, tuples_processed, query_start, last_update)
            VALUES (current_stats.pid, current_stats.bytes_processed, current_stats.tuples_processed, 
                   current_stats.query_start, curr_time)
            ON CONFLICT (pid) 
            DO UPDATE SET 
                bytes_processed = EXCLUDED.bytes_processed,
                tuples_processed = EXCLUDED.tuples_processed,
                last_update = EXCLUDED.last_update;            
            -- Aggregate totals
            total_current_bytes_rate := total_current_bytes_rate + current_bytes_rate;
            total_current_tuples_rate := total_current_tuples_rate + current_tuples_rate;
           if total_only = false then
            -- Print process info
                RAISE NOTICE '|%|%|%|%|%|%|%|%| %|%|%', 
                --RAISE NOTICE '% | %8s | %-20s | %-12s | %6.2f | %6d | %8.2f | %12.2f | %12.2f | %16.2f | %s',
                to_char(curr_time, 'HH24:MI:SS'),
                format('%8s', current_stats.pid::text),
                rpad(coalesce(current_stats.table_name::text, '-'), 20),
                to_char(curr_time - current_stats.query_start, 'HH24:MI:SS'),
                format('%8s', ROUND(current_stats.bytes_processed / (1024.0 * 1024.0), 2)),
                format('%12s',current_stats.tuples_processed::text),
                format('%9s', ROUND(avg_bytes_rate, 2)),
                format('%11s',ROUND(avg_tuples_rate, 0)),
                format('%8s', ROUND(current_bytes_rate, 2)),
                format('%11s',ROUND(current_tuples_rate, 0)),
                trim(regexp_replace(current_stats.query_part, '\s+', ' ', 'g'));
           end if;    
        END LOOP;        
        -- Calculate and print summary statistics
        IF NOT first_iteration AND active_workers > 0 THEN
            RAISE NOTICE ' %          Total workers: %                                                      %|%', 
                to_char(curr_time, 'HH24:MI:SS'),
                format('%3s',  active_workers::text),
                format('%11s', ROUND(total_current_bytes_rate, 2)), 
                format('%11s', ROUND(total_current_tuples_rate, 0));
        ELSIF active_workers = 0 and show_idle_msg THEN
            RAISE NOTICE ' % No COPY processes', to_char(curr_time, 'HH24:MI:SS'); 
        END IF;        
        -- Clean up old records from temp table
        DELETE FROM copy_process_stats WHERE last_update < curr_time - interval '1 hour';        
        -- Save current values for next iteration
        prev_time := curr_time;
        first_iteration := FALSE;
        -- Sleep for 5 seconds
        PERFORM pg_sleep(refresh_interval);       
        commit;
    END LOOP;   
    -- Clean up (though this code loops forever and will be terminated via Ctrl-C, thus never gets here)
    DROP TABLE IF EXISTS copy_process_stats;
END;
$$;