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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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: 8psql 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
Show quoted text
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