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\WebHelpDeskCopy Code
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.backupCopy Code
   • PostgreSQL 13:

     pgsql13\bin\pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U [username] [database_name] > whd_pgdump.backupCopy Code
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 suCopy Code
2. Then, go to the Web Help Desk directory:

   cd /usr/local/webhelpdesk/Copy Code
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.backupCopy Code
   • PostgreSQL 13:

     pgsql13/bin/pg_dump -Fc -v -h 127.0.0.1 -p 20293 -U [username] [database_name] > whd_pgdump.backupCopy Code
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/Copy Code
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.backupCopy Code
   • PostgreSQL 13:

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

   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\WebHelpDeskCopy Code
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.backupCopy Code
   • PostgreSQL 13:

     pgsql13\bin\pg_restore -Fc -v -h 127.0.0.1 -p 20293 -U [username] -d [database_name] path_to_whd_pgdump.backupCopy Code
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
   Copy Code

How to Restore the Database on Linux or Virtual Appliance

1. First, connect and go to:

   sudo su
   cd /usr/local/webhelpdesk/
   Copy Code
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.backupCopy Code
   • 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
     Copy Code
   • 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.backupCopy Code

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/Copy Code
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
     Copy Code
   • 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
     Copy Code
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
   Copy Code

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.