Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Fun Things to do with Logical Decoding

Fun Things to do with Logical Decoding

With 9.4 came logical decoding but what is it and how can it be used? Besides being a precursor to bi-directional replication there are plenty of use cases for this and many don't even require you to implement a plugin. We'll look at trigger-less auditing, partial replication and full statement replication.

F1e0e0c3c3196a63c9b17a2344fb6a61?s=128

Mike Fowler

July 07, 2015
Tweet

Transcript

  1. Migrating Rant & Rave to PostgreSQL Mike Fowler, mike@mlfowler.com mike.fowler@rantandrave.com

    PGDayUK 2014 Fun Things to do with Logical Decoding Mike Fowler PGDay UK 2015
  2. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 About Me • Head of Systems Engineering at Rant & Rave • Been using PostgreSQL for over 10 years (since 7.4) • Contributed some XML support – XMLEXISTS/xpath_exists() – xml_is_well_formed() • Contributed a number of bugfixes to the JDBC driver
  3. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Overview • What is Logical Decoding? • Replications Slots • Output Plugins • Enabling Logical Decoding • What does Logical Decoding make possible?
  4. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 What is Logical Decoding? “The process of extracting all persistent changes to a database's tables into a coherent, easy to understand format which can be interpreted without detailed knowledge of the database's internal state” “Implemented by decoding the contents of the write-ahead log, which describe changes on a storage level, into an application-specific form such as a stream of tuples or SQL statements”
  5. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 No, really - what is Logical Decoding? • The results of logical decoding give a perspective on the logical changes made during a transaction • The process of logical decoding allows human understanding of the physical changes made during a transaction • Achieved by creating a replication slot with a plugin to produce data for a receiver – Basic example plugin available in contrib – New utility pg_recvlogical to consume plugin output – Can roll your own to match your requirements
  6. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Some PostgreSQL Basics • PostgreSQL is a transactional database • All changes in PostgreSQL are recorded in a Write Ahead Log (WAL) before any data on disk is actually changed • The WAL is the key to replication – Essentially log based binary replication – Master runs the walsender to transmit new segments – Slaves run walreceivers to receive the segments
  7. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 A Basic Example We'll use a simple table called sample: CREATE TABLE sample ( id SERIAL PRIMARY KEY, data JSON, captured TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
  8. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 A Basic Example INSERT INTO sample (data) VALUES ('{"beverage":"coffee","temp":96}'); • The backend takes our INSERT and calculates the values for the omitted columns id and captured • The backend also determines exactly what needs to be changed on disk and commits this to the WAL
  9. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Replication Slots • 9.4 introduced replication slots • Capability to retain WAL segments on the server instead of relying on continuous archiving • Allows master to retain segments even when a slave is offline • There are two types of replication slot – Physical – Logical
  10. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Logical Replication Slots • Entries in a logical slot are WAL segments that have been decoded by means of an output plugin • Output plugins are responsible for transforming the data from the WAL into the format the consumer of a logical replication slot desires • contrib/test_decoding is a working example of the interface and can be used to play
  11. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Output Plugin Rules • Only transactions that have been successfully flushed to disk are decoded • Transactions that are rolled back never get decoded • Changes to UNLOGGED or TEMPORARY tables are also not decoded • Concurrent transactions are decoded in commit order
  12. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Making an Output Plugin • Consists of building a shared library that implements a number of functions as detailed in section 46.6 of the manual – An initialization function – Transaction begin, row change and transaction end callbacks – Optional plugin start & stop callbacks – 9.5 also adds an optional origin filter callback • For a given transaction the begin & end is called once with the row change called for each changed row even if multiple rows were changed in a single statement
  13. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Output Plugin Considerations • Keep the plugin simple – It's running as part of your database – It's going to process all your changes • Offload complex filter/analysis to the slot consumer • Drop the slot if it's no longer in use to stop using server resources SELECT pg_drop_replication_slot('pgday_slot');
  14. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Enabling Logical Decoding • Enable replication slots (will require restart) – wal_level must be set to 'logical' – max_replication_slots must be at least 1 • Create a replication slot pgday15=# SELECT * FROM pg_create_logical_replication_slot('pgday_slot', 'test_decoding'); slot_name | xlog_position ------------+--------------- pgday_slot | 0/16C9A78 (1 row)
  15. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Revisiting the Basic Example We used a simple table called sample: CREATE TABLE sample ( id SERIAL PRIMARY KEY, data JSON, captured TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
  16. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Revisiting the Basic Example INSERT INTO sample (data) VALUES ('{"beverage":"coffee","temp":96}'); • The backend took our INSERT and calculated the values for the omitted columns id and captured • The backend also determined exactly what needed to be changed on disk and committed it to the WAL • This time the test_decoding plugin was invoked when the WAL segment entered the logical replication slot
  17. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Consuming the Plugin Output • All data from the plugin will remain in the slot until consumed • Data can be consumed in two ways – SQL interface • Useful for experimentation and demonstration • Can be used to form a basic programmatic integration with non-C applications (e.g. Java using JDBC) – pg_recvlogical • Similar to pg_receivexlog • Can be used to output to a file for later consumption
  18. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 The Output from our Example pgday15=# SELECT * FROM pg_logical_slot_get_changes('pgday_slot', NULL, NULL); location | xid | data -----------+-----+---------------------------------- 0/16C9ED0 | 723 | BEGIN 723 0/16C9ED0 | 723 | table public.sample: INSERT: id[integer]:1 data[json]:'{"beverage":"coffee","temp":96}' captured[timestamp without tz]:'2015-07-03 19:52:39.497161' 0/16CA000 | 723 | COMMIT 723 (3 rows)
  19. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 pg_recvlogical • Use pg_recvlogical to record to a single file – -f option to specify output file – -F to specify frequency of fsync in seconds • Run pg_recvlogical on a separate server – -h to specify hostname or ip – -p port • Can also create/delete slots
  20. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Some simple LD Use Cases • Trigger-less auditing – Keep a record of all changes in transaction order – Change only query log • Trigger auditing • Basic throughput analysis
  21. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Limitations of test_decoding • Only details the new/changed row • No detail of a WHERE clause • No timestamps in output to help cross reference • Changed indexes don't make for easy reading table public.sample: UPDATE: old-key: id[integer]:3 new-tuple: id[integer]:4 data[json]:'{"beverage":"coffee","temp":96}' captured[timestamp without time zone]:'2015-07-07 00:07:56.305964'
  22. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Enhancing test_decoding • Plugins can use most of backend infrastructure – Can use output functions – Read only access to relations – Can not use anything that requires a transaction • Information logged to WAL can be tuned with REPLICA IDENTITY – Can be adjusted per table with ALTER TABLE – DEFAULT keeps old value of PRIMARY KEY – USING INDEX will keep values of columns covered
  23. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Example: decoder_raw • Enhanced version of test_decoding by Michael Paquier • Appends WHERE clauses based on data derived from REPLICA IDENTITY – If DEFAULT, PRIMARY KEY column(s) appear in WHERE • Removes the commit number from BEGIN and COMMIT • Available on Git Hub https://github.com/michaelpq/pg_plugins/tree/master/decoder_raw
  24. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 decoder_raw Output =# SELECT slot_name FROM pg_create_logical_replication_slot('custom_slot', 'decoder_raw'); slot_name -------------- custom_slot (1 row) =# CREATE TABLE aa (a int primary key, b text); CREATE TABLE =# INSERT INTO aa VALUES (1, 'aa'), (2, 'bb'); INSERT 0 2 =# UPDATE aa SET b = 'cc' WHERE a = 1; UPDATE 1 =# DELETE FROM aa WHERE a = 1; DELETE 1
  25. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 decoder_raw Output =# SELECT data FROM pg_logical_slot_peek_changes('custom_slot', NULL, NULL, 'include-transaction', 'on'); data ---------------------------------------------------- BEGIN; COMMIT; BEGIN; INSERT INTO public.aa (a, b) VALUES (1, 'aa'); INSERT INTO public.aa (a, b) VALUES (2, 'bb'); COMMIT; BEGIN; UPDATE public.aa SET a = 1, b = 'cc' WHERE a = 1 ; COMMIT; BEGIN; DELETE FROM public.aa WHERE a = 1 ; COMMIT; (12 rows)
  26. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Partial Replication • Can be achieved in two ways – Plugin can filter out unneeded tables – Slot consumer can discard unneeded tables • Useful if you have write notes independent of a cluster • Streaming partial replication is possible – If output of plugin is well structured pg_recvlogical could be piped straight into a psql client
  27. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Full Statement Replication • Changes can be streamed to other DBMS platforms – Allows replication to non-PostgreSQL servers – Useful for keeping a backup during a migration to PostgreSQL • Differs from most versions of statement replication – The master computes the statement and determines the changes – Values of columns have already been computed so will be identical (e.g. CURRENT_TIMESTAMP)
  28. Fun Things to do with Logical Decoding Mike Fowler, mike@mlfowler.com

    mike.fowler@rantandrave.com PGDayUK 2015 Conclusion • Logical Decoding exposes meta-data about committed transactions • Plugins can be written to enrich and format the meta-data for placement in replication slots • Data in replication slots can be consumed by external programs using SQL or pg_recvlogical to perform a number of useful tasks
  29. Migrating Rant & Rave to PostgreSQL Mike Fowler, mike@mlfowler.com mike.fowler@rantandrave.com

    PGDayUK 2014 Questions? Mike Fowler PGDay UK 2015
  30. Migrating Rant & Rave to PostgreSQL Mike Fowler, mike@mlfowler.com mike.fowler@rantandrave.com

    PGDayUK 2014 Thank you! Mike Fowler PGDay UK 2015