如何修复Postgres [Window]的自动备份脚本？ [英] How to fix Automated Backup script for postgres [Window]?

问题描述

我查找了重复的问题 PostgreSQL：Windows中的自动备份和其他来源 https://wiki.postgresql.org/wiki/Automated_Backup_on_Windows 。我已经尝试为自己创建一个简单的批处理脚本[例如，设置路径，设置密码...等]，以便将来还原数据库。但是，看来我的备份数据库批处理脚本根本不起作用。我不知道我的错误点在哪里。

I have look up a duplicate question PostgreSQL: Automated Backup in Windows and other source https://wiki.postgresql.org/wiki/Automated_Backup_on_Windows. I have try to make a simple batch script for my own [eg. Setup Path , Set up password... etc] in order to do restore database in the future. However, it seems like my batch script for backup database does not work at all. I can't figure out where is my mistaken point.

这是我的备份postgres数据库的批处理脚本。

Here is my batch script for backup postgres database.

@echo off    
   SET PGPASSWORD=%Ech0-5910&123
   set root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\
   echo on  
   cd %root%
   echo on 
   pg_dump.exe -h 192.168.1.161 -p 5432 -U postgres -F c -b -v -f "D:\Backup\DatabaseBackUp\SQL\123456.backup" testdb

更新的脚本遵循@Gerhard Barnard的答案

Updated Script follow @Gerhard Barnard answer

@echo off    
  echo 192.168.1.161:5432:_wolfcom:postgres:R0m3o^%%Ech0-5910^&>"%APPDATA%\postgresql\pgpass.conf"
  set "root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\"
  cd /d "%root%"
  pg_dump.exe -h 192.168.1.161 -p 5432 -U postgres -F c -b -v -f "D:\Backup\DatabaseBackUp\SQL\123456.backup" _wolfcom
  pause

推荐答案

您的代码应包含所有路径用双引号消除空格。请记住， cmd 将每个以空格分隔的工作解释为新命令。我们需要逃避& ，因为它会批量成为物理运算符，最后更喜欢使用 / d cd 时使用c>选项，以防您来自其他驱动器盘符：

Your code should wrap all paths in double quotes to eliminate whitespace. Keep in mind cmd interprets each space delimited work as a new command. We need to escape the & as it will become a physical operator in batch, lastly it is prefered to use the /d option when using cd in case you come from another drive letter:

@echo off    
 SET "PGPASSWORD=%Ech0-5910^&123"
 set "root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\"
 cd /d "%root%"
 pg_dump.exe -h 192.168.1.161 -p 5432 -U postgres -F c -b -v -f "D:\Backup\DatabaseBackUp\SQL\123456.backup" testdb

还请注意，您切勿在 pg_dump 命令，因此您也需要考虑这一点。最佳实践是编辑

Also note, you never use the password in your pg_dump command, so you need to consider that as well. Best practice is to edit

%APPDATA%\postgresql\pgpass.conf

并添加

*:5432:*:username:password

自动执行脚本中的该部分：

to automate that part in your script:

@echo off
  echo *:5432:*:postgres:%Ech0-5910^&123>""%APPDATA%\postgresql\pgpass.conf"
  set "root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\"
  cd /d "%root%"
  pg_dump.exe -h 192.168.1.161 -p 5432 -U postgres -F c -b -v -f "D:\Backup\DatabaseBackUp\SQL\123456.backup" testdb

如果 dir 不存在，请创建它 ％APPDATA％\postgresql

If the dir does not exist, create it "%APPDATA%\postgresql"

这篇关于如何修复Postgres [Window]的自动备份脚本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！