GaussDB (PostgreSQL-Compatible Mode)
Setting Compatibility Modes
- If the source database type is PostgreSQL, syntax can also be migrated to GaussDB (PostgreSQL-compatible).
- Create a PostgreSQL-compatible database.
- Log in to the GaussDB instance as a user who has the permission to create databases.
- Create a PostgreSQL-compatible GaussDB database.
create database databasename dbcompatibility = 'PG';
- Run the following command. If the value of datcompatibility is PG, the database is created successfully.
select * from pg_database where datname = 'databasename';
Setting GUC Parameters
For a PostgreSQL-compatible GaussDB database, you can set GUC parameters listed in Table 1 to enhance compatibility.
GaussDB provides many parameters, which determine how the database system works. Before modifying these parameters, you need to understand the impact on databases. Otherwise, unexpected results may occur.
| PostgreSQL-Compatible Configuration Item | Compatibility Configuration Item | Description | Supported Database | Enabled |
|---|---|---|---|---|
| behavior_compat_options | display_leading_zero | Specifies how floating-point numbers are displayed. 0 before the decimal point is displayed. | Centralized/Distributed | Yes |
| bind_procedure_searchpath | Specifies the search path of the database object in a stored procedure for which no schema name is specified. | Centralized/Distributed | Yes | |
| correct_to_number | Specifies the compatibility of the to_number() result. | Centralized/Distributed | Yes | |
| return_null_string | Specifies how to display the empty result (empty string '') of the lpad() and rpad() functions. | Centralized/Distributed | Yes | |
| allow_procedure_compile_check | Controls the compilation check of the SELECT and OPEN CURSOR statements in a stored procedure. | Centralized/Distributed | Yes | |
| plsql_compile_check_options | plsql_expression_check | Specifies whether to enable the PL/SQL compilation check for GaussDB. If this parameter is set to plsql_expression_check, the system checks whether functions, stored procedures, and packages can be successfully compiled upon creation. | Centralized | Yes |
Procedure
Centralized
- Connect to the GaussDB database and switch to the GaussDB installation user.
su - omm
In the preceding command, omm is an example. Change it to the actual installation user.
- Run the following commands to configure the parameters:
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='display_leading_zero,bind_procedure_searchpath,correct_to_number,return_null_string,allow_procedure_compile_check'";
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "plsql_compile_check_options='plsql_expression_check'";
- The preceding GUC parameter settings are applied to migration from PostgreSQL to GaussDB Centralized V2.0-8.200 Enterprise Edition. For GUC parameters of other migration scenarios, replace the content before and after the equal sign (=) in the preceding commands with parameters and values displayed in the pre-check function.
- After schema migration, change the value (plsql_expression_check) of GUC parameter plsql_compile_check_options to an empty string.
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "plsql_compile_check_options=''";
Distributed
- Connect to the GaussDB database and switch to the GaussDB installation user.
su - omm
In the preceding command, omm is an example. Change it to the actual installation user.
- Run the following commands to configure the parameters:
gs_guc reload -Z coordinator -Z datanode -N all -I all -c "behavior_compat_options='display_leading_zero,bind_procedure_searchpath,correct_to_number,return_null_string,allow_procedure_compile_check'";
The preceding GUC parameter settings are applied to migration from PostgreSQL to GaussDB Distributed V2.0-8.200 Enterprise Edition. For GUC parameters of other migration scenarios, replace the content before and after the equal sign (=) in the preceding commands with parameters and values displayed in the pre-check function.
What is your overall rating for this page?
Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot