The Easy Way to Setup PostgreSQL 10 Logical Replication

概述按：转载一篇介绍PostgreSQL 10.0的逻辑复制特性使用方法的文章，逻辑复制特性的实现机制稍后有时间分析。 原文地址：https://www.openscg.com/2017/06/the-easy-way-to-setup-postgresql-10-logical-replication/ One of the most exciting enhancements that comes

按：转载一篇介绍Postgresql 10.0的逻辑复制特性使用方法的文章，逻辑复制特性的实现机制稍后有时间分析。

原文地址：https://www.openscg.com/2017/06/the-easy-way-to-setup-postgresql-10-logical-replication/

One of the most exciting enhancements that comes with the new Postgresql 10 release,is Logical Replication. The functionality,although not yet as extensive aspgLogicalwhich it is based on,provIDes a powerful replication option when you want control over table backups at a finite level – allowing all kinds of ETL goodness like:

replicating only certain tables,columns,or rows consolIDating multiple databases into a single one sharing a subset of the database between multiple databases replicating between different major versions of Postgresql Want to try it without the fuss? Installing multiple Postgresql 10 sandBoxes on your localhost in 4 easy steps

You want to try it but can’t face installation hell? TheBigSQL distributionhas your back. We make it easy.

Make 2 directorIEs:

# the logical replication publisher mkdir pub_test # the logical replication subscriber mkdir sub_test

Navigate to your pub_test directory:

cd pub_test

Install the a postgresql 10 instance (windows users don’t prefix the pgc command with ./ as shown in the following commands):

# use this for MAC / linux: python -c "$(curl -fsSL http://s3.amazonaws.com/pgcentral/install.py)" # use this for windows: @powershell -noprofileExecutionPolicy unrestricted Command "IEx ((new-object net.webclIEnt).DownloadString('http://s3.amazonaws.com/pgcentral/install.ps1'))"# run pgc commands to install pg10 cd bigsql ./pgc install pg10 pgc start pg10 # make sure the instance is runningpgc status

Repeat all of the commands in step 3 after navigating to your sub_test directory:

cd sub_test

Take note that: The first installation,pub_test,will default to port 5432. The second installation,sub_test,will default to port 5433. Edit postgresql.conf

For logical replication to work,you will first need to change the configuration parameterwal_levelto ‘logical’.

So,you will need to edit the file postgresql.conf forBOTHof your Postgresql (pub_test AND sub_test). Navigate to the directory where postgresql.conf is located:

cd {pub_test,sub_test}/bigsql/datapg10

Open the file with your favorite editor and changewal_levelfrom ‘hot_standby’ to ‘logical’

#------------------------------------------------------------------------------# WRITE Ahead LOG# - Settings - wal_level = logical # (change requires restart)

Navigate to the bigsql directory and restart pg10:

cd ../..pgc restart pg10

Exercise: Rock Legends Database

For this exercise,we will create a rock legends database and setup logical replication on the rocker girls table.

Create the publisher

Connect as a superuser to the Postgresql instance that will serve as the publisher:

#publisher instance in running on port 5432 - the default  psql h localhost p 5432U postgres

Create rock_legends_db database:

postgres=# CREATE DATABASE rock_legends_db; postgres \c rock_legends_db You are Now connected to database "rock_legends_db" as user "postgres". 

Create table rocker_girls:

CREATE table rocker_girls ( name @H_604_301@text PRIMARY KEY band @H_604_301@text NOT NulL record_creation_date timestamptz NOT NulL DEFAulT Now());

Add record to rocker_girls table:

INSERT INTO rocker_girlsname band) VALUES('DebbIE Harry''BlondIE');

Check to see that rocker_girls table with data was created:

rock_legends_db select *from rocker_girls  name | band  record_creation_date ------------------------------------------------DebbIEHarryBlondIE2017-053012:5212.17787304(1 row)

Setup the database as the publisher for the rocker_girls table:

CREATE PUBliCATION pub_rock FOR table rocker_girls;

Exit psql:

=# \q

Create the subscriber

Connect as a superuser to the Postgresql instance that will serve as the subscriber:

#subscriber instance in running on port 5433 - the default 5433U postgres

Repeat steps 2 – 3 from above.DO NOTadd any records to the table!

Setup the database as the subscriber:

CREATE SUBSCRIPTION sub_rock CONNECTION 'host=localhost port=5432 dbname=rock_legends_db' PUBliCATION pub_rock;

Verify that the rocker_girls “DebbIE Harry” row was replicated:

)

DebbIE Harry has been replicated!

Test the replication by adding another record

Connect to the publisher:

psql U postgres d rock_legends_db

Add a new record:

'Janis Joplin''Big brother and the Holding Company');

disconnect from publisher and connect to subscriber:

 \q psql 5433d rock_legends_db

Run select all on rocker_girls table:

 name -----------------------------------JanisJoplinBigbrotherand the HoldingCompany31082257.7083332 rows)

Janis has been replicated!

What’s next

In upcoming posts,I’ll show more ways to manage your replications. In the meantime,you can continue to test in your sandBoxes. And in case you need to blow away your logical replication …

How to delete the subscriber and publisher

 drop subscription testsub NOTICE: dropped replication slot  on publisher DROP SUBSCRIPTION rock_legends_db drop publication alltables DROP PUBliCATION

By Holly Orr | June 7th,2017 总结

以上是内存溢出为你收集整理的The Easy Way to Setup PostgreSQL 10 Logical Replication全部内容，希望文章能够帮你解决The Easy Way to Setup PostgreSQL 10 Logical Replication所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。