Backups can be used to keep a copy of important files and store it safely on the local system, external hard disk drive, or other storage device. These copies can be used to restore the original file if it is lost during any incident or deleted by mistake. PostgreSQL allows the user to create a backup of the databases created on its server and then restore it in case of any emergency.

This guide will explain how to backup and restore the PostgreSQL database using the command line.

How to Backup and Restore PostgreSQL Databases Using Command Line?

To create a backup and then restore PostgreSQL databases using the command line, open the terminal from Windows:

Type the following command to head inside the “bin” directory of the PostgreSQL folder from the local system:

cd C:\Program` Files\PostgreSQL\15\bin

Running the above code will direct the user inside the selected directory:

Inside the bin directory, use the following command to create a backup of the PostgreSQL database:

pg_dump -U postgres -d JOIN > C:\Users\Lenovo\Desktop\JOIN.sql

The above code uses the pg_dump keyword to extract the databases into a scripting file from the PostgreSQL server. After that, the username of PostgreSQL and the name of the database to be backed up in the local system. The “>” is followed by the path of the directory from the local system where the backup file will be stored on the computer:

The backup file has been stored on the local system at the provided location:

Use the following command to connect to the PostgreSQL user:

psql -U postgres

Executing the above query will prompt the user to enter the password to connect to the user:

After connecting to the “postgres” user, use the following command to remove a PostgreSQL database:

DROP DATABASE "JOIN";

Running the above code will drop/delete the database from the Postgres user:

After deleting the “JOIN” database from the PostgreSQL server, create another database by typing this query:

CREATE DATABASE "JOIN";

A new PostgreSQL database has been created:

Use the following query to restore the File from the local directory:

pg_restore --dbname=JOIN --verbose C:\Users\Lenovo\Desktop\JOIN.sql

The above query contains the pg_restore keyword which is used to restore the database using the backed-up file. Type the name of the database and then enter the path of the backup file from the local system:

The database has been restored successfully as displayed in the screenshot below:

That’s all about backing up and restoring PostgreSQL databases using the command line.

Conclusion

To back up and restore PostgreSQL databases using the command line, open the terminal from Windows and head into the PostgreSQL directory. Create a backup of the database using the “pg_dump” command and store it in the local directory on the computer. After that, delete/drop the PostgreSQL database and then create another database to get the restored files in. Use the “pg_restore” command to restore the file using the backed-up file stores in the local system.