How to backup PostgreSQL database

In this PostgreSQL tutorial, today we will learn about “How to backup PostgreSQL database“. It is a common problem that people face. We will see here different ways to backup the PostgreSQL database.

• How to backup postgresql database in windows
• How to backup postgresql database in linux
• How to backup postgresql database in ubuntu
• How to backup postgresql database using pgadmin 4
• How to backup postgresql database automatically
• How to backup postgresql database using pg_dump
• How to backup postgresql database command line
• How to backup postgresql database using pgadmin 3

Sometimes our computer gets crashed or we need to move your data from one computer to another or maybe your computer gets corrupted like we want to move the database from the USA to Cannada location. Our important data will be lost so in that case if we have a backup, we can restore that data.

How to backup postgresql database in windows 10

• In Windows OS, to get a backup of your PostgreSQL database you need to know about a utility in Postgresql is pg_dump.
• pg_dump is a utility and its work is to extract a database into a text file with SQL commands, that are fed to any other version of Postgres or a different configuration of machines will restore the database in the same state when the database was created.
• Now it is time to work on some examples.

pg_dump dbname > outfile
       or
pg_dump dbname > file.sql

We can also provide a user name and password like below:

pg_dump -U username dbname > outfile
       or
pg_dump -U username dbname > file.sql

if you want to use another database server then use command-line options -h ( for a host ) and -p ( for a port of that host ) with pd_dump, here the default host is the localhost.

• pg_dump: a utility program for backup of database
• dbname : Name of the database that you want to backup
• outfile : It is text/script file which is generated by pg_dump or backup file

1. In windows, press Windows key + R and type cmd, and press enter.
2. In command prompt type psql -U username, If it asks for a password then enter the password.
3. To check available databases type \l

4. Suppose you want to backup the dvdrental database, type the following command and replace the parameter according to your username, database, and path.

pg_dump -U postgres -d dvdrental > E:\Backup\dvdrental.sql

5. Go to the directory that you have mentioned for the output of the backup file.

• Now, you have successfully backup your PostgreSQL database.
• Remember you can always change the output format of your backup file, Here you have a backup file in SQL format.
• So if you need backup in another format, it’s easy all you need to do is provide the option -F with c for custom format archive file, d for directory format archive and t for tar fromat archive.

pg_dump -F c -U postgres -d dvdrental > E:\Backup\dvdrental

In the above code, it will output in a custom format archive.

Read: PostgreSQL Export Table to CSV

How to backup postgresql database in ubuntu and linux

If you are a Unbuntu or Linux user follow these steps.

1. open your terminal and and swtich over account using given command that you have created like default user is postgres.

sudo -i -u name_of_user
        or
sudo -i -u postgres

2. Enter psql to access Postgresql prompt, from here you can access the databases and type \l to show available databases, look for a database that you want to backup.

3. Now look at available databases, Suppose you want to backup the Postgres database from the above databases.

• All you need to do is type.

pg_dump dbname > outfile
       or
pg_dump dbname > file.sql

• I have explained this command in the first sub-section “ How to backup postgresql database in windows 10 ” of this tutorial, Go read about it and then come back here.
• Please exit from postgresql prompt by typing exit or login into postgrsql account using “sudo -i -u postgres“.
• Create a backup of postgres databse and type ls command to look for created backup of database in current directory .

pg_dump -d postgres > postgres.sql

• Now you know “How to create a backup in ubuntu environment”.
• Remember, if you are using linux terminal whether kali, ubuntu or other operating systems and if it is based on linux then you can perform same command for all the operating systems to get backup of databases.

Read PostgreSQL TO_TIMESTAMP function

How to backup postgresql database using pgadmin 3 and 4

• It is easy to take backup using pgadmin in PostgreSQL, As I told before postgresql uses pg_dump utility to provide backup of a database in different format whether you are using command-line, Linux terminal, or pgadmin graphical user interface ( GUI ).
• In all ways whether explicitly or implicitly, it uses pg_dump.

Please follow the below step to take a backup of the database.

1. open pgadmin using windows or linux search bar.

2. Enter the password if asks for it.

3. Select the database that you want to backup, click the right button and click on the option Backup.

4. A backup dialog appears after clicking on the backup option in above step 3.

• Filename: Where the path and name of the file are entered.
• Format: Postgres supports many formats for backup of databases like custom format, tar format, Plain format, and directory.

Select a custom format and click on Backup.

5. Go to the directory where backups are saved and you will get a file in a custom format.

Now, You have successfully backup.

Read: PostgreSQL drop all tables

How to backup postgresql database automatically

Before knowing how to back up the PostgreSQL database automatically, let us try to understand why do we need to make the backup process automatic.

