Any backing up tips are welcome

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
User avatar
auggy
Bear Rating Trainee
Bear Rating Trainee
Posts: 14
Joined: 16 Mar 2013, 21:17

Any backing up tips are welcome

Postby auggy » 19 Mar 2013, 20:09

So far I'm just using the two lines below in a bash script. I still need to pick where I'm going to send backups.


Code: Select all

#! /bin/bash

tar czf tt-rss-backup_$(date +%d%m%y).tar.gz /var/www/rss/

mysqldump -h localhost  --user <mysql user> --password=<mysql password> TTRSS > ttrss_mysql_$(date +%d%m%y).sql



andy
Bear Rating Trainee
Bear Rating Trainee
Posts: 5
Joined: 17 Mar 2013, 15:22

Re: Any backing up tips are welcome

Postby andy » 19 Mar 2013, 20:54

Personally I've have something similar for years. I have a script that dumps the databases to individual <database>.sql files in my /var/www/ dir, every night, and I let it over-write the last file in there. I then use CrashPlan, which to me and my purposes have proven to be the best backup solution (cheap as hell for unlimited). I have /var/www/ as a backup dir, so all my sites including the databases gets backed up; CP does revisions so I can choose a database from a date at any point in time really. Works very smoothly.

Good luck with your backups!

Aldursil
Bear Rating Master
Bear Rating Master
Posts: 106
Joined: 18 Mar 2013, 03:11

Re: Any backing up tips are welcome

Postby Aldursil » 02 Jun 2013, 06:32

Does anyone have a script they are using to back up a PostgreSQL database for TTRSS they would not mind sharing to the community? Ideally I would like something that would work with a cron job and do rotating backups so you could specify to keep x many days or weeks and then delete the old ones.

Thanks

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Any backing up tips are welcome

Postby sleeper_service » 02 Jun 2013, 06:48

auggy wrote:So far I'm just using the two lines below in a bash script. I still need to pick where I'm going to send backups.


Code: Select all

#! /bin/bash

tar czf tt-rss-backup_$(date +%d%m%y).tar.gz /var/www/rss/

mysqldump -h localhost  --user <mysql user> --password=<mysql password> TTRSS > ttrss_mysql_$(date +%d%m%y).sql




take a look at http://sourceforge.net/projects/automysqlbackup/

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Any backing up tips are welcome

Postby sleeper_service » 02 Jun 2013, 06:53

Aldursil wrote:Does anyone have a script they are using to back up a PostgreSQL database for TTRSS they would not mind sharing to the community? Ideally I would like something that would work with a cron job and do rotating backups so you could specify to keep x many days or weeks and then delete the old ones.

Thanks

if you google 'auto postgresql backup' there's a number of things out there, I don't have any experiece with any of them, unlike the automysqlbackup that I recommended just above. I did see an autopostgresbackup (or something like) that might just be a port of the automysqlbackup, which is a script, and thus oughta be fairly easy to convert.

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Any backing up tips are welcome

Postby gbcox » 02 Jun 2013, 06:57


User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Any backing up tips are welcome

Postby sleeper_service » 02 Jun 2013, 07:08

personally, I'm just using automatic zfs snapshots for my pg backup ;)

Code: Select all

