Hi!
Le 2014-06-17 à 08:31, Oliver <ofabelo@gmail.com> a écrit :
> Hi,
> I'm a newbie in postgresql. I've mounted my first postgresql instance, it is empty now, only with default postgres
DB.
> It is under Linux, with 2 filesystems, one for data and another for archiving (I've enabled archiving as it will be
forproduction).
> Could someone recommend me a strategy for backups, scripts and so on?
> Can base backup be done with the system up (postgres up), isn't it?
> Would it be ok if I do a base backup each week and archiving backup each day?
> As I've not configured backups (and archiving deletion), I've had my first problem and it is that my archiving
filesystem(FS) is full and archiver process is showing "failed" with the last wal file copy (normal as archiving FS is
full).
> Please, recommend me what I should make now .. I should create another network FS for base backups and archiving
backups?When I have my first base backup, could I then delete archiving files, isn't it?
> My archiving FS has 20GB, I don't understand as with a system without load (it will be for production, but it hasn't
databasesnow .. only postgres), how it full the FS in a few days ... Is it normal?
> Thanks beforehand.
Welcome to PostgreSQL!
The PostgreSQL manual has a great section on backup and restore:
http://www.postgresql.org/docs/current/static/backup.html
I found value in « Instant PostgreSQL Backup and Restore How-To » at
http://www.packtpub.com/how-to-postgresql-backup-and-restore/book
Regarding your questions:
* Yes, base backups can be made while the server is up and running. PostgreSQL has a tool named pg_basebackup to do
justthat http://www.postgresql.org/docs/current/static/app-pgbasebackup.html. I personally use OmniPITR to handle my
basebackups and continuous archiving https://github.com/omniti-labs/omnipitr . There also exists WAL-E
https://github.com/wal-e/wal-ewhich backs up your data to S3 / Rackspace CloudFiles automatically.
* Your WAL files are of no value once you have a new base backup: the new base backup includes all previous WAL files.
Youcan think of a base backup as a snapshot. WAL files describe changes to the last snapshot. Depending on your rate of
change,you can delete obsolete WAL files that are older than « a few days » than the last base backup. I personally
keep3 weeks of WAL files, 2 weeks of base backups.
* The vacuum daemon will vacuum databases regularly, and checkpoints will also occur on a schedule, even on a system
withoutactivity. Those processes will generate some amount of WAL archives. WAL archives compress very well: 16MB to
4MBis very typical on my system.
* My database is too big to do pg_dump (3 TiB), so I dont, but I have weekly base backups, plus the WAL archives which
Ikeep for three weeks.
Hope that helps!
François Beausoleil