Still paranoid

Within days of posting about how to backup and restore a Calckey server there was a rebrand and everything changed to Firefish; I’m still paranoid though, so I still need a Firefish backup and restore process.

Make the directories on the server to send the Firefish backups to

ssh into the server and:

mkdir /home/foo/Backups/firefish
mkdir /home/foo/Backups/firefish/files
mkdir /home/foo/Backups/firefish/postgresql

The following method uses ‘pg_basebackup’ to take a full copy of the whole PostgreSQL database.

This process can be run whilst Firefish (or any other application using PostgreSQL) is running, so is ideal for scheduling with cron.

I tried ‘pg_dump’ and ‘pg_restore’ for the single database but I couldn’t get it to restore properly.

Also:

The command ‘pg_basebackup’ needs a username / password for the command to run.

Fortunately, PostgreSQL has the ability to store this username / password combo in a file.

This is the .pgpass file.

The postgres user password may be already known but I found that in the Calckey / Firefish installation it wasn’t set, so to update or change the postgres user password:

sudo su - postgres

psql

At the postgres prompt type:

\password

Enter new password twice.

Type:

\q

To quit.

.pgpass

The file .pgpass in a user’s home directory can contain passwords to be used if the connection requires a password

https://www.postgresql.org/docs/current/libpq-pgpass.html

Obviously the .pgpass file needs creating, and appropriate permissions granting.

Firstly, create the .pgpass file:

sudo nano /home/$USER/.pgpass

#hostname:port:database:username:password
*:*:*:ThePostgresAccount:ThePostgresAccountPassword

Then, own it:

sudo chown $USER:$USER /home/$USER/.pgpass

Finally, give the file the correct permissions:

chmod 600 /home/$USER/.pgpass

Write it to the environment variables:

export PGPASSFILE='/home/$USER/.pgpass'

Check the environment variable using:

env

The ‘pg_basebackup’ command will now not ask for a password:

pg_basebackup -h localhost -p 5432 -U postgres_user -D /home/foo/Backups/firefish/postgresql -Fp -Xs -P

Backup the Firefish database

Firstly, create the database backup script:

sudo nano /etc/init.d/backup_firefish_db.sh

Then give the file the correct permissions:

sudo chmod +x /etc/init.d/backup_firefish_db.sh

backup_firefish_db.sh 

#!/bin/bash

