gem-hunter
Language: PLpgSQL
postgres MV fast refresh module
This project enables Postgres fast refresh capability using materialised view logs to track changes and offer an alternative to the complete refresh.
The fast refresh process was designed to be installed into its own schema that contains the functions needed to run the MV process, with three data dictionary tables and 3 roles.
The workflow for the MV log creation is shown in the diagram below:
The workflow for the MV creation is shown in the diagram below:
The install of the fast refresh functions is designed to live in its own schema in the database that is specified via the MODULEOWNER parameter.
To install the MV code, you need to navigate to the folder where the repo has been downloaded and edit the module_set_variables.sh file. This is where all the variables are stored for where we want to install the fast refresh functions.
The SOURCEUSERNAME/SOURCEPASSWORD & MVUSERNAME/MVPASSWORD parameters are not needed to install the fast refresh functions they are used for the test harness set-up.
cd pg-mv-fast-refresh vi module_set_variables.sh MODULEOWNER=<MODULEOWNER> - The module owner username MODULE_HOME=<MODULE_HOME> - The Module home path MODULEOWNERPASS=<MODULEOWNERPASS> - Password for module owner PGRS_MVIEW INSTALL_TYPE=<INSTALL_TYPE> - The install type for example FULL (New install) - UPDATE (Update existing install). Default value set to FULL HOSTNAME=<HOSTNAME> - Hostname for database PORT=<PORT> - port for database DBNAME=<DBNAME> - Database Name PGUSERNAME=<PGUSERNAME> - DB username for the module installation run PGPASSWORD=<PGPASSWORD> - DB username password for the module installation run SOURCEUSERNAME=<SOURCEUSERNAME> - DB username for the source tables for the MV SOURCEPASSWORD=<SOURCEPASSWORD> - DB password for the source tables user MVUSERNAME=<MVUSERNAME> - DB username for the MV owner MVPASSWORD=<MVPASSWORD> - DB password for the MV owner LOG_FILE=<LOG_PATH> - Path to logfile output location
Here is an example of the parameter settings used the test case: we have an RDS instance pg-tonytest.test.com with a database testpoc and a master username dbamin
The fast refresh functions will be installed under the schema testpoc by the install package.
We then have a source schema testpocsource. This is where the source data tables will go for the test harness and a testpocmv, which is the schema where the MV will be built.
export MODULEOWNER=testpoc export MODULE_HOME=/var/lib/pgsql/pg-mv-fast-refresh export MODULEOWNERPASS=xxxxxxx export INSTALL_TYPE=FULL export HOSTNAME=pg-tonytest.test.com export PORT=5432 export DBNAME=postgres export PGUSERNAME=dbadmin export PGPASSWORD=xxxxxxx export SOURCEUSERNAME=testpocsource export SOURCEPASSWORD=xxxxxxx export MVUSERNAME=testpocmv export MVPASSWORD=xxxxxxx export LOG_FILE=/tmp/fast_refresh_module_install_`date +%Y%m%d-%H%M`.log
Now change the permissions on the script runCreateFastRefreshModule.sh to execute and then run.
chmod 700 runCreateFastRefreshModule.sh $ ./runCreateFastRefreshModule.sh Check log file - /tmp/fast_refresh_module_install_20191119-1423.log $
This should just take seconds to run. When it’s complete, check the log file in the location you set. In my example, it’s in /tmp. The status is shown at the bottom; below is the example of the run I performed.
cat /tmp/fast_refresh_module_install_20191119-1423.log INFO: Set variables INFO: LOG_FILE parameter set to /tmp/fast_refresh_module_install_20191119-1423.log INFO: MODULEOWNER parameter set to testpoc INFO: PGUSERNAME parameter set to dbadmin INFO: HOSTNAME parameter set to pg-tonytest.test.com INFO: PORT parameter set to 5432 INFO: DBNAME parameter set to postgres INFO: MODULE_HOME parameter set to /var/lib/pgsql/pg-mv-fast-refresh INFO: INSTALL_TYPE parameter set to FULL INFO: Run testpoc schema build script INFO: Connect to postgres database postgres via PSQL session …….. cut lines……….. GRANT INFO: Running Module Deployment Error Checks INFO: All Objects compiled successfully INFO: No Errors Found INFO: Completed Module Deployment Error Checks
After this install the functions will be installed under the MODULEOWNER schema.
To uninstall the module just execute the dropFastRefreshModule.sh script and it will prompt you to ask if you want to remove the module schema.
$ ./dropFastRefreshModule.sh Are you sure you want to remove the module schema - testpoc (y/n)?y yes selected the schemas - testpoc will be dropped INFO: Drop Module Schema complete check logfile for status - /tmp/dropFastRefreshModule_20191119-1430.log
There is a test harness script create_test_harness.sh that will create six tables and insert some data into the tables and then create a complex MV. The script is exceuted as below
The SOURCEUSERNAME/SOURCEPASSWORD & MVUSERNAME/MVPASSWORD parameters are needed in the module_set_variables.sh. The SOURCEUSERNAME is the schema where the base tables will be created and the MVUSERNAME is the schema where the materialized view will be created.
$ pwd /var/lib/pgsql/pg-mv-fast-refresh/test_harness $ ./create_test_harness.sh INFO: Build Complete check logfile for status - /tmp/test_harness_install_20191119-1425.log
If you check the output of the log file you will see the objects being created and the MV being created.
To remove the test harness just execute the drop_test_harness.sh script and this will remove the test objects.
$ ./drop_test_harness.sh
INFO: Drop Complete check logfile for status - /tmp/test_harness_drop_20191119-1428.log
There is a pipeline checks scripts that will install the module, create some test data and build 91 materialized view's then drop all the objects, schemas and users. This is mandatory to run if you want to contribute to the code it confirms that the modules will deploy ok and the MV's create with no errors.
$ ./run_pipeline_checks.sh all Starting pipeline script with option all Starting time - Mon Dec 16 12:54:08 UTC 2019 Stage 1: Creating the fast refresh module objects in schemas testpoc Stage 2: Creating the schemas testpocdata and testpocview Stage 3: Creating the test objects and data in schema testpocdata Stage 4: Creating the MV logs in testpocdata Stage 5: Creating 90 test MV's in schema testpocview Stage 6: Test phase Stage 6.1: Update 1 row and refresh all MV's including running 1 materialized view in parallel 4 Stage 7: Dropping the test harness objects Stage 8: Check for problems Pipeline run type all ran with no issues Run completion time - Mon Dec 16 12:55:02 UTC 2019