Railway•11mo ago

Database migration data backup (attempt 2)

I just saw the message regarding database migration. How do I take a backup of data on our Postgres database before the migration and is data backed up buy you guys before the migration takes place anyway. What are the fallback options if something goes wrong with this?

13 Replies

Percy•11mo ago

Project ID: 52d66897-89e5-45a0-b983-517609e05c8c

Brody•11mo ago

the legacy database is never removed, the only thing done to the legacy database is a simple rename, if something goes wrong you simply revert the deployment

jeefskiOP•11mo ago

OK, thank you. What is the easiest way to do an offsite backup in any case? My project ID is if still needed: 52d66897-89e5-45a0-b983-517609e05c8c

Brody•11mo ago

id say using pg_dump is a pretty good way

jeefskiOP•11mo ago

Thanks, I'll investigate that. Looks like I can just follow this and connect to the remote host/DB: https://blog.railway.app/p/postgre-backup

Railway Blog

How to Backup and Restore Your Postgres Database

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

Brody•11mo ago

indeed!!

jeefskiOP•11mo ago

Variables seem to be in the console. Will give it a shot.

Deani1232•11mo ago

In a specific folder on your computer, run this in terminal to back up: pg_dump -Fc -U [DB USER] -h [DB HOST] -p [DB PORT] [DB NAME] > data.sql Saves as data.sql. Restore it via: pg_restore --disable-triggers -U [DB USER] -h [DB HOST] -p [DB PORT] -d [DB NAME] data.sql Recommend writing out the password and what these commands look like as copy/paste values in a text editor beforehand. This is also a good way to populate staging databases as well. Would recommend trying it in a different database instance first so that you can try it out before touching a production one.

Brody•11mo ago

yo thank you so much for that

Deani1232•11mo ago

I have a list of like 20 of these commands I run when we do migrations lmao The production ones on the textpad have 30 lines of new spaces from the other ones so I can't use them by accident. I do not trust myself

Brody•11mo ago

railway should really have a way to do this natively, but thats a bit out of the scope here lol

jeefskiOP•11mo ago

I got ChatGPT to write me a simple script 😆 :

#!/bin/bash

# Define the PostgreSQL credentials and backup file information
PGUSER=""
PGHOST=""
PGPORT=""
PGDATABASE=""

# Generate a timestamp in the format yyyy-mm-dd-hh-mm
TIMESTAMP=$(date +"%Y-%m-%d-%H-%M")

# Define the backup file name with the timestamp
BACKUP_FILENAME="backup_$TIMESTAMP.tar"

# Prompt for the database password
echo "Please enter the PostgreSQL password:"
read -s PGPASSWORD

# Export the password to be used by pg_dump
export PGPASSWORD

# Execute the pg_dump command
pg_dump -U $PGUSER -h $PGHOST -p $PGPORT -W -F t $PGDATABASE > $BACKUP_FILENAME

# Unset the password for security reasons
unset PGPASSWORD

echo "Backup completed successfully."

#!/bin/bash

# Define the PostgreSQL credentials and backup file information
PGUSER=""
PGHOST=""
PGPORT=""
PGDATABASE=""

# Generate a timestamp in the format yyyy-mm-dd-hh-mm
TIMESTAMP=$(date +"%Y-%m-%d-%H-%M")

# Define the backup file name with the timestamp
BACKUP_FILENAME="backup_$TIMESTAMP.tar"

# Prompt for the database password
echo "Please enter the PostgreSQL password:"
read -s PGPASSWORD

# Export the password to be used by pg_dump
export PGPASSWORD

# Execute the pg_dump command
pg_dump -U $PGUSER -h $PGHOST -p $PGPORT -W -F t $PGDATABASE > $BACKUP_FILENAME

# Unset the password for security reasons
unset PGPASSWORD

echo "Backup completed successfully."

Brody•11mo ago

cool!

Gaming

Programming

Database migration data backup (attempt 2)