Frequently Asked Question
How to Auto back up PostgreSQL Database
Last Updated 2 years ago
1. First create batch file with the commands to back up the pg database.
Below shown the commands to back up a database. Please update with the details with your credentials.
| 12 | set PGPASSWORD=pg_dump -h "" -U "" -f "" "" |
Below shown the commands shown in the video with my credentials and db name.
| 12 | set PGPASSWORD=rootpg_dump -h "localhost" -U "postgres" -f "E:\\autobc\\hr_backup" "HR_DATA" |
2. After running the command in command prompt,
3. To create separate back up file, You can use the below command. It will create separate back up file with file name as current date and time.
| 12 | set PGPASSWORD=rootpg_dump -h "localhost" -U "postgres" -f "E:\\autobc\\hr_backup_%date:~-4%_%date:~3,2%_%date:~0,2%__%time:~0,2%_%time:~3,2%_%time:~6,2%" "HR_DATA" |
4. Now create a bat file using notepad with the above commands. Go to save as and save it as .bat extension.
The bat file created will look like this,
5. Now create a task scheduler in windows to open this bat file periodically so that it will create the back up in the location specified. Below mentioned the step by step process to create the task
5.1 Open task scheduler, Click on create basic task
5.2 Enter name for the task and click next.
5.3 Set the trigger, Set on what interval you need to execute the task.
5.4 Select start a program.
5.5 Browse the bat file and get the path for the bat file inside the create task wizard.
5.6 Click finish to create the task.
Now once the task is created. It will execute the bat file and the back up will be created automatically.