PGConfig API

This project it's API that pgconfig.org uses to calculate the tuning values and stuff.

Thanks

Thanks to Chuck Boecking, by sponsoring this tool. :)

How it works

The web interface (pgconfig.org website or just UI) access this api on the address https://api.pgconfig.org/v1/tuning/get-config.

You can call it from curl, eg:

$ curl 'https://api.pgconfig.org/v1/tuning/get-config'
{"data": [{"category": "memory_related","description": "Memory Configuration","parameters": [{"config_value": "512MB","format": "Bytes","name": "shared_buffers"},{"config_value": "2GB","format": "Bytes","name": "effective_cache_size"},{"config_value": "20MB","format": "Bytes","name": "work_mem"},{"config_value": "128MB","format": "Bytes","name": "maintenance_work_mem"}]},{"category": "checkpoint_related","description": "Checkpoint Related Configuration","parameters": [{"config_value": "512MB","format": "Bytes","name": "min_wal_size"},{"config_value": "2GB","format": "Bytes","name": "max_wal_size"},{"config_value": 0.7,"format": "Float","name": "checkpoint_completion_target"},{"config_value": "15MB","format": "Bytes","name": "wal_buffers"}]},{"category": "network_related","description": "Network Related Configuration","parameters": [{"config_value": "*","format": "String","name": "listen_addresses"},{"config_value": 100,"format": "Decimal","name": "max_connections"}]}],"jsonapi": {"version": "1.0"},"links": {"self": "http://api.pgconfig.org/v1/tuning/get-config"},"meta": {"arguments": {},"copyright": "PGConfig API","version": "2.0 beta"}}

With a little formating, looks like this:

{  
   "data":[  
      {  
         "category":"memory_related",
         "description":"Memory Configuration",
         "parameters":[  
            {  
               "config_value":"512MB",
               "format":"Bytes",
               "name":"shared_buffers"
            },
            {  
               "config_value":"2GB",
               "format":"Bytes",
               "name":"effective_cache_size"
            },
            {  
               "config_value":"20MB",
               "format":"Bytes",
               "name":"work_mem"
            },
            {  
               "config_value":"128MB",
               "format":"Bytes",
               "name":"maintenance_work_mem"
            }
         ]
      },
      {  
         "category":"checkpoint_related",
         "description":"Checkpoint Related Configuration",
         "parameters":[  
            {  
               "config_value":"512MB",
               "format":"Bytes",
               "name":"min_wal_size"
            },
            {  
               "config_value":"2GB",
               "format":"Bytes",
               "name":"max_wal_size"
            },
            {  
               "config_value":0.7,
               "format":"Float",
               "name":"checkpoint_completion_target"
            },
            {  
               "config_value":"15MB",
               "format":"Bytes",
               "name":"wal_buffers"
            }
         ]
      },
      {  
         "category":"network_related",
         "description":"Network Related Configuration",
         "parameters":[  
            {  
               "config_value":"*",
               "format":"String",
               "name":"listen_addresses"
            },
            {  
               "config_value":100,
               "format":"Decimal",
               "name":"max_connections"
            }
         ]
      }
   ],
   "jsonapi":{  
      "version":"1.0"
   },
   "links":{  
      "self":"http://api.pgconfig.org/v1/tuning/get-config"
   },
   "meta":{  
      "arguments":{  

      },
      "copyright":"PGConfig API",
      "version":"2.0 beta"
   }
}

Basically, the important data are in the data node, grouped by categories, just like in the UI. :)

A important thing about this is that you can format the output displayed more conveniently, only informing the format=conf parameters, eg:

$ curl 'https://api.pgconfig.org/v1/tuning/get-config?format=conf'
# Generated by PGConfig 2.0 beta
## http://pgconfig.org

# Memory Configuration
shared_buffers = 512MB
effective_cache_size = 2GB
work_mem = 20MB
maintenance_work_mem = 128MB

# Checkpoint Related Configuration
min_wal_size = 512MB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 15MB

# Network Related Configuration
listen_addresses = '*'
max_connections = 100

Another options for the format parameters are json (the default value) and alter_system, take a look:

$ curl 'https://api.pgconfig.org/v1/tuning/get-config?format=alter_system'
-- Generated by PGConfig 2.0 beta
---- http://pgconfig.org

-- Memory Configuration
ALTER SYSTEM SET shared_buffers TO '512MB';
ALTER SYSTEM SET effective_cache_size TO '2GB';
ALTER SYSTEM SET work_mem TO '20MB';
ALTER SYSTEM SET maintenance_work_mem TO '128MB';

