Thread: Handle PGRES_COPY_BOTH in psql for logical replication?
Hello Hackers,
At the moment one can open up a replication connection using psql and use all of the commands available on this special type of connection, except for START_REPLICATION:
$ psql -d "dbname=test replication=database"
psql (9.4.1)
Type "help" for help.
test=# IDENTIFY_SYSTEM;
systemid | timeline | xlogpos | dbname
---------------------+----------+-----------+---------
6146106447402521313 | 1 | 0/76422B0 | bw_test
(1 row)
test=# START_REPLICATION SLOT test LOGICAL 0/0;
unexpected PQresultStatus: 8
test=#
This is due to PGRES_COPY_BOTH not being handled in psql. With introduction of logical replication I think it makes sense to support this mode, for the sake of (semi-)interactive testing from psql.
For example, one could try replication from different wal positions, different output plugins and/or plugin options, w/o re-establishing the connection, interactively by editing the command directly in psql prompt, as opposed to using pg_recvlogical and editing the command line switches.
I can make a patch if there's general interest for this feature.
Cheers.
--Alex
On 2015-06-05 09:20:41 +0200, Shulgin, Oleksandr wrote: > Hello Hackers, > > At the moment one can open up a replication connection using psql and use > all of the commands available on this special type of connection, except > for START_REPLICATION: > > $ psql -d "dbname=test replication=database" > psql (9.4.1) > Type "help" for help. > > test=# IDENTIFY_SYSTEM; > systemid | timeline | xlogpos | dbname > ---------------------+----------+-----------+--------- > 6146106447402521313 | 1 | 0/76422B0 | bw_test > (1 row) > > test=# START_REPLICATION SLOT test LOGICAL 0/0; > unexpected PQresultStatus: 8 > test=# > > This is due to PGRES_COPY_BOTH not being handled in psql. With > introduction of logical replication I think it makes sense to support this > mode, for the sake of (semi-)interactive testing from psql. How would that look like? It's just binary data you'd get back? And what would be the feedback messages and such look like, and what would be the content? > For example, one could try replication from different wal positions, > different output plugins and/or plugin options, w/o re-establishing the > connection, interactively by editing the command directly in psql prompt, > as opposed to using pg_recvlogical and editing the command line switches. You can mostly use the sql functions for that. Regards, Andres
On Fri, Jun 5, 2015 at 9:26 AM, Andres Freund <andres@anarazel.de> wrote:
On 2015-06-05 09:20:41 +0200, Shulgin, Oleksandr wrote:
> Hello Hackers,
>
> At the moment one can open up a replication connection using psql and use
> all of the commands available on this special type of connection, except
> for START_REPLICATION:
>
> $ psql -d "dbname=test replication=database"
> psql (9.4.1)
> Type "help" for help.
>
> test=# IDENTIFY_SYSTEM;
> systemid | timeline | xlogpos | dbname
> ---------------------+----------+-----------+---------
> 6146106447402521313 | 1 | 0/76422B0 | bw_test
> (1 row)
>
> test=# START_REPLICATION SLOT test LOGICAL 0/0;
> unexpected PQresultStatus: 8
> test=#
>
> This is due to PGRES_COPY_BOTH not being handled in psql. With
> introduction of logical replication I think it makes sense to support this
> mode, for the sake of (semi-)interactive testing from psql.
How would that look like? It's just binary data you'd get back? And what
would be the feedback messages and such look like, and what would be the
content?
It would be whatever the logical decoding plugin sends. Makes perfect sense for OUTPUT_PLUGIN_TEXTUAL_OUTPUT to see it on the terminal. And you could even use binary with psql's \o, if you want (though usefulness is less apparent to me in this case).
Since my intent is to just use it for testing the look of text logical output, I'm not planning any support for feedback messages in this case.
> For example, one could try replication from different wal positions,
> different output plugins and/or plugin options, w/o re-establishing the
> connection, interactively by editing the command directly in psql prompt,
> as opposed to using pg_recvlogical and editing the command line switches.
You can mostly use the sql functions for that.
Maybe I'm missing something, which functions do you have in mind exactly?
--
Alex
On 2015-06-05 09:43:45 +0200, Shulgin, Oleksandr wrote: > > How would that look like? It's just binary data you'd get back? And what > > would be the feedback messages and such look like, and what would be the > > content? > It would be whatever the logical decoding plugin sends. Makes perfect > sense for OUTPUT_PLUGIN_TEXTUAL_OUTPUT to see it on the terminal. And you > could even use binary with psql's \o, if you want (though usefulness is > less apparent to me in this case). Hm. I severely doubt that that's a useful thing to do. > > For example, one could try replication from different wal positions, > > > different output plugins and/or plugin options, w/o re-establishing the > > > connection, interactively by editing the command directly in psql prompt, > > > as opposed to using pg_recvlogical and editing the command line switches. > > > > You can mostly use the sql functions for that. > > > > Maybe I'm missing something, which functions do you have in mind exactly? pg_logical_slot_get_changes() etc?
On Fri, Jun 5, 2015 at 10:22 AM, Andres Freund <andres@anarazel.de> wrote:
>
> Maybe I'm missing something, which functions do you have in mind exactly?
pg_logical_slot_get_changes() etc?
1. With get/peek_changes one cannot specify start_lsn. A parameter upto_lsn is supported instead.
2. The functions return when either of the upto_* limits is reached or there are no more data to decode, while with internal command it should wait for more data until interrupted by user.
Anyway, using pg_recvlogical is perfectly fine by me, it's just psql can pass the command, but is not ready to handle the request. Maybe just having are more sensible error message for PGRES_COPY_BOTH is the way to go.
Anyway, using pg_recvlogical is perfectly fine by me, it's just psql can pass the command, but is not ready to handle the request. Maybe just having are more sensible error message for PGRES_COPY_BOTH is the way to go.
--
Alex