如何使用代码备份/还原PostgreSQL
本文关键字：还原 PostgreSQL 备份 何使用 代码 | 更新日期: 2023-09-27 17:57:56
我尝试过这种方法，但它不起作用任何人都可以纠正它或分享Backup/Restore PostgreSQL using VB.NET 的一些教程
并且这些方法用于备份/恢复此处为commandType = pg_dump和commandSentence = -i -h localhost -p 5432 -U postgres -F c -b -v -f C:'Documents and Settings'GDS'Desktop'backup'RStar.backup RStar但在我试图放置备份文件的文件夹中没有返回任何内容
private void executeCommand(string commandType,string commandSentence )
{
try
{
System.Diagnostics.ProcessStartInfo info = new System.Diagnostics.ProcessStartInfo();
info.FileName = "C:''Program Files''PostgreSQL''9.2''bin''" + commandType + ".exe ";
info.Arguments = commandSentence;
info.CreateNoWindow = true ;
info.UseShellExecute = false;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = info;
proc.Start();
proc.WaitForExit();
if (commandType == "pg_dump")
toolStripStatusLabel1.Text = "Backup successfuly created";
else if (commandType == "pg_restore")
toolStripStatusLabel1.Text = "Restore successfuly executed";
else if(commandType=="shp2pgsql")
toolStripStatusLabel1.Text = "Your selected shape file successfuly transfered to PostGIS";
else if (commandType == "pgsql2shp")
toolStripStatusLabel1.Text = "Your selected layer from PostGIS successfuly converted to shape file";
}
catch (Exception ex)
{
toolStripStatusLabel1.Text = ex.ToString();
}
}
只是为了增强字节响应并使用Net Core 3.1 Linux和Windows系统
您可以使用PGPASSWORD而不是PGPASSFILE，这样您就可以省略为凭据创建中间文件的操作。
对于linux，您需要考虑如何使用Process在linux中运行sh脚本：外壳脚本文件（.sh）不是从linux 上的c#core运行的
要在linux中设置变量，应该使用export而不是set。
这里是我在linux和windows操作系统中恢复数据库的例子（Net Core 3.1）：
string Set = RuntimeInformation.IsOSPlatform(OSPlatform.Windows) ? "set " : "export ";
public async Task PostgreSqlRestore(
string inputFile,
string host,
string port,
string database,
string user,
string password)
{
string dumpCommand = $"{Set}PGPASSWORD={password}'n" +
$"psql -h {host} -p {port} -U {user} -d {database} -c '"select pg_terminate_backend(pid) from pg_stat_activity where datname = '{database}''"'n" +
$"dropdb -h " + host + " -p " + port + " -U " + user + $" {database}'n" +
$"createdb -h " + host + " -p " + port + " -U " + user + $" {database}'n" +
"pg_restore -h " + host + " -p " + port + " -d " + database + " -U " + user + "";
//psql command disconnect database
//dropdb and createdb remove database and create.
//pg_restore restore database with file create with pg_dump command
dumpCommand = $"{dumpCommand} {inputFile}";
await Execute(dumpCommand);
}
执行方法
private Task Execute(string dumpCommand)
{
return Task.Run(() =>
{
string batFilePath = Path.Combine(Path.GetTempPath(), $"{Guid.NewGuid()}." + (RuntimeInformation.IsOSPlatform(OSPlatform.Windows) ? "bat" : "sh"));
try
{
string batchContent = "";
batchContent += $"{dumpCommand}";
File.WriteAllText(batFilePath, batchContent, Encoding.ASCII);
ProcessStartInfo info = ProcessInfoByOS(batFilePath);
using System.Diagnostics.Process proc = System.Diagnostics.Process.Start(info);
proc.WaitForExit();
var exit = proc.ExitCode;
... ommit error handler code ...
proc.Close();
}
catch (Exception e)
{
// Your exception handler here.
}
finally
{
if (File.Exists(batFilePath)) File.Delete(batFilePath);
}
});
}
ProcessInfoByOS方法
private static ProcessStartInfo ProcessInfoByOS(string batFilePath)
{
ProcessStartInfo info;
if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows))
{
info = new ProcessStartInfo(batFilePath)
{
};
}
else
{
info = new ProcessStartInfo("sh")
{
Arguments = $"{batFilePath}"
};
}
info.CreateNoWindow = true;
info.UseShellExecute = false;
info.WorkingDirectory = AppDomain.CurrentDomain.BaseDirectory;
info.RedirectStandardError = true;
return info;
}
这里是转储方法
public async Task PostgreSqlDump(
string outFile,
string host,
string port,
string database,
string user,
string password)
{
string dumpCommand =
$"{Set}PGPASSWORD={password}'n" +
$"pg_dump" + " -Fc" + " -h " + host + " -p " + port + " -d " + database + " -U " + user + "";
string batchContent = "" + dumpCommand + " > " + "'"" + outFile + "'"" + "'n";
if (File.Exists(outFile)) File.Delete(outFile);
await Execute(batchContent);
}
转储方法（其中pgDumpPath是pg_dump.exe的路径，outFile是输出文件路径）：
public void PostgreSqlDump(
string pgDumpPath,
string outFile,
string host,
string port,
string database,
string user,
string password)
{
String dumpCommand = "'"" + pgDumpPath + "'"" + " -Fc" + " -h " + host + " -p " + port + " -d " + database + " -U " + user + "";
String passFileContent = "" + host + ":" + port + ":" + database + ":" + user + ":" + password + "";
String batFilePath = Path.Combine(
Path.GetTempPath(),
Guid.NewGuid().ToString() + ".bat");
String passFilePath = Path.Combine(
Path.GetTempPath(),
Guid.NewGuid().ToString() + ".conf");
try
{
String batchContent = "";
batchContent += "@" + "set PGPASSFILE=" + passFilePath + "'n";
batchContent += "@" + dumpCommand + " > " + "'"" + outFile + "'"" + "'n";
File.WriteAllText(
batFilePath,
batchContent,
Encoding.ASCII);
File.WriteAllText(
passFilePath,
passFileContent,
Encoding.ASCII);
if (File.Exists(outFile))
File.Delete(outFile);
ProcessStartInfo oInfo = new ProcessStartInfo(batFilePath);
oInfo.UseShellExecute = false;
oInfo.CreateNoWindow = true;
using (Process proc = System.Diagnostics.Process.Start(oInfo))
{
proc.WaitForExit();
proc.Close();
}
}
finally
{
if (File.Exists(batFilePath))
File.Delete(batFilePath);
if (File.Exists(passFilePath))
File.Delete(passFilePath);
}
}
public void { BackupDatabase(server,port, user,password, dbname, "backupdir", dbname, "C:''Program Files''PostgreSQL''11''bin''");
}
public static string BackupDatabase(
string server,
string port,
string user,
string password,
string dbname,
string backupdir,
string backupFileName,
string backupCommandDir)
{
try
{
Environment.SetEnvironmentVariable("PGPASSWORD", password);
string backupFile = backupdir + backupFileName + "_"+DateTime.Now.ToString("yyyy") + "_" + DateTime.Now.ToString("MM") + "_" + DateTime.Now.ToString("dd") + ".backup";
string BackupString = " -f '"" + backupFile + "'" -F c"+
" -h " + server + " -U " + user + " -p " + port + " -d " + dbname;
Process proc = new System.Diagnostics.Process();
proc.StartInfo.FileName = backupCommandDir + "''pg_dump.exe";
proc.StartInfo.Arguments = BackupString;
proc.StartInfo.RedirectStandardOutput = true;//for error checks BackupString
proc.StartInfo.RedirectStandardError = true;
proc.StartInfo.UseShellExecute = false;//use for not opening cmd screen
proc.StartInfo.CreateNoWindow = true;//use for not opening cmd screen
proc.Start();
proc.WaitForExit();
proc.Close();
return backupFile;
}
catch (Exception ex)
{
return null;
}
https://sagartajpara.blogspot.com/2017/03/postgres-database-backup-in-c.html
public void Backup()
{
try
{
DateTime Time = DateTime.Now;
int year = Time.Year;
int month = Time.Month;
int day = Time.Day;
int hour = Time.Hour;
int minute = Time.Minute;
int second = Time.Second;
int millisecond = Time.Millisecond;
//Save file to C:' with the current date as a filename
string path;
path = "D:''" + year + "-" + month + "-" + day + "-" + hour + "-" + minute + ".sql";
StreamWriter file = new StreamWriter(path);
ProcessStartInfo psi = new ProcessStartInfo();
psi.FileName = "mysqldump";
psi.RedirectStandardInput = false;
psi.RedirectStandardOutput = true;
psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
psi.UseShellExecute = false;
Process process = Process.Start(path);
string output;
output = process.StandardOutput.ReadToEnd();
file.WriteLine(output);
process.WaitForExit();
file.Close();
process.Close();
}
catch (IOException ex)
{
MessageBox.Show("Error , unable to backup!");
}
}