snipt

Ctrl+h for KB shortcuts

SQL

Delete Database That Has Active Connections

1
2
3
SELECT * FROM pg_stat_activity WHERE datname = '<database_name>';
SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<database_name>';
DROP DATABASE '<database_name>'
https://snipt.net/embed/26717b31e0878d6cd8dbedee6492e2e4/
/raw/26717b31e0878d6cd8dbedee6492e2e4/
26717b31e0878d6cd8dbedee6492e2e4
sql
SQL
3
2019-07-17T00:53:44
True
False
False
Mar 10, 2014 at 10:02 PM
/api/public/snipt/120719/
delete-database-that-has-active-connections
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">pg_stat_activity</span> <span class="k">WHERE</span> <span class="n">datname</span> <span class="o">=</span> <span class="s1">&#39;&lt;database_name&gt;&#39;</span><span class="p">;</span> </span><span id="L-2"><a name="L-2"></a><span class="k">SELECT</span> <span class="n">pg_terminate_backend</span> <span class="p">(</span><span class="n">pg_stat_activity</span><span class="p">.</span><span class="n">pid</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">pg_stat_activity</span> <span class="k">WHERE</span> <span class="n">pg_stat_activity</span><span class="p">.</span><span class="n">datname</span> <span class="o">=</span> <span class="s1">&#39;&lt;database_name&gt;&#39;</span><span class="p">;</span> </span><span id="L-3"><a name="L-3"></a><span class="k">DROP</span> <span class="k">DATABASE</span> <span class="s1">&#39;&lt;database_name&gt;&#39;</span> </span></pre></div> </td></tr></table>
postgresql

Bash

Run a PostgreSQL SQL script

psql -v ON_ERROR_STOP=1 -U <username> -d <database_name> -1 -f <sql_file>
https://snipt.net/embed/3180df5e2cee20c0e448a08bb4e8db23/
/raw/3180df5e2cee20c0e448a08bb4e8db23/
3180df5e2cee20c0e448a08bb4e8db23
bash
Bash
1
2019-07-14T17:20:06
True
False
False
Feb 18, 2014 at 05:08 AM
/api/public/snipt/99549/
run-an-postgresql-sql-script
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>psql -v <span class="nv">ON_ERROR_STOP</span><span class="o">=</span><span class="m">1</span> -U &lt;username&gt; -d &lt;database_name&gt; -1 -f &lt;sql_file&gt; </span></pre></div> </td></tr></table>
postgresql