postgresql – Fox’s Projects

Quick PostgreSQL user/pass commands

myusername = the username you want to give access to the database
mypassword = the password you want to assign to the user

sudo -u postgres psql
create database mydb;
create user myusername with encrypted password 'mypassword';
grant all privileges on database mydb to myusername;

Done.

Mastodon: Manual backups

This is an updated version (26-08-2024)

This manual is a transcript of the way that I have made a backup of my Mastodon instance. Please make sure you are careful and use your brain while following this manual.

Preparing the backup folder

Log in as root
```
cd /home/mastodon
```
```
mkdir backups
```
```
cd backups
```

Making the backups

Database (three steps)

#NOTE: You might need to elevate to ROOT first before entering the commands in this section below.
su - mastodon
cd /home/mastodon/backups
pg_dump -U mastodon mastodon_production -F t > DATE_FILENAME.tar

Example: pg_dump -U mastodon mastodon_production -F t > 2024-02-02-mastodon_production.tar

Settings file (one step)
```
cp /home/mastodon/live/.env.production /home/mastodon/backups/DATE_.env.production
```
Example: cp /home/mastodon/live/.env.production /home/mastodon/backups/2024-02-02-.env.production
Redis (two steps, perform as root)
```
exit
cp /var/lib/redis/dump.rdb /home/mastodon/backups/DATE_dump.rdb
```
Example: cp /var/lib/redis/dump.rdb /home/mastodon/backups/2024-02-02-REDIS-dump.rdb

You can now check your backups folder to see if all three files are present. This is also a good moment to copy the backup files to another, safe, location.

If you wish to backup your assets like images and stuff (User-uploaded files), backup the folder named “public/system”. Keep in mind that this folder can be rather large. Actually, it can become rather massive.

Mastodon: PG_DUMP backups

```
su - mastodon
```

pg_dump -U mastodon mastodon_production > FILENAME.sql

pg_dump -U mastodon mastodon_production -F t > FILENAME.tar

Sauce: https://backup.ninja/news/postgresql-backups-what-is-pgdumpall

Mastodon: “There are pending database migrations. Please run them to ensure the application behaves as expected”

“There are pending database migrations. Please run them to ensure the application behaves as expected” or “Niet alle databasemigraties zijn voltooid. Je moet deze uitvoeren om er voor te zorgen dat de applicatie blijft werken zoals het hoort” can be solved with the following command, executed as user mastodon:

1.
su - mastodon

2. 
cd live

3.
RAILS_ENV=production bundle exec rails db:migrate

results in: