Как в Poestgres читать таблицу по несколько строк?

Попробуйте добавить нумерацию в выборку:

-- Копирование batch-ами
CREATE OR REPLACE FUNCTION public.loop_users()
    RETURNS Int 
        AS $$
        DECLARE 
            x Int ;
        BEGIN
            FOR x in 1..182256 BY 1000 LOOP
                INSERT INTO copy_users 
                select *,row_number()over(order by field1,field2 ...) rn
                from users
                where rn >= x and rn < x+1000;
            END LOOP;
           RETURN x;
        END;
        $$
        LANGUAGE 'plpgsql';

Порядок задается выражением (order by field1,field2 ...). Нужно, чтобы он выдавал однозначный порядок. Иначе, я думаю, результат будет непредсказуем. Ну и индексы на принимающей стороне нужно бы отключить. Если такой порядок однозначен, сначала можно осторожно на принимающей стороне определить последнюю записанную строку и продолжать с этой строки.
На отдающей стороне запросу придется делать сортировку по выбранным полям, что тоже может быть долго)

Если вы используете функцию не имеет большого значения какими порциями вы будите переносить данные. Это будет одна транзакция в которой будет зафиксирован перенос 100% данных исходной таблицы в целевую при выходе из функции. Что бы переносить как вы говорите "порциями" нужно зафиксировать каждый отдельный перенос и затем начать новый. Управление транзакциями возможно в процедурах (в функциях нет). Вот вариант копирования идентичных таблиц с сортировкой по ключевому полю: Тестовые таблицы:

CREATE TABLE tsource (
    id bigserial NOT NULL,
    data int8 NOT NULL,
    CONSTRAINT tsource_pkey PRIMARY KEY (id)
);

CREATE TABLE ttarget (
    id bigserial NOT NULL,
    data int8 NOT NULL,
    CONSTRAINT ttarget_pkey PRIMARY KEY (id)
);

Заполняем построчно таблицу источник с фиксацией каждой строки COMMIT (для примера просто)

CREATE PROCEDURE datagen()
LANGUAGE plpgsql
AS $$
BEGIN
 FOR i IN 1..5236 LOOP
    INSERT INTO tsource (data) VALUES (i);
    COMMIT;
 END LOOP;
  
END;
$$;
CALL datagen();

Процедура копирования с пагинацией через OFFSET:

CREATE OR REPLACE PROCEDURE copy( 
  source TEXT, 
  orderkey TEXT, 
  target TEXT, 
  bucket_size INT
    )
LANGUAGE 'plpgsql'
AS $BODY$
 DECLARE 
  scount INT;
  soffset INT DEFAULT 0;
  cmd TEXT;
 BEGIN
  cmd = $$SELECT COUNT(*) FROM $$ || source;
  EXECUTE cmd INTO scount;

  WHILE soffset < scount
    LOOP
      cmd = $$INSERT INTO $$ || target || 
    $$ SELECT * FROM $$ || source || $$ ORDER BY $$ || orderkey || 
    $$ LIMIT $1 OFFSET $2;$$;

    EXECUTE cmd
        USING bucket_size, soffset;
    COMMIT;
     soffset = soffset + bucket_size;
    END LOOP;
 END; 
$BODY$;

CALL copy('tsource', 'id', 'ttarget', 100);

Можно посмотреть здесь

Как было справедливо замечено, при больших значениях OFFSET он становится не эффективен. На вопрос может ли быть более эффективным страничное копирование ответит только explain и эксперимент в конкретной рабочей нагрузке. Возможно снижение дисковой буферизации и времени блокировки может дать эффект. Значит нужно максимально снизить сервисную нагрузку на обслуживание пагинации. Если допустить, что размеры переносимых частей не обязательно должны быть одинаковыми то можно перейти на условную пагинацию думаю это будет быстрее первого варианта:

CREATE OR REPLACE PROCEDURE copy( 
  source TEXT, 
  target TEXT, 
  bucket_size INT
    )
LANGUAGE 'plpgsql'
AS $BODY$
 DECLARE 
  maxkey INT;
  soffset INT DEFAULT 0;
  cmd TEXT;
 BEGIN
  cmd = $$SELECT id FROM $$ || source || $$ ORDER BY id DESC LIMIT 1$$;
  EXECUTE cmd INTO maxkey;

  WHILE soffset < maxkey
    LOOP
      cmd = $$INSERT INTO $$ || target || 
    $$ SELECT * FROM $$ || source || $$ WHERE id > $$ || soffset || 
    $$ AND id <= $$ || (soffset + bucket_size)::TEXT ;

    EXECUTE cmd;
    COMMIT;
     soffset = soffset + bucket_size;
    END LOOP;
 END; 
$BODY$;

здесь

Самым быстрым будет именно

insert into public.copy_users select * from public.users

Все прочие варианты будут медленнее, но могут использоваться в жизни чтобы хотя бы как-то понимать, на каком этапе копирование, с какой скоростью идёт и сколько осталось.

20гб это не так уж много, вероятно куда-то упираетесь существенно.

• посмотрите какие и сколько у вас индексов на таблице куда копируете, т.е. на copy_users. Если это возможно по задаче, то удалить с таблицы все индексы и построить их после копирования данных - будет быстрее.
• аналогично с foreign key, проверьте их наличие, проверьте наличие индексов на той таблице, куда эти FK ссылаются
• если на copy_users есть триггеры - посмотрите, можете ли вы их отключить/удалить на время копирования данных. Особенно for each row триггеры даже быстрые сами по себе, но помноженные на число копируемых строк в таблицу - величина существенная
• посмотрите вообще на определение таблицы, нет ли чего интересного. RLS политики, переопределения rule, необычный (не heap) table access method, не FDW ли это вообще вместо локальной таблицы
• далее железо - где выполняется запрос? есть ли ресурсы именно железа или ресурсы IO или CPU и так перегружены. На HDD, с недостатком RAM и с необходимостью обновлять индексы в процессе - будет работать примерно бесконечно
• далее настройки - может быть нужно временно поднять max_wal_size и checkpoint_timeout

---

Для нарезки операции на части нужно какое-то селективное поле или комбинацию полей.

select attname, null_frac, n_distinct from pg_stats where schemaname = ? and tablename = ?;

данные статистики самой базы можно использовать как отправную точку. При этом смотрим на существующие индексы, какие поля можем использовать для эффективного чтения данных (если есть уникальные индексы - то это наши фавориты).

Далее выбираем диапазон значений по вкусу и пробуем запустить копирование первого куска небольшого размера. Нам нужно оценить, сколько это займёт времени. Считаем грубо примерное время на всё копирование данных и оцениваем, подходит ли это.

Делать ровно N, не больше и не меньше строк в пачке, не имеет смысла. Нужен только разумного времени выполнения шаг (до десятка минут). Получилось не 100тыс строк, а 5тыс или 500тыс - ну и ладно. Главное что знаем где сейчас процесс и что он вообще идёт.

хинт: "итератор" через limit offset или row_number будут самыми худшими идеями. Просто потому, как оба этих метода работают на самом деле, сколь много бесполезной работы те делают на смещении в условный миллион строк (внимательно посмотрите в explain - даже если у вас Index Scan, то вы прочитаете все 1001000 строк только чтобы отбросить первые 1000000 и вернуть 1000)