Postgres for Oracle DBAs – Parameter Changes

Table of Contents

What is included in this blog post ?

This is Part 6 of the “Postgres for Oracle DBAs” series where we compare ‘parameter changes’. We covered 1) Default tablespaces 2) Executing sql scripts 3) Shutdown modes 4) Startup modes 5) alert log vs postgres log 6) Pg_dump Vs Datapump backups 7) Pg_restore Vs impdp in part 1 ,2,3,4 and 5

SPFILE and PFILE - What is equivalent in Postgres ?

In Oracle world there are two different files associated with param changes – SPFILE and PFILE . PFILE is a text file and can be edited with an editor like vi and SPFILE is a binary file . SPFILE allows dynamic changes in Oracle

In postgres we have two files associated with parameter changes – postgresql.conf and postgresql.auto.conf . We generally edit postgresql.conf to make permanent changes and use ALTER SYSTEM for dynamic changes followed by a pg_reload_conf() . Any changes made by ALTER SYSTEM are written to postgresql.auto.conf

Check location of parameter file - Oracle Vs PG

In Oracle you can check the location of param file using a command like below(if you are using spfile)

show parameter spfile ;

In Postgres you can check the location of param file using a command like below

SHOW config_file;

Below is the comparison diagram (for quick reference)

v$parameter vs pg_settings

In Oracle we generally use v$parameter to check the value of a parameter and its properties . Pg_settings is the equivalent in Postgres

In Oracle you can check the value of a parameter using a command like below

SELECT name,value FROM v$parameter WHERE name like ‘%sga%’;

In Postgres you can check the value of a parameter using a command like below

SELECT name,setting,short_desc from pg_settings where name like ‘%shared%’ ;

Below is the comparison diagram (for quick reference)

SHOW command to check the value of a parameter - Oracle Vs PG

We can also use show commands to check the value for a particular parameter

In Oracle you can use a command like below to check param value(Below command would check for parameters that contain ‘sga’ in their name)

SHOW PARAMETER <parameter name>;

In Postgres you can use a command like below to check a parameter

SHOW <parameter name> ;

Below is the comparison diagram (for quick reference)

Oracle Vs PG - How to check whether a parameter is static or dynamic ?

In Oracle you can use a command like below to check whether a parameter is dynamic or static . If ISYSYS_MODIFIABLE is set to IMMEDIATE it indicates that this parameter can be changed dynamically

SELECT name, type, value, issys_modifiable FROM v$parameter WHERE name = ‘<parameter name>’;

In Postgres you can check the same using a command like below

SELECT name,setting FROM pg_settings WHERE context = ‘postmaster’ AND name = ‘<setting name>’;

When the context is postmaster for a particular parameter it needs a restart . Other context values like sighup,superuser,etc.. can be changed dynamically . Please check https://www.postgresql.org/docs/14/view-pg-settings.html for different types of context

Below is the comparison diagram (for quick reference)

Static parameter - How to change

In Oracle you need to run a command like below followed by a restart –

ALTER SYSTEM SET <parameter name>=<new value> SCOPE=SPFILE;

Restart Oracle

In Postgres you can run a command like below followed by a restart . You can also directly edit the conf file and restart postgres

ALTER SYSTEM SET <parameter name>=’<value>’;

Restart Postgres

Below is the comparison diagram (for quick reference)

Dynamic parameter - How to change

In Oracle you need to run a command to change dynamic parameter (note – you can also use scope=memory for dynamic changes depending on your use case)

ALTER SYSTEM SET <parameter name>=<new value> SCOPE=BOTH;

In Postgres you can run a command like below to change dynamic parameter. NOTE – After you run ALTER SYSTEM , a reload of config is needed to change

ALTER SYSTEM SET <parameter name>=’<value>’;

Select pg_reload_conf() ;

Below is the comparison diagram (for quick reference)

Config changes at session,cluster,db levels - Oracle Vs PG

In Oracle you can use ALTER SESSION command to modify a parameter at session level . Not all params are modifiable at session level . You can use a command like below to know if some param is modifiable at session level

SELECT NAME, VALUE FROM V$PARAMETER WHERE ISSES_MODIFIABLE=’TRUE’;

In Postgres you can use below command to check if a particular parameter can be modified at session level

SELECT * FROM pg_settings where context = ‘user’ and name=<param name>

You can make a session level change in oracle using below command

alter session set <paramname>=’value’;

In Postgres you can achieve the same using a command like below

set session <paramanme> to <value> ;

In Postgres you can make parameter changes at different levels – Cluster level , DB level , User level , Session etc.. (Example below)

Cluster level :

ALTER SYSTEM SET work_mem TO ‘128MB’;

Database level :

ALTER DATABASE database_name SET work_mem TO ‘128MB’;

User level :

ALTER ROLE user_name SET work_mem TO ’64MB’;

Transaction level:

BEGIN;

SET LOCAL work_mem TO ’64MB’;

Default settings - New server build

In Postgres you need to change default values for multiple parameters – depending on your workload and use case . Below are some params that are changed during build time (Only a sample list – not complete list)

Shared_buffers

Max_connections

Log_line_prefix

Work_mem

More..

You can use a third party tool like PGTUNE to arrive at your values https://pgtune.leopard.in.ua/

Extensions and shared_preload_libraries

In PG world people generally use one or more extensions . You need to modify the parameter shared_preload_libraries during your build time to avoid unnecessary restart issues in the future

For example if you are using timescaledb and pg_stat_statements, you can change shared_preload_libraries as shown below

shared_preload_libraries=’pg_stat_statements,timescaledb’

PGTUNE , Cybertec config etc..

Tools like Pgtune and Cybertec configurator can help in calculating values for your usecase . But please dont rely on these tools blindly – Please benchmark for your usecase and determine the final values https://pgconfigurator.cybertec-postgresql.com/ and https://pgtune.leopard.in.ua/ are the URLs for these utilities

Pg_settings Vs Pg_file_settings

In previous sections we saw how pg_settings view can be used to check the value of a parameter , check whether a parameter is static/dynamic ,.. What is the significance of pg_file_settings then ?

Listed below are the columns in pg_file_settings. This can prove to be quite useful when

1) you have multiple config files(using include) , you can quickly check the contents of all files at one place 2) Detect syntax errors in config file (invalid parameter , incorrect setting etc..)

Kernel config changes - onprem

In addition to the postgres config changes , you may want to optimize kernel settings to suit your workloads(below are sample list – not a complete list)

swappiness

Overcommit_memory

Hugepages settings

Dirty_ratio

Shared memory segment related settings

File system mount options

etc..

Oracle Vs PG - Config changes summary

  • Pg_settings is equivalent to v$parameter in Oracle
  • Set session and alter session are equivalents (to perform session level changes)
  • There is a concept of two files PFILE and SPFILE . In PG we dont have exactly similar concept of binary type vs plain text type – Postgres conf file is in readable format and can be edited
  • There are third party utilities like PGTUNE and Cybertec configurator which can be used to tune your config
  • Oracle has hidden parameters and parameter history tracking using AWR snapshots – Equivalent is not available in PG

Conclusion

In part 6 of the series “Postgres for Oracle DBAs” we compared “parameter changes”. Also check our performance articles –Pg_fincore and pg_buffercache to troubleshoot performance issues , Pgbouncer multiple instances(How we increased our TPS using multiple instances) , Postgres and temporary files , Pgpool and performance tuning, How we improved Lambda performance by 130x etc..

Want to learn Postgres ? Register for webinar (link on homepage)

Looking for Postgres support ? Please reach us at support@klouddb.io

Share this Post :