117 lines
3.6 KiB
C#
117 lines
3.6 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Diagnostics;
|
|
using System.IO;
|
|
using Npgsql;
|
|
|
|
public class PostgresBackupProcessor
|
|
{
|
|
private readonly string _host = "localhost";
|
|
private readonly string _user = "postgres";
|
|
private readonly string _password = "Pa$$1234";
|
|
//private readonly string _port = "5432";
|
|
private readonly string _port = "5434";
|
|
private readonly string _baseDb = "postgres"; // for admin tasks like drop/create
|
|
|
|
public DataTable ProcessBackup(string backupFilePath, string query, string tempDbName = "temp_restore_db")
|
|
{
|
|
DropDatabase(tempDbName);
|
|
CreateDatabase(tempDbName);
|
|
RestoreCustomBackup(tempDbName, backupFilePath);
|
|
DataTable result = ExecuteQuery(tempDbName, query);
|
|
DropDatabase(tempDbName);
|
|
return result;
|
|
}
|
|
|
|
private void DropDatabase(string dbName)
|
|
{
|
|
|
|
using (var conn = new NpgsqlConnection(GetConnectionString(_baseDb,true)))
|
|
{
|
|
conn.Open();
|
|
using (var terminate = new NpgsqlCommand($@"
|
|
SELECT pg_terminate_backend(pid)
|
|
FROM pg_stat_activity
|
|
WHERE datname = '{dbName}' AND pid <> pg_backend_pid();", conn))
|
|
{
|
|
terminate.ExecuteNonQuery();
|
|
}
|
|
using (var cmd = new NpgsqlCommand($@"DROP DATABASE IF EXISTS ""{dbName}"";", conn))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
|
|
private void CreateDatabase(string dbName)
|
|
{
|
|
using (var conn = new NpgsqlConnection(GetConnectionString(_baseDb)))
|
|
{
|
|
conn.Open();
|
|
using (var cmd = new NpgsqlCommand($@"CREATE DATABASE ""{dbName}"";", conn))
|
|
{
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
}
|
|
}
|
|
public static string GetPGProgess(string pgProcessName)
|
|
{
|
|
return System.IO.Path.Combine(Mindrockets.Helpers.Config.Instance.GetValueFromConfig("pgPath", @"C:\Program Files\PostgreSQL\15\bin"), pgProcessName);
|
|
|
|
}
|
|
private void RestoreCustomBackup(string dbName, string backupFilePath)
|
|
{
|
|
var psi = new ProcessStartInfo
|
|
{
|
|
FileName = GetPGProgess("pg_restore.exe"),
|
|
Arguments = $"-U {_user} -h {_host} -p {_port} -d {dbName} \"{backupFilePath}\"",
|
|
UseShellExecute = false,
|
|
RedirectStandardOutput = true,
|
|
RedirectStandardError = true,
|
|
CreateNoWindow = true
|
|
};
|
|
|
|
psi.EnvironmentVariables["PGPASSWORD"] = _password;
|
|
|
|
using (var process = Process.Start(psi))
|
|
{
|
|
string output = process.StandardOutput.ReadToEnd();
|
|
string error = process.StandardError.ReadToEnd();
|
|
process.WaitForExit();
|
|
|
|
if (process.ExitCode != 0)
|
|
{
|
|
throw new Exception("pg_restore failed:\n" + error);
|
|
}
|
|
}
|
|
}
|
|
|
|
private DataTable ExecuteQuery(string dbName, string query)
|
|
{
|
|
var dt = new DataTable();
|
|
using (var conn = new NpgsqlConnection(GetConnectionString(dbName)))
|
|
{
|
|
conn.Open();
|
|
using (var cmd = new NpgsqlCommand(query, conn))
|
|
using (var reader = cmd.ExecuteReader())
|
|
{
|
|
dt.Load(reader);
|
|
}
|
|
}
|
|
return dt;
|
|
}
|
|
|
|
private string GetConnectionString(string dbName, bool admin = false)
|
|
{
|
|
string baseConn = $"Host={_host};Port={_port};Username={_user};Password={_password};Database={dbName};";
|
|
|
|
if (admin)
|
|
{
|
|
// Disable multiplexing (pipelining) for DROP DATABASE
|
|
baseConn += "Multiplexing=false;";
|
|
}
|
|
|
|
return baseConn;
|
|
}
|
|
}
|