Question & Answer
Question
How do you modify Netezza database configuration parameters in the postgresql.conf file?
Answer
The IBM PureData System for Analytics appliance uses a Postmaster database on the host as the main repository of all system catalog meta-data. This includes information about all databases, users, groups and permissions on the system, as well as all DDL for tables, views, sequences, synonyms, functions and procedures.
This does not include actual end user data, which is stored down on the blades.
The main Postmaster configuration file is /nz/data/postgresql.conf. Here is an example from the beginning of the file:
#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form
#
# name = value
#
# (The `=' is optional.) White space is collapsed, comments are
# introduced by `#' anywhere on a line. The complete list of option
# names and allowed values can be found in the PostgreSQL
# documentation. Examples are:
#log_connections = on
#fsync = off
#max_connections = 64
This file is re-read daily as part of the log rotation procedure.
Parameters can be viewed and changed on a per session basis with the SQL SET and SHOW commands. Here is an example:
[nz@nz12345]$ nzsql
Welcome to nzsql, the IBM Netezza SQL interactive terminal.
Type: \h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
SYSTEM(ADMIN)=> show enable_mergejoin;
NOTICE: ENABLE_MERGEJOIN is on
SHOW VARIABLE
SYSTEM(ADMIN)=> set enable_mergejoin = off;
SET VARIABLE
SYSTEM(ADMIN)=> show enable_mergejoin;
NOTICE: ENABLE_MERGEJOIN is off
SHOW VARIABLE All modified parameters return to their default values after disconnecting and reconnecting.
A change can be made globally across all users by editing the value in /nz/data/postgresql.conf:
A parameter can be enabled by setting it to ON, TRUE, or 1. They all mean the same thing.
A parameter can be disabled by setting it to OFF, FALSE, or 0. These all mean the same thing.
There are two ways to have the change take effect:
First, have the postmaster UNIX process reload the modified postgresql.conf file with:
pkill -HUP postmasterThe second way to enable the change is to restart the database with nzstop followed by nzstart.
The first method has the advantage of not rebooting all the blades which takes several minutes.
It is recommended for customers to double check with IBM Customer Support before modifying these settings.
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
249775
Modified date:
17 October 2019
UID
swg21683548