On 20th of October, Robert Haas committed patch:
Rename "pg_xlog" directory to "pg_wal".
"xlog" is not a particularly clear abbreviation for "write-ahead log",
and it sometimes confuses users into believe that the contents of the
"pg_xlog" directory are not critical data, leading to unpleasant
consequences. So, rename the directory to "pg_wal".
This patch modifies pg_upgrade and pg_basebackup to understand both
the old and new directory layouts; the former is necessary given the
purpose of the tool, while the latter merely avoids an unnecessary
We may wish to consider renaming other programs, switches, and
functions which still use the old "xlog" naming to also refer to
"wal". However, that's still under discussion, so let's do just this
much for now.
Continue reading Waiting for PostgreSQL 10 – Rename “pg_xlog" directory to “pg_wal".
One of my clients is upgrading some servers. The procedure we have took some time to get to current state, and we found some potential problems, so decided to write more about it.
Continue reading How to pg_upgrade …
I had to deal with this question, or some version of it, quite a few times. So, decided to write a summary on what one could (or should) do, after data is in database, and application is running. Namely – setup some kind of replication and backups.
What to use, how, and why? This is what this post is all about.
Continue reading I have PostgreSQL, loaded some data, and have app using it. Now what?
On 25th of January, Heikki Linnakangas committed patch:
Add recovery_target='immediate' option.
This allows ending recovery as a consistent state has been reached. Without
this, there was no easy way to e.g restore an online backup, without
replaying any extra WAL after the backup ended.
MauMau and me.
Continue reading Waiting for 9.4 – Add recovery_target='immediate' option.
Recently someone was looking for help with script from Pg wiki.
I never really checked it, but when talking with the guy needing help, I looked at it. And didn't really like it.
Continue reading How to make backups of PostgreSQL?
Around the time that Xzilla wrote about the book, Packt contacted me and asked for a review.
Since I generally don't really read technical books, I declined the offer, but Sandy from Packt was very persistent, and asked if I could inform about book giveaway contest.
The book is definitely PostgreSQL related, and Xzilla suggested to check it out, so here it goes:
Continue reading Win a book contest – PostgreSQL Backup and Restore How-to
As of yesterday OmniPITR got following changes/fixes:
- Fixed bug which caused immediate finish request be treated the same as smart finish request.
- Fixed problem with using omnipitr-backup-slave on PostgreSQL 9.0 slave, which is using streaming replication.
- Added option to omnipitr-restore, so that you can now use it for streaming-replication slaves
I'm very ashamed of the first thing (smart/immediate finish request), as it was simply my lack of test.
Second thing – the problem was that with streaming replication slave behaves a bit differently than normally, and so the backup procedure had to be modified.
As for third – restore command in streaming replication environment has to behave differently than normal restore command – i.e. it should finish, with error, as soon as it will be called for wal file that does not exist in wal archive. Which is direct opposite of what should be done normally.
Now, omnipitr-restore got switch (-sr) to make it work correctly in SR situation.
Links for svn/docs are listed on project page.
On 23rd of January, Magnus Hagander committed patch which adds:
Add pg_basebackup tool for streaming base backups
This tool makes it possible to do the pg_start_backup/
copy files/pg_stop_backup step in a single command.
There are still some steps to be done before this is a
complete backup solution, such as the ability to stream
the required WAL logs, but it's still usable, and
could do with some buildfarm coverage.
In passing, make the checkpoint request optionally
fast instead of hardcoding it.
Magnus Hagander, reviewed by Fujii Masao and Dimitri Fontaine
Continue reading Waiting for 9.1 – pg_basebackup
Well, the biggest information is that hot-backups on slave work. And they work fine. Really fine.
Some more information (with nice graph!):
Continue reading OmniPITR – hot backup on slave
Question from title sounds weird to you? It's just a ‘rm backup_filename'? Well. I really wish it was so simple in some cases.
One of the servers I'm looking into, there is interesting situation:
- quite busy database server (2k tps is the low point of the day)
- very beefy hardware
- daily backups, each sized at about 100GB
- backups stored on ext3 filesystem with default options
- before launching daily backup, script removes oldest backup (we keep 3 days of backups on this machine)
Continue reading How to remove backups?