pg_dump and pg_restore are command-line utilities for backing up and restoring PostgreSQL databases. The tar format is one of the non-plain text archive formats that pg_dump can produce and which pg_restore is designed to handle.

Backing Up with pg_dump (TAR format) 

To create a backup in the tar format, use the -Ft (format tar) option. You should also typically use the -b option to include large objects, as they are not included by default in older versions.

bash

pg_dump -U username -h hostname -p port -Ft -b dbname -f backupfile.tar

• -U username: Specifies the database username.
• -h hostname: Specifies the host where the database server is running.
• -p port: Specifies the port number.
• -Ft: Specifies the output format as tar archive.
• -b: Includes large objects in the dump.
• dbname: The name of the database to back up.
• -f backupfile.tar: The output file path.

Restoring with pg_restore (from TAR format) 

pg_restore is used to restore a database from an archive file created by pg_dump using one of the non-plain-text formats (custom, directory, or tar). The target database must already exist before running pg_restore.

bash

pg_restore -U username -h hostname -p port -d dbname backupfile.tar

• -d dbname: Specifies the name of the database to connect to and restore data into.
• backupfile.tar: The input archive file path.

Useful pg_restore options:

• –verbose: Provides detailed information about the restoration process.
• –clean: Cleans (drops) existing database objects before re-creating them.
• –no-acl –no-owner: Prevents the restoration of access privileges (ACLs) and object ownership, which can be helpful when restoring to a different environment with different roles.

After the restore is complete, it is wise to run ANALYZE on the database to ensure the optimizer has current statistics for good performance.

Backup ( dump ) & Restore Postgre Sql dengan format DUMP

alternatif lain pg_dump dan pg_restore yang aman menggunakan opsi -Fc dan restore menggunakan opsi –no-owner dan –no-privileges

pg_dump -h localhost -U username -Fc nama_database -f nama_file.dump dan restore dengan

pg_restore -U postgres -d nama_database_baru --no-owner --no-privileges nama_file.dump