How to Backup and Restore the PostgreSQL Database Via CLI

Learn how to backup and restore the Postgres database via CLI. Our PostgreSQL Support team is here to help you with your questions and concerns.

How to Backup and Restore the PostgreSQL Database Via CLI

Regular Web Help Desk (WHD) database backups play an important role in ensuring data integrity and disaster recovery. Whether we are running Web Help Desk on Windows, Linux, macOS, or a Virtual Appliance, today we will learn how to back up and restore the PostgreSQL database for both PostgreSQL 9 and PostgreSQL 13 environments.

How to Back Up the PostgreSQL Database on a Windows Server

1. First, open a command prompt and head to the Web Help Desk installation directory:

   cd C:\Progra~1\WebHelpDesk
2. Then, run the command as per the PostgreSQL version:
   • PostgreSQL 9:

     pgsql9\bin\pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U whd [database_name] > whd_pgdump.backup
   • PostgreSQL 13:

     pgsql13\bin\pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U [username] [database_name] > whd_pgdump.backup
3. Now, enter the database password when prompted. The default username for PostgreSQL 9 is `whd`.
   The `whd_pgdump.backup` backup file will be saved in `C:\Progra~1\WebHelpDesk`.

Working with cloud-hosted Postgres? Check out our guide on backing up a PostgreSQL database on DigitalOcean.

How to Back Up the PostgreSQL Database on Linux or Virtual Appliance

1. To begin with, use SSH to connect to the Linux server. If we are using a virtual appliance, log in as admin, then switch to root:

   sudo su
2. Then, go to the Web Help Desk directory:

   cd /usr/local/webhelpdesk/
3. Now, run the backup command:
   • PostgreSQL 9:

     pgsql9/bin/pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U whd whd > whd_pgdump.backup
   • PostgreSQL 13:

     pgsql13/bin/pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U [username] [database_name] > whd_pgdump.backup
4. Then, enter the password when prompted. The backup will be saved as `whd_pgdump.backup` in `/usr/local/webhelpdesk/`.

If you’re managing Postgres within containerized environments, you might also want to learn how to back up and restore PostgreSQL on Kubernetes.

Back Up the Database on macOS

1. Go to Applications > Utilities and open a terminal.
2. Then, go to the Web Help Desk Directory:

   cd /Library/WebHelpDesk/
3. Now, run the backup command according to the version:
   • PostgreSQL 9:

     pgsql9/bin/pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U whd whd > whd_pgdump.backup
   • PostgreSQL 13:

     pgsql13/bin/pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U [username] [database_name] > whd_pgdump.backup

   The backup will be created in `/Library/WebHelpDesk/`.

How to Restore the PostgreSQL Database on Windows Server

Here’s how to restore your backup file to your Web Help Desk environment. If you’re restoring from raw .sql or custom-format files, this step-by-step guide to restoring PostgreSQL from files will come in handy.

1. First, open a command prompt:

   cd C:\Progra~1\WebHelpDesk
2. Then, run the restore command as per the version:
   • PostgreSQL 9:

     pgsql9\bin\pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
   • PostgreSQL 13:

     pgsql13\bin\pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
3. Now, use the `–clean` flag to drop existing data before restoring:

   pgsql13\bin\pg_restore --clean -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
   
   

How to Restore the Database on Linux or Virtual Appliance

1. First, connect and go to:

   sudo su
   
   cd /usr/local/webhelpdesk/
   
   
2. Then, run the restore command as per the version:
   • PostgreSQL 9:

     pgsql9/bin/pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
   • PostgreSQL 13:

     pgsql13/bin/pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
     
     
   • To replace existing data:

     pgsql13/bin/pg_restore --clean -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup

For enterprise environments, managing long-term retention policies is key. Learn how Azure PostgreSQL handles long-term backup retention to keep your data secure over time.

Restore the Database on macOS

1. First, open a terminal:

   cd /Library/WebHelpDesk/
2. Then, run the restore command as per the version:
   • PostgreSQL 9:

     pgsql9/bin/pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
     
     
   • PostgreSQL 13:

     pgsql13/bin/pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
     
     
3. To overwrite existing data, run:

   pgsql13/bin/pg_restore --clean -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backup
   
   

Bonus Tips

• Schedule regular backups using a task scheduler or cron job.
• Keep copies of the backups in a secure, off-site location.
• Periodically test the restore process to ensure data reliability.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

Backing up and restoring the Postgres database in the Web Help Desk doesn’t have to be complicated. By running the commands according to the OS and PostgreSQL version, we won’t have to worry about data loss.

In brief, our Support Experts demonstrated how to backup and restore a Postgres backup database via CLI.