wiki:replicating

Version 4 (modified by niles, 6 years ago) (diff)

--

Postgres 10 Logical Replication

See also https://blog.2ndquadrant.com/logical-replication-postgresql-10/ although I'm not sure how long that will hang around, the internet being what it is.

These are my notes on getting logical replication going in postgres between a "master" and a "slave" node. Both systems should have port 5432 (the default postgres port) open to each other. On CentOS 7, this required the following commands as root :

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

And do this to be sure port 5432 is listed as being open :

# firewall-cmd --list-ports

There may be other firewall issues to address depending on your setup.

PART 1 : THINGS TO DO ON BOTH NODES

Install PG 10 or greater, so as root :

# yum install postgresql10-server postgresql10-contrib postgresql10-devel

Strictly speaking you don't need postgresql10-devel, but I use it for other stuff so I grab it.

Then init the database and make postgres run by default :

# /usr/pgsql-10/bin/postgresql-10-setup initdb
# systemctl start postgresql-10
# systemctl enable postgresql-10

Users using postgres 10 would be wise to add the PG10 binary directory to the start of their path, so add this to $HOME/.bashrc :

PATH=/usr/pgsql-10/bin:$PATH; export PATH

You should then be able to become the linux user 'postgres' :

# su - postgres

And run 'psql', set the superuser password and exit the database :

postgres=# ALTER USER postgres WITH PASSWORD 'bigBadTruck';
postgres=# \q

Then as 'postgres' edit the file $HOME/10/data/postgresql.conf and change the line :

#wal_level = replica

To :

wal_level = logical

Still as postgres, edit the pg_hba.conf file. It will be named something like 10/data/pg_hba.conf. If the IP addresses of the two machines are 100.37.24.123 and 100.37.24.122, I set it up to look like this on both machines :

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             127.0.0.1/32            md5
host    all             all             localhost               md5
host    all             all             localhost               ident
host    bladerunner     deckard         100.37.24.123/32         md5
host    bladerunner     deckard         100.37.24.122/32         md5
host    bladerunner     postgres        100.37.24.123/32         md5
host    bladerunner     postgres        100.37.24.122/32         md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
hostnossl    replication     all             100.37.24.122/32         md5
hostnossl    replication     all             100.37.24.123/32         md5
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

'deckard' and 'bladerunner' are a user and a database we'll set up in a bit.

That is almost certainly overkill as it allows full communication both ways, but that may be good for testing here. It is probably worth looking into using 'host' rather than 'hostnossl', too, which I *think* needs encryption.

And then restart the database with :

/usr/pgsql-10/bin/pg_ctl restart -D /var/lib/pgsql/10/data

There will likely be more database restarts required, it may be worth putting that restart command in a script in the home directory for user postgres.

You should then be able to revert to being logged in as whoever you are (the default for me being 'noien') and edit the file $HOME/.pgpass to look like :

$ cat $HOME/.pgpass
localhost:*:*:postgres:bigBadTruck

Set protections on that file appropriately :

$ chmod 600 $HOME/.pgpass

You should then be able, as yourself, to access the database with :

$ psql -Upostgres

Check that you can, and once at the database prompt, create a user with replication :

CREATE USER deckard WITH PASSWORD 'origami';
CREATE DATABASE bladerunner;
GRANT ALL PRIVILEGES ON DATABASE bladerunner TO deckard;
ALTER USER deckard WITH REPLICATION;

Exit the database prompt and add this user to $HOME/.pgpass so that the file looks like :

$ cat $HOME/.pgpass
localhost:*:*:postgres:bigBadTruck
localhost:*:*:deckard:origami

You should then be able to get to the database prompt as user deckard using database bladerunner :

$ psql -Udeckard bladerunner 

At that prompt, create a simple table :

DROP TABLE IF EXISTS expireTimes;
CREATE TABLE expireTimes ( retirement VARCHAR(255) );

You need to do this on both master and slave nodes, replication will not make the table for you on the slave node.

Then check connectivity between the two machines. On both machines, add the users for the database on the other machine to the $HOME/.pgpass file, like so :

