How to Backup and Restore Your Postgres Database

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.

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.

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.

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>

Keep in mind, Heroku’s Postgres Image uses a specialized dump format. We have heard reports of users needing to use the following command to get a platform agnostic dump. Note: Heroku heroku pg:backups:download will not work.

pg_dump -h <host> -d <database> -U <user> -p <port> -W -F t > latest.dump

We have a Heroku specific restoration command in the next section.

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.

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

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.

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.

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.

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

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

Reference the dump file that you got from before and run the following commands with the Railway DB connection variables.

pg_restore -U <USER> -h <HOST> -p <PORT> -W -F t -d <DATABASE> latest.dump

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.

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!