{"id":5417,"date":"2018-06-01T23:57:36","date_gmt":"2018-06-01T21:57:36","guid":{"rendered":"http:\/\/www.sigterritoires.fr\/?p=5417"},"modified":"2018-10-17T12:50:12","modified_gmt":"2018-10-17T10:50:12","slug":"replicating-a-postgresql-database-in-a-windows-workstation","status":"publish","type":"post","link":"https:\/\/www.sigterritoires.fr\/index.php\/en\/replicating-a-postgresql-database-in-a-windows-workstation\/","title":{"rendered":"Replicating a postgresql database in a windows workstation"},"content":{"rendered":"

We will configure a\u00a0logic<\/em>\u00a0\u00a0\u00a0replication of a database table of our linux server in a postgresql installation in a windows workstation.<\/p>\n

The replication allows us to keep an updated copy of the centralized database in a remote station.\u00a0This copy will not be used for local updates since it will be read-only.\u00a0On the other hand, it allows us to work offline and much faster, since the exchanges between the central database and the local replicate contain only the updates of the centralized database.<\/p>\n

For our example, we will use a table called\u00a0\u00a0\u00a0partner_institutions<\/em><\/strong>\u00a0, located in a named database\u00a0\u00a0\u00a0postgres<\/em><\/strong>\u00a0\u00a0<\/em><\/strong>\u00a0in a server.<\/p>\n

Setting up the replication in the server base (master)<\/strong><\/h2>\n
ACTIVATION OF THE LOGIC REPLICATION OF POSTGRESQL 10<\/strong><\/h4>\n
Firstly, we need to change the wal level parameter of the\u00a0\u00a0postgresql.conf file, in the server, adding the value\u00a0\u00a0\u00a0logical<\/em><\/strong>\u00a0.<\/p>\n
$\"\"$ <\/p>\n
Then, you must restart postgresql so the change can be implemented.<\/p>\n
To verify that the modification has been achieved execute the command using pgAdmin (or the online command).<\/p>\n
show wal_level;<\/em><\/strong><\/p>\n
$\"\"$ <\/p>\n
HOW TO CREATE A USER WITH REPLICATION PRIVILEGES<\/strong><\/h4>\n
To create a user, called\u00a0\u00a0\u00a0rep<\/em><\/strong>\u00a0\u00a0\u00a0with replication privileges, run the command<\/p>\n
CREATE ROLE rep REPLICATION LOGIN PASSWORD ‘Password’;<\/em><\/strong><\/p>\n
$\"\"$ <\/p>\n
and to give the new user rights on the table institutions_partners, enter the command:<\/p>\n
GRANT ALL ON partner_institutions TO rep;<\/em><\/strong><\/p>\n
$\"\"$ <\/p>\n
HOW TO CREATE A PUBLICATION OF THE TABLE<\/strong><\/h4>\n
To create the publication of the table, enter the following command:<\/p>\n
CREATE PUBLICATION publi_partners FOR TABLE partner_institutions;<\/em><\/strong><\/p>\n
$\"\"$ <\/p>\n
To finish the implementation on the server side, you must allow the user rep to connect to the postgresql database:<\/p>\n
Add the line<\/p>\n
# TYPE\u00a0\u00a0\u00a0\u00a0\u00a0DATABASE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0USER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0CIDR-ADDRESS\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0METHOD
\nHost\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0rep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00.0.0.0\/0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0md5<\/p>\n
In the pg_hba.conf file.<\/p>\n
And restart postgresql in the server so the change can be implemented.<\/p>\n
How to Setup the replication on the remote station (slave)<\/strong><\/h2>\n
CREATING THE TABLE IN THE REMOTE POST<\/strong><\/h4>\n
First, you have to create the institution partners table in the base of the remote station.<\/p>\n
One way, among others, is to copy the sql definition from the server table and execute it on the remote machine.<\/p>\n
$\"\"$ <\/p>\n
<\/p>\n
Do not forget that before running the sql command on the remote station, you have \u00a0to remove the last line<\/p>\n
GRANT ALL ON TABLE public.institutions_partners TO rep;<\/em><\/strong><\/p>\n
because this user does not exist in the remote machine.<\/p>\n
However, do not forget to make a GRANT\u00a0\u00a0\u00a0for the users of the table in the remote machine so that they can connect to the table.\u00a0Then the users will be able to use the data contained in the table, but they will not be able to modify them.\u00a0Only the update, inserts and deletes performed in the master table will automatically appear in the slave table.<\/p>\n
$\"\"$ <\/p>\n
HOW TO CREATE A SUBSCRIPTION <\/strong><\/h4>\n
To subscribe the newly created table to the publication made in the server, enter the following command:<\/p>\n
CREATE SUBSCRIPTION subscri_partners CONNECTION ‘dbname = postgres host = 195.83.124.222 user = rep password = Password port = 5433’ PUBLICATION publi_partners;<\/em><\/strong><\/p>\n
$\"\"$ <\/p>\n
Once executed, enter the command:<\/p>\n
table partner_institutions;<\/em><\/strong><\/p>\n
to verify that the replication has occurred.<\/p>\n
$\"\"$ <\/p>\n
Each time the master table is updated, the changes will be reflected on the slave table.<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
We will configure a\u00a0logic\u00a0\u00a0\u00a0replication of a database table of our linux server in a postgresql installation in a windows workstation. The replication allows us to keep an updated copy of the centralized database in a remote…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"give_campaign_id":0,"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"sfsi_plus_gutenberg_text_before_share":"","sfsi_plus_gutenberg_show_text_before_share":"","sfsi_plus_gutenberg_icon_type":"","sfsi_plus_gutenberg_icon_alignemt":"","sfsi_plus_gutenburg_max_per_row":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[1260,1338],"tags":[1351,1429],"class_list":["post-5417","post","type-post","status-publish","format-standard","hentry","category-non-classe-en","category-postgresql-en","tag-postgresql-en","tag-replication-en"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p6XU0A-1pn","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/5417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/comments?post=5417"}],"version-history":[{"count":0,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/posts\/5417\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/media?parent=5417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/categories?post=5417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sigterritoires.fr\/index.php\/wp-json\/wp\/v2\/tags?post=5417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}