On the back of today’s other post about migrating your redis database across servers I thought it only made sense to talk about migrating your Postgres database as well, seeing as I migrated my Postgres database for my Firefish instance as well.
Postgres’ built-in migration tool
This probably isn’t going to be breaking news to anyone, but Postgres has a built-in tool for migrating databases. It’s called
pg_dump and it’s pretty easy to use. The syntax is as follows:
pg_dump -h <host> -p <port> -U <username> -d <database> > <outputfile>
This allows you to pretty easily dump your entire database to a file. This file can then be imported into another database using the
psql -h <host> -p <port> -U <username> -d <database> < <inputfile>
You can also pretty easily combine the two commands using a pipe:
pg_dump -h <host> -p <port> -U <username> -d <database> | psql -h <host> -p <port> -U <username> -d <database>
Making it even easier
I’m lazy, so I don’t want to have to type out that entire command every time I want to migrate my database. Thankfully, I don’t have to. I can just write a simple bash script to do it for me. Here’s what I came up with:
#! /usr/bin/env bash # This script is used to migrate a postgres database from one server to another. # Usage: pgmigrate <source> <destination> # Example: pgmigrate postgres://user:pass@localhost:5432/source postgres://user:pass@localhost:5432/destination set -e if [ $# -ne 2 ]; then echo "Usage: pgmigrate <source> <destination>" exit 1 fi SOURCE=$1 DESTINATION=$2 echo "Migrating from $SOURCE to $DESTINATION" echo "Dumping source database" pg_dump $SOURCE > /tmp/dump.sql if ! psql $DESTINATION -c "select 1" > /dev/null 2>&1; then echo "Destination database does not exist. Creating it" dbname=$(echo $DESTINATION | sed -e 's/.*\///') dest=$(echo $DESTINATION | sed -e 's/\/[^/]*$//') psql $dest -c "create database $dbname" fi echo "Restoring to destination database" psql $DESTINATION < /tmp/dump.sql echo "Cleaning up" rm /tmp/dump.sql echo "Done"
This script takes two arguments, the source database and the destination database. It then dumps the source database to a file, creates the destination database if it doesn’t exist, and then restores the source database to the destination database. It then cleans up after itself and exits.
Here’s an example of how to use it:
pgmigrate postgres://user:pass@localhost:5432/source postgres://user:pass@localhost:5432/destination
How simple is that?
I hope this post was helpful to you. I don’t see this one being as niche and potentially useful as the redis one, but I figured I’d write it up anyway, if for no othr reason than my propensity for borking my linux system, losing my files, and having to start over from scratch.