All tables in Postgres should have a `REPLICA IDENTITY` available so that Postgres logical replication can be used

This issue has been migrated from [#16224](https://github.com/matrix-org/synapse/issues/16224).

---

**Description:**

When migrating a Postgres database, there are a few options:

- SQL dump (`pg_dump` which is then fed into `psql` on the restoring end). Simple but in my case this is taking 150 minutes to restore, so I have outgrown this solution really as 150 minutes of downtime is quite hard to schedule.
- Use physical replication and perform a failover to the secondary once it's caught up. This is possible today (and matrix.org uses physical replication for a hot standby), but physical replication is not very granular — you have to use it for the whole Postgres cluster, can't select just one database — and needs the versions of Postgres to match and seems to otherwise enforce some constraints about the setup on both sides needing to match. (As the manual says, really you're better off just having the primary and secondary be identical in as many ways as possible!)
- Use logical replication and perform a failover to the secondary once it's caught up. This approach is quite elegant to use from Postgres — you first have to `pg_dump --schema-only` and restore that, then use `CREATE PUBLICATION` (primary) and `CREATE SUBSCRIPTION` (secondary) and Postgres takes care of the rest... even across different Postgres versions and different machine architectures... **but with Synapse this currently causes a problem on the primary**.

The problem is that Postgres needs to identify individual rows in the tables using a so-called `REPLICA IDENTITY`. This defaults to the primary key of the table if one is set — but many Synapse tables just don't have a primary key.

The net effect is that once you start logical replication, all `UPDATE`s and `DELETE`s to the tables without primary keys now fail. In turn, this causes basic features like `/sync` to stop working (as it deletes from `device_inbox` at least).

```
psycopg2.errors.ObjectNotInPrerequisiteState: cannot delete from table "device_inbox" because it does not have a replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
```

You can set the `REPLICA IDENTITY` per table manually, either to an existing unique index or to the full record as a fallback if you really have no better option. (see https://www.postgresql.org/docs/15/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY)

It would be nice to do this in Synapse so that logical replication works out of the box. We might even consider keeping a lint around to check that all tables have a replica identity?

I think this SQL can be used to find tables that are set to use the default replica identity, but which don't have a primary key (i.e. they don't have a valid replica identity after all):
```sql
WITH tables_no_pkey AS (
SELECT tbl.table_schema, tbl.table_name
FROM information_schema.tables tbl
WHERE table_type = 'BASE TABLE'
AND table_schema not in ('pg_catalog', 'information_schema')
AND NOT EXISTS (
SELECT 1
FROM information_schema.key_column_usage kcu
WHERE kcu.table_name = tbl.table_name
AND kcu.table_schema = tbl.table_schema
)
)
SELECT oid::regclass FROM tables_no_pkey INNER JOIN pg_class ON oid::regclass = table_name::regclass
WHERE relreplident = 'd';
-- d = default
```

This currently gives me
```
event_relations
federation_stream_position
federation_inbound_events_staging
local_media_repository_thumbnails
local_media_repository_url_cache
device_federation_outbox
event_search
ignored_users
insertion_events
monthly_active_users
push_rules_stream
room_stats_state
blocked_rooms
current_state_delta_stream
cache_invalidation_stream_by_instance
deleted_pushers
appservice_room_list
device_inbox
device_lists_outbound_last_success
device_lists_remote_cache
device_lists_stream
device_lists_remote_extremeties
e2e_cross_signing_signatures
device_federation_inbox
e2e_room_keys_versions
event_auth
event_auth_chain_links
insertion_event_extremities
presence_stream
ratelimit_override
remote_media_cache_thumbnails
room_alias_servers
room_stats_earliest_token
user_directory
threepid_guest_access_tokens
state_groups_state
user_filters
users_in_public_rooms
user_threepid_id_server
users_pending_deactivation
insertion_event_edges
users_who_share_private_rooms
worker_locks
device_auth_providers
device_lists_changes_in_room
device_lists_remote_resync
e2e_room_keys
state_group_edges
stream_ordering_to_exterm
event_push_summary
device_lists_outbound_pokes
e2e_cross_signing_keys
erased_users
stream_positions
event_push_actions_staging
user_ips
user_signature_stream
batch_events
user_daily_visits
user_directory_search
```