Heroku PostgreSQL
How to backup Managed Heroku PostgreSQL databases via SimpleBackups
Follow these steps to create backups for a Heroku-managed PostgreSQL database using SimpleBackups. The process is designed to be straightforward, ensuring that your Heroku PostgreSQL database is securely backed up.
How to backup your Heroku database
Prerequisites
- A SimpleBackups account.
- Access to your Heroku Postgres service database
- Network access from SimpleBackups to your Heroku PostgreSQL database.
Step 1: Create a Backup User in Heroku
- Log in to your Heroku console.
- Navigate to the Datastores list
- Click on The database you wish to backup
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2F1cc2c67e-f775-4590-b005-985950b174e7%2FScreenshot_2024-05-06_at_3.34.32_AM.png?table=block&id=01c8becc-d771-43b2-96cd-cd5d8fd9b8a5&cache=v2)
- Click on the Settings tab and then on View Credentials…
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2Fb54c873a-7653-46a3-b66d-6d792846be50%2FScreenshot_2024-05-06_at_3.35.26_AM.png?table=block&id=92af49c1-aac3-434b-80f2-5d4cd77d4644&cache=v2)
- Copy the URI
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2F62a59935-e326-41ee-acd8-ac2427b632f0%2FScreenshot_2024-05-06_at_3.36.12_AM.png?table=block&id=71d9ec2e-aa31-4de5-a731-d3e4e69528ff&cache=v2)
Step 2: Create a New Database Backup on SimpleBackups
- Navigate to the "Backups" section and click on "Create Backup."
- Select "Database Backup."
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2F266449cf-9ed9-438c-a060-b6facb03002f%2FScreenshot_2024-05-06_at_3.37.06_AM.png?table=block&id=d7856e2a-5ac4-4136-bfa1-baaef2d4a963&cache=v2)
- You may use your own server to dump the backups, but we recommend the “serverless” option.
Step 3: Configure Database Backup Settings
- Choose PostgreSQL as the database type.
- Click “Paste connection string instead”
- Fill in the complete connection string for your Heroku Postgres database (make sure the password is added as well and not the asterisks *****)
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2Fc18e9eb4-b38f-47ad-a472-eb07a8baf803%2FScreenshot_2024-05-06_at_3.37.45_AM.png?table=block&id=6fc79cf5-42ab-413e-a84c-cc7740b91787&cache=v2)
- Click “Validate Connection”
Step 4: Schedule and Customize Your Backup
- Set a schedule for your backups according to your needs (e.g., daily, weekly).
- Customize your backup settings, such as retention policies and storage.
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2F3feb7fdd-662e-410c-b68e-a3cf05c344c2%2FScreenshot_2024-05-06_at_3.38.24_AM.png?table=block&id=141e8338-218f-4202-9334-e9a6e33eea6a&cache=v2)
Step 5: Save and Activate the Backup
- Review your backup configuration to ensure all details are correct.
- Save your backup configuration. SimpleBackups will automatically start the backup process according to your schedule.
By following these steps, you can ensure that your Heroku-managed PostgreSQL database is securely backed up, protecting your data against accidental loss or corruption.
Restoring backups
In order to restore your backup, Go to your backup page, then navigate into logs.
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2F7f16714f-448c-4891-b9ce-12960a4b41cc%2FScreenshot_2024-05-06_at_3.39.39_AM.png?table=block&id=518b803b-3a93-474a-97c4-3eb3d726ca03&cache=v2)
Click on the “i” button on the backup that’s on the point of time you’d like to restore to.
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2Fd2202d19-a206-4a9b-aafb-d27effd43e7f%2FScreenshot_2024-05-06_at_3.40.36_AM.png?table=block&id=ab042437-a5f4-49ab-9868-f10cd7ac019e&cache=v2)
Click on the Restore tab, then generate a new download link, and copy it.
![Notion image](https://www.notion.so/image/https%3A%2F%2Fprod-files-secure.s3.us-west-2.amazonaws.com%2F877bbb47-2797-4f53-a4ed-4585ee282117%2F7a3108f8-02df-4950-a244-166464c88ecf%2FScreenshot_2024-05-06_at_3.42.51_AM.png?table=block&id=0993b329-09df-4ebd-8cba-65ae75aa0617&cache=v2)
Run the following command
wget -O - "PasteTheSignedDownloadLinkHereBetweenTheQuotes" | gunzip -c > decrypted-backup.pgsql
Then, run the pg_restore
command to restore your database like so:
pg_restore -d "your_db_uri" ./decrypted-backup.pgsql
pg_restore
updated to the latest version to avoid facing restore issues. Last updated on May 6, 2024