如何使用代码备份/恢复PostgreSQL？
如何使用代码备份/恢复PostgreSQL？
提问于 2014-04-12 14:26:51
我已经尝试过this方法，但它不起作用，任何人都可以改正它或分享一些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();
}
}回答 4
Stack Overflow用户
发布于 2015-06-18 05:09:52
转储方法(其中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);
}
}Stack Overflow用户
发布于 2020-05-24 05:42:20
只是为了增强字节响应，并与Net Core 3.1 Linux和Windows系统一起工作
您可以使用PGPASSWORD而不是PGPASSFILE，因此可以省略为凭据创建中间文件。
对于linux，您需要考虑如何在linux中使用Process：Shell Script File(.sh) does not run from c# core on linux运行sh脚本。
要在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);
}Execute方法
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;
}这里的Dump方法
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);
}Stack Overflow用户
发布于 2019-07-16 14:13:43
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
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23026949
复制相关文章
相似问题
腾讯云开发者
