Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-19711

Read/Write splitting of DB operations to support primary/replica database configurations

XMLWordPrintable

    • MOODLE_20_STABLE, MOODLE_39_STABLE
    • MOODLE_39_STABLE
    • srdjan-slave-conn
    • Hide

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

      It would give an advantage on clusters if Moodle could allow slave (read-only) databases. Moodle could use master DB server for all write operations and could use slave DB server for all read operations. It will make clustering with Moodle simpler to setup and maintain.

      DB layer 2.0 could make it possible (easier) to do.

            Votes:
            32 Vote for this issue
            Watchers:
            48 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 4 days, 3 hours, 32 minutes
                4d 3h 32m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.