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 / OmniDB3 / PostgreSQL 10 Logical Replication with OmniDB
William Ivanski

PostgreSQL 10 Logical Replication with OmniDB

September 19, 2017/0 Comments/in OmniDB, William's PlanetPostgreSQL /by William Ivanski

1. Introduction

Logical replication uses a publish/subscribe model and so we create publications on the upstream (or publisher) and subscriptions on downstream (or subscriber). For more details about it, please refer to this blog post from my colleague Petr Jelinek, and also to the PostgreSQL documentation.

Here we will show how to build a test environment to play with this new feature from PostgreSQL 10, and how to configure it using OmniDB 2.1.

2. Building test environment

Let’s build a 2-node test environment to illustrate how to configure PG10 logical replication feature within OmniDB.

2.1. Pull OmniDB repo

The first thing you need to do is to download OmniDB in the repo from GitHub and make sure you are in the development branch. Run the following:

2.2. Create 2 virtual machines with PostgreSQL 10

On your host machine, you need to have installed:

  • VirtualBox
  • Vagrant
  • Vagrant plugin vbguest

Please refer to the VirtualBox and Vagrant websites for more information.

For this test environment to work, you need to put both machines in the same internal network. If necessary, please edit the file Vagrantfile for both machines like this:

config.vm.network "private_network", ip: '192.168.56.101', :name => 'vboxnet0', :adapter => 2
Now, on your terminal (assuming you are on the root directory of OmniDB repo), to create the first virtual machine with PostgreSQL 10, you need to do:
cd OmniDB/OmniDB_app/tests/vagrant/multinode/pg10_node1
vagrant up
While inside this directory, you can connect via SSH to the machine with vagrant ssh. User vagrant has root access with sudo su. Now let’s create the second virtual machine with PostgreSQL 10:
cd ../pg10_node2
vagrant up
You can connect to this machine via SSH the same way. Also, port 5432 of the fist machine is mapped to the port 5401 of the host machine, and port 5432 of the second machine is mapped to the port 5402 of the host machine. From the host machine, you can connect to both virtual machines through OmniDB, using the user omnidb.

3. Configuring logical replication

3.1. Set PostgreSQL parameter “wal_level”

You need to connect to each machine and set wal_level = logical. For the first machine, assuming you are on the root folder of OmniDB:

[email protected]:~$ cd OmniDB/OmniDB_app/tests/vagrant/multinode/pg10_node1
[email protected]:~$ vagrant ssh
[email protected]:~$ sudo su
[email protected]:/home/vagrant# echo "wal_level = logical" >> /etc/postgresql/10/main/postgresql.conf
[email protected]:/home/vagrant# systemctl restart postgresql
Now do the same for the other machine.

3.2. Use OmniDB to connect to both machines

Assuming you have OmniDB (server or app) installed in your host machine, you can connect to both databases in order to test the connection. In OmniDB, create 2 connections like this:

Then click on the Test Connection action on each connection. You will have to type the password of the user omnidb, which is also omnidb.

3.3. Create a test table on both machines

Connect to both databases and create a test table, like this:

CREATE TABLE customers (
 login text PRIMARY KEY,
 full_name text NOT NULL,
 registration_date timestamptz NOT NULL DEFAULT now()
)
Using OmniDB, it would look like this:

3.4. Create a publication on the first machine

Inside the connection node, expand the Logical Replication node, then right click in the Publications node, and choose the action Create Publication. OmniDB will open an SQL template tab with the CREATE PUBLICATION command ready for you to make some adjustments and run:

After adjusting and executing the command, you can right click the Publications node again and click on the Refresh action. You will see that will be created a new node with the same name you gave to the publication. Expanding this node, you will see the details and the tables for the publication:

3.5. Create a subscription on the second machine

Inside the connection node, expand the Logical Replication node, then right click in the Subscriptions node, and choose the action Create Subscription. OmniDB will open a SQL template tab with the CREATE SUBSCRIPTION command ready for you to make some adjustments and run:

After adjusting and executing the command, you can right click the Subscriptions node again and click on the Refresh action. You will see that will be created a new node with the same name you gave to the subscription. Expanding this node, you will see the details, the referenced publications and the tables for the subscription:

Also, the CREATE SUBSCRIPTION command created a logical replication slot called testsub (the same name as the subscription) in the first machine:

4. Testing the logical replication

So far we’ve created:

  • Table public.customers on both nodes 1 and 2
  • Publication testpub for table public.customers on node 1
  • Subscription testsub on node 2, referencing publication testpub

To test the replication is working, let’s create some data on the node 1. Right click on the table public.customers, then point to Data Actions, then click on the action Edit Data. In this grid, you are able to add, edit and remove data from the table. Add 2 sample rows, like this:

Then, on the other node, check if the table public.customers was automatically populated. Right click on the table public.customers, then point to Data Actions, then click on the action Query Data:

As we can see, both rows created in the first machine were replicated into the second machine. This tells us that the logical replication is working. And it was configured using the OmniDB web interface.

4. More features

Right clicking on the publication or the subscription, which shows Alter and Drop actions, you can use to change and remove publications and subscriptions.

You can also add and remove tables from a publication:

Tags: 2QLovesPG, logical replication, OmniDB, PostgreSQL, PostgreSQL10
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

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
The Barman is ready for PostgreSQL 10 Setting up a build machine for Visual Studio 2017
Scroll to top
×