-- Checkpoint Related Configuration
ALTER SYSTEM SET min_wal_size TO '512MB';
ALTER SYSTEM SET max_wal_size TO '2GB';
ALTER SYSTEM SET checkpoint_completion_target TO '0.7';
ALTER SYSTEM SET wal_buffers TO '15MB';

-- Network Related Configuration
ALTER SYSTEM SET listen_addresses TO '*';
ALTER SYSTEM SET max_connections TO '100';

In short: to change the output, all you need is to do it's put the parameters in the URL.

Available parameters

The list below lists the available parameters:

Parameter	Possible values	Default Value	Description
pg_version	from 9.0 version until 9.6	9.6	Sets the PostgreSQL version
total_ram	any value above 1GB	2GB	Total memory dedicated to the PostgreSQL.
max_connections	any value above 1	100	expected number of connections
environment_name	WEB, OLTP, DW, Mixed and Desktop	WEB	Sets the environment that the server will run (more details below)
os_type	Linux, Windows and Unix	Linux	Sets the type of operating system used
arch	x86-64 and i686	x86-64	Sets the server architecture
format	json, conf and alter_system	json	changes the output format
show_doc	true and false	false	Shows the documentation (valid only for the json format)
include_pgbadger	true and false	false	Add the settings to enable pgbadger
log_format	stderr, csvlog and syslog	stderr	Sets de default log format for the pgbadger. (Used only when include_pgbadger is true)

Important Don't forget, when setting the total_ram parameter, set the value like the expression [0-9]{1,}GB, eg: 4GB.

About the environment

The list below explains a bit more about the environments:

Name	Description	Use cases
WEB	General web applications	web applications like portal or corporate application
OLTP	Applications with a large volume of transactions	Applications of ERP type or big corporate systems with a lot of simultaneous transactions
DW	Dataware house applications	General Business Inteligence applications
Mixed	Environments who share the database and the application in the same server	Small applications, typically running on the web
Desktop	Development environment	development machine, support or pre-sales

Full example

The example below its used by the UI:

$ curl 'https://api.pgconfig.org/v1/tuning/get-config?environment_name=WEB&format=alter_system&include_pgbadger=true&log_format=stderr&max_connections=100&pg_version=9.6&total_ram=2GB'

How the values are calculated?

In an attempt to make the process simpler, i created a API context to list the rules. It can be access by the URL below:

• /v1/tuning/get-rules

Important: This context supports the follow parameters: os_type, arch e environment_name.

The fields who contains details how each parameter are calculated are formula and max_value, eg:

...
"format": "Bytes",
"formula": "TOTAL_RAM / 4",
"max_value": "2047MB",
"name": "shared_buffers"
...

Note that the values are influenced by the filters mentioned above.

Calling the get-rules context

I recommend that you open the URL below on the browser for easy viewing (or just format the json):

curl 'https://api.pgconfig.org/v1/tuning/get-rules?os_type=Windows&arch=i686&environment_name=OLTP'

Another API Options

Address	Description	Output example
/v1/tuning/get-config-all-environments	show rules for all environments	... "data": [ { "configuration": [..], "environment": "WEB" }, { "configuration": [..], "environment": "OLTP" }, { "configuration": [..], "environment": "DW" }, { "configuration": [..], "environment": "Mixed" }, { "configuration": [..], "environment": "Desktop" } ] ...
/v1/tuning/list-environments	Show all environments	... "data": [ "WEB", "OLTP", "DW", "Mixed", "Desktop" ], ...
/v1/generators/pgbadger/get-config	Show the pgbadger configurations (accepts the format parameter)	... "data": [ { "category": "log_config", "description": "Logging configuration for pgbadger", "parameters": [ { "config_value": "on", "name": "logging_collector" }, { "config_value": "on", "name": "log_checkpoints" }, { "config_value": "on", "name": "log_connections" }, { "config_value": "on", "name": "log_disconnections" }, { "config_value": "on", "name": "log_lock_waits" }, { "config_value": "0", "name": "log_temp_files" }, { "config_value": "C", "format": "String", "name": "lc_messages" }, { "comment": "Adjust the minimum time to collect data", "config_value": "10s", "format": "Time", "name": "log_min_duration_statement" }, { "config_value": "0", "name": "log_autovacuum_min_duration" } ] }, ...

Another contexts are being developed.