Thread: unexpected PQresultStatus: 8 with simple logical replication
Hi all,
I'm trying to learn about how to subscribe to events in a logical replication slot. I'm finding that the documentation is sparse and mostly focused on binary WAL replication. I'd like to get a toy example working where I can see changes in my terminal. I'm able to create a replication slot, but trying to start it results in the error in the subject of my email:
psql "dbname=postgres replication=database user=postgres" -c "CREATE_REPLICATION_SLOT foobar LOGICAL test_decoding"
slot_name | consistent_point | snapshot_name | output_plugin
-----------+------------------+---------------+---------------
foobar | 0/D9C59A60 | 0000127E-1 | test_decoding
(1 row)
psql "dbname=postgres user=postgres" -c "SELECT slot_name, plugin, restart_lsn FROM pg_replication_slots"
slot_name | plugin | restart_lsn
-----------+---------------+-------------
foobar | test_decoding | 0/D9C59A28
(1 row)
psql "dbname=postgres replication=database user=postgres" -c "START_REPLICATION SLOT foobar LOGICAL 0/D9C59A28"
unexpected PQresultStatus: 8
Advice?
Many thanks,
Brent
On Sat, Jan 10, 2015 at 9:22 AM, Brent Tubbs <brent.tubbs@gmail.com> wrote: > psql "dbname=postgres replication=database user=postgres" -c > "START_REPLICATION SLOT foobar LOGICAL 0/D9C59A28" > unexpected PQresultStatus: 8 psql is not smart enough to understand that with a replication connection.. > Advice? You should use pg_logical_slot_peek_changes or pg_logical_slot_get_changes to grab changes from a replication slot using psql. If you are looking at something at a lower level, have a look at pg_recvlogical as an example of logical change receiver. It is a good start point as well if your goal is to implement your own receiver. In any case, the documentation provides some examples: http://www.postgresql.org/docs/current/static/logicaldecoding-example.html -- Michael
That's working! The blog posts I linked earlier made me think that I had to explicitly call START_REPLICATION, but I guess that's not necessary?
For anyone else following this thread, I found some better examples at http://pgci.eisentraut.org/jenkins/job/postgresql_master_world/Documentation/logicaldecoding-example.html.
On Sat, Jan 10, 2015 at 4:48 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Sat, Jan 10, 2015 at 9:22 AM, Brent Tubbs <brent.tubbs@gmail.com> wrote:
> psql "dbname=postgres replication=database user=postgres" -c
> "START_REPLICATION SLOT foobar LOGICAL 0/D9C59A28"
> unexpected PQresultStatus: 8
psql is not smart enough to understand that with a replication connection..
> Advice?
You should use pg_logical_slot_peek_changes or
pg_logical_slot_get_changes to grab changes from a replication slot
using psql. If you are looking at something at a lower level, have a
look at pg_recvlogical as an example of logical change receiver. It is
a good start point as well if your goal is to implement your own
receiver.
In any case, the documentation provides some examples:
http://www.postgresql.org/docs/current/static/logicaldecoding-example.html
--
Michael
On 2015-01-10 10:24:56 -0800, Brent Tubbs wrote: > That's working! The blog posts I linked earlier Which blogpost is that? At least this thread doesn't seem to contain a reference. > made me think that I had to explicitly call START_REPLICATION, but I > guess that's not necessary? START_REPLICATION over a replication connection is the streaming variant of logical decoding. Unfortunately psql can't deal with that (and it doesn't make sense to support it, as you need to send back feedback messages and such). The SQL function Michael linked to are there for easier accessibility, but don't support streaming. Other than that, they're the same. Hope that clears up things a bit? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
I mixed up my email threads. I was following http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-logical-replication-protocol/
Looking back at it now, I can see that it didn't use psql to call START_REPLICATION.
On Sat, Jan 10, 2015 at 12:39 PM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2015-01-10 10:24:56 -0800, Brent Tubbs wrote:
> That's working! The blog posts I linked earlier
Which blogpost is that? At least this thread doesn't seem to contain a
reference.
> made me think that I had to explicitly call START_REPLICATION, but I
> guess that's not necessary?
START_REPLICATION over a replication connection is the streaming variant
of logical decoding. Unfortunately psql can't deal with that (and it
doesn't make sense to support it, as you need to send back feedback
messages and such). The SQL function Michael linked to are there for
easier accessibility, but don't support streaming. Other than that,
they're the same.
Hope that clears up things a bit?
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On Sun, Jan 11, 2015 at 7:38 AM, Brent Tubbs <brent.tubbs@gmail.com> wrote: > I mixed up my email threads. I was following > http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-logical-replication-protocol/ > > Looking back at it now, I can see that it didn't use psql to call > START_REPLICATION. That's confusing btw, I am pushing a fix with more details. -- Michael
Thanks for your help here, on those blog posts, and in the pg_plugins repo.
This weekend I was able to make https://bitbucket.org/btubbs/decoder_json/ a thing.
On Sun, Jan 11, 2015 at 12:53 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
On Sun, Jan 11, 2015 at 7:38 AM, Brent Tubbs <brent.tubbs@gmail.com> wrote:
> I mixed up my email threads. I was following
> http://michael.otacoo.com/postgresql-2/postgres-9-4-feature-highlight-logical-replication-protocol/
>
> Looking back at it now, I can see that it didn't use psql to call
> START_REPLICATION.
That's confusing btw, I am pushing a fix with more details.
--
Michael