The Configurations Panel

Use the Configurations panel (shown in Figure 4.6) to define, modify and apply a named configuration.

Figure 4.6 - The Configurations panel.

A configuration is a named set of parameters that (when applied), change the behavior of a cluster by overriding the default parameters specified in the postgresql.conf or

pgpool.conf file. If possible, you should use the Configurations panel to modify parameters that reside in the postgresql.conf and pgpool.conf files.

Before making any changes to the configuration of your Cloud Database cluster, we recommend you review Chapter 4.2.2.3, Best Practices for Working with Configurations.

Using the Configurations Panel Controls

Figure 4.7 - The Configurations panel controls.

Use the controls along the top of the Configurations panel (see Figure 4.7) to manage named configurations:

 Use the drop down list-box under Select a configuration to select a configuration to modify, delete or apply to specified nodes of a Cloud Database cluster.

 Use the AddConfiguration button to create a new configuration. To create a new configuration, click the AddConfiguration button.

Figure 4.8 - The Add Configuration dialog.

When the ^AddConfiguration dialog opens (see Figure 4.8), enter the name of the new configuration, and click ^OK. When the creation of the new configuration is complete, Cloud Database will display the new configuration name in the

Selectaconfiguration listbox.

 Use the DeleteConfiguration button to delete the configuration specified in the Select a configuration drop-down list box.

Figure 4.9 - The Delete Selected Configuration popup.

When the ^DeleteSelected Configuration popup (shown in Figure 4.9) opens, click OK to confirm that you wish to delete the configuration.

 Use the radio buttons next to Master or Replica to specify that the selected configuration should be applied to the master node of the cluster, or to all of the replica nodes that reside within the cluster.

 Use the Apply button to deploy a configuration on the selected node (or nodes).

Modifying a Parameter Value

You may not change the parameter settings shown in the Default configuration;

instead, you must choose a configuration for modification from the ^Selecta

configuration drop-down listbox (shown in Figure 4.10).

Figure 4.10 - The Configurations panel, displaying a new named configuration.

Use the tabs at the top of the parameter table to select a configuration file to modify:

 Click the postgresql.conf tab to access parameters that reside in the

postgresql.conf file.

 Click the pgpool.conf tab to access parameters that control load balancing.

To modify a parameter, right click on the parameter name, and select ^Edit from the context menu, or double-click a parameter value in the MASTERVALUE column. The

Configuration setting dialog (shown in Figure 4.11) opens, displaying a brief description of the parameter and the current parameter value.

Figure 4.11 - The Configuration setting dialog.

Use the fields on the Configurationsetting dialog to specify a new value for the parameter, and to mark a parameter for deployment:

 Use the ^{ParameterValue} field to specify a new value for the parameter.

 Check the Enabled box to mark the parameter modification for application to your cluster. Please note that the modification will not be deployed until you select the ^Apply option on the Configurations control panel.

Select OK to exit the dialog and preserve changes to the configuration, or Cancel to exit without making any changes. Upon exiting the Configurationsetting dialog, parameters marked for deployment will display a checkmark next to the parameter name (if Enabled), and the new parameter value will be displayed in the MASTERVALUE

column.

To deploy those parameters that are marked as ^Enabled, click the ^Apply button on the

Configurations control panel. The Apply settings dialog opens (as shown in Figure 4.12).

Figure 4.12 - The Apply settings dialog.

Check the box next to the parameter file that you wish to deploy; select the OK

button to apply any parameter modifications; click ^Cancel to exit without applying any changes.

4.2.2.1 Modifying postgresql.conf Parameters

Parameter values in the postgresql.conf file determine Postgres server behavior as it pertains to:

Parameters within the postgresql.conf file also enable and determine the behavior of Advanced Server utilities, such as SQL Protect, Infinite Cache, and Dynatune.

Some modifications to the postgresql.conf parameters require a server restart to take effect, while others require a parameter reload. Parameters modified through the

Configurations panel (shown in Figure 4.13) will take affect when applied; if you are modifying parameters directly on the server (via an ssh connection), you can review the comments in the ^postgresql.^conf file to determine if a server restart or reload is required.

