Drop Postgresql database

Question

I am trying to drop a database from aspnet(c#). But It is giving me an error: There are some connections. If I delete all the connections:

SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.pid <> pg_backend_pid() and pg_stat_activity.datname = 'databsename';

It gives me another error: connection is lose.

It is like if i execute the sentence above, It is closing all my connections.

I use npgsql as connector.

Here my code: The code inside a button:

NpgsqlConnection _connPgComienzo = new NpgsqlConnection("my_connection_to_other_DDBB_in_the_same_server;");

    try
    {

        _connPgComienzo.Open();
        FileInfo file1 = new FileInfo(Server.MapPath("desconectar.sql"));
        string script_crear_bbdd = file1.OpenText().ReadToEnd();
        var m_createdb_cmd1 = new NpgsqlCommand(script_crear_bbdd, _connPgComienzo);
        m_createdb_cmd1.ExecuteNonQuery();
        _connPgComienzo.Close();

        _connPgComienzo.Open();
        FileInfo file2 = new FileInfo(Server.MapPath("drop_bbdd.sql"));
        string script_crear_bbdd2 = file2.OpenText().ReadToEnd();
        var m_createdb_cmd2 = new NpgsqlCommand(script_crear_bbdd2, _connPgComienzo);
        m_createdb_cmd2.ExecuteNonQuery();
        _connPgComienzo.Close();

    }
    catch (Exception ex)
    {

    }

desconectar.slq:

SELECT pg_terminate_backend (pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pid <> pg_backend_pid() AND pg_stat_activity.datname = 'theDDBBIWantToDrop';

drop_bbdd.sql:

 DROP DATABASE theDDBBIWantToDrop;

http://stackoverflow.com/questions/17449420/postgresql-unable-to-drop-database-because-of-some-auto-connections-to-db?rq=1 — Endrju, Jun 09 '14 at 11:14
It is not working for me. And I dont want to prevent connecting, I want to drop the database suddenly. I edit to say that yes, It is working in a postgresql client(maestro), but not working in C# — Za7pi, Jun 09 '14 at 11:19
Show us the code that is not working. Edit your question and paste it. — Endrju, Jun 09 '14 at 11:21
@Za7pi can you terminate your database using the above query using `pAdmin` or whatever you use ?? — Vivek S., Jun 09 '14 at 11:25
@Za7pi are you trying to `restore` `PostgreSQL-Database` using `c#` or what ?? — Vivek S., Jun 09 '14 at 11:27
does `db` in your connection string and `db` you want to drop is same ?? — Vivek S., Jun 09 '14 at 11:50
Your `catch (Exception ex) {}` is muting all errors. Please add some diagnostics to it, e.g. `Console.WriteLine(ex.ToString())` then see what's the error messsage. It will help pinpointing the cause. EDIT: I've just realized that your app is a web app. So add some logging to see what's the exact error message. — Endrju, Jun 09 '14 at 14:01
Yes, It is a web app. I am catching the errors but I dont put It here. The error is: error: database "myDDBB" is being accessed by other users — Za7pi, Jun 09 '14 at 14:11

score 0 · Answer 1 · answered Jun 09 '14 at 11:47

*Try to use psql to terminate your postgreSQL database

for that you have to create a batch file(*.bat) using c#, This .bat file must contain the following script to terminate your database connection

psql -U postgres -d postgres -c SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname ='yourdb'

For example

Private Sub TerminateDBactivity()
    Dim strPG_dumpPath As String
    Dim strSub As String
    strPG_dumpPath = Application.StartupPath.ToString()
    strPG_dumpPath = strPG_dumpPath.Replace("\", "\\")
    Dim a As Integer = strPG_dumpPath.IndexOf(":\\", 0)
    a = a + 2
    strSub = strPG_dumpPath.Substring(0, (a - 2))
    strPG_dumpPath = strPG_dumpPath.Substring(a, (strPG_dumpPath.Length - a))
    Dim sbSB1 As New StringBuilder(strPG_dumpPath)
    sbSB1 = sbSB1.Replace("\\", vbCrLf & vbCrLf & "cd ")
    Dim sbSB2 As New StringBuilder("cd /D ")
    sbSB2 = sbSB2.Append(strSub)
    sbSB2 = sbSB2.Append(":\")
    sbSB1 = sbSB2.Append(sbSB1)
    sbSB1 = sbSB1.Append(vbCrLf & vbCrLf & "cd PG" & vbCrLf & vbCrLf)
    Dim sw As New StreamWriter(Application.StartupPath.ToString() & "\PG\pgTerminate.bat")
    If sbSB1.Length <> 0 Then
        sbSB1 = sbSB1.Append("psql -U postgres  -d postgres -c ""SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '" & gStrDBName & "';")
        sw.WriteLine(sbSB1)
        sw.Dispose()
        sw.Close()
        Dim processDB As New ProcessStartInfo
        processDB.FileName = Application.StartupPath.ToString() & "\PG\pgTerminate.bat"
        processDB.WindowStyle = ProcessWindowStyle.Hidden
        Process.Start(processDB).WaitForExit()
        Cursor.Current = Cursors.Default
    End If
End Sub

this code will create pgTerminate.bat file in this path D:\Apps\RSTAR PGSQL DOTCONNECT\bin\Debug\PG
pgTerminate.bat looks like

cd /D D:\Apps

cd RSTAR PGSQL DOTCONNECT

cd bin

cd Debug

cd PG

psql -U postgres  -d postgres -c SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'mydb';

Note : before trying code you should manually create terminate bat file and test whether its working or not

The bat is ok? Is this line inside bat or outside? `cd /D D:\Apps` — Za7pi, Jun 09 '14 at 11:53
@Za7pi its inside only...that defines the path to `psql` in your case this will change — Vivek S., Jun 09 '14 at 11:55
My code: `string str_Path = Server.MapPath(".") + "\\run.bat"; ProcessStartInfo processInfo = new ProcessStartInfo(str_Path); processInfo.UseShellExecute = false; Process batchProcess = new Process(); batchProcess.StartInfo = processInfo; batchProcess.Start();` bat: `cd RSTAR PGSQL DOTCONNECT cd bin cd Debug cd PG psql -U postgres -d postgres -c SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'DDBB';` error: database "myDDBB" is being accessed by other users — Za7pi, Jun 09 '14 at 12:10

Drop Postgresql database

1 Answers1