I’m working on a Blue/Green deployment across two RDS Postgres instances using this pattern:

Build table_1 into RDS_1 > 
Copy data into table_1_aux in RDS_2 using postgres_fdw > 
Drop table_1 and then rename table_1_aux to table_1 in RDS_2

table_1 structure and indexes might change, and therefore, I’d like the new schema and indexes to also be imported into RDS2.

My issue this query does not import the indexes from the remote server:

-- Create auxiliary table and copy data from staging
CREATE TABLE test_schema.test_table_aux (LIKE test_schema_remote.test_table INCLUDING INDEXES);

How can I make this pattern work including the indexes?

How to reproduce – Server 1:

-- On Staging DB

DROP SCHEMA IF EXISTS test_schema CASCADE;
CREATE SCHEMA test_schema;


DROP TABLE IF EXISTS test_schema.test_table;

CREATE TABLE test_schema.test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    value INTEGER NOT NULL
);

CREATE INDEX idx_test_table_name ON test_schema.test_table(name);
CREATE INDEX idx_test_table_value ON test_schema.test_table(value);

-- Insert some test data
INSERT INTO test_schema.test_table (name, value) VALUES ('test1', 100), ('test2', 200), ('test3', 300);

In Server 2:

-- On Production DB
-- Drop FDW extension if exists and related server and user mapping
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;

-- Create FDW extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Drop server and user mapping if exists
DROP SERVER IF EXISTS staging_server CASCADE;
DROP USER MAPPING IF EXISTS FOR testuser SERVER staging_server;

-- Create server for staging DB using service name for hostname
CREATE SERVER staging_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'test-db-dbt', dbname 'testdb', port '5432');

-- Create user mapping for staging server
CREATE USER MAPPING FOR testuser
    SERVER staging_server
    OPTIONS (user 'testuser', password 'testpassword');

DROP SCHEMA IF EXISTS test_schema_remote CASCADE;
CREATE SCHEMA test_schema_remote;

-- Import foreign schema from staging DB
IMPORT FOREIGN SCHEMA test_schema
    FROM SERVER staging_server
    INTO test_schema_remote;
DROP SCHEMA IF EXISTS test_schema CASCADE;
CREATE SCHEMA test_schema;

-- Drop auxiliary table if exists
DROP TABLE IF EXISTS test_schema.test_table_aux;

-- Create auxiliary table and copy data from staging
CREATE TABLE test_schema.test_table_aux (LIKE test_schema_remote.test_table INCLUDING INDEXES);

-- Verify that the indexes did not get copied
select
    c.relnamespace::regnamespace as schema_name,
    c.relname as table_name,
    i.indexrelid::regclass as index_name,
    i.indisprimary as is_pk,
    i.indisunique as is_unique
from pg_index i
join pg_class c on c.oid = i.indrelid
where c.relname = 'test_table_aux';