repro_small.sql

CREATE EXTENSION postgres_fdw;
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END
$d$;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;

CREATE TABLE local_data (id int, data text);
INSERT INTO local_data SELECT i, 'test'|| i FROM generate_series(1, 10000) i;

CREATE TABLE sharded_data (id int, data text) PARTITION BY HASH(id);
CREATE TABLE sharded_data_p0  PARTITION OF sharded_data FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE sharded_data_p1_remote (id int, data text);
CREATE FOREIGN TABLE sharded_data_p1  PARTITION OF sharded_data FOR VALUES WITH (modulus 4, remainder 1)
SERVER loopback OPTIONS (table_name 'sharded_data_p1_remote');
CREATE TABLE sharded_data_p2_remote (id int, data text);
CREATE FOREIGN TABLE sharded_data_p2  PARTITION OF sharded_data FOR VALUES WITH (modulus 4, remainder 2)
SERVER loopback OPTIONS (table_name 'sharded_data_p2_remote');
CREATE TABLE sharded_data_p3_remote (id int, data text);
CREATE FOREIGN TABLE sharded_data_p3  PARTITION OF sharded_data FOR VALUES WITH (modulus 4, remainder 3)
SERVER loopback OPTIONS (table_name 'sharded_data_p3_remote');

insert into sharded_data select * from local_data ;
delete from sharded_data;

alter server loopback options (add batch_size '100');
insert into sharded_data select * from local_data ;