{"id":13006,"date":"2019-11-13T09:01:36","date_gmt":"2019-11-13T08:01:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/"},"modified":"2019-11-13T09:01:36","modified_gmt":"2019-11-13T08:01:36","slug":"postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/","title":{"rendered":"PostgreSQL 13 will allow to drop databases even when there are connections"},"content":{"rendered":"<p>One of the most popular blogs I&#8217;ve written about PostgreSQL was about <a href=\"https:\/\/www.dbi-services.com\/blog\/what-the-hell-are-these-template0-and-template1-databases-in-postgresql\/\" target=\"_blank\" rel=\"noopener noreferrer\">the three databases<\/a> that PostgreSQL creates by default (well, actually it is <a href=\"https:\/\/www.postgresql.org\/docs\/current\/app-initdb.html\" target=\"_blank\" rel=\"noopener noreferrer\">initdb<\/a> that creates the databases). One of the points mentioned in that post was, that you can not drop a database as long as there are sessions against the database you want to drop. This will change with PostgreSQL 13, which is currently in development.<\/p>\n<p><!--more--><\/p>\n<p>Lets start with a short demo on PostgreSQL 12. I&#8217;ll create a new database and connect to it:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@centos8pg:\/home\/postgres\/ [121] sq\npsql (12.0)\nType \"help\" for help.\n\npostgres=# select version();\n                                                version                                                 \n--------------------------------------------------------------------------------------------------------\n PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit\n(1 row)\n\npostgres=# create database tmp;\nCREATE DATABASE\npostgres=# c tmp\nYou are now connected to database \"tmp\" as user \"postgres\".\ntmp=# \n<\/pre>\n<p>In another session I&#8217;ll try to drop the database which was just created above:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\nostgres@centos8pg:\/home\/postgres\/ [121] psql postgres\npsql (12.0)\nType \"help\" for help.\n\n[local]:5433 postgres@postgres=# l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n tmp       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n(4 rows)\n\n[local]:5433 postgres@postgres=# drop database tmp;\nERROR:  database \"tmp\" is being accessed by other users\nDETAIL:  There is 1 other session using the database.\nTime: 5123.981 ms (00:05.124)\n[local]:5433 postgres@postgres=# \n<\/pre>\n<p>That does not work as the session which is currently connected prevents me from dropping the database. The same test in PostgreSQL 13:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@centos8pg:\/home\/postgres\/ [pgdev] psql postgres\npsql (13devel)\nType \"help\" for help.\n\n[local]:5432 postgres@postgres=# select version();\n                                                  version                                                  \n-----------------------------------------------------------------------------------------------------------\n PostgreSQL 13devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit\n(1 row)\n\nTime: 5.388 ms\n[local]:5432 postgres@postgres=# create database tmp;\nCREATE DATABASE\nTime: 164.435 ms\n[local]:5432 postgres@postgres=# c tmp\nYou are now connected to database \"tmp\" as user \"postgres\".\n[local]:5432 postgres@tmp=# \n<\/pre>\n<p>In another session dropping the database:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres@centos8pg:\/home\/postgres\/ [pgdev] psql postgres\npsql (13devel)\nType \"help\" for help.\n\n[local]:5432 postgres@postgres=# l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n tmp       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n(4 rows)\n\n[local]:5432 postgres@postgres=# drop database tmp with (force);\nDROP DATABASE\nTime: 153.670 ms\n[local]:5432 postgres@postgres=# l\n                                  List of databases\n   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   \n-----------+----------+----------+-------------+-------------+-----------------------\n postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | \n template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres          +\n           |          |          |             |             | postgres=CTc\/postgres\n(3 rows)\n\n[local]:5432 postgres@postgres=# \n<\/pre>\n<p>Nice. What happened to the session connected to that database?<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\n[local]:5432 postgres@tmp=# select 1;\nFATAL:  terminating connection due to administrator command\nserver closed the connection unexpectedly\n        This probably means the server terminated abnormally\n        before or while processing the request.\nThe connection to the server was lost. Attempting reset: Failed.\nTime: 6.241 ms\n: @!&gt;? \n<\/pre>\n<p>Of course, the session is gone.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the most popular blogs I&#8217;ve written about PostgreSQL was about the three databases that PostgreSQL creates by default (well, actually it is initdb that creates the databases). One of the points mentioned in that post was, that you can not drop a database as long as there are sessions against the database you [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-13006","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 13 will allow to drop databases even when there are connections - dbi Blog<\/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:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 13 will allow to drop databases even when there are connections\" \/>\n<meta property=\"og:description\" content=\"One of the most popular blogs I&#8217;ve written about PostgreSQL was about the three databases that PostgreSQL creates by default (well, actually it is initdb that creates the databases). One of the points mentioned in that post was, that you can not drop a database as long as there are sessions against the database you [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-13T08:01:36+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"PostgreSQL 13 will allow to drop databases even when there are connections\",\"datePublished\":\"2019-11-13T08:01:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/\"},\"wordCount\":157,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/\",\"name\":\"PostgreSQL 13 will allow to drop databases even when there are connections - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-11-13T08:01:36+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 13 will allow to drop databases even when there are connections\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 13 will allow to drop databases even when there are connections - dbi Blog","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:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 13 will allow to drop databases even when there are connections","og_description":"One of the most popular blogs I&#8217;ve written about PostgreSQL was about the three databases that PostgreSQL creates by default (well, actually it is initdb that creates the databases). One of the points mentioned in that post was, that you can not drop a database as long as there are sessions against the database you [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/","og_site_name":"dbi Blog","article_published_time":"2019-11-13T08:01:36+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"PostgreSQL 13 will allow to drop databases even when there are connections","datePublished":"2019-11-13T08:01:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/"},"wordCount":157,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/","name":"PostgreSQL 13 will allow to drop databases even when there are connections - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-11-13T08:01:36+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-13-will-allow-to-drop-databases-even-when-there-are-connections\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 13 will allow to drop databases even when there are connections"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13006","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=13006"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13006\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13006"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}