Difference between revisions of "Tarsnap"
m (add postgresql variant and reword the rest) |
m (improve dump script) |
||
Line 36: | Line 36: | ||
=== Setting PostgreSQL up === | === Setting PostgreSQL up === | ||
− | Same as above but PostgreSQL variant | + | Same as above but PostgreSQL variant; you'll have to repeat the GRANT statements after connecting to ''every'' database you want backed up. From what I can tell, there is no way to give a user read-only permission across all PostgreSQL databases. |
<pre> | <pre> | ||
CREATE ROLE archive WITH LOGIN ENCRYPTED PASSWORD 'CHANGEMETOSOMETHINGSECURE'; | CREATE ROLE archive WITH LOGIN ENCRYPTED PASSWORD 'CHANGEMETOSOMETHINGSECURE'; | ||
Line 47: | Line 47: | ||
=== Scripting the dumps === | === Scripting the dumps === | ||
− | We store scripts in <code>/usr/local/scripts</code> but you can put them wherever. This one is named <code>/usr/local/scripts/pre-acts.sh</code>. | + | We store scripts in <code>/usr/local/scripts</code> but you can put them wherever. This one is named <code>/usr/local/scripts/pre-acts.sh</code>. '''''Make sure to change the variables according to your needs.''''' |
<pre> | <pre> | ||
#!/bin/sh | #!/bin/sh | ||
+ | |||
+ | ###################################################### | ||
+ | # MAKE SURE TO CHANGE VALUES ACCORDING TO YOUR NEEDS # | ||
+ | ###################################################### | ||
+ | |||
+ | # Create a timestamp for dump names | ||
DAY=$(date +%Y-%m-%d) | DAY=$(date +%Y-%m-%d) | ||
− | |||
− | |||
− | |||
− | |||
− | # | + | # Pick database server, postgres or mysql |
− | + | DATABASE=postgres | |
+ | |||
+ | # Dump file location | ||
+ | DUMPFILE=/root/db_dumps/$DATABASE-backup-$DAY | ||
− | # | + | ############################# |
+ | # POSTGRES-SPECIFIC OPTIONS # | ||
+ | ############################# | ||
+ | # Set the read-only user's password | ||
+ | PGPASSWORD='CHANGEMETOSOMETHINGSECURE' | ||
+ | # Space-separated list of databases for backing up | ||
+ | DBNAME="dbname1 dbname2 dbname3" | ||
+ | if [ "$DATABASE" = "mysql" ]; then | ||
+ | touch $DUMPFILE | ||
+ | chown 0:0 $DUMPFILE | ||
+ | chmod 600 $DUMPFILE | ||
+ | mysqldump -u archive -p$PGPASSWORD --all-databases > $DUMPFILE | ||
+ | elif [ "$DATABASE" = "postgres" ]; then | ||
+ | for i in $DBNAME | ||
+ | do | ||
+ | PGDUMP=$DUMPFILE-$i | ||
+ | touch $PGDUMP | ||
+ | chown 0:0 $PGDUMP | ||
+ | chmod 600 $PGDUMP | ||
+ | pg_dump -h 127.0.0.1 -U archive -Fp -n public -O -x -d $i > $PGDUMP | ||
+ | done | ||
+ | else | ||
+ | echo "Please set the database variable" | ||
+ | fi | ||
+ | exit | ||
</pre> | </pre> | ||
Revision as of 03:16, 9 August 2021
Large portions of this guide were taken from Michael W Lucas' Tarsnap Mastery. I highly recommend it and anything else he's written.
Contents
Installation
The package is called tarsnap
on most distributions and is usually included in the official repos. After installation, generate a key and associate it with your Tarnsap account using the following command.
tarsnap-keygen --keyfile /root/tarsnap.key --user name@example.com --machine hostname
Configuration
The configuration file is usually stored in /etc/tarsnap.conf
. You may need to copy the sample configuration to the production config before editing. This is what we use but make sure to look at the sample and read the manual before copying and pasting this.
cachedir /usr/local/tarsnap-cache keyfile /root/tarsnap.key nodump print-stats checkpoint-bytes 1G humanize-numbers
Automation
We use ACTS for automation. It stands for Another Calendar-based Tarsnap Script and manages backup creation and rotation. It keeps 31 daily backups, 12 monthly backups, and never deletes yearly backups; maintaining those are on you. When you feel like a specific yearly backup is no longer necessary, delete it yourself.
After adding a new user to our database that has read-only access to everything, we'll configure ACTS, create pre- and post-backup scripts for database dumps, then set up email alerts.
Database dumps
Refer to SQL Snippets for more quick commands regarding SQL databases.
Setting MySQL up
Add a new user with a complicated password and minimal permissions to all databases with the following SQL command.
grant lock tables,show view,select on *.* to 'archive'@'localhost' identified by 'CHANGEMETOSOMETHINGSECURE';
Setting PostgreSQL up
Same as above but PostgreSQL variant; you'll have to repeat the GRANT statements after connecting to every database you want backed up. From what I can tell, there is no way to give a user read-only permission across all PostgreSQL databases.
CREATE ROLE archive WITH LOGIN ENCRYPTED PASSWORD 'CHANGEMETOSOMETHINGSECURE'; \c dbname GRANT USAGE ON SCHEMA public TO archive; GRANT SELECT ON ALL TABLES IN SCHEMA public TO archive; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO archive;
Scripting the dumps
We store scripts in /usr/local/scripts
but you can put them wherever. This one is named /usr/local/scripts/pre-acts.sh
. Make sure to change the variables according to your needs.
#!/bin/sh ###################################################### # MAKE SURE TO CHANGE VALUES ACCORDING TO YOUR NEEDS # ###################################################### # Create a timestamp for dump names DAY=$(date +%Y-%m-%d) # Pick database server, postgres or mysql DATABASE=postgres # Dump file location DUMPFILE=/root/db_dumps/$DATABASE-backup-$DAY ############################# # POSTGRES-SPECIFIC OPTIONS # ############################# # Set the read-only user's password PGPASSWORD='CHANGEMETOSOMETHINGSECURE' # Space-separated list of databases for backing up DBNAME="dbname1 dbname2 dbname3" if [ "$DATABASE" = "mysql" ]; then touch $DUMPFILE chown 0:0 $DUMPFILE chmod 600 $DUMPFILE mysqldump -u archive -p$PGPASSWORD --all-databases > $DUMPFILE elif [ "$DATABASE" = "postgres" ]; then for i in $DBNAME do PGDUMP=$DUMPFILE-$i touch $PGDUMP chown 0:0 $PGDUMP chmod 600 $PGDUMP pg_dump -h 127.0.0.1 -U archive -Fp -n public -O -x -d $i > $PGDUMP done else echo "Please set the database variable" fi exit
This one is stored in /usr/local/scripts/post-acts.sh
. It deletes all dumps older than 5 days.
#!/bin/sh find /root/db_dumps/ -type f -mtime +5 -delete
Make sure these are marked as executable with chmod +x /path/to/script.sh
.
ACTS configuration
ACTS can be installed by simply wget
ing acts
and acts.conf.sample
directly from the repo. Alternatively, you can clone the whole repository and symlink acts
to wherever you like for easier updates then copy acts.conf.sample
to /etc/acts.conf
.
Make sure you reference the sample configuration file but these are the base options we use. Add additional directories to the backuptargets
line, omitting the preceding /
. Until you run ACTS and verify that your configuration works properly, leave verbose
set to 1. After you're sure everything works, set it to 0.
backuptargets="usr/local/scripts root/db_dumps" tarsnapbackupoptions="--one-file-system --humanize-numbers" verbose=1 prebackupscript=/usr/local/scripts/pre-acts.sh postbackupscript=/usr/local/scripts/post-acts.sh
Cronjob
This runs ACTS at 02:30 on every day of every month.
# min hour day month weekday command 30 2 * * * /usr/local/scripts/acts
Failure emails
TODO
Backups
Yes, backups for the backups are necessary. Specifically, backups of backup keys are necessary; if you lose the key, you lose your backups. There is no way around this. While saving your key in a password manager, in a text file on your PC, on someone else's PC (please don't ever do this), or even on another server are all potential solutions, it's good to have an offline copy Just In Case™. We use paperbackup because it's easily read by both machines (QR codes) and humans (plain text); run your key through this, print the resulting PDF, put it in a folder, put the folder in a box, and put the box somewhere very very safe.