Monday, January 9, 2012

Postgresql - the pg_settings view

This view is roughly the equivalent to the v$spparameter/v$parameter views in Oracle. Each setting related with the running instance is detailed and documented here . From now on I'll try to relate everything I study about PostgreSQL with its counterpart on Oracle, so I reinforce the topics I learned in the OCA certification.

pg_settings

The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.

http://www.postgresql.org/docs/9.1/static/view-pg-settings.html

How many such settings are there?
postgres=# select count(*) from pg_settings;
 count 
-------
   219
(1 row)

One thing I regard worth looking up, is how to tell what settings were changed but are not yet applied as you need to restart the server for them to take effect (those with the postmaster context).

Let's play around a little bit. What are those settings?

postgres=# select name,setting from pg_settings where context = 'postmaster';
              name              |               setting                
--------------------------------+--------------------------------------
 allow_system_table_mods        | off
 archive_mode                   | off
 autovacuum_freeze_max_age      | 200000000
 autovacuum_max_workers         | 3
 bonjour                        | off
 bonjour_name                   | 
 config_file                    | /var/lib/pgsql/data1/postgresql.conf
 data_directory                 | /var/lib/pgsql/data1
 external_pid_file              | 
 hba_file                       | /var/lib/pgsql/data1/pg_hba.conf
 hot_standby                    | off
 ident_file                     | /var/lib/pgsql/data1/pg_ident.conf
 listen_addresses               | localhost
 logging_collector              | on
 max_connections                | 100
 max_files_per_process          | 1000
 max_locks_per_transaction      | 64
 max_pred_locks_per_transaction | 64
 max_prepared_transactions      | 0
 max_wal_senders                | 0
 port                           | 5432
 shared_buffers                 | 3072
 shared_preload_libraries       | 
 silent_mode                    | off
 ssl                            | off
 ssl_ciphers                    | ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH
 superuser_reserved_connections | 3
 track_activity_query_size      | 1024
 unix_socket_directory          | 
 unix_socket_group              | 
 unix_socket_permissions        | 0777
 wal_buffers                    | 96
 wal_level                      | minimal
(33 rows)


Let's say I want to restrict the max_connections setting to half its default value.

postgres=# update pg_catalog.pg_settings set setting = '50' where name = 'max_connections';
ERROR:  parameter "max_connections" cannot be changed without restarting the server

Aha! So, it's much more simple than that, it won't let you do it through this view! Well,on these grounds, my question does not apply to PostgreSQL.

So I guess, this implementation is analogous to starting the Oracle instance with a pfile, so no need to have two views as oracle does, one for current settings and one for those written in the spfile that might not be effective just yet.

No comments:

Post a Comment