Introduction To PostgreSQL Backups With PSQL, pgAdmin & Heroku

A how-to guide for primary PG backups and moving data from a production environment to local

We often want accurate test data for our local environments when developing new features or fixing bugs. There are many ways to accomplish this goal. One of our favorite ways at Advisory Alpha is using Heroku’s PG:PULL command or running a pg_dump backup using pgAdmin. We use that command to pull the entire database to our local environment. Let’s walk through our flow for removing data from a specific table from our Staging and Production environments to our local systems.

Technology this tutorial will utilize

1. Heroku (Not required)
2. PostgreSQL
3. PSQL
4. pgAdmin

Assumptions

This tutorial assumes you already have pgAdmin installed and PostgreSQL installed.

STEP 1: Hooking up Postgres database to pgAdmin

As mentioned above, we use Heroku for application deployment and configuration. Heroku provides an excellent SQL database service called “Heroku Postgres” — It gives CLI commands and a dashboard for query visualization and database management. This is where we will look; first, we need to find our database credentials and use those to sync with pgAdmin.

Here is the step-by-step process we take at Advisory Alpha to connect a Heroku Postgres DB to pgAdmin:

1. Login to Heroku, navigate to the desired application, and open the “Heroku Postgres” addon:

2. Once on the Heroku Postgres dashboard, navigate to the “Credentials” tab:

Credentials page for Heroku Postgres

3. Sign in to pgAdmin, create a server group, then create a server within the newly created server group; you should see this modal:

4. Input the corresponding credentials from Heroku in the pgAdmin fields. Once correctly configured, save the new database.

5. Open the database folder, open “schemas,” open “public,” and then find the database table you want to backup:

6. Give the backup a filename, set the format to “plain,” navigate to the “data/objects” tab, and configure the backup to only include the necessary files:

7. Once you save that backup to a file location, you are now ready to copy that to your local database.

Step 2: Using PSQL locate the backup and set the local database

PSQL is a terminal-based front-end to PostgreSQL. It’s a great tool with so many features; I have not scratched the surface of the capability, but I usually use a GUI to interact with Postgres, but PSQL is capable of all the things pgAdmin is. I do recommend looking up PSQL and referencing the documentation here.

1. Open PSQL from the console. Open it in the local database to which you want to copy the backup. My local database has the name ‘advisor-squared_development’ here is how I access my local database from PSQL:

› psql -d advisor-squared_development
psql (14.6 (Homebrew))
Type "help" for help.

advisor-squared_development=#

2. Use \i and locate the backup file like so:

advisor-squared_development=# \i ../../../Desktop/test

3. The file I saved the backup to was on the desktop under “test” — As you can see in the example above, this will copy the backup to my local database.

4. Press enter and the table will now be updated to your local database instance.

Wrapping Up:

That was a quick rundown of how we handle backups and pull data from a specific environment to our local databases. There are, of course, many ways to do this. If you want to remove the entire database from one domain to another, your best bet is Heroku pg:pull — but if you want to backup a specific table the tutorial above is a good option.

Resources:

1. https://www.postgresguide.com/utilities/psql/
2. https://devcenter.heroku.com/articles/managing-heroku-postgres-using-cli#pg-push-and-pg-pull
3. https://www.postgresql.org/docs/current/app-pgdump.html