Frequently Asked Question

How to Auto back up PostgreSQL Database
Last Updated 4 months 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.

12set PGPASSWORD=pg_dump -h "" -U "" -f "" ""

Below shown the commands shown in the video with my credentials and db name.

12set PGPASSWORD=rootpg_dump -h "localhost" -U "postgres" -f "E:\\autobc\\hr_backup" "HR_DATA"
2. After running the command in command prompt,
image
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.
12set 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.
image

The bat file created will look like this,

image
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

image

5.2 Enter name for the task and click next.

image

5.3 Set the trigger, Set on what interval you need to execute the task.

image

5.4 Select start a program.

image

5.5 Browse the bat file and get the path for the bat file inside the create task wizard.

image

5.6 Click finish to create the task.

image

Now once the task is created. It will execute the bat file and the back up will be created automatically.

Please Wait!

Please wait... it will take a second!