Cobra Softwares Blog

Follow the steps below to install the PostgreSQL client on APT-based systems such as Ubuntu and Debian:

1. Update the APT package index.
   console

   $ sudo apt install -y postgresql-client
   

2. Install the PostgreSQL client.
   console

   $ sudo apt install -y postgresql-client
   
   Install the PostgreSQL client on Rocky Linux and AlmaLinux

   Run the following command to install the PostgreSQL client on RPM-based distributions like Rocky Linux and AlmaLinux:

   console

   $ sudo dnf install -y postgresql
   
   Install PostgreSQL client ON Windows
   
   Visit the PostgreSQL download page and download the latest PostgreSQL client compatible with your Windows system. The installer includes pg_dump, pg_restore, and other command-line tools.

   Backup the PostgreSQL Database

   You can use the pg_dump and pg_dumpall utilities to create backups of individual databases or all databases on a PostgreSQL server. Below is the general syntax for pg_dump:

   pg_dump <options> --host=<database-server-host> --port=<port> --username=<user> --dbname=<database-name> -f <database-name>.dump

   1. To back up a specific PostgreSQL database, use the following command. Replace the placeholder values as needed.
      console

      $ pg_dump -Fd -v --host=db.example.com --port=5432 --username=db_user --dbname=example_db -f example_db.dump
      

      In the above command:

      • -Fd: Specifies the directory format for the output (you can use -Fc for a compressed custom format).
      • -v: Enables verbose output.
      • -f: Specifies the output file or directory.
   2. To back up all databases on the PostgreSQL server, use the pg_dumpall utility.
      console

      $ pg_dumpall -v --host=db.example.com --port=5432 --username=db_user > full-backup.sql
      

      Note

      pg_dumpall only supports plain SQL format. For custom formats or parallel dumps, use pg_dump individually per database.

   3. After the backup completes, verify that the backup file or directory exists in your working location.
      console

      $ ls
      

   Restore the PostgreSQL Database

   You can use pg_restore or psql to restore PostgreSQL database backups, depending on the format of your backup. Below is the general syntax for pg_restore.

   pg_restore --host=<database-server-host> --port=<port> --username=<user> --dbname=<database-name> <database-name>.dump

   1. Connect to your PostgreSQL server using the psql client.
      console

      $ psql --host=db.example.com --port=5432 --username=db_user --dbname=postgres
      

   2. Create a new target database with the same name as the source database.
      psql

      postgres=# CREATE DATABASE example_db;
      

      Note

      If you’re restoring a single database backup, you must manually create the target database before running pg_restore. For full backups using pg_dumpall, this step is not necessary as all databases are recreated automatically.

   3. Exit the PostgreSQL prompt.
      psql

      postgres=# \q
      

   4. Restore a single database using pg_restore.
      console

      $ pg_restore --host=db.example.com --port=5432 --username=db_user --dbname=example_db backup.dump
      

   5. To restore all the databases from a pg_dumpall full backup, use the psql utility.
      console

      $ psql --host=db.example.com --port=5432 --username=db_user -f full-backup.sql
      

      In this above command:

      • -f: Specifies the SQL file to restore.
      • --username: A superuser is typically required to restore roles and permissions.
      • This command restores all databases and roles as they existed during the backup.
      Note

      Ensure the target PostgreSQL database server is empty or does not contain conflicting roles or databases before restoring a pg_dumpall backup.

   6. After the database restoration is complete, connect to the PostgreSQL server and switch to the restored database to verify the restoration.
      psql

      postgres=# \c example_db
      

   7. List all the tables to confirm that the restoration is completed.
      psql

      postgres=# \dt
      

   8. Verify that table data is available.
      psql

      postgres=# SELECT * FROM app_users;
      

      Replace app_users with the table name present in your database.

   9. Exit the PostgreSQL client.
      psql

      postgres=# \q
      

   Conclusion

   In this article, you learned how to back up and restore PostgreSQL databases using the pg_dump, pg_dumpall, and pg_restore utilities. These tools offer a reliable way to export individual databases or entire PostgreSQL clusters for migration, testing, or disaster recovery. You installed the PostgreSQL client, performed single and full database backups, and restored data on a target server using both custom and plain SQL formats.