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).
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 = 220.127.116.11 user = rep password = Password port = 5433’ PUBLICATION publi_partners;
Once executed, enter the command:
to verify that the replication has occurred.
Each time the master table is updated, the changes will be reflected on the slave table.