# The remote backup process
# ~~~~~~~~~~~~~~~~~~~~~~~~~
# Backup the whole of the PostgreSQL database
rm -R /home/foo/Backups/firefish/postgresql/*;
pg_basebackup -h localhost -p 5432 -U postgres -D /home/foo/Backups/firefish/postgresql -Fp -Xs -P;

Backup the Firefish files directory

The firefish /files/ directory holds all of the ‘Drive’ files.

(All of those avatars, headers, wallpapers and meme collections.)

Firstly, create the /files/ backup script:

sudo nano /etc/init.d/backup_firefish_files.sh

Then, give the file the correct permissions:

sudo chmod +x /etc/init.d/backup_firefish_files.sh

backup_firefish_files.sh 

#!/bin/bash

# The remote backup process
# ~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Remove the file to show that a previous backup was created
rm /home/foo/Backups/firefish_backup_created
#
# Backup the Firefish configs
rm -r /home/foo/Backups/firefish/configs/*
cp -r /home/firefish/firefish/.config/default.yml /home/foo/Backups/firefish/configs/
#
# Backup the Firefish /files/ directory
rm -r /home/foo/Backups/firefish/files/
cp -r /home/firefish/firefish/files /home/foo/Backups/firefish/files/

Because the /files/ directory is owned by the Firefish user it needs a sudo cron job.

sudo crontab -e

#
# Backup the Firefish /files/ directory hourly
0 * * * * sh /etc/init.d/backup_firefish_files.sh
#

Archive the two backups

Firstly, create the archive script:

sudo nano /etc/init.d/backup_firefish_zipped.sh

Then give the file the correct permissions:

sudo chmod +x /etc/init.d/backup_firefish_zipped.sh

backup_firefish_zipped.sh 

#!/bin/bash

# The remote backup process
# ~~~~~~~~~~~~~~~~~~~~~~~~~
#
# The backup the whole of the PostgreSQL database
# is called by the user crontab '/etc/init.d/backup_firefish_db.sh'
#
# The backup of the Firefish 'default.yml' and the /files/ directory
# is called by the sudo crontab '/etc/init.d/backup_firefish_files.sh'
#
#  So call this in normal crontab after '/etc/init.d/backup_firefish_db.sh'
rm /home/firefish/Backups/backup_firefish.zip;
zip -r /home/foo/Backups/backup_firefish.zip /home/foo/Backups/firefish
#
# Create a file to show that the backup has successfully completed
touch /home/foo/Backups/firefish_backup_created

I backup the whole PostgreSQL and the /files/ database hourly using cron.

Then I archive (zip) the whole lot.

crontab -e

#
# Backup the whole of the PostgreSQL database hourly
# Then put the previous /files/ backup and this db backup into one zip
0 * * * * sh /etc/init.d/backup_firefish_db.sh; sh /etc/init.d/backup_firefish_zipped.sh
#

Firefish Restore Process

Obviously there is no sense in keeping a server backup on the server where the original files are held.

That’s why there are two backups:

  • A pair of folders on the server containing the database and the needed files
  • A single zipped file that can be copied as part of a daily backup routine off the server

This way, I can easily restore from the hourly created server files and in the case of a yet-to-happen catastrophic event I can copy over the last locally held zip archive and restore from there.

A screenshot showing some of the Firefish backup and restore script in an editor. Firefish Script Screenshot

I’m not going to cover how to get the zip archive on and off the server (hint: I use ssh keys and scp).

The following script executed server-side will restore (and give the correct permissions to) a Firefish PostgreSQL database and the /files/ directory.

Firstly, create the restore script:

sudo nano /etc/init.d/restore_firefish.sh

Then, give the file the correct permissions:

sudo chmod +x /etc/init.d/restore_firefish.sh

restore_firefish.sh 

#!/bin/bash

# The remote restore process
# ~~~~~~~~~~~~~~~~~~~~~~~~~~
#
# Restore from the hourly backup on the server

clear

# Check to see if a backup has previously completed successfully
# If a backup is in place then carry on and restore it
if [ ! -f /home/foo/Backups/firefish_backup_created ]; then
    echo "No previous backup to restore!"
    exit 0
fi

# Restore the Firefish 'default.yml' config
echo -e "\e[1;33mRestore the Firefish 'default.yml' config...\e[0m"
sudo cp /home/foo/Backups/firefish/configs/* /home/firefish/firefish/.config/
echo -e "\e[32mDone\e[0m"
echo ""

# Restore the Firefish /files/ directory
echo -e "\e[1;33mRestore the Firefish /files/ directory...\e[0m"
sudo cp /home/foo/Backups/firefish/files/* /home/firefish/firefish/files
echo -e "\e[32mDone\e[0m"
echo ""

# Stop the running PostgreSQL service
echo -e "\e[1;33mStop the running PostgreSQL service...\e[0m"
sudo systemctl stop postgresql.service
echo -e "\e[32mDone\e[0m"
echo ""

# Current PostgreSQL status
echo -e "\e[1;33mCurrent PostgreSQL status...\e[0m"
sudo systemctl status postgresql
echo -e "\e[32mDone\e[0m"
echo ""

# chown the existing PostgreSQL installation
echo -e "\e[1;33mchown the existing PostgreSQL installation...\e[0m"
sudo chown -R foo:foo /var/lib/postgresql/15/main/
echo -e "\e[32mDone\e[0m"
echo ""

# Remove the existing PostgreSQL files
echo -e "\e[1;33mRemove the existing PostgreSQL files...\e[0m"
sudo rm -fr /var/lib/postgresql/15/main/*
echo -e "\e[32mDone\e[0m"
echo ""

# Copy the latest backup to the PostgreSQL target location
echo -e "\e[1;33mCopy the latest backup to the PostgreSQL target location...\e[0m"
sudo cp -R /home/foo/Backups/firefish/postgresql/* /var/lib/postgresql/15/main/
echo -e "\e[32mDone\e[0m"
echo ""

# Make the PostgreSQL user take ownership of the target location
echo -e "\e[1;33mMake the PostgreSQL user take ownership of the target location...\e[0m"
sudo chown -R postgres:postgres /var/lib/postgresql/15/main/
echo -e "\e[32mDone\e[0m"
echo ""

# Start the PostgreSQL service
echo -e "\e[1;33mStart the PostgreSQL service...\e[0m"
sudo systemctl start postgresql.service
echo -e "\e[32mDone\e[0m"
echo ""

# Current PostgreSQL status
echo -e "\e[1;33mCurrent PostgreSQL status...\e[0m"
sudo systemctl status postgresql
echo -e "\e[32mDone\e[0m"
echo ""

# Is Firefish running
echo -e "\e[1;33mIs Firefish running...\e[0m"
systemctl status --no-pager site.consummatetinkerer.net.service
echo -e "\e[32mDone\e[0m"
echo ""

However, rather than using the sh command, the script needs running using bash:

bash /etc/init.d/firefish_restore.sh

To see the correctly coloured prompts.

Finally

(I did mention this in a prior post but it bears repeating.)

For anyone interested in getting into the SQL side, pgAdmin is a useful tool.

I installed it using:

curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/jammy pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

sudo apt update

sudo apt install pgadmin4

A screenshot of the Linux application pgAdmin, used to administer PostgreSQL databases. The Calckey Fediverse Server uses PostgreSQL as its back end darabase engine. pgAdmin Screenshot

However you Fediverse, Enjoy!

All of the Firefish code examples can be found on Forgejo , along with some SQL Snippets .