Whether you're thinking of using PostgreSQL 9.0 yet or not, you maybe interested in a small new utility that is designed to help you clean out old WAL archive files.
pg_archivecleanup is a standalone program which simply removes all WAL files older than a certain filename. You could use it like this:
pg_archivecleanup /my/archive 000000010000DEAD0000BEEF
The good thing here is that the tool works just fine for any release of PostgreSQL, not just 9.0. Mostly useful for bash or Windows scripts, since people will no doubt confirm it's trivial to do this in Python or perl etc..
The utility was designed as a dual purpose tool, since with 9.0 pg_standby is no longer required, yet you still need some way to clear down an archive. So you can also include it in the archive_cleanup_command, like so
archive_cleanup_command = 'pg_archivecleanup /my/archive %r'
which gets called regularly with changing values of %r to clean out your archive directories.
Not big, but its useful.
Tuesday, 5 October 2010
Friday, 1 October 2010
PostgreSQL Admin Book: Last Edit
I've been working on a PostgreSQL Administration book for some time now and am very happy to report that I'm done with the last edit on the last chapter. Hannu finished his a few days earlier.
Phew!
The full title of the book is "PostgreSQL 9 Administration Cookbook" and has been written by Hannu Krosing and myself. We've written it together over the course of 9 months or so. I guess it's no surprise to some, but these things always take more time than you expect.
The book is in the form of short, punchy recipes that tell you what you need to know on a topic quickly with examples, then we describe how it works later. Some of the recipes are really basic to make sure we address the frequent questions, but around half are advanced topics. It's certainly taken some time to express both the basic and the advanced topics succinctly.
An advanced database system needs advanced books to show it off in the best light. I very much hope it is up to the high standards of the PostgreSQL community and moves us all forwards.
I'll write some more about it later. All I can really express now is relief!
You can pre-order the book here:
http://www.2ndquadrant.com/postgresql-books/
Phew!
The full title of the book is "PostgreSQL 9 Administration Cookbook" and has been written by Hannu Krosing and myself. We've written it together over the course of 9 months or so. I guess it's no surprise to some, but these things always take more time than you expect.
The book is in the form of short, punchy recipes that tell you what you need to know on a topic quickly with examples, then we describe how it works later. Some of the recipes are really basic to make sure we address the frequent questions, but around half are advanced topics. It's certainly taken some time to express both the basic and the advanced topics succinctly.
An advanced database system needs advanced books to show it off in the best light. I very much hope it is up to the high standards of the PostgreSQL community and moves us all forwards.
I'll write some more about it later. All I can really express now is relief!
You can pre-order the book here:
http://www.2ndquadrant.com/postgresql-books/
Thursday, 24 June 2010
Hot Bugs & Cold Beer
2ndQuadrant is sponsoring a number of Bugs & Beer events at PostgreSQL user groups around the world.
The idea is to get together with your friends and see if we can shake out any more bugs in Hot Standby prior to release.
Particular focus on these areas:
* transactions holding locks before/during/after base backup taken
* prepared transactions
* correctness of results
* VACUUMs on master
* any other ways you can find to break it
Please write to me at simon@2ndQuadrant.com if you'd like funding for a local event.
All the best,
The idea is to get together with your friends and see if we can shake out any more bugs in Hot Standby prior to release.
Particular focus on these areas:
* transactions holding locks before/during/after base backup taken
* prepared transactions
* correctness of results
* VACUUMs on master
* any other ways you can find to break it
Please write to me at simon@2ndQuadrant.com if you'd like funding for a local event.
All the best,
Thursday, 17 June 2010
What's a VLF?
I read a good blog called http://sqlfool.com/
The funniest thing about it for me was the artful blogger talked about having 87,000 VLFs, taking up 1.5TB, though didn't mention what a VLF was.
Anyway, I checked - VLFs are WAL files in SQLServer. In case, like me, you didn't know, VLF stands for Virtual Log File. So it's a virtual physical thing. Got it. :-\
Which means the blogged-about related to 1.5TB worth of WAL files in the equivalent of pg_xlog. Ouch!
Ah! That reminds me: on both SQLServer and DB2 the log contains undo information, which means that the transaction log can't reuse files while transactions are still active. So long running write transactions causes the log to grow in size and can eventually fill the disk. This can be a real pain for DBAs and requires workarounds. That effect doesn't occur with PostgreSQL, so pg_xlog never overflows for that reason. With PostgreSQL, undo information is effectively stored in the database itself in the form of previous row versions. So a database grows as changes occur. Long running write statements can have a similar effect in PostgreSQL, but not long running transactions. So, one more problem that us Postgres-people get to miss out on.
And just to complete the circle, I will avoid explaining what a WAL file is here.
The funniest thing about it for me was the artful blogger talked about having 87,000 VLFs, taking up 1.5TB, though didn't mention what a VLF was.
Anyway, I checked - VLFs are WAL files in SQLServer. In case, like me, you didn't know, VLF stands for Virtual Log File. So it's a virtual physical thing. Got it. :-\
Which means the blogged-about related to 1.5TB worth of WAL files in the equivalent of pg_xlog. Ouch!
Ah! That reminds me: on both SQLServer and DB2 the log contains undo information, which means that the transaction log can't reuse files while transactions are still active. So long running write transactions causes the log to grow in size and can eventually fill the disk. This can be a real pain for DBAs and requires workarounds. That effect doesn't occur with PostgreSQL, so pg_xlog never overflows for that reason. With PostgreSQL, undo information is effectively stored in the database itself in the form of previous row versions. So a database grows as changes occur. Long running write statements can have a similar effect in PostgreSQL, but not long running transactions. So, one more problem that us Postgres-people get to miss out on.
And just to complete the circle, I will avoid explaining what a WAL file is here.
Subscribe to:
Posts (Atom)
Posts
