How to Backup Your Amazon RDS for PostgreSQL Data and Restore on Your Own Server

Amazon Relational Database Service (Amazon RDS) is another service by Amazon, which allows you to deploy a scalable relational database in minutes in the cloud without the need to worry about infrastructure setup during the initial development phase. Amazon RDS supports popular databases, including PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server. This service not only allows you to create and deploy scalable databases but also facilitates easy and secure administration with AWS Management Console. This blog walks you through an overview of Amazon RDS for PostgreSQL, highlighting some of the exciting features and benefits. The next section explains how to take advantage of tools like Backup Ninja to manage Amazon RDS PostgreSQL backups to your preferred S3 storage.

Amazon RDS for PostgreSQL

PostgreSQL is a popular open source database used and also enhanced by over a million developers around the world. PostgreSQL database deployment does demand a high level of technical skills to manage. However, with Amazon RDS PostgreSQL, you get to spin up a PostgreSQL instance within minutes. The AWS Management Console allows you to configure database parameters, security, connectivity, and other settings in just a few clicks. You also get to have access to an enormous library of resources to help you in troubleshooting or configure advanced optional settings to meet your requirements. This service not only provides ease of management but also supports high availability and durability for minimal downtime. Amazon RDS for PostgreSQL also comes with monitoring capabilities, backup management, and security features which are common to all other Amazon RDS databases as well.

Managing PostgreSQL on Amazon RDS

AWS Management console or Amazon RDS APIs allows you to perform various functions to manage PostgreSQL on Amazon RDS effectively. Below are some of the commonly used API:

• CreateDBInstance: Used to create new DB instance, provision storage capacity, specify backup retention policy, and an option to setup for Multi A-Z deployment.
• ModifyDBInstance: This API is used to scale the available resources for the DB instance for load balancing.
• DeleteDBInstance: Delete or terminate a DB instance.
• CreateDBSnapshot: Create a snapshot of the DB instance for restoration when required.
• RestoreDBInstanceToPointinTime: This API allows to create a new DB instance from point-in-time backup or restore to any point within the retention period.

Amazon RDS PostgreSQL Backups Using Backup Ninja

Although there are APIs to take snapshots of the PostgreSQL database instance, you might want to keep your backup management on an entirely different platform as part of disaster recovery planning. There are many vendors offering backup as a service like Backup Ninja, Solarwind, Veeam, which makes backup management relatively easy and cost-effective.

Backup Ninja is a backup service for you to take backups for open source databases, files, and directories in an automated way. You get to configure different types of backups like incremental, partial, or full backups for leading open source databases like MySQL, MariaDB, Percona, TimescaleDB, MongoDB, and PostgreSQL at its native method using a simple web interface. Store the backups locally or on any cloud provider securely in your preferred region. Backup Ninja also allows you to monitor all your backups and backup events centrally.

Installing Backup Ninja Agent on Your Server

To schedule backups on Backup Ninja, you will need to install the agent on your backup server. This agent will then communicate with your database and take backups according to the backup schedule you have created. For security purposes, Backup Ninja does not store any security keys and uses advanced encryption algorithms to protect your backup. Following are the simple steps to install the Backup Ninja agent on your server.

Step 1: Set your backup target

In the first step, you need to decide the type of backup you would like to have. You can either choose to backup and restore or only the backup option where you can always edit and add in the restore option. Next, select the database technology and the version. The version selection is enabled when you select the database server type here.

Step 2: Creating a Database User

Backup Ninja requires a database user with the privilege to backup and restore the database. For a managed database, click on the checkbox below to use the existing user to manage the database backups. Fill in the hostname, which could be the endpoint or an IP address, and the password and proceed to the next step.

Step 3: Install the Backup Ninja Agent

To install the agent, you will need to copy and execute the installation script on the backup server.

Step 4: Server Discovery

Once the installation is complete, the server will be discovered automatically and displayed on the screen.

You will see the discovered server added to the list of servers in the Servers listing page.

The next sections show you how to schedule backups for a PostgreSQL database instance on Amazon RDS.

Scheduling Amazon RDS PostgreSQL Database Backup Using Backup Ninja

Step 1: Complete the schedule details.

Give a name for the backup schedule and select the server you want to backup. The backup method will list the backup tools installed in the server automatically. Backup type allows you to choose either a full or partial backup type. Select the pg_dump and partial backup type to backup the selected PostgreSQL database from Amazon RDS.

Step 2: Configure the backup storage.

Next select where you would like to store the backup. You can have a combination of local and cloud storage configured on the same screen. If you would like to add more storage locations, simply click on the Add New Storage button and fill in the next storage location. Retention here is the duration of how long you would want to keep the backup.

Step 3: Backup name, compression, and encryption setting

Give a name to your backup, or you can choose to leave it at the default setting. If you are concerned about the backup size, enable the compression toggle and the backup will be compressed using the gzip compression. You can also enable the encryption to have encrypted backups.

Step 4: Set the backup frequency.

The last step is to set the frequency of the backup task, and the backups will be scheduled to run according to the database server settings.

View the created schedule on the scheduled listing. Click on the play button to start or pause the schedule.

You can view the status of the scheduled backups on the Backup page. Here, the listing will show you the status of the backup, the storage location, and its size, which will be easier for you to retrieve at any point of time.

Conclusion

Backup is an integral part of disaster recovery planning; therefore, a specific service or tool to manage backups is an excellent option to have in any organisation. Amazon RDS has an API which takes backup snapshots for all database engines. Still, if you would like to have an alternative way to backup your PostgreSQL database from Amazon RDS, tools like Backup Ninja would be an ideal choice. With Backup Ninja, you can automate your database, files and directories backups to your preferred backup location. Also, Backup Ninja creates database backups using legacy tools which makes it easy for the backup migration to another platform at any time.