$ zfs list -r -t snapshot z1/pgdata|sort
NAME                                                USED  AVAIL  REFER  MOUNTPOINT
z1/[email protected]_daily-2013-05-25-18h58      113K      -  13.6M  -
z1/[email protected]_daily-2013-05-26-18h58      113K      -  13.6M  -
z1/[email protected]_daily-2013-05-27-18h58     33.3M      -  46.8M  -
z1/[email protected]_daily-2013-05-28-18h58     47.7M      -  61.5M  -
z1/[email protected]_daily-2013-05-30-18h58     53.0M      -  69.4M  -
z1/[email protected]_daily-2013-05-31-18h58     53.9M      -  72.2M  -
z1/[email protected]_frequent-2013-06-01-21h13  13.5M      -  72.4M  -
z1/[email protected]_frequent-2013-06-01-21h28  14.6M      -  72.6M  -
z1/[email protected]_frequent-2013-06-01-21h43  10.7M      -  72.8M  -
z1/[email protected]_hourly-2013-05-31-22h58    51.5M      -  71.8M  -
z1/[email protected]_hourly-2013-05-31-23h58    50.9M      -  71.9M  -
z1/[email protected]_hourly-2013-06-01-00h58    50.9M      -  71.9M  -
z1/[email protected]_hourly-2013-06-01-01h58    51.0M      -  72.0M  -
z1/[email protected]_hourly-2013-06-01-02h58    50.6M      -  71.6M  -
z1/[email protected]_hourly-2013-06-01-03h58    51.1M      -  71.9M  -
z1/[email protected]_hourly-2013-06-01-04h58    50.9M      -  71.8M  -
z1/[email protected]_hourly-2013-06-01-05h58    51.3M      -  72.0M  -
z1/[email protected]_hourly-2013-06-01-06h58    51.1M      -  72.1M  -
z1/[email protected]_hourly-2013-06-01-07h58    51.0M      -  72.0M  -
z1/[email protected]_hourly-2013-06-01-08h58    51.2M      -  72.2M  -
z1/[email protected]_hourly-2013-06-01-09h58    51.2M      -  72.2M  -
z1/[email protected]_hourly-2013-06-01-10h58    50.8M      -  72.0M  -
z1/[email protected]_hourly-2013-06-01-11h58    51.1M      -  72.3M  -
z1/[email protected]_hourly-2013-06-01-12h58    51.4M      -  72.5M  -
z1/[email protected]_hourly-2013-06-01-13h58    51.6M      -  72.6M  -
z1/[email protected]_hourly-2013-06-01-14h58    51.3M      -  72.3M  -
z1/[email protected]_hourly-2013-06-01-15h58    50.9M      -  72.2M  -
z1/[email protected]_hourly-2013-06-01-16h58    50.9M      -  72.1M  -
z1/[email protected]_hourly-2013-06-01-17h58    51.0M      -  72.4M  -
z1/[email protected]_hourly-2013-06-01-19h58    51.1M      -  72.2M  -
z1/[email protected]_hourly-2013-06-01-20h58    31.2M      -  72.3M  -
z1/[email protected]_hourly-2013-06-01-21h58    8.39M      -  72.5M  -
z1/[email protected]_monthly-2013-06-01-18h58   51.1M      -  72.2M  -
z1/[email protected]_weekly-2013-05-22-18h58     113K      -  13.6M  -
z1/[email protected]_weekly-2013-05-29-18h58    50.8M      -  66.9M  -


have I mentioned that I love zfs?

AngryChris
Bear Rating Master
Bear Rating Master
Posts: 135
Joined: 08 Apr 2013, 02:42

Re: Any backing up tips are welcome

Postby AngryChris » 02 Jun 2013, 07:11

I put this in /usr/local/sbin/dbbackup and run it from cron.

Code: Select all

#!/bin/bash

datestamp=$(date +%Y%m%d)
retention=${1:-14}

umask 0077
/usr/bin/pg_dump -U ttrss -C ttrssdb | /bin/gzip -c > /sysmgmt/databases/ttrssdb_${datestamp}.sql.gz
/usr/bin/find /sysmgmt/databases -type f -name ttrssdb_\*.sql.gz -mtime +$retention -exec rm {} \;


Code: Select all

[email protected]:~$ crontab -l
# m h  dom mon dow   command
0 2 * * *   /usr/local/sbin/dbbackup 60
[email protected]:~$

I have a /sysmgmt symlink to /home/sysmgmt where various items from / are backed up (/home is a separate mirrored volume). You'll need to create an appropriate .pgpass in the home directory of the postgres account. The script will keep 14 days of history by default (you can change that by editing the retention variable. If you want to change retention without editing the script, you can provide the days retained as an argument (I use 60 days in this example). The umask ensures that only the postgres user can access the backups.

Code: Select all

