Use logrotate to store 7 daily, 4 weekly and 12 yearly db backups

Question

I've been trying to use logrotate to store a database backup for:

• last 7 days (daily)
• last 4 weeks (weekly)
• last 12 months (monthly)

I've done a logrotate script like this, but only the daily rotations are done.

What I am missing or doing wrong? Is the date format *'weekly'* string breaking the script? If so, how can achieve to set a distinctive name to each type of rotated file? For example using 'extension .week'?

Note: I've used the prerotate and postrotate on weekly and monthly rotations to create a dummy file to check if these are beeing executed, but the files are never created.

Note 2: the file /backups/db.sql is created correctly

#daily (son)
"/backups/db.sql" {
        daily
        rotate 7
    missingok
    copy
    compress
}

#weekly (father)
"/backups/db.sql" {
        weekly
        rotate 4
    missingok
    copy
    dateext
    dateformat _weekly_%Y-%m-%d.
    compress
    prerotate
        touch /backups/weekly_pre.txt
    endscript
    postrotate
        touch /backups/weekly_post.txt
    endscript
}

#monthly (grandfather)
"/backups/db.sql" {
        monthly
        rotate 12
    missingok
    copy
    dateformat _monthly_%Y-%m-%d.
    compress
    prerotate
        touch /backups/monthly_pre.txt
    endscript
    postrotate
        touch /backups/monthly_post.txt
    endscript
}

If I execute logrotate manually with

logrotate -d /path/to/logrotate_job

I get

# logrotate -d /path/to/logrotate_job
reading config file logrotate_job
reading config info for "/backups/db.sql"
error: db_backup:11 duplicate log entry for /backups/db.sql
error: found error in "/backups/db.sql" , skipping
removing last 1 log configs

Handling 2 logs

rotating pattern: "/backups/db.sql"  after 1 days (7 rotations)
empty log files are rotated, old logs are removed
considering log /backups/db.sql
  log does not need rotating

rotating pattern: "/backups/db.sql"  1048576 bytes (no old logs will be kept)
empty log files are rotated, old logs are removed

It seems that the same file cannot be rotated, so how can rotate the daily file if its name includes the date?

Trying to set in weekly task:

nodate

prerotate cp dailyrotated to dailyrotated.week

And setting in monthly

nodate

prerotate cp weeklyrotated to weeklyrotated.month

Solution 1

I've just stumble upon this problem and happened to read this thread.

During my research, I've found another way of solving the problem of the duplicate path in the logrotate config file:

Creating symbolic links is less than painful and tricks perfectly the logrotate parser in that you present him with different paths to access the same files.

In the filesystem :

$ls -l
mysql.monthly -> mysql/
mysql.weekly -> mysql/
mysql

In the logrotate conf file :

/path/mysql/*.sql.gz {
    daily
    rotate 8
    copy
    compress
    ifempty
    missingok
    olddir /path/mysql/archives
    sharedscripts
    nocreate
}

/path/mysql.weekly/*.sql.gz {
        weekly
        copy
        rotate 4
        compress
        ifempty
        missingok
        olddir /path/mysql/archives/1-Semaine
        nocreate
}

/path/mysql.monthly/*.sql.gz {
        monthly
        copy
        rotate 12
        compress
        ifempty
        missingok
        olddir /path/mysql/archives/2-Mois
}

Solution 2

The problem is that you're using the same logfile name in each of the entries.

A weekly rotation happens once it's gone more than a week since the last time that log file was rotated. Since the file gets rotated each day, you'll never get to the weekly run. (You'll also never get to the montly run, for the same reason.)

So, in order to keep the weekly and monthly backups you need to change the filename before running the logrotate. The simplest way would be to create a separate cron job that copies db.sql to db.sql.weekly once a week, and copies it to db.sql.monthly once a month. When you've done that, you can change your logrotate config to read

#daily (son)
"/backups/db.sql" {
        daily
        rotate 7
    missingok
    copy
    compress
}

#weekly (father)
"/backups/db.sql.weekly" {
        weekly
        rotate 4
    missingok
    copy
    dateext
    dateformat %Y-%m-%d.
    compress
    prerotate
        touch /backups/weekly_pre.txt
    endscript
    postrotate
        touch /backups/weekly_post.txt
    endscript
}

#monthly (grandfather)
"/backups/db.sql.monthly" {
        monthly
        rotate 12
    missingok
    copy
    dateformat %Y-%m-%d.
    compress
    prerotate
        touch /backups/monthly_pre.txt
    endscript
    postrotate
        touch /backups/monthly_post.txt
    endscript
}

Solution 3

I think your target files should have a different name for both weekly and monthly. If you look at the status file it stores target name + Date so that it knows when target was last run.

It is a log rotation system not really a backup system.

I would recommend a different strategy. and use something like rsnapshot which is designed to do something like this.

It might help to run logrotate with the -d option to see what decisions it makes ?

Alternatively, use a different target

#Weekly example 1
/backups/db.sql.1.gz {
# some strategy to copy/rename most recent or oldest daily 
# (note db.sql.1.gz above is most recent )

}

#Weekly example 2 (another way)
/backups/db.sql.*.gz {
# some strategy to copy most recent or archive the set

}

#Monthly 
/backups/db.sql_weekly*.gz {
# some strategy to copy most recent
}

But you want the monthly to run before the weekly and the weekly to run before the daily (rsnapshot this is built into rsnapshot).

Comments

[Admin]

To start with, you've got the weekly section set to daily.

[Admin]

changed, anyways the prerotate and postrotate files are not beeing created

[Jenny D]

That was a clever way to get around the filename problem, kudos!

[Jenny D]

Very clever; I never thought of that but it's a neat solution!