wal archiving question
Hi all -
We have postgres 9.0.4 since an year. Replication also set up and
works fine. On the master. The wal files are growing and it's almost gone
to 90% of the disk. How can I handle the situation. Can I delete the old
files. Please advice
Regards
Thanks for the help
akp geek wrote:
We have postgres 9.0.4 since an year. Replication also set up and
works fine. On the master. The wal files are growing and it's
almost gone to 90% of the disk. How can I handle the situation.
Can I delete the old files. Please advice
Replicating how? Are these WAL files in the pg_xlog directory, the
location your archive script copies them to, or somewhere else?
What is your overall backup strategy? How many base backups do you
keep, how far back? What sort of recovery options do you need
(e.g., point in time recovery to any point in the last year, or
something less extreme?)
-Kevin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Resolved by subject fallback
akp geek wrote:
We have postgres 9.0.4 since an year. Replication also set up and works fine.
On the master. The wal files are growing and it's almost gone to 90% of the disk.
How can I handle the situation. Can I delete the old files. Please advice
Are the WAL files (the ones in pg_xlog) growing or the WAL archives?
If the former, then you maybe got an archiving problem
(consult the server log).
If the latter, you'd have to manually delete some,
but take care that you don't delete any that you still
need either for restoring a backup or for the standby to
catch up.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
WAL archives is growing, not the files in pg_xlog.
Regards
On Wed, Dec 5, 2012 at 3:38 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote:
Show quoted text
akp geek wrote:
We have postgres 9.0.4 since an year. Replication also set up and works
fine.
On the master. The wal files are growing and it's almost gone to 90% of
the disk.
How can I handle the situation. Can I delete the old files. Please
advice
Are the WAL files (the ones in pg_xlog) growing or the WAL archives?
If the former, then you maybe got an archiving problem
(consult the server log).If the latter, you'd have to manually delete some,
but take care that you don't delete any that you still
need either for restoring a backup or for the standby to
catch up.Yours,
Laurenz Albe
akp geek <akpgeek@gmail.com> writes:
WAL archives is growing, not the files in pg_xlog.
You need to prune the archives of files that are too old to be
interesting. This doesn't happen automatically since PG has no
idea what your archiving requirements are.
Be sure to keep WAL files at least back to the start of your
last base backup. Some people like to keep them back to the
next-to-last base backup.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
thank you all. There is a mistake I did then. I created a base backup when
I started the DB , that was exactly an year ago. I need to do that again
and then delete the old files.
Regards
On Wed, Dec 5, 2012 at 4:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
akp geek <akpgeek@gmail.com> writes:
WAL archives is growing, not the files in pg_xlog.
You need to prune the archives of files that are too old to be
interesting. This doesn't happen automatically since PG has no
idea what your archiving requirements are.Be sure to keep WAL files at least back to the start of your
last base backup. Some people like to keep them back to the
next-to-last base backup.regards, tom lane
akp geek <akpgeek@gmail.com> writes:
thank you all. There is a mistake I did then. I created a base backup when
I started the DB , that was exactly an year ago. I need to do that again
and then delete the old files.
Yeah, you should be making new base backups a lot more frequently than
that, probably. Consider: if your DB died today and you had to restore
from the base backup, you'd have to replay a year's worth of WAL to get
back where you were. That'd take ages. So the bloat in the archive
directory is just one reason not to do it like that.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
thank you so much for all the inputs.. One final question is , Do we have
to stop the database when we create the base backup?
Regards
On Wed, Dec 5, 2012 at 4:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
akp geek <akpgeek@gmail.com> writes:
thank you all. There is a mistake I did then. I created a base backup
when
I started the DB , that was exactly an year ago. I need to do that again
and then delete the old files.Yeah, you should be making new base backups a lot more frequently than
that, probably. Consider: if your DB died today and you had to restore
from the base backup, you'd have to replay a year's worth of WAL to get
back where you were. That'd take ages. So the bloat in the archive
directory is just one reason not to do it like that.regards, tom lane
akp geek wrote:
thank you so much for all the inputs.. One final question is , Do we have to stop the database when we
create the base backup?
No, see
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#BACKUP-BASE-BACKUP
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general