[email protected]:/sysmgmt/databases$ ls -lrt | tail
-rw------- 1 postgres postgres 20673186 May 23 02:00 ttrssdb_20130523.sql.gz
-rw------- 1 postgres postgres 21097298 May 24 02:00 ttrssdb_20130524.sql.gz
-rw------- 1 postgres postgres 21489308 May 25 02:00 ttrssdb_20130525.sql.gz
-rw------- 1 postgres postgres 21696296 May 26 02:00 ttrssdb_20130526.sql.gz
-rw------- 1 postgres postgres 21572533 May 27 02:00 ttrssdb_20130527.sql.gz
-rw------- 1 postgres postgres 21725109 May 28 02:00 ttrssdb_20130528.sql.gz
-rw------- 1 postgres postgres 22239039 May 29 02:00 ttrssdb_20130529.sql.gz
-rw------- 1 postgres postgres 22855767 May 30 02:00 ttrssdb_20130530.sql.gz
-rw------- 1 postgres postgres 23276101 May 31 02:00 ttrssdb_20130531.sql.gz
-rw------- 1 postgres postgres 23717151 Jun  1 02:00 ttrssdb_20130601.sql.gz
[email protected]:/sysmgmt/databases$

Athanasius
Bear Rating Trainee
Bear Rating Trainee
Posts: 38
Joined: 02 Apr 2013, 21:01

Re: Any backing up tips are welcome

Postby Athanasius » 02 Jun 2013, 19:37

sleeper_service wrote:personally, I'm just using automatic zfs snapshots for my pg backup ;)

Isn't there some chance that the PG files, when taken as a whole, will be in an inconsistent state just as you take a snapshot? Certainly I know MySQL can definitely suffer from this problem and it's highly recommended to use mysqldump to be sure of a coherent backup.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: Any backing up tips are welcome

Postby fox » 02 Jun 2013, 19:45

Either that or stop postgres before snapshot creation. Everything else is risky.

Aldursil
Bear Rating Master
Bear Rating Master
Posts: 106
Joined: 18 Mar 2013, 03:11

Re: Any backing up tips are welcome

Postby Aldursil » 02 Jun 2013, 21:37



This works great. Thanks!

gbcox
Bear Rating Master
Bear Rating Master
Posts: 149
Joined: 25 Apr 2013, 04:52

Re: Any backing up tips are welcome

Postby gbcox » 02 Jun 2013, 22:41

Aldursil wrote:This works great. Thanks!


You're welcome. For those who are running either MySQL or MariaDB backup instructions are here:
http://tso.bzb.us/2013/04/mariadbmysql- ... edora.html

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Any backing up tips are welcome

Postby sleeper_service » 03 Jun 2013, 04:41

Athanasius wrote:
sleeper_service wrote:personally, I'm just using automatic zfs snapshots for my pg backup ;)

Isn't there some chance that the PG files, when taken as a whole, will be in an inconsistent state just as you take a snapshot? Certainly I know MySQL can definitely suffer from this problem and it's highly recommended to use mysqldump to be sure of a coherent backup.


good point, I don't know, zfs snapshots are atomic. I'd have to ask my friend the postgres consultant about that.

the one time I did a snapshot and then blew up the database with a poorly adviced import, did a rollback and started postgres up again. it said it needed to do a recovery restart and opened right up.

Oracle has stated flatly that they guarantee that it will recover successfully from any zfs snapshot. I figured that postgres would be the same.

User avatar
LifeWOutMilk
Bear Rating Disaster
Bear Rating Disaster
Posts: 52
Joined: 02 Apr 2013, 21:57

Re: Any backing up tips are welcome

Postby LifeWOutMilk » 03 Jun 2013, 05:09

From http://www.postgresql.org/docs/8.4/inte ... -file.html it appears that you should generally be okay using zfs snapshots, the rollback afterwards is normal and expected.

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: Any backing up tips are welcome

Postby sleeper_service » 03 Jun 2013, 06:05

LifeWOutMilk wrote:From http://www.postgresql.org/docs/8.4/inte ... -file.html it appears that you should generally be okay using zfs snapshots, the rollback afterwards is normal and expected.


looks good, zfs snaps are about as reliable as you can get. and yeah, I expected the recovery restart, but it only took a second more before the db opened.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 7 guests