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 / 2ndQuadrant3 / PGLogical 1.1 packages for PostgreSQL 9.6beta1
Petr Jelinek

PGLogical 1.1 packages for PostgreSQL 9.6beta1

May 12, 2016/0 Comments/in 2ndQuadrant, Petr's PlanetPostgreSQL, pglogical, PostgreSQL /by Petr Jelinek

We have made pglogical 1.1 packages available for PostgreSQL 9.6beta1 for both rpm and deb based distributions. They are available for install from our standard pglogical package repository.

You may ask why do we release packages for beta version of Postgres? Well, one of the reasons is that you can use pglogical to replicate your existing PostgreSQL 9.5 or 9.4 database to the 9.6beta1 in real-time and run tests on it to help weed out any remaining bugs in the beta release. Here is a quick tutorial on how to do that.

First step is to install the PostgreSQL 9.6beta1, check the release announcement for information about how to do that. Second step is to install pglogical as explained on installation instructions page.

Now for the actual replication setup. It’s fairly easy. Start with making sure that the PostgreSQL is configured to allow the logical replication:

wal_level = 'logical'
max_worker_processes = 10   # one per database needed on provider node
                            # one per node needed on subscriber node
max_replication_slots = 10  # one per node needed on provider node
max_wal_senders = 10        # one per node needed on provider node
shared_preload_libraries = 'pglogical'

Changing the above settings requires restart of the server.

You should also allow incoming replication connections in pg_hba.conf (just like when setting up physical streaming replication). The line in pg_hba.conf should look something like this:

host    replication     postgres        10.0.0.2/32            md5

See the pg_hba documentation page for more info.

Next, install the pglogical extension on provider database and create the pglogical node there:

CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=providerhost port=5432 dbname=db'
);

Then do the same on the new 9.6 database:

CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=subscriberhost port=5432 dbname=db'
);

Note that he connection strings should lead to the database in which you are executing these commands.

Back on the provider database, add the tables you want to replicate to the default replication set. Simple way of doing this is to add all tables in the public schema like this:

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

And finally, again on the subscriber database, create a subscription which connects to the provider and starts replicating.

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=providerhost port=5432 dbname=db',
    synchronize_structure := true
);

Here the connection string should be the same as the one used when creating the provider pglogical node. The synchronize_structure := true means that pglogical will copy all table structures from provider database to the subscriber database (using standard pg_dump).

And that’s it, you now have working replication between your existing PostgreSQL 9.5 or 9.4 database and the new PostgreSQL 9.6beta1.

Check the pglogical documentation and project pages for additional information about the functions used in this post and on how to do more than just simple replication.

Tags: beta, logical replication, packages, pglogical, PostgreSQL, PostgreSQL 9.6, replication, testing
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 *

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
Postgres-XL 9.5r1.1 released Using Java arrays to insert, retrieve, & update PostgreSQL arrays
Scroll to top
×