• Sometimes you want to save time and effort or don’t want to do the same task, again and again, like manually.
• To make a backup automatically, I am going to explain two ways.
• One is for “Windows” users and the other you can guess “Linux“.

For Windows, you need to know two things, first is about the batch script and if you know about the bat file, then it is good to know.

I am going to define batch file definition from Wikipedia “A batch file is a script file in DOS, OS/2, and Microsoft Windows. It consists of a series of commands to be executed by the command-line interpreter, stored in a plain text file.“

In simple words what you need to do is open a notepad or any text editor application and write line by line command and save that file with .bat extension, run the file in command prompt.

The second thing is about the application in Windows “Task Scheduler” and don’t worry about it, I will explain you in a very easy step.

1. Go to location where Postgresql installed on your system C:\Program Files\PostgreSQL\13

2. Create a batch file using notepad, open notepad and write the code given below in the file and save the file name with postgre.bat.

@echo off
   for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
     set dow=%%i
     set month=%%j
     set day=%%k
     set year=%%l
   )
   set datestr=%month%_%day%_%year%
   echo datestr is %datestr%
    
   set BACKUP_FILE=postgresdata_%datestr%.backup
   echo backup file name is %BACKUP_FILE%
   SET PGPASSWORD=12345
   echo on
   bin\pg_dump -U postgres -d postgres > D:\Backup\%BACKUP_FILE% 

3. Now you have completed 50% of the work and your first step completed.

• Here I am going to explain the second thing “Task Scheduler“, So “What is this?” task scheduler is a tool or utility that you can use to create actions or tasks which will run automatically when certain conditions are met.
• Generally, it is used for maintenance purposes like disk cleaning, updates, backup, etc.
• So we take postgres.bat file and schedule the backup of the database using Task Scheduler, if you want to backup every morning or evening and night, you can schedule according to your need.

4. Open Task Scheduler, press Windows + R keys, and type taskschd.msc in the box, and press enter. and expand Task Scheduler Library.

5. create a new folder under Task Scheduler Library by right-clicking and name it “PostgreScript“.

6. Select the ( A )PostgreScript folder, go to ( B )Actions Panel, and click on ( C )Create Basic Task…

7. Click on Create Basic Task and enter the name of Action “Postgre” then click Next.

8. Define Task Trigger “When do you want to start the task” like daily, everyday and monthly, etc.

And after defining the trigger click on the Next button.

9. If you have selected a daily trigger then set the start date and time for the task means your task will start at that day and time.

10. Select Action or “What action do you want to perform the task?“

Then select Start a program from the given actions.

11. Select the location of a batch file that you have created to get a backup of the database.

12. Look at the summary of the scheduled task that you have defined, if it is ok then click on Finish otherwise make changes according to your need.

Now you have successfully created a task that will execute daily at 14:50 every day for the backup of the database.

---

For Linux users, you need to know about Crontab, with the help of crontab you can take backup of your databases at a specific time automatically but “What is the Crontab?”.

• Crontab is a command that allows opening cron table which contains a list of predefined tasks to run at regular time intervals.
• So you will create a new file with the help of crontab and in that file, we are going to define the task as backup and that file will execute at a given time every day or every month whatever the time you set.

Open your terminal and type the given command to create a task and hit enter, it will open the crontab file for editing.

crontab -e

• The question is “How crontab works”, crontab -e command opens the crontab file which is a simple text file containing a list of predefined tasks and these predefined tasks are executed by the cron daemon in the background at right time.

Syntax or Format :

MIN HOUR DOM MON DOW CMD

Field    Description    Allowed Value
MIN        Minute              0 to 59
HOUR    Hour                 0 to 23
DOM      Day of Month      1-31
MON      Month                   1-12
DOW     Day Of Week         0-6
CMD      Command         Any command to be executed.

• Save the file by pressing CTRL + O and press Enter
• The backup will performed on 12:02 everyday.

You may also like reading the following articles.

• Postgres RegEx
• PostgreSQL DATE_PART() Function
• Postgresql escape single quote
• PostgreSQL DROP TABLE
• PostgreSQL DROP COLUMN
• How to import CSV file into PostgreSQL

So in this tutorial, we have covered and learned lots of things about “how to backup PostgreSQL database” in different environments and also in different ways.

We have covered the following topics :

• How to backup postgresql database in windows
• How to backup postgresql database in Linux
• How to backup postgresql database in ubuntu
• How to backup postgresql database using pgadmin 4
• How to backup postgresql database automatically
• How to backup postgresql database using pg_dump
• How to backup postgresql database command line
• How to backup postgresql database using pgadmin 3