How to Backup and Restore Your Postgres Database

Aug 31, 2021

In this post, we will go over the process of backing up and restoring your Postgres database hosted on Railway.

PostgreSQL, also known as Postgres, is a free and open-source relational database. It's a popular choice for applications big and small: and you can deploy it for free as a plugin on Railway.

Getting Started

If you haven't already, you will need to have psql installed. psql is going to be the command line interface that we will be using to interface with our Railway Postgres database. This includes pg_dump and pg_restore the two commands we will be using.

How to Install PSQL CLI

MacOS: Those on MacOS can use Homebrew to install Postgres, this includes the psql client.

brew update
brew install postgresql

Ubuntu: You can install the Postgres client separately without having to install the full server.

sudo apt-get update
sudo apt-get install postgresql-client

Windows: We recommend using WSL2 or you can install the Postgres tools with the Windows installer.

We also assume you have a Postgres instance on Railway with tables populated with data.

Backing Up Your Database

We will be using a tool that comes with the psql tools called pg_dump. The command pg_dump writes a file locally a copy of your database.

pg_dump -U <username> -h <host> -p <port> -W -F t <db_name> > <output_filename>

Getting Your Postgres Credentials

In order to execute the command. We will need the following information retrievable from the Project Variables page of your Railway project. Under the Project Variables, after you expand the PostgreSQL variables dropdown you should take note of the following information.

An image from Notion
  1. The username: PGUSER
  2. The host name: PGHOST
  3. The port to the database instance: PGPORT
  4. The name of the database: PGDATABASE
  5. The password to the database: PGPASSWORD

We can now structure the command to use these parameters in the following way.

pg_dump -U PGUSER -h PGHOST -p PGPORT -W -F t PGDATABASE > YOUR_FILENAME_HERE

Your command should look like the following.

pg_dump -U postgres -h containers-us-west-15.railway.app -p 5802 -W -F t railway > mydatabasebackup
💡
Keep in mind to run the command in the directory where you want the backup to be stored!

After everything looks good! You are good to execute the command. After when you press enter, you will be prompted to enter your PGPASSWORD to authenticate into the DB to perform the command.

Command Result

The command takes some time to run, keep in mind, the more data in the database, the longer it will take to complete. We can see after the command completes, if we were to run ls or the Windows equivalent we can see the backup in the directory where we ran the command in.

An image from Notion

Restoring Your Database Backup

As mentioned in Getting Started, we can use pg_restore to restore the backup we made of our database. Head over to your other project with a PostgreSQL plugin instantiated. I have a sample project with no data that we will be restoring our data to.

An image from Notion

We will need the same parameters that we got last time from the Project Variables section as shown before.

The PG Restore Command

pg_restore connects to a PostgreSQL database and restores a backup made from pg_dump. The command is structured in the following way:

pg_restore -U <username> -h <host> -p <port> -W -F t -d <db_name> <dump_file_name>

After you gather your variables for the database instance you want to restore to, your command should look like the following:

pg_restore -U postgres -h containers-us-west-15.railway.app -p 6473 -W -F t -d railway mydatabasebackup
⚠️
If you are using Prisma ORM, your command might finish with a warning. Don't fret, your data would import as normal.

After the command completes: your tables and data should be restored as normal. You can see the results of DB under the data tab in your plugin.

An image from Notion

Closing

And with that you should have all the information you need to backup and restore your PostgreSQL database plugin on Railway.

If you have any suggestions for more guides, let us know on Discord!