PGCon2014 - Final Release
PGCon 2014
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Andres Freund |
| Schedule | |
|---|---|
| Day | Talks - Day 2 - Fri May 23 - 2014-05-23 |
| Room | Morisset 221 |
| Start time | 16:15 |
| Duration | 00:45 |
| Info | |
| ID | 722 |
| Event type | Lecture |
| Track | Hacking |
| Language used for presentation | English |
SELECT * FROM changes; -- Changeset Extraction
9.4 saw a great deal of development around a feature (now) called changeset extraction. This talk will explain what the feature does, which areas of use we see, what the state of the feature in 9.4 is and which additional features around it we want to see in future releases of postgres.
Usecases for the changeset extraction feature are:
- Replication Solutions
- Auditing
- Cache Invalidation
- Federation
- ...
Changeset extraction is the ability to extract a consistent stream of changes in the order they happened - which is very useful for replication, auditing among other things. But since that's a fairly abstract explanation, how about a short example?
-- create a new changestream
postgres=# SELECT * FROM create_decoding_replication_slot('slot', 'test_decoding');
slotname | xlog_position
----------+---------------
slot | 0/477D2398
(1 row)
-- perform some DML
postgres=# INSERT INTO replication_example(data) VALUES('somedata');
INSERT 0 1
-- and now, display all the changes
postgres=# SELECT * FROM decoding_slot_get_changes('slot', 'now', 'include-timestamp', 'yes');
location | xid | data
------------+---------+---------------------------------------------------------------------
0/477D2510 | 1484040 | BEGIN 1484040
0/477D2628 | 1484040 | table "replication_example": INSERT: id[int4]:1 data[text]:somedata
0/477D2628 | 1484040 | COMMIT 1484040 (at 2014-01-20 01:18:49.901553+01)
(3 rows)
All this works with a low overhead and a configurable output format.