Your scenario of what might make an inconsistent backup is exactly right.
You can use mysqldump to take a consistent backup by adding the flag --lock-all-tables. The caveat is, as the name suggests, that all tables will be locked against writes until the backup is completed. Which depending on the size of your database could be a long time and cause great disruption.
There are some alternatives. Some of which are commercial. I'm going assume you are using MyISAM, which is generally more tricky to backup consistently than InnoDB.
One solution is to place MySQL's data onto storage that facilitates quicker backups by another method. Such as a SAN or LVM2 block device, which support snapshots. You will still have to place MySQL into a locked state but because snapshots take very little time to complete it the disruptive effect is negligible. You can then start another MySQL daemon against the snapshot of data should wish to take a consistent mysqldump and export it elsewhere at leisure.
I use a similar method myself against iSCSI backed storage.
CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!
This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.
What you should do is follow Chris James's answer:
sudo -u postgres psql postgres
# \password postgres
Enter new password:
To explain it a little bit. There are usually two default ways to login to PostgreSQL server:
By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql
. Note that sudo -u
does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).
So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.
Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.
Best Answer
the same as you do for any other repetitive task that can be automated - you write a script to do the backup, and then set up a cron job to run it.
a script like the following, for instance:
(Note: it has to be run as the postgres user, or any other user with the same privs)
EDIT :
pg_dumpall -D
switch (line 27) is deprecated, now replaced with--column-inserts
https://wiki.postgresql.org/wiki/Deprecated_Features