npgsql
npgsql copied to clipboard
Support exporting data via LogicalReplicationConnection
When creating a replication slot, you typically export a snapshot which you then use to export data that existed in the database before the slot was created so that your replication client can process both, the preexisting data and the data that follow via the logical replication stream later. As shown in the example I provided at https://stackoverflow.com/a/78626612/2648024, the current way to do this is to open a regular NpgsqlConnection, start a transaction and use the exported snapshot to read the preexisting data. Generally there is no problem with that but it is a wart that you have to open a separate connection to read the data from the snapshot. What I hate most about this is the fact that you cannot use a user account that is confined to replication connections via pg_hba.conf because you also need to open a regular NpgsqlConnection. From the backend's perspective this would not be necessary since logical replication connections have basic support for issuing queries via the simple protocol. We already had a discussion about the possibility of supporting arbitary queries in LogicalReplicationConnection via the simple protocol but this is non-trivial (logical replication conections have a separate SQL parser that only provides a subset of the simple protocol so we'd have to special-case it like we did in https://github.com/npgsql/npgsql/pull/3928) and maybe also solution in search of a problem (after all nobody asked for this yet).
I think it would be easier to just support exporting data via something like our NpgsqlBinaryExporter (or a TextReader) and that this would already cover 95% of the things you might want to do via a LogicalReplicationConnection.