Restore postrgres without ending connections
<p>I run a number of queries for adhoc analysis against a postgres database. Many times I will leave the connection open through the day instead of ending after each query.</p>

<p>I receive a postgres dump over scp through a shell script every five minutes and I would like to restore the database without cutting the connections. Is this possible?</p>

<p>One of the few activities that you cannot perform while a user is connected is dropping the database.<br>
So &ndash; if that is what you are doing during restore &ndash; you'll have to change your approach. Don't drop the database (don't use the <code>-C</code> option in <code>pg_dump</code> or <code>pg_restore</code>), but rather drop and recreate the schemas and objects that don't depend on a schema (like large objects).<br>
You can use the <code>-c</code> flag of <code>pg_dump</code> or <code>pg_restore</code> for that.</p>

<p>The other problem you might run into is connections with open transactions (state &ldquo;idle in transaction&rdquo;). Such connections can hold locks that keep you from dropping and recreating objects, and you'll have to use <code>pg_terminate_backend()</code> to get rid of them.</p>

python database postgresql restore