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 is going to be the command line interface that we will be using to interface with our Railway Postgres database. This includes
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
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>
Heroku Specific Instructions
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.
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.
- The username:
- The host name:
- The port to the database instance:
- The name of the database:
- The password to the database:
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.
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.
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
Heroku Specific Instructions
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!