Hide
Testing requirements and setup
- This testing requires the creation of a PostgreSQL read/write pair:
Set up Postgres master/slave replication
Option A: Docker
- Clone the test repo to somewhere handy (outside of Moodle)
git clone https://github.com/andrewnicols/MDL-19711
|
- Run the images:
cd MDL-19711
|
docker-compose up
|
Notes:
To access the database servers, open a new terminal and change to the same directory as before
For master:
docker-compose exec master bash
|
For slave:
docker-compose exec slave bash
|
Option B: Ubuntu
Postgres - https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
These instructions will include setting up a PostgreSQL replica pair.
Create Postgres master/slave replication (Ubuntu) - Common steps
A) Prepare two hosts, one MASTER, the other SLAVE
B) Install postgres on both
C) sudo apt-get update && sudo apt-get install postgresql
D) systemctl stop postgresql (on both hosts)
Configure Master
- Edit /etc/postgresql/<version>/main/postgresql.conf and set the following options:
Notice: (where <version> is the postgres version, eg 9.6 or 10)
listen_addresses = '<your master ip>'
|
wal_level = hot_standby
|
synchronous_commit = local
|
archive_mode = on
|
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
|
max_wal_senders = 2
|
wal_keep_segments = 10
|
synchronous_standby_names = 'slave'
|
# name of your slave instance
|
- Save the file and create the following directories and set correct ownership:
- mkdir -p /var/lib/postgresql/<version>/main/archive/
- chmod 700 /var/lib/postgresql/<version>/main/archive/
- chown -R postgres:postgres /var/lib/postgresql/<version>/main/archive/
- Edit /etc/postgresql/9.6/main/pg_hba.conf and paste in the following block:
# Localhost
|
host replication replica 127.0.0.1/32 md5
|
|
# PostgreSQL Master IP address
|
host replication replica <your master ip>/32 md5
|
|
# PostgreSQL SLave IP address
|
host replication replica <your slave ip>/32 md5
|
Notice: Where <host master ip> and <your slave ip> are the address of your master and slave hosts respectively.
- Save the file and restart postgres:
systemctl restart postgresql
|
Setup replication user
- Enter the following commands:
sudo su - postgres
|
psql
|
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';
|
Configure slave
- Edit /etc/postgresql/<version>/main/postgresql.conf and set the following options:
listen_addresses = '<your ip>'
|
wal_level = hot_standby
|
synchronous_commit = local
|
max_wal_senders = 2
|
wal_keep_segments = 10
|
synchronous_standby_names = 'slave'
|
hot_standby = on
|
- Save the file.
Copy PG data from Master to Slave
- On the slave server, do the following steps:
su - postgres
|
cd <version> #being 9.5 or 10, etc
|
mv main main.org
|
mkdir main/
|
chmod 700 main/
|
pg_basebackup -h <master ip> -U replica -D /var/lib/postgresql/<version>/main -P --xlog
|
Notice: This will prompt for the replica user password that was created previously.
- Create /var/lib/postgresql/<version>/main/recovery.conf with the following contents:
|
standby_mode = 'on'
|
primary_conninfo = 'host=<master ip> port=5432 user=replica password=aqwe123@ application_name=pgslave001'
|
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
|
trigger_file = '/tmp/postgresql.trigger.5432'
|
- Save the file and set permissions and start postgres:
chmod 600 recovery.conf
|
systemctl start postgresql
|
Checking replication is working
- On the master instance:
sudo su - postgres
|
psql -x -c "select * from pg_stat_replication;"
|
Expected result:
-[ RECORD 1 ]----+------------------------------
|
pid | 1388
|
usesysid | 16384
|
usename | replica
|
application_name | pgslave001
|
client_addr | 192.168.1.2
|
client_hostname |
|
client_port | 39110
|
backend_start | 2020-04-15 12:13:57.892788+10
|
backend_xmin |
|
state | streaming
|
sent_location | 0/4000220
|
write_location | 0/4000220
|
flush_location | 0/4000220
|
replay_location | 0/4000220
|
sync_priority | 0
|
sync_state | async
|
Notice: The output may vary from installation to installation, however the overall results should be similar.
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
|
Result:
application_name | state | sync_priority | sync_state
|
------------------+-----------+---------------+------------
|
pgslave001 | streaming | 0 | async
|
(1 row)
|
Notice: The output may vary from installation to installation, however the overall results should be similar.
Create a test database:
- On the master, run:
sudo su - postgres
|
psql
|
CREATE TABLE replica_test (pgtest varchar(100));
|
INSERT INTO replica_test VALUES ('moodle.org');
|
INSERT INTO replica_test VALUES ('This is from Master');
|
INSERT INTO replica_test VALUES ('pg replication test');
|
- On the slave, run:
sudo su - postgres
|
psql
|
select * from replica_test;
|
The result show display what was entered into the master:
pgtest
|
---------------------
|
catalyst-au.net
|
This is from Master
|
pg replication test
|
(3 rows)
|
If that works correctly, then replication is up and running correctly.
Postgres logging
- Edit /etc/postgresql/<version>/main/postgresql.conf and set the following options:
log_statement = 'all'
|
log_directory = 'pg_log'
|
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
|
logging_collector = on
|
log_min_error_statement = error
|
Notice: Do this on master and slaves hosts.
Moodle tests (set moodle logging - developer mode)
Test without a read slave
- Setup your moodle/php/apache (or nginx) instance as per the standard Moodle setup instructions and test that its working correctly before proceeding to read-replica setup.
- Expected results:
Moodle operates as normal
Test with a read slave
- When moodle is setup and working, append the read-replica options to config.php in the dboptions sections:
'readonly' => [
|
'instance' => '192.168.1.2',
|
'connecttimeout' => 2,
|
'latency' => 0.5,
|
'exclude_tables' => [
|
'config',
|
],
|
]
|
- Open a terminal on both master and slave and tail the postgres log files.
tail -F /var/log/postgres/postgresql.log
|
Note: The location of this log file may differ between installations
- Log into moodle
- Confirm that you see SELECT queries appearing in the logs on both the slave and master
- Confirm that you do not see any DELETE, TRUNCATE, INSERT, or UPDATE queries occuring on the slave
- Confirm that you see a range of DELETE, TRUNCATE, INSERT, or UPDATE queries occuring on the master
Test lazy master connection
- Update Moodle config.php to set invalid master credentials.
- Attempt to do anything in Moodle
- Expected results:
|
Error: Database connection failed
|
It is possible that the database is overloaded or otherwise not running properly.
|
The site administrator should also check that the database details have been correctly specified in config.php
|
- Restore settings and confirm that master/slave operation has resumed.
Test when slave isn't available*
- shutdown postgres service on the slave and conduct activity on the moodle site.
- Expected results:
- Site operations remains normal from a user perpective
- all queries are resumed by the postgres master
- Moodle error logs should record the following messages:
PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused\n\tIs the server running on host "192.168.1.2" and accepting\n\tTCP/IP connections on port 5432? in /var/www/moodle/lib/dml/pgsql_native_moodle_database.php on line 184, referer: [http://moodle.catalyst-au.net/my/]
|
- Restore settings and confirm that master/slave operation has resumed.
Test read + write inside a transaction
- Test read/writes in a transaction with the following snippet run on the moodle instance:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $t = $DB->start_delegated_transaction(); echo $DB->count_records("assign")."\n"; $t->allow_commit();'
|
- Expected result:
- Query will appear in postgres master logs:
2020-04-17 11:23:49 AEST [3609-1] moodle@moodle LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED; SAVEPOINT moodle_pg_savepoint
|
2020-04-17 11:23:49 AEST [3609-2] moodle@moodle LOG: execute <unnamed>: SELECT COUNT('x') FROM mdl_assign
|
2020-04-17 11:23:49 AEST [3609-3] moodle@moodle LOG: statement: RELEASE SAVEPOINT moodle_pg_savepoint; COMMIT
|
- Running the following snippet will show the query result appear in the postgres slave's logs:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); echo $DB->count_records("assign")."\n";'
|
- Expected result:
- Query will appear in postgres slave logs:
2020-04-17 11:23:49 AEST [1609-5] moodle@moodle DETAIL: parameters: $1 = '10'
|
2020-04-17 11:23:49 AEST [1609-6] moodle@moodle LOG: execute <unnamed>: SELECT * FROM mdl_course WHERE category = $1
|
2020-04-17 11:23:49 AEST [1609-7] moodle@moodle DETAIL: parameters: $1 = '0'
|
Test the db lock tables
- Run the following snippt to test that db_locks query only appears in the postgres master logs:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $DB->get_records("lock_db");'
|
- Expected results:
Query will only appear in postgres master logs:
2020-04-17 11:23:49 AEST [1609-6] moodle@moodle LOG: execute <unnamed>: SELECT * FROM mdl_lock_db
|
Test table exclusion config
- Run query to demonstrate select query display on the postgres slave logs:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $DB->get_records("user");'
|
- Confirm that only the slave logged the query
- Alter the moodle config.php and 'user' to the 'exclude_tables' stanza:
'exclude_tables' => [
|
'user',
|
],
|
# Run query to demonstrate the select query is diverted to the postgres master only:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $DB->get_records("user");'
|
-
- Expected result:
- Confirm that only the master logged the query
Show
Testing requirements and setup
This testing requires the creation of a PostgreSQL read/write pair:
Set up Postgres master/slave replication
Option A: Docker
Clone the test repo to somewhere handy (outside of Moodle)
git clone https://github.com/andrewnicols/MDL-19711
Run the images:
cd MDL-19711
docker-compose up
Notes:
To access the database servers, open a new terminal and change to the same directory as before
For master:
docker-compose exec master bash
For slave:
docker-compose exec slave bash
Option B: Ubuntu
Postgres - https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/
These instructions will include setting up a PostgreSQL replica pair.
Create Postgres master/slave replication (Ubuntu) - Common steps
A) Prepare two hosts, one MASTER, the other SLAVE
B) Install postgres on both
C) sudo apt-get update && sudo apt-get install postgresql
D) systemctl stop postgresql (on both hosts)
Configure Master
Edit /etc/postgresql/<version>/main/postgresql.conf and set the following options:
Notice: (where <version> is the postgres version, eg 9.6 or 10)
listen_addresses = '<your master ip>'
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/9.6/main/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'slave'
# name of your slave instance
Save the file and create the following directories and set correct ownership:
mkdir -p /var/lib/postgresql/<version>/main/archive/
chmod 700 /var/lib/postgresql/<version>/main/archive/
chown -R postgres:postgres /var/lib/postgresql/<version>/main/archive/
Edit /etc/postgresql/9.6/main/pg_hba.conf and paste in the following block:
# Localhost
host replication replica 127.0 . 0.1 / 32 md5
# PostgreSQL Master IP address
host replication replica <your master ip>/ 32 md5
# PostgreSQL SLave IP address
host replication replica <your slave ip>/ 32 md5
Notice: Where <host master ip> and <your slave ip> are the address of your master and slave hosts respectively.
Save the file and restart postgres:
systemctl restart postgresql
Setup replication user
Enter the following commands:
sudo su - postgres
psql
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';
Configure slave
Edit /etc/postgresql/<version>/main/postgresql.conf and set the following options:
listen_addresses = '<your ip>'
wal_level = hot_standby
synchronous_commit = local
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'slave'
hot_standby = on
Save the file.
Copy PG data from Master to Slave
On the slave server, do the following steps:
su - postgres
cd <version> #being 9.5 or 10, etc
mv main main.org
mkdir main/
chmod 700 main/
pg_basebackup -h <master ip> -U replica -D /var/lib/postgresql/<version>/main -P --xlog
Notice: This will prompt for the replica user password that was created previously.
Create /var/lib/postgresql/<version>/main/recovery.conf with the following contents:
standby_mode = 'on'
primary_conninfo = 'host=<master ip> port=5432 user=replica password=aqwe123@ application_name=pgslave001'
restore_command = 'cp /var/lib/postgresql/9.6/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
Save the file and set permissions and start postgres:
chmod 600 recovery.conf
systemctl start postgresql
Checking replication is working
On the master instance:
sudo su - postgres
psql -x -c "select * from pg_stat_replication;"
Expected result:
-[ RECORD 1 ]----+------------------------------
pid | 1388
usesysid | 16384
usename | replica
application_name | pgslave001
client_addr | 192.168 . 1.2
client_hostname |
client_port | 39110
backend_start | 2020 - 04 - 15 12 : 13 : 57.892788 + 10
backend_xmin |
state | streaming
sent_location | 0 / 4000220
write_location | 0 / 4000220
flush_location | 0 / 4000220
replay_location | 0 / 4000220
sync_priority | 0
sync_state | async
Notice: The output may vary from installation to installation, however the overall results should be similar.
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
Result:
application_name | state | sync_priority | sync_state
------------------+-----------+---------------+------------
pgslave001 | streaming | 0 | async
( 1 row)
Notice: The output may vary from installation to installation, however the overall results should be similar.
Create a test database:
On the master, run:
sudo su - postgres
psql
CREATE TABLE replica_test (pgtest varchar(100));
INSERT INTO replica_test VALUES ('moodle.org');
INSERT INTO replica_test VALUES ('This is from Master');
INSERT INTO replica_test VALUES ('pg replication test');
On the slave, run:
sudo su - postgres
psql
select * from replica_test;
The result show display what was entered into the master:
pgtest
---------------------
catalyst-au.net
This is from Master
pg replication test
( 3 rows)
If that works correctly, then replication is up and running correctly.
Postgres logging
Edit /etc/postgresql/<version>/main/postgresql.conf and set the following options:
log_statement = 'all'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
logging_collector = on
log_min_error_statement = error
Notice: Do this on master and slaves hosts.
Moodle tests (set moodle logging - developer mode)
Test without a read slave
Setup your moodle/php/apache (or nginx) instance as per the standard Moodle setup instructions and test that its working correctly before proceeding to read-replica setup.
Expected results:
Moodle operates as normal
Test with a read slave
When moodle is setup and working, append the read-replica options to config.php in the dboptions sections:
'readonly' => [
'instance' => '192.168.1.2' ,
'connecttimeout' => 2 ,
'latency' => 0.5 ,
'exclude_tables' => [
'config' ,
],
]
Open a terminal on both master and slave and tail the postgres log files.
tail -F /var/log/postgres/postgresql.log
Note: The location of this log file may differ between installations
Log into moodle
Confirm that you see SELECT queries appearing in the logs on both the slave and master
Confirm that you do not see any DELETE , TRUNCATE , INSERT , or UPDATE queries occuring on the slave
Confirm that you see a range of DELETE , TRUNCATE , INSERT , or UPDATE queries occuring on the master
Test lazy master connection
Update Moodle config.php to set invalid master credentials.
Attempt to do anything in Moodle
Expected results:
Error: Database connection failed
It is possible that the database is overloaded or otherwise not running properly.
The site administrator should also check that the database details have been correctly specified in config.php
Restore settings and confirm that master/slave operation has resumed.
Test when slave isn't available*
shutdown postgres service on the slave and conduct activity on the moodle site.
Expected results:
Site operations remains normal from a user perpective
all queries are resumed by the postgres master
Moodle error logs should record the following messages:
PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused\n\tIs the server running on host "192.168.1.2" and accepting\n\tTCP/IP connections on port 5432? in /var/www/moodle/lib/dml/pgsql_native_moodle_database.php on line 184, referer: [http://moodle.catalyst-au.net/my/]
Restore settings and confirm that master/slave operation has resumed.
Test read + write inside a transaction
Test read/writes in a transaction with the following snippet run on the moodle instance:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $t = $DB->start_delegated_transaction(); echo $DB->count_records("assign")."\n"; $t->allow_commit();'
Expected result:
Query will appear in postgres master logs:
2020 - 04 - 17 11 : 23 : 49 AEST [ 3609 - 1 ] moodle @moodle LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED; SAVEPOINT moodle_pg_savepoint
2020 - 04 - 17 11 : 23 : 49 AEST [ 3609 - 2 ] moodle @moodle LOG: execute <unnamed>: SELECT COUNT( 'x' ) FROM mdl_assign
2020 - 04 - 17 11 : 23 : 49 AEST [ 3609 - 3 ] moodle @moodle LOG: statement: RELEASE SAVEPOINT moodle_pg_savepoint; COMMIT
Running the following snippet will show the query result appear in the postgres slave's logs:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); echo $DB->count_records("assign")."\n";'
Expected result:
Query will appear in postgres slave logs:
2020 - 04 - 17 11 : 23 : 49 AEST [ 1609 - 5 ] moodle @moodle DETAIL: parameters: $ 1 = '10'
2020 - 04 - 17 11 : 23 : 49 AEST [ 1609 - 6 ] moodle @moodle LOG: execute <unnamed>: SELECT * FROM mdl_course WHERE category = $ 1
2020 - 04 - 17 11 : 23 : 49 AEST [ 1609 - 7 ] moodle @moodle DETAIL: parameters: $ 1 = '0'
Test the db lock tables
Run the following snippt to test that db_locks query only appears in the postgres master logs:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $DB->get_records("lock_db");'
Expected results:
Query will only appear in postgres master logs:
2020 - 04 - 17 11 : 23 : 49 AEST [ 1609 - 6 ] moodle @moodle LOG: execute <unnamed>: SELECT * FROM mdl_lock_db
Test table exclusion config
Run query to demonstrate select query display on the postgres slave logs:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $DB->get_records("user");'
Confirm that only the slave logged the query
Alter the moodle config.php and 'user' to the 'exclude_tables' stanza:
'exclude_tables' => [
'user' ,
],
# Run query to demonstrate the select query is diverted to the postgres master only:
php -r 'define("CLI_SCRIPT", 1); require("config.php"); $DB->get_records("user");'
Expected result:
Confirm that only the master logged the query