2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Francesco's PlanetPostgreSQL3 / PostgreSQL 9.4 for administrators (part one)
2ndQuadrant Press

PostgreSQL 9.4 for administrators (part one)

November 13, 2014/1 Comment/in Francesco's PlanetPostgreSQL /by 2ndQuadrant Press

 

 

2 elephants

 

Version 9.4 of PostgreSQL, soon to be released, has many innovations for administrators, including the introduction of support for logical replication, which is the first step towards the integration of multi-master replication into core PostgreSQL. In this two-part article we will show you the main new features for administrators; we begin with logical replication, and describe the following concepts:

  • Physical replication slots
  • WAL level “logical”
  • Logical replication slots
  • Logical decoding
  • Replica identity

The development of these features is a direct result of the work carried out by 2ndQuadrant, in particular by Andres Freund, the main developer of Bi-Directional Replication (BDR). BDR is an open source solution of multi-master replication based on PostgreSQL, whose code is being progressively included in the core of PostgreSQL with the aim of becoming an integral part in the coming years.

In the next article we will talk about other new features dedicated to administrators.

Physical replication slots

Physical replication slots are a data-structure that keep track of the state of the standby and the WALs it needs, even when the standby is offline. In this way it is no longer necessary to estimate wal_keep_segments or have to configure continuous archiving. Therefore they are not only useful for physical replication, but are essential for logical replication where only a subset of the data are being replicated between servers.

More information can be found in an article by Craig Ringer Replication slots of PostgreSQL 9.4. – https://www.2ndquadrant.com/postgresql-9-4-slots/

Wal level “logical”

The wal_level configuration parameter can now be set to the value “logical”. Using this setting, the WAL files will have a size slightly larger than they have now with the value “hot_standby”, but will contain the information required to run logical decoding.

Logical replication slots

Once you have set wal_level = logical in the postgresql.conf, you can start using logical replication slots. These are similar in concept but, unlike the physical replication slots, these operate on a single data base, and stream changes to the replication server.

Logical decoding

Logical decoding uses the replication slots and decoding plugins to send the changes in the db and make them understandable to external agents. To see the changes, one can use the pg_logical_slot_get_changes and pg_logical_slot_peek_changes functions. The difference between the two functions is that the first consumes changes in the queue and the second reads them only, without removing them. The output of the function depends on the plugin used to create the slot. Three plugins were developed:

  • test_decoding – the default plugin;
  • wal2json – shows the changes in JSON format ;
  • decoder_raw – reconstructs the query that has applied the change.

Replica identity

REPLICA IDENTITY is a new table-level parameter that can be used to control the information written to WAL to identify tuple in which data has changed.  There are 4 possible values:

  • DEFAULT:  writes the old value of the primary key if it has been changed.
  • USING INDEX: writes the values of the index defined with this option. The index needs to be unique, not partial and not deferrable, and its columns must be NOT NULL.
  • FULL: all the column values are written in the WALs – useful if there is no primary key on the table.
  • NOTHING: does not write information on the old record. This is the default for the system tables.

Using the functions and the decoding plugins, you can write your own consumers and remove your database dependencies on trigger-based replication solutions bulkier and slower.

The blog of Michael Paquier contains an example of SQL code showing the use of logical replication.

Conclusion

In the next instalment, we will take a look at the other main new PostgreSQL 9.4 features in the field of Operations, including pg_prewarm , easier tablespace management, time delayed standbys, WAL management … See you next time!

 

Tags: 9.4 logical decoding, BDR, logical replication, operations, postgres, Postgres-BDR, PostgreSQL, replica identity, replication slots
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply

Trackbacks & Pingbacks

  1. PostgreSQL 9.4 for administrators (part two) | 2ndQuadrant says:
    December 4, 2014 at 12:17 pm

    […] the previous instalment, we introduced the logical replication feature which has been added to PostgreSQL 9.4. Let’s […]

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
… and now for something completely different! Italian PGDay, eighth edition: over 120 attendees!
Scroll to top
×