English
How to Backup and Restore Postgres Pro in 2 Ways?
2023-03-23 | Dan Zeng
Postgres Pro is a relational database management system (RDBMS) based on open-source PostgreSQL. It is designed to provide enterprise-class features and performance, including advanced security and scalability options while maintaining compatibility with PostgreSQL. Postgres Pro is developed and maintained by a team of database experts at Postgres Professional, a leading company in the PostgreSQL community.
Data backup is a safety net that ensures the data is always accessible and secure. In this article, we’re going to talk about how to back up and restore Postgres Pro easily in 2 ways. Without further ado, let’s get started.
Backup and Restore Postgres Pro Using Pg-probackup
Pg-probackup is a tool used to handle the backup and recovery process of PostgreSQL and Postgres Pro database clusters. It is compatible with PostgreSQL 9.6, 10, 11, 12, 13, 14, and 15.
You can perform full, page-level incremental backup and restore, validate backups, and compress and deduplicate data with it. You can also backup and restore a remote instance and restore partially specified databases.
But note that pg-probackup can take only physical online backups and Windows systems cannot use the remote mode. It also requires the database server for backup and restore must be compatible with the block_size and wal_block_size parameters and has an identical major release number.
1. Install pg-probackup.
On Windows: install from the release page.
On Ubuntu/Debian (Postgres Pro Standard and Enterprise):
sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup-forks/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" > /etc/apt/sources.list.d/pg_probackup-forks.list'
sudo wget -O - https://repo.postgrespro.ru/pg_probackup-forks/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update
sudo apt-get install pg-probackup-{std,ent}-{14,13,12,11,10,9.6}
sudo apt-get install pg-probackup-{std,ent}-{14,13,12,11,10,9.6}-dbg
On RHEL (Postgres Pro Standard and Enterprise):
rpm -ivh https://repo.postgrespro.ru/pg_probackup-forks/keys/pg_probackup-repo-forks-rhel.noarch.rpm
yum install pg_probackup-{std,ent}-{14,13,12,11,10,9.6}
yum install pg_probackup-{std,ent}-{14,13,12,11,10,9.6}-debuginfo
2. Initialize the backup catalog to store all WAL and backup files.
pg_probackup init -B backup_path_to_the_empty_catalog
3. Add a new backup instance to the backup catalog.
pg_probackup add-instance -B backup_directory -D data_dir --instance instance_name [remote_options]
4. Create another database role with the minimum permissions.
5. Create a database backup.
pg_probackup backup -B backup_dir --instance instance_name -b backup_mode
Create a full backup under the default WAL delivery Archive mode.
pg_probackup backup -B backup_dir --instance instance_name -b FULL
Create a full backup under the optional WAL delivery Stream mode.
pg_probackup backup -B backup_dir --instance instance_name -b FULL --stream --temp-slot
6. Validate the database backup for recovery.
pg_probackup validate -B backup_dir --instance instance_name --recovery-target-xid=transaction_ID
7. Restore the database cluster.
pg_probackup restore -B backup_dir --instance instance_name -i backup_id
8. Perform an incremental restore to replace only the invalid and modified pages in an existing directory.
pg_probackup restore -B backup_dir --instance instance_name -D data_dir -I incremental_mode
9. Recover the designated database only.
pg_probackup restore -B backup_dir --instance instance_name --db-include=database_name
10. Exclude database(s) from the recovery task.
pg_probackup restore -B backup_dir --instance instance_name --db-exclude=db1 --db-exclude=db2
Backup and Restore Postgres Pro Using Vinchin Backup & Recovery
Pg-probackup is a multi-functional database backup utility for PostgreSQL and Postgres Pro. But it has known limitations and certain prerequisites for installation and usage. You can make the backup and recovery for both PostgreSQL and Postgres Pro easier.
Vinchin Backup & Recovery automates data backup for 6 databases including Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, 12 virtualizations like VMware, Hyper-V, Xen, RHEV/oVirt, Linux & Windows servers, and NAS.
Manage various backups and tasks centrally in one web console.
Make scheduled data backup automatically.
Offer full, differential, incremental, and transaction log backups varying with databases.
Deduplicate and compress 50% of the database backup size.
Restore the database to the original path or a new destination.
Encrypt data with the AES-256 standard and the transfer path with SSL.
Reject any suspicious access with the IO monitor in real-time.
Download the 60-day free trial to protect your database comprehensively now:
Postgres Pro backup:
1. In the Vinchin console, go to Physical Backup> Database Backup> Backup, and select an installed database agent on the left pane and the backup instance on the right.
2. Choose the target node and storage available. You can add your NAS storage to Vinchin software and backup the data to your NAS here.
3. Set up the backup strategies as you like.
4. Review and complete. The job will run by the specified time, or start immediately by clicking the Options> Start Full/ Start Incremental based on your choice.
Postgres Pro recovery:
1. Go to Physical Backup> Database Backup> Restore, select a restore point from backups.
2. Tick in the box of the target instance. 
3. Choose to restore to the original or a new path, enter the temporary directory and transmission network, and put the speed limit optionally. 
4. Review and complete. The job will run at the specified time.
Conclusion
This article introduced 2 ways to backup and restore Postgres Pro, which is a backup utility pg-probackup, and a professional database backup solution Vinchin Backup & Recovery. Choose one based on your specific needs, and if you prefer an intuitive and simple solution, then Vinchin is definitely the go-to choice for you.
Share on:
