Thread: Backup setup
I need to back up our database off-site for disaster recovery. If I just back up the entire database data directory (i.e. /var/lib/pgsql/data) will I be able to restore from there? Or should I instead just dump the data, using pg_dump, and back up the dump?
The advantage of the first method would be that I would not have to wait for pg_dump (it takes quite long on our 60G+ database) and would just be able to configure the backup agent to monitor the data directory and do differential backups of the files there every hour or so.
Your suggestions are much appreciated!
-Gabor
On Wednesday 23 April 2008 11:14, Gabor Siklos wrote: > I need to back up our database off-site for disaster recovery. If I just > back up the entire database data directory (i.e. /var/lib/pgsql/data) will > I be able to restore from there? Or should I instead just dump the data, > using pg_dump, and back up the dump? > The advantage of the first method would be that I would not have to wait > for pg_dump (it takes quite long on our 60G+ database) and would just be > able to configure the backup agent to monitor the data directory and do > differential backups of the files there every hour or so. > Your suggestions are much appreciated! > -Gabor I would use pg_dump. It will ensure that you get a complete set of data and not something half written. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com
"Gabor Siklos" <gabor@karamaan.com> writes: > I need to back up our database off-site for disaster recovery. If I just > back up the entire database data directory (i.e. /var/lib/pgsql/data) will I > be able to restore from there? This will not work. Please read http://www.postgresql.org/docs/8.3/static/backup.html (adjust link per your PG version) regards, tom lane
I need to back up our database off-site for disaster recovery. If I just back up the entire database data directory (i.e. /var/lib/pgsql/data) will I be able to restore from there? Or should I instead just dump the data, using pg_dump, and back up the dump?
The advantage of the first method would be that I would not have to wait for pg_dump (it takes quite long on our 60G+ database) and would just be able to configure the backup agent to monitor the data directory and do differential backups of the files there every hour or so.
Your suggestions are much appreciated!
-Gabor
On Wednesday 23 April 2008, "Gabor Siklos" <gabor@karamaan.com> wrote: > I need to back up our database off-site for disaster recovery. If I just > back up the entire database data directory (i.e. /var/lib/pgsql/data) > will I be able to restore from there? Technically you can do this, if you do it per the PITR instructions in the manual (ie. you also need all the WAL logs produced during and around the backup window). > Or should I instead just dump the > data, using pg_dump, and back up the dump? This is easier to restore from. > The advantage of the first method would be that I would not have to wait > for pg_dump (it takes quite long on our 60G+ database) and would just be > able to configure the backup agent to monitor the data directory and do > differential backups of the files there every hour or so. Differential backups of a running database will produce garbage. The proper way to keep running backups of the database is through the PITR mechanism. Very few applications that write to disk can be safely backed up while running. You might want to look at your whole backup strategy to see if you're really backing up what you think you're backing up. -- Alan
On 2008-04-23 17:22, Terry Lee Tucker wrote: > On Wednesday 23 April 2008 11:14, Gabor Siklos wrote: >> The advantage of the first method would be that I would not have to wait >> for pg_dump (it takes quite long on our 60G+ database) and would just be >> able to configure the backup agent to monitor the data directory and do >> differential backups of the files there every hour or so. > > I would use pg_dump. It will ensure that you get a complete set of data and > not something half written. I'd doing a "pg_dump -b -F t" and then compute differences between previous and current backup using program "rdiff" from package "librsync". Then difference file is compressed, encrypted and shipped offsite nightly. But my database is much smaller than yours. But it works well for a 6GB backup files on entry level server hardware. Pozdrawiam Tometzky -- Best of prhn - najzabawniejsze teksty polskiego UseNet-u http://prhn.dnsalias.org/ Chaos zawsze pokonuje porządek, gdyż jest lepiej zorganizowany. [ Terry Pratchett ]