Skip to content

Latest commit

 

History

History
428 lines (382 loc) · 13.6 KB

README.md

File metadata and controls

428 lines (382 loc) · 13.6 KB
Raw

PGConfig API

Donate

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:

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.