Blog GIS & Territories

We will configure a logic   replication 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 station. This copy will not be used for local updates since it will be read-only. On 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.

For our example, we will use a table called   partner_institutions , located in a named database   postgres   in a server.

Setting up the replication in the server base (master)

ACTIVATION OF THE LOGIC REPLICATION OF POSTGRESQL 10

Firstly, we need to change the wal level parameter of the  postgresql.conf file, in the server, adding the value   logical .

Then, you must restart postgresql so the change can be implemented.

To verify that the modification has been achieved execute the command using pgAdmin (or the online command).

show wal_level;

HOW TO CREATE A USER WITH REPLICATION PRIVILEGES

To create a user, called   rep   with replication privileges, run the command

CREATE ROLE rep REPLICATION LOGIN PASSWORD ‘Password’;

and to give the new user rights on the table institutions_partners, enter the command:

GRANT ALL ON partner_institutions TO rep;

HOW TO CREATE A PUBLICATION OF THE TABLE

To create the publication of the table, enter the following command:

CREATE PUBLICATION publi_partners FOR TABLE partner_institutions;

To finish the implementation on the server side, you must allow the user rep to connect to the postgresql database:

Add the line

# TYPE     DATABASE        USER              CIDR-ADDRESS        METHOD
Host            postgres              rep                        0.0.0.0/0                  md5

In the pg_hba.conf file.

And restart postgresql in the server so the change can be implemented.

How to Setup the replication on the remote station (slave)

CREATING THE TABLE IN THE REMOTE POST

First, you have to create the institution partners table in the base of the remote station.

One way, among others, is to copy the sql definition from the server table and execute it on the remote machine.

Do not forget that before running the sql command on the remote station, you have  to remove the last line

GRANT ALL ON TABLE public.institutions_partners TO rep;

because this user does not exist in the remote machine.

However, do not forget to make a GRANT   for the users of the table in the remote machine so that they can connect to the table. Then the users will be able to use the data contained in the table, but they will not be able to modify them. Only the update, inserts and deletes performed in the master table will automatically appear in the slave table.

HOW TO CREATE A SUBSCRIPTION

To subscribe the newly created table to the publication made in the server, enter the following command:

CREATE SUBSCRIPTION subscri_partners CONNECTION ‘dbname = postgres host = 195.83.124.222 user = rep password = Password port = 5433’ PUBLICATION publi_partners;

Once executed, enter the command:

table partner_institutions;

to verify that the replication has occurred.

Each time the master table is updated, the changes will be reflected on the slave table.

Si cet article vous a intéressé et que vous pensez qu'il pourrait bénéficier à d'autres personnes, n'hésitez pas à le partager sur vos réseaux sociaux en utilisant les boutons ci-dessous. Votre partage est apprécié !

Atilio Francois