Customer has about 4960 tables in different schemas and will apply a logical replication whole database. Is there any easy way to set 4960 tables' replica identity full. (alter <schema.table> replica identity full)

Customer is regularly upgrading/updating commercial licensed (not opensource) product and each update/upgrade reset table specifications so need to apply replica identity settings again after upgrade/update :)

So I am looking for better solution instead of applying 4960 alter statements


  • Generate the alterations in a loop? What exactly are you looking for here? Feb 6 at 11:58
  • Why "full"? The primary key is better. Feb 6 at 12:16
  • Hello @LaurenzAlbe Could not change anything on table design. Vendor and customer do not let me and as I wrote in my question. Table properties resets by installer while update or upgrade Feb 6 at 12:51
  • 1
    Hello @RichardHuxton Waht about following code ? I am not good at plpgsql DO $$ DECLARE schema_name TEXT; table_name TEXT; BEGIN FOR schema_name, table_name IN SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema IN ('schema1',!schema2',.....) LOOP EXECUTE format('ALTER TABLE %I.%I REPLICA IDENTITY FULL;', schema_name, table_name); END LOOP; END $$; Feb 6 at 12:51
  • I can't really read the code properly in a comment, but yes, that seems to be the general idea. Feb 6 at 13:04


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.