DB Backup using mutt
This article is of course not intended for admins, but rather for web developers who have a small database on the server that is slowly being filled. And the data seems to be there to do so - slowly and small, and nothing foretells misfortune, but still somehow it is not in itself. What if micro-collapse happens tomorrow within this particular server or this particular hosting company. And everywhere see evil hackers and bots.
In general, in order to sleep well, you need to ensure that the contents of the database are periodically stored outside the working server in case, for some reason, access to the contents of the server is lost. For those who (like me) have not grown to replicate databases on separate servers, my answer is to send dumps by mail.
I will not tell here that alternative (perhaps more correct) ways are possible to achieve the same effect. Here is described how I did it, what rakes met.
')
So, the whole process is conventionally divided into three parts:
Our OS is Ubuntu.
Our DBMS is Postgresql.
Assume:
If we now try to execute the dump command ...
... then most likely the terminal will ask you for a password. But it does not suit us, because This command will be executed automatically in a bash script, and I would like the password to be substituted by itself.
To do this, in the home folder of the Ubuntu user (say, sergey) create a hidden .pgpass file. Just in case, it will look like this:
Open this file and paste one line:
... where
Now the team ...
... should end with the creation of the pg_dump.sql.gz archive in the / tmp folder.
ERRORS: You may encounter errors related to database permissions, which are caused by the fact that the postgresuser user does not have rights to the mydatabase database. We need him to provide them.
I will not talk about the history and diversity of mail clients under Linux (especially since I do not know myself). In short, set mutt.
In the home folder of the Ubuntu user (and we remember that this is / home / sergey /, and there is another .pgpass file), we create a hidden .muttrc file and paste the following into the file:
... where
- moya_pochta@gmail.com - address, where the dumps will be sent from;
- pochtapass - password from this mail.
If you have Yandex mail, then in the 4th line instead of gmail.com you insert yandex.ru, and everything will work.
We try to send our file:
Here:
ERRORS: You may get the following error - SASL authentication. This means that it was not possible to enter the sending mail (this is moya_pochta@gmail.com). There are three reasons for this:
First, we write the script pg_mutt.sh and put here / home / sergey / (in this case it doesn’t matter where).
Here you need to replace drugaya_pochta@gmail.com, mydatabase, postgresuser with your data.
Make the pg_mutt.sh file executable:
Create a cron task to work every Sunday at 3.00:
Be sure to periodically check the contents of what comes in the form of a file called pg_dump_mydatabase _ *. Sql.gz, because it may well turn out that you are now sleeping soundly instead of following it.
In general, in order to sleep well, you need to ensure that the contents of the database are periodically stored outside the working server in case, for some reason, access to the contents of the server is lost. For those who (like me) have not grown to replicate databases on separate servers, my answer is to send dumps by mail.
I will not tell here that alternative (perhaps more correct) ways are possible to achieve the same effect. Here is described how I did it, what rakes met.
')
So, the whole process is conventionally divided into three parts:
- dump the database to a local folder, archive it
- mailing archive
- creating a bash script and cron job that executes this script with the necessary frequency
Our OS is Ubuntu.
Our DBMS is Postgresql.
1. Save locally
Assume:
- username DBMS - postgresuser
- DBMS user password - userpass
- reserved database name - mydatabase
- The path to the local save folder is / tmp
- file name for local saving - pg_dump.sql (after archiving you get pg_dump.sql.gz)
If we now try to execute the dump command ...
pg_dump -U postgresuser -h localhost --inserts mydatabase | gzip > /tmp/pg_dump.sql.gz ... then most likely the terminal will ask you for a password. But it does not suit us, because This command will be executed automatically in a bash script, and I would like the password to be substituted by itself.
To do this, in the home folder of the Ubuntu user (say, sergey) create a hidden .pgpass file. Just in case, it will look like this:
/home/sergey/.pgpass Open this file and paste one line:
localhost:5432:mydatabase:postgresuser:userpass ... where
- postgresuser - the username of the DBMS
- userpass - DBMS user password
- mydatabase is the name of the reserved database
Now the team ...
pg_dump -U postgresuser -h localhost --inserts mydatabase | gzip > /tmp/pg_dump.sql.gz ... should end with the creation of the pg_dump.sql.gz archive in the / tmp folder.
ERRORS: You may encounter errors related to database permissions, which are caused by the fact that the postgresuser user does not have rights to the mydatabase database. We need him to provide them.
2. We send archive by mail
I will not talk about the history and diversity of mail clients under Linux (especially since I do not know myself). In short, set mutt.
sudo apt-get install mutt In the home folder of the Ubuntu user (and we remember that this is / home / sergey /, and there is another .pgpass file), we create a hidden .muttrc file and paste the following into the file:
set from = moya_pochta@gmail.com set imap_user = moya_pochta@gmail.com set imap_pass = "pochtapass" set smtp_url = smtp://moya_pochta@smtp.gmail.com:587/ set smtp_pass = "pochtapass" # don't let your firewall kill your idle connection set imap_keepalive = 900 # do not copy sent mail set copy = no set move = no set folder = imaps://imap.gmail.com:993 set spoolfile = +INBOX #or +[Gmail]/Important set postponed = +[Gmail]/Drafts # cache set header_cache = ~/.mutt/cache/headers set message_cachedir = ~/.mutt/cache/bodies set certificate_file = ~/.mutt/certificates ... where
- moya_pochta@gmail.com - address, where the dumps will be sent from;
- pochtapass - password from this mail.
If you have Yandex mail, then in the 4th line instead of gmail.com you insert yandex.ru, and everything will work.
We try to send our file:
mutt -s "Dump" -a /tmp/pg_dump.sql.gz -- drugaya_pochta@gmail.com < /dev/null Here:
- “Dump” - letter subject
- /tmp/pg_dump.sql.gz - our dump
- drugaya_pochta@gmail.com - address, where the dumps will be sent
ERRORS: You may get the following error - SASL authentication. This means that it was not possible to enter the sending mail (this is moya_pochta@gmail.com). There are three reasons for this:
- wrong login and / or password;
- The mail account is not allowed access to incomprehensible applications (such as mutt). If you have gmail, then you need to enable account access for unverified applications, right here ;
- HZ. For example, I could not get through to one specific gmail account through mutt, although I changed access permissions and rechecked my login and password. Other accounts work fine, with both gmail and Yandex mail.
3. Automate
First, we write the script pg_mutt.sh and put here / home / sergey / (in this case it doesn’t matter where).
#!/bin/bash EMAIL="drugaya_pochta@gmail.com" DATABASE="mydatabase" DATE=$(date +"%Y-%m-%d") FILE="/tmp/pg_dump_${DATABASE}_$DATE.sql.gz" pg_dump -U postgresuser -h localhost --inserts $DATABASE | gzip > $FILE mutt -s "Dump $DATABASE $DATE" -a $FILE -- $EMAIL < /dev/null Here you need to replace drugaya_pochta@gmail.com, mydatabase, postgresuser with your data.
Make the pg_mutt.sh file executable:
chmod +x pg_mutt.sh Create a cron task to work every Sunday at 3.00:
crontab -e 0 3 * * 0 /home/sergey/pg_mutt.sh Conclusion
Be sure to periodically check the contents of what comes in the form of a file called pg_dump_mydatabase _ *. Sql.gz, because it may well turn out that you are now sleeping soundly instead of following it.
Source: https://habr.com/ru/post/311018/
All Articles