$ cat $HOME/.pgpass
localhost:*:*:postgres:bigBadTruck
localhost:*:*:deckard:origami
the.other.machine.name:5432:bladerunner:deckard:origami
the.other.machine.name:5432:*:postgres:bigBadTruck

You should then, on both machines, be able to access the other machine both as the postgres user and the deckard user, using the bladerunner database, like so :

psql -h the.other.machine.name -U postgres bladerunner
psql -h the.other.machine.name -U deckard bladerunner

Making sure those commands work at the linux prompt ensures that the connectivity between the databases on the two machines is OK.

So, you now have postgres 10 running on two systems. Both of them have a user named deckard, with a database named bladerunner, and in that database is an empty table named expireTimes. Connectivity between the two machines is OK.

PART 2 : SETUP ON THE MASTER NODE

In order to get some data flowing through the expireTimes table on the master node, write a script called "doInsert.sh" that looks like this :

#!/bin/bash
t=`date +"%Y/%m/%d %H:%M:%S %B %A %d"`
echo INSERT INTO expireTimes VALUES \(\'$t\'\)\; | psql -Udeckard bladerunner
exit 0

Then set executable status on that file :

$ chmod +x doInsert.sh 

And similarly write a script called "doDelete.sh" that looks like :

#!/bin/bash
sleep 30;
t=`date --date="1 hour ago" +"%Y/%m/%d %H:%M:%S %B %A %d"`
echo DELETE FROM expireTimes WHERE retirement \< \'$t\'\; | psql -Udeckard bladerunner
exit 0

And again set executable status on the file. The first script will insert the current time (in a somewhat screwball format) into the database, the second script will delete times that are older than an hour.

Run them both in cron, running the insert one every minute and the delete one every ten minutes, like so :

* * * * * /home/noien/replication/setup/doInsert.sh &> /home/noien/replication/setup/doInsert.log
*/10 * * * * /home/noien/replication/setup/doDelete.sh &> /home/noien/replication/setup/doDelete.log

Check that the times are going into the table :

$ psql -Udeckard bladerunner
psql (10.3)
Type "help" for help.

bladerunner=> select retirement from expireTimes order by retirement;
             retirement              
-------------------------------------
 2018/04/13 14:51:01 April Friday 13
 2018/04/13 14:52:01 April Friday 13
 2018/04/13 14:53:01 April Friday 13
 2018/04/13 14:54:01 April Friday 13
 2018/04/13 14:55:01 April Friday 13

After a while, there should always be between 60 and 70 entries in the table :

bladerunner=> select count(*) from expireTimes;
 count 
-------
    60

So, this is the little test table that we are going to replicate on the slave node. We have to "publish" this table on the master node.a Still in the database :

bladerunner=> CREATE PUBLICATION harrison FOR TABLE expireTimes; 

Interestingly, after publishing the delete script will not be able to delete entries from the table. Attempts to delete will result in an error :

ERROR:  cannot delete from table "expiretimes" because it does not have a replica identity and publishes deletes
HINT:  To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.

So to follow that hint, do the following :

bladerunner=> ALTER TABLE expireTimes REPLICA IDENTITY FULL; 

After which deletes can happen again. I'm not sure that FULL is the optimal setting for all cases, more reading may be required.

We should now be able to see the publication on the master node :

bladerunner=> \dRp+
                Publication harrison
  Owner  | All tables | Inserts | Updates | Deletes 
---------+------------+---------+---------+---------
 deckard | f          | t       | t       | t
Tables:
    "public.expiretimes"

PART 3 : SETUP ON THE SLAVE NODE

Subscriptions must be done by the database super user, postgres. So log in to the slave machine and go to the bladerunner database as postgres with :

 $ psql -Upostgres bladerunner 

Then create a subscription that connects to the publication we have on the master machine :

 bladerunner=# CREATE SUBSCRIPTION ford CONNECTION 'host=the.master.machine.name dbname=bladerunner user=deckard password=origami' PUBLICATION harrison; 

Note that you do need to specify the password, the .pgpass file does not affect the CREATE SUBSCRIPTION command. I'm not sure what would happen if the password on the master node were to change.

You should then be able to see the subscription :

bladerunner=# \dRs+
                                                          List of subscriptions
 Name |  Owner   | Enabled | Publication | Synchronous commit |                                 Conninfo                                  
------+----------+---------+-------------+--------------------+---------------------------------------------------------------------------
 ford | postgres | t       | {harrison}  | off                | host=the.master.machine.name dbname=bladerunner user=deckard password=origami
(1 row)

And monitor it in more depth with the pg_stat_subscription table :

bladerunner=# SELECT * FROM pg_stat_subscription;
 subid | subname |  pid  | relid | received_lsn |      last_msg_send_time       |     last_msg_receipt_time     | latest_end_lsn |        latest_end_time        
-------+---------+-------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
 16413 | ford    | 22102 |       | 0/199F168    | 2018-04-13 16:57:05.488469-06 | 2018-04-13 16:56:43.719121-06 | 0/199F168      | 2018-04-13 16:57:05.488469-06

Similarly on the master machine you should be able to access the database (this time there's no need to be the postgres user) with :

 $ psql -Udeckard bladerunner 

And then monitor the other end by looking at the pg_stat_replication table :

bladerunner=> select pid, usesysid, usename, application_name, client_addr, client_hostname, client_port, backend_start from pg_stat_replication;
  pid  | usesysid | usename | application_name | client_addr   |     client_hostname      | client_port |         backend_start         
-------+----------+---------+------------------+---------------+--------------------------+-------------+-------------------------------
 31018 |    16384 | deckard | ford             | 100.37.24.123 | the.slave.machine.name   |       32826 | 2018-04-13 16:38:52.349168-06

And the two tables on the two machines should then be mirror images of each other, with updates on the master popping up on the slave (also sometimes called the 'standby') machine.

NOTE : If a new table is added to the publication on the master, the slave subscription will not see them by default. In that case you would have to do this on the slave node :

 bladerunner=# ALTER SUBSCRIPTION ford REFRESH PUBLICATION; 

PART 4 : TRIGGERS

If we want to keep track of what was inserted when into our new, replicated table, we might reasonably think that the following trigger setup would do it :

-- Make a table to hold what was inserted into the expireTimes table, and when it was inserted
DROP TABLE insertTimes;
CREATE TABLE insertTimes( insertTime TIMESTAMP, insertValue VARCHAR(255));

-- Make a function that will update the insertTimes table
CREATE OR REPLACE FUNCTION insert_trigger_fc() returns TRIGGER as $insert_trigger$
 BEGIN
 INSERT INTO insertTimes VALUES (LOCALTIMESTAMP, NEW.RETIREMENT);
 DELETE FROM insertTimes WHERE insertTime <  LOCALTIMESTAMP - INTERVAL '1 HOUR';
 RETURN NEW;
END
$insert_trigger$ LANGUAGE plpgsql;

-- Make a trigger that will call the function when expireTimes is updated
DROP TRIGGER IF EXISTS insert_trigger ON expireTimes;
CREATE TRIGGER insert_trigger AFTER INSERT OR UPDATE ON expireTimes
 FOR EACH ROW EXECUTE PROCEDURE insert_trigger_fc();

And that does work on the master node :

bladerunner=> SELECT * FROM  insertTimes;
         inserttime         |             insertvalue             
----------------------------+-------------------------------------
 2018-04-16 11:25:01.16447  | 2018/04/16 11:25:01 April Monday 16
 2018-04-16 11:26:01.849943 | 2018/04/16 11:26:01 April Monday 16

However on the slave node the trigger will not fire by default. You have to either :

ALTER TABLE expireTimes ENABLE REPLICA TRIGGER insert_trigger;

or (and I prefer this, because if I have a trigger I pretty much want it to fire) :

ALTER TABLE expireTimes ENABLE ALWAYS TRIGGER insert_trigger;

Niles Oien April 2018.