{"id":993,"date":"2023-06-23T16:54:51","date_gmt":"2023-06-23T14:54:51","guid":{"rendered":"https:\/\/simon-frey.com\/blog\/?p=993"},"modified":"2024-03-22T10:43:52","modified_gmt":"2024-03-22T09:43:52","slug":"how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits","status":"publish","type":"post","link":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/","title":{"rendered":"How to fix the &#8220;pg: too many connections&#8221; error. Postgres and its three connection limits"},"content":{"rendered":"\n<p>Are you running into any of the following postgres connection limit errors<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>pq: sorry, too many clients already<\/code><\/li><li><code>pg: too many connections for database \"exampledatabase\"<\/code><\/li><li><code>pg: too many connections for role \"examplerole\"<\/code><\/li><\/ul>\n\n\n\n<p>Yes? Great news: This article will help you to understand where to find that limit and how to increase it.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">What are this &#8220;too many&#8221; connection limits in postgres?<\/h2>\n\n\n\n<p>These limits check how many connections are open to your database and limit them, so that e.g. one users does not use up all resources of your database. This is a security mechanism, which makes a lot of sense.<\/p>\n\n\n\n<p>Sometimes it creates problems if the limits are set too small for your actually production workload.<\/p>\n\n\n<div class=\"lazyblock-ad-Z20K1Oc wp-block-lazyblock-ad\"><div style=\"display:block;font-family:sans-serif; border:2px solid #00000020;padding: 0.5em;margin-top:1em;margin-bottom:1em;\">\n  <div style=\"display:flex;justify-content:center;align-items:center;gap:10px;\">\n  <div style=\"line-height: 1.3em;text-align:left;\"><h3>Highly skilled DevOps\/SRE Freelancer<\/h3>\n  <p>I am Simon, the author of this blog. And I have great news: <b style=\"font-weight:bold;\">You can work with me<\/b><\/p>\n  <p>As DevOps and Infrastructure freelancer, I will help you choose the right Infrastructure technology for your company, fix your cloud problems and support your team in building scalable products.<\/p>\n  <p>I work with Golang, Docker, Kubernetes, Google Cloud, AWS and Terraform.<\/p>\n  <p>Checkout my <a href=\"https:\/\/simon-frey.com\/cv\" target=\"_blank\">CV<\/a> to learn more or directly contact me via the button below.<\/p>\n  <\/div>  \n  <img decoding=\"async\" data-src=\"https:\/\/simon-frey.com\/cv\/img\/simon-frey.jpg\" alt=\"Simon Frey Header image\" style=\"height:10em;\" src=\"data:image\/svg+xml;base64,PHN2ZyB3aWR0aD0iMSIgaGVpZ2h0PSIxIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPjwvc3ZnPg==\" class=\"lazyload\">\n\n  <\/div>\n  \n  <a href=\"mailto:contact@simon-frey.com\" style=\"display:block;text-align:center;color:black;text-decoration:none;border:solid 2px black;padding:10px;border-radius:5px;margin-top:1em;\">Let&#8217;s work together!<\/a>\n<\/div><\/div>\n\n\n<h2 class=\"wp-block-heading\">The three different connection limits in postgres<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Instance wide connection limit<\/h3>\n\n\n\n<p>You are hitting this limit when you run into the <code>pq: sorry, too many clients already<\/code> error.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Get instance wide connection limit<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW max_connections;<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Update instance wide connection limit<\/h4>\n\n\n\n<p>This limit is the hardest to set as you need to set it in the postgres config and restart your database. <\/p>\n\n\n\n<p><strong>Important<\/strong>: As stated <a href=\"https:\/\/stackoverflow.com\/a\/32584211\">in this stackoverflow answer<\/a>, just changing the max_connections might not be enough and your postgress might become unstable! You should know what you are doing<\/p>\n\n\n\n<p>1. Figure out where your postgres config is stored<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW config_file;<\/code><\/pre>\n\n\n\n<p>2. Change the value in the file<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>max_connections = 1000<\/code><\/pre>\n\n\n\n<p>3. Save the file and restart your database<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Per database connection limit<\/h3>\n\n\n\n<p>You are hitting this limit when you run into the <code>pg: too many connections for database \"exampledatabase\"<\/code> error.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Get per database connection limit<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT datname,datconnlimit FROM pg_database WHERE datname='exampledatabase';<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Update per database connection limit<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE exampledatabase CONNECTION LIMIT 950;<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Per role\/user connection limit<\/h3>\n\n\n\n<p>You are hitting this limit when you run into the <code>pg: too many connections for role \"examplerole\"<\/code>error.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Get per role\/user connection limit<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT rolname,rolconnlimit FROM pg_roles WHERE rolname='examplerole';<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Update instance wide connection limit<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER USER examplerole CONNECTION LIMIT 400;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">What values to set for the limits<\/h2>\n\n\n\n<p>If you decide to set the limits (it is not always required. Going with YOLO and having no limits should work for most production loads as well), then there is only one important rule<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>instance limit &gt; database limit &gt; user limit<\/code><\/pre>\n\n\n\n<p>Your instance limit should be by far the biggest, then the database limit should be smaller than the instance limit and then the user limit smaller than the database one. <\/p>\n\n\n\n<p>Postgres will not inform you if you try to set a limit that is higher than the parent limit. Always check all three!<\/p>\n\n\n\n<p>&#8212;<\/p>\n\n\n\n<p>Hope this little guide helped you to set your postgres limits correctly. I failed on this in the past, and it was quite a burden to figure out why clients weren&#8217;t able to connect. <\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Are you running into any of the following postgres connection limit errors pq: sorry, too many clients already pg: too many connections for database &#8220;exampledatabase&#8221; pg: too many connections for role &#8220;examplerole&#8221; Yes? Great news:&hellip;<\/p>\n<p><a href=\"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/\" class=\"more-link\">Read more<span class=\"screen-reader-text\"> of How to fix the &#8220;pg: too many connections&#8221; error. Postgres and its three connection limits<\/span><span aria-hidden=\"true\"> &rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1006,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[21,231],"tags":[63,19,233,49],"class_list":["post-993","post","type-post","status-publish","format-standard","hentry","category-linux","category-sre","tag-devops","tag-linux","tag-postgres","tag-sre"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How to fix the &quot;pg: too many connections&quot; error. Postgres and its three connection limits - Blog by Simon Frey<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Simon Frey\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to fix the \"pg: too many connections\" error. Postgres and its three connection limits - Blog by Simon Frey","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/","twitter_misc":{"Written by":"Simon Frey","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#article","isPartOf":{"@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/"},"author":{"name":"Simon Frey","@id":"https:\/\/simon-frey.com\/blog\/#\/schema\/person\/34753982b648415636ee7a079f3e19a3"},"headline":"How to fix the &#8220;pg: too many connections&#8221; error. Postgres and its three connection limits","datePublished":"2023-06-23T14:54:51+00:00","dateModified":"2024-03-22T09:43:52+00:00","mainEntityOfPage":{"@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/"},"wordCount":397,"publisher":{"@id":"https:\/\/simon-frey.com\/blog\/#\/schema\/person\/34753982b648415636ee7a079f3e19a3"},"image":{"@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#primaryimage"},"thumbnailUrl":"https:\/\/simon-frey.com\/blog\/wp-content\/uploads\/2023\/06\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits.png","keywords":["DevOps","linux","postgres","SRE"],"articleSection":["Linux","SRE"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/","url":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/","name":"How to fix the \"pg: too many connections\" error. Postgres and its three connection limits - Blog by Simon Frey","isPartOf":{"@id":"https:\/\/simon-frey.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#primaryimage"},"image":{"@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#primaryimage"},"thumbnailUrl":"https:\/\/simon-frey.com\/blog\/wp-content\/uploads\/2023\/06\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits.png","datePublished":"2023-06-23T14:54:51+00:00","dateModified":"2024-03-22T09:43:52+00:00","breadcrumb":{"@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#primaryimage","url":"https:\/\/simon-frey.com\/blog\/wp-content\/uploads\/2023\/06\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits.png","contentUrl":"https:\/\/simon-frey.com\/blog\/wp-content\/uploads\/2023\/06\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits.png","width":2048,"height":1075},{"@type":"BreadcrumbList","@id":"https:\/\/simon-frey.com\/blog\/how-to-fix-the-pg-too-many-connections-error-postgres-and-its-three-connection-limits\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/simon-frey.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to fix the &#8220;pg: too many connections&#8221; error. Postgres and its three connection limits"}]},{"@type":"WebSite","@id":"https:\/\/simon-frey.com\/blog\/#website","url":"https:\/\/simon-frey.com\/blog\/","name":"Blog by Simon Frey","description":"","publisher":{"@id":"https:\/\/simon-frey.com\/blog\/#\/schema\/person\/34753982b648415636ee7a079f3e19a3"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/simon-frey.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/simon-frey.com\/blog\/#\/schema\/person\/34753982b648415636ee7a079f3e19a3","name":"Simon Frey","logo":{"@id":"https:\/\/simon-frey.com\/blog\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/simon-frey.com","https:\/\/www.linkedin.com\/in\/simonfrey\/","https:\/\/x.com\/eu_frey"]}]}},"_links":{"self":[{"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/posts\/993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/comments?post=993"}],"version-history":[{"count":9,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/posts\/993\/revisions"}],"predecessor-version":[{"id":1132,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/posts\/993\/revisions\/1132"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/media\/1006"}],"wp:attachment":[{"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/media?parent=993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/categories?post=993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/simon-frey.com\/blog\/wp-json\/wp\/v2\/tags?post=993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}