Testing requirements and setup

1. This testing requires the creation of a PostgreSQL read/write pair:

Set up Postgres master/slave replication

Option A: Docker

1. Clone the test repo to somewhere handy (outside of Moodle)

   git clone https://github.com/andrewnicols/MDL-19711

2. 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

1. 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

2. Save the file and create the following directories and set correct ownership:
3. mkdir -p /var/lib/postgresql/<version>/main/archive/
4. chmod 700 /var/lib/postgresql/<version>/main/archive/
5. chown -R postgres:postgres /var/lib/postgresql/<version>/main/archive/
6. 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.

1. Save the file and restart postgres:

   systemctl restart postgresql

Setup replication user

1. Enter the following commands:

   sudo su - postgres

   psql

   CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'aqwe123@';

Configure slave

1. 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

2. Save the file.

Copy PG data from Master to Slave

1. 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.

1. 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'

2. Save the file and set permissions and start postgres:

   chmod 600 recovery.conf

   systemctl start postgresql

Checking replication is working

1. 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:

1. 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');

2. 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

1. 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

1. 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.
   1. Expected results:
      Moodle operates as normal

Test with a read slave

1. 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',

   ],

   ]

2. 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

1. Log into moodle
   1. Confirm that you see SELECT queries appearing in the logs on both the slave and master
   2. Confirm that you do not see any DELETE, TRUNCATE, INSERT, or UPDATE queries occuring on the slave
   3. Confirm that you see a range of DELETE, TRUNCATE, INSERT, or UPDATE queries occuring on the master

Test lazy master connection

1. Update Moodle config.php to set invalid master credentials.
2. Attempt to do anything in Moodle
   1. 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

3. Restore settings and confirm that master/slave operation has resumed.

Test when slave isn't available*

1. shutdown postgres service on the slave and conduct activity on the moodle site.
   1. Expected results:
      1. Site operations remains normal from a user perpective
      2. all queries are resumed by the postgres master
      3. 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/]

2. Restore settings and confirm that master/slave operation has resumed.

Test read + write inside a transaction

1. 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();'

2. Expected result:
   1. 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

3. 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";'

4. Expected result:
   1. 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

1. 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");'

   1. 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

1. 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");'

   1. Confirm that only the slave logged the query
2. 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");'

1. 1. Expected result:
   2. Confirm that only the master logged the query