Hi! Le 2014-06-17 à 08:31, Oliver <ofab...@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 for production). > 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 databases now .. 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 just that http://www.postgresql.org/docs/current/static/app-pgbasebackup.html. I personally use OmniPITR to handle my base backups and continuous archiving https://github.com/omniti-labs/omnipitr . There also exists WAL-E https://github.com/wal-e/wal-e which 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. You can 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 keep 3 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 without activity. Those processes will generate some amount of WAL archives. WAL archives compress very well: 16MB to 4MB is 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 I keep for three weeks. Hope that helps! François Beausoleil -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