For more information about manually modifying configuration files or controlling the server, please see Chapter 13, Cloud Database System Administration.

Figure 4.13 - The postgresql.conf file.

For more information about the postgresql.conf parameters, please refer to the Postgres Plus Advanced Server Guide, available from EnterpriseDB at:

http://www.enterprisedb.com/docs/en/9.1/asguide/Postgres_Plus_Advanced_Server_Guide-07.htm - P601_23230

4.2.2.2 Modifying pgpool.conf Parameters

Use the parameters shown on the ^pgpool.^conf tab (shown in Figure 4.14) to modify Cloud Database server behavior related to load balancing.

Figure 4.14 - The pgpool.conf file.

Modifications to the pgpool.conf file do not require a Postgres server restart; the parameters are automatically updated when you save the configuration file without interrupting current users.

By default, all write transactions are routed to the master node of the Cloud Database cluster; replica nodes can execute read-only queries. The white_function_list and

black_function_list parameters specify the names of Postgres functions or user-defined functions that may be invoked (or not invoked) on a replica node.

 The white_function_list specifies functions that may be executed on replica nodes. Any function not specified on the list is assumed to be unsafe for

execution by a replica.

 The black_function_list specifies functions that will not be executed on replica nodes. Any function not specified on the list is assumed to be safe for execution by a replica.

Though the Postgres nextval() and setval() functions can be invoked via a

SELECT statement, they perform a write function, modifying the state of the database by incrementing a sequence. By default, Cloud Database includes these functions in the black_function_list to prevent them from executing on a replica node. If you have user-defined functions that should (similarly) not be executed on a replica node, you should add the function name to the

black_function_list.

Please note that you can specify either a white_function_list or a

black_function_list, but not both.

 The reset_query_list parameter specifies the SQL commands sent to reset the connection to the backend when exiting a session. Use a semi-colon as a delimiter when specifying multiple commands (;). The default

value of is .

4.2.2.3 Best Practices for Working with Configurations

Cloud Database allows you to create and apply custom configurations to the master or replica nodes that reside in your cluster. Please note that changing parameter settings can have unintended consequences, ranging from degraded performance to system crashes.

Consequently, we recommend that only an advanced user who accepts these risks, and has experience with both Postgres and cloud environments modify parameter settings.

There are several ways that you can minimize the risks involved when making parameter changes:

 Always make a snapshot backup of your data before making parameter changes.

For information about taking a backup, refer to Chapter 7, Managing Backups and Recovery.

 Always setup a test cluster to test parameter changes, to ensure they have the intended effect before deploying them to your production environment. Make the test environment mirror the final target environment as much as possible - this is easy to accomplish by restoring a production backup into a similar size cluster as the original. For more details, see Chapter 5.1.1, Creating a Developer Sandbox.

 Only change one parameter at a time (or as few as possible when dealing with interdependent settings) and monitor its effect until you are comfortable with the result.

 Make parameter changes on a copy of the existing configuration that is in use for the master or replicas. That way, if the parameter changes prove detrimental it will be easy for you to re-apply the original settings. If you are making changes to configuration files through ^ssh, make a backup of the configuration files before making any changes, so they can be easily restored.

When adjusting parameters, be mindful of that fact that the master node in the cluster processes both read and write requests, while the replica nodes in the cluster accept only read requests. You can tune the master node and the replica nodes independently to quickly have an impact (either positive or negative) on your write or read performance.

For a complete summary of the parameters available in PostgreSQL and Postgres Plus Advanced Server, see Chapter 2 of the Postgres Plus Advanced Server Guide,

Configuration Parameters, available at:

http://www.enterprisedb.com/docs/en/9.0/asguide/Postgres_Plus_Advanced_Serv er_Guide-07.htm - P600_23360

The Postgres Plus Advanced Server Guide provides information about each parameter, including the privileges required to alter the parameter, and if parameter modifications

require a server restart or reload. The documentation also includes detailed sections about using the parameters that affect:

 Performance

 Resource Consumption and Memory

 Query Planning and Optimizer Hints

 Error Reporting and Logging

 Oracle Compatibility settings

 Advanced Server feature-specific settings