{"id":244,"date":"2020-12-11T00:05:45","date_gmt":"2020-12-11T00:05:45","guid":{"rendered":"https:\/\/blog.inplico.uk\/?p=244"},"modified":"2024-07-29T18:02:10","modified_gmt":"2024-07-29T17:02:10","slug":"postgresql-replication","status":"publish","type":"post","link":"https:\/\/blog.inplico.uk\/?p=244","title":{"rendered":"Postgresql replication"},"content":{"rendered":"

PREAMBLE<\/span><\/h4>\n

It goes without saying that if you want to set up replication then you need a master and a slave postgresql server.\u00a0 There is a bit of configuration to do in order to get it running.<\/p>\n

This how to was originally written for older versions of Postgresql but has been updated to reflect the significant changes in later versions.\u00a0 It has been tested against version 15.<\/p>\n

WARNING:<\/span> Before continuing with this tutorial you should ensure that you can connect to both servers using SSL as replicating over the internet without ssl is a little bit insane. See here<\/a><\/p>\n

We are using sha-256 encryption for passwords (which is now the default) so the first thing we want to do is edit postgresql.conf on the master and check that password_encryption is set correctly.<\/p>\n

vi \/etc\/postgresql\/15\/main\/postgresql.conf<\/pre>\n
NOTE: The directory path will change depending on the version.<\/p>\n
Look for the “password_encryption” directive and change it to “scram-sha-256”.\u00a0 This line may be commented out, if it is then uncomment it.<\/p>\n
password_encryption = scram-sha-256             # md5 or scram-sha-256<\/pre>\n
If you have already set a password for your replication user that is not sha-256 encoded then you will have to set it again or you will not be able to authenticate.\u00a0 The reason quoted below is explained in a stack overflow question here.<\/a><\/p>\n
Each user password hash is saved in the table pg_authid. It includes the hashing algorithm that is used to transform the password to its hash.\r\n\r\nWhen setting the password_encryption in postgresql.conf, you are setting the default encryption, i.e. the one used when creating a user or when (re)setting your password. The table pg_authid is not updated.\r\n\r\nWhen changing pg_hba.conf, you are saying to accept only passwords hashed using the given method. The table pg_authid is not updated.<\/pre>\n
MASTER<\/span><\/h4>\n
On the master the first thing we need to do is create a user for replication purposes so start a postgresql shell and add a user.<\/p>\n
CREATE ROLE replicate LOGIN REPLICATION ENCRYPTED PASSWORD 'mySecretPassword';<\/pre>\n
While we are in the postgresql shell we also want to create a slot.\u00a0 For some reason we create a slot using a SLEECT statement, not sure why, but it is unimportant in any event.<\/p>\n
SELECT * FROM pg_create_physical_replication_slot('replicate');<\/pre>\n
The slot does not have to have the same name as the user, we just use “replicate<\/strong>” in our example for the sake of simplicity.\u00a0 NOTE:\u00a0 It is not necessary to create a slot as postgresql will create a temporary slot if one isn’t explicitly created.<\/p>\n
Now we can exit our postgresql client shell and move on to editing our pg_hba.conf<\/strong> file, usually located in \/etc\/postgresql\/11\/main<\/strong><\/p>\n
# TYPE DATABASE USER ADDRESS METHOD\r\n...\r\nhostssl replication  replicate    xxx.xxx.xxx.xxx\/xx   scram-sha-256\r\n....<\/pre>\n
IMPORTANT: <\/strong><\/span>Replace xxx.xxx.xxx.xxx\/xx with the ip and subnet mask of the slave.<\/p>\n
The line highlighted above will allow the user replicate to use the built in role ‘replication’ from the slave.\u00a0 The type is set to ‘hostssl<\/strong>‘ to force the use of ssl encryption and ‘scram-sha-256<\/strong>‘ is the method used to decrypt the password.<\/p>\n
Next you need to edit the postgresql.conf file to tell the server that it is acting as a master server.<\/p>\n
#vi \/etc\/postgresql\/11\/main\/postgresql.conf<\/pre>\n
edit the file as follows, uncommenting anything that is commented out.<\/p>\n
listen_addresses = 'localhost,xxx.xxx.xxx.xxx'\r\nwal_level = replica\r\nmax_wal_senders = 10\r\nwal_keep_size = 1024MB\r\n<\/pre>\n
Note that xxx.xxx.xxx.xxx is the address of the interface you want to listen on, you can add several addresses here if you are listening on the WAN port and the LAN port for example, albeit if this is the case you may just wish to use a wildcard instead.<\/p>\n
listen_address = '*'<\/pre>\n
Once you have done that you can restart the master and move on to configuring the slave.<\/p>\n
#service postgresql restart\r\n<\/pre>\n
 <\/p>\n
SLAVE<\/span><\/h3>\n
Before you configure the slave you need to create an ssl certificate using the Certificate Authority that you should have already created on the master (this is a prerequisite).<\/p>\n
Once you have generated your certificate you need to copy the .key<\/strong> and .crt<\/strong> files that you have generated across to the slave along with the postgresCA.crt<\/strong> file.\u00a0 These should be in the \/pgsql<\/strong> folder.<\/p>\n
Make sure that the postgres user owns them and that they are accessible and that the .key<\/strong> file has limited permissions.<\/p>\n
chown postgres:postgres \/pgsql\/*.crt\r\nchown postgres:postgres \/pgsql\/*.key\r\nchmod 400 \/pgsql\/*.key<\/pre>\n
 <\/p>\n
Next stop the slave server<\/p>\n
#service postgresql stop<\/pre>\n
Now you can configure the slave in a similar manner to the master.\u00a0 Firstly edit postgresql.conf.<\/p>\n
WARNING<\/span>:\u00a0 If you have a recovery.conf<\/strong> file in your data directory you need to get rid of it or postgresql will not start.\u00a0 More information provided here https:\/\/www.cybertec-postgresql.com\/en\/recovery-conf-is-gone-in-postgresql-v12<\/a>\/ (Acknowledgement).<\/p>\n
Find the following directives in the file and set\/uncomment them paying particular attention to the primary_conninfo line where you put the connection string.<\/p>\n
NOTE that the text between the ‘ ‘ after the primary_conninfo directive is all on the same line.<\/p>\n
You will need to change the host, password, sslcert and sslkey directives to reflect your configuration.<\/p>\n
primary_conninfo = 'host=XXX.XXX.XXX.XXX port=5432 user=replicate password=mysecretpassword sslmode=require sslcert=\/pgsql\/slave.crt sslkey=\/pgsql\/slave.key sslrootcert=\/pgsql\/postgresCA.crt' \r\nprimary_slot_name = 'replicate'         # replication slot on sending server\r\n#promote_trigger_file = ''              # file name whose presence ends recovery\r\nhot_standby = on                        # \"off\" disallows queries during recovery\r\n                                        # (change requires restart)\r\n#max_standby_archive_delay = 30s        # max delay before canceling queries\r\n                                        # when reading WAL from archive;\r\n                                        # -1 allows indefinite delay\r\n#max_standby_streaming_delay = 30s      # max delay before canceling queries\r\n                                        # when reading streaming WAL;\r\n                                        # -1 allows indefinite delay\r\nwal_receiver_create_temp_slot = on      # create temp slot if primary_slot_name\r\n                                        # is not set\r\nwal_receiver_status_interval = 10s      # send replies at least this often\r\n                                        # 0 disables\r\nhot_standby_feedback = on               # send info from standby to prevent\r\n                                        # query conflicts\r\n#wal_receiver_timeout = 60s             # time that receiver waits for\r\n                                        # communication from primary\r\n                                        # in milliseconds; 0 disables\r\nwal_retrieve_retry_interval = 5s        # time to wait before retrying to\r\n                                        # retrieve WAL after a failed attempt\r\n#recovery_min_apply_delay = 0           # minimum delay for applying changes during recovery\r\n<\/pre>\n
Next we need to remove any existing data and copy the folders from the master<\/p>\n
rm -R \/pgsql\/15 \r\npg_basebackup -v -h xxx.xxx.xxx.xxx -U replicate -P -D \/pgsql\/data --slot replicate \r\nchown -R postgres:postgres \/pgsql\/15<\/pre>\n
where xxx.xxx.xxx.xxx is the ip address of the master. If you have set up postgresql according to this site then the data directory will be \/pgsql<\/strong>.\u00a0 Note you may also need to set the permissions if you are not logged in as the postgres user.<\/p>\n
You will be prompted for the ‘replicate<\/strong>‘ user’s password.\u00a0 If all is well then the script should terminate with something like<\/p>\n
177156\/177156 kB (100%), 1\/1 tablespace\r\n<\/pre>\n
The slave’s data folder should now be populated with a copy of the master.<\/p>\n
NOTE<\/strong>: If you have issues performing this task then you need to resolve them before proceeding.<\/p>\n
Now in order for our slave to know it is acting as a standby server we need to create an empty text file in the data directory.<\/p>\n
touch \/pgsql\/15\/standby.signal\r\nchown postgres:postgres \/pgsql\/15\/standby.signal<\/pre>\n
You should now be able to start the server and all should work as it should.<\/p>\n
systemctl start postgrtesql<\/pre>\n
Troubleshooting<\/h3>\n
The log files in \/var\/log\/postgresql on the master and the slave may help resolve problems.<\/p>\n
On the master you can issue the following in the psql client<\/p>\n
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn FROM pg_stat_replication;<\/pre>\n
which should return a record.\u00a0 The state should be “streaming”<\/p>\n
Most of the problems are caused by permissions so review permissions on the data directory and for the ssl certificates.<\/p>\n
Double check the connection string paying attention to the names of the certificates and make sure that they match the files stored in \/pgsql\/<\/p>\n
On the slave from the psql client run<\/p>\n
\\x\r\nSELECT * FROM pg_stat_wal_receiver;<\/pre>\n
Again there should be a record for the master.<\/p>\n
Check the password for the replication user is encoded with sha-256<\/p>\n
SELECT rolpassword from pg_authid where rolname = 'replicate';\r\n\r\n<\/pre>\n
rollpassword<\/strong> should begin with\u00a0 SCRAM-SHA-256<\/strong><\/p>\n
If there is no result then check that the user exists.<\/p>\n
NOTE: One of my databases has lots of roles that cause \\du to bring up reams of gibberish.\u00a0 For this reason I use this lookup query instead:<\/p>\n
SELECT usename AS role_name,\r\n  CASE \r\n     WHEN usesuper AND usecreatedb THEN \r\n       CAST('superuser, create database' AS pg_catalog.text)\r\n     WHEN usesuper THEN \r\n        CAST('superuser' AS pg_catalog.text)\r\n     WHEN usecreatedb THEN \r\n        CAST('create database' AS pg_catalog.text)\r\n     ELSE \r\n        CAST('' AS pg_catalog.text)\r\n  END role_attributes\r\nFROM pg_catalog.pg_user\r\nORDER BY role_name desc;<\/pre>\n
 <\/p>\n
WAL files not being deleted
\n<\/span><\/h4>\n
For some reason the WAL files used for replication do not always get deleted and simply build up until you run out of space on the partition.<\/p>\n
To get rid of them manually from psql -U postgres run<\/p>\n
postgres=# \r\nSELECT slot_name,\r\n   lpad((pg_control_checkpoint()).timeline_id::text, 8, '0') ||\r\n   lpad(split_part(restart_lsn::text, '\/', 1), 8, '0') ||\r\n   lpad(substr(split_part(restart_lsn::text, '\/', 2), 1, 2), 8, '0')\r\n   AS wal_file\r\nFROM pg_replication_slots;\r\nslot_name |         wal_file\r\n-----------+--------------------------\r\n postgres2 | 0000000100000000000000B0\r\n(1 row)\r\n\r\npostgres=# \r\n    SELECT pg_drop_replication_slot('postgres2');\r\n pg_drop_replication_slot\r\n--------------------------\r\n\r\n(1 row)<\/pre>\n
and the shortly after most of the WAL files will have disappeared.\u00a0 Change “postgres2<\/strong>” in the second statement to whatever “slot_name<\/strong>” is returned in the first.<\/p>\n
Acknowledgement: https:\/\/stackoverflow.com\/questions\/49539938\/postgres-wal-file-not-getting-deleted<\/p>\n
<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"
PREAMBLE It goes without saying that if you want to set up replication then you need a master and a slave postgresql server.\u00a0 There is a bit of configuration to do in order to get it running. This how to was originally written for older versions of Postgresql but has been updated to reflect the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-244","post","type-post","status-publish","format-standard","hentry","category-a-series-of-hints-and-tips-when-using-the-postgresql-library-libpq-fe-h"],"_links":{"self":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/244","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=244"}],"version-history":[{"count":29,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/244\/revisions"}],"predecessor-version":[{"id":517,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=\/wp\/v2\/posts\/244\/revisions\/517"}],"wp:attachment":[{"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=244"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=244"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.inplico.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=244"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}