Recently, on irc, there have been some talks with people using various pg_dump/pg_dumpall calls to get dumps of database.
I voiced my ideas, but figured it could be good subject for a blog post.
Continue reading How to efficiently dump PostgreSQL databases
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.
On 24th of March, Andrew Dunstan committed patch:
Add parallel pg_dump option. New infrastructure is added which creates a set number of workers (threads on Windows, forked processes on Unix). Jobs are then handed out to these workers by the master process as needed. pg_restore is adjusted to use this new infrastructure in place of the old setup which created a new worker for each step on the fly. Parallel dumps acquire a snapshot clone in order to stay consistent, if available. The parallel option is selected by the -j / --jobs command line parameter of pg_dump. Joachim Wieland, lightly editorialized by Andrew Dunstan.
Continue reading Waiting for 9.3 – Add parallel pg_dump option.
Some time ago I wrote a piece on speeding up dump/restore process using custom solution that was parallelizing process.
Later on I wrote some tools (“fast dump and restore") to do it in more general way.
But all of them had a problem – to get consistent dump you need to stop all concurrent access to your database. Why, and how to get rid of this limitation?
So, you just installed your PostgreSQL, and you have no idea how to use it – there is no icon in the menu of your OS, so how can you use it? Well, with the dreadful command line.
Of course – some people will never get used to textual programs. They need a GUI. That's fine. Not understandable for me, but who am I to judge. But knowing at least a basic things about standard command line tools for PostgreSQL can save you a lot of headache in some cases. Plus – you always have them so these are treated as default programs to use.
Continue reading Command line tools? In XXI century? No way! Yes way!
On 14h of December, Andrew Dunstan committed patch:
Add --exclude-table-data option to pg_dump. Andrew Dunstan, reviewed by Josh Berkus, Robert Haas and Peter Geoghegan. This allows dumping of a table definition but not its data, on a per table basis. Table name patterns are supported just as for --exclude-table.
Continue reading Waiting for 9.2 – excluding data of table from dump
On 8th of February, Tom Lane committed patch:
Core support for "extensions", which are packages of SQL objects. This patch adds the server infrastructure to support extensions. There is still one significant loose end, namely how to make it play nice with pg_upgrade, so I am not yet committing the changes that would make all the contrib modules depend on this feature. In passing, fix a disturbingly large amount of breakage in AlterObjectNamespace() and callers. Dimitri Fontaine, reviewed by Anssi Kääriäinen, Itagaki Takahiro, Tom Lane, and numerous others
On May, 12ve, Bruce Momjian committed new contrib module for 9.0 – pg_upgrage.
As I understand – this is what was available before as pg-migrator.
If you're not familiar with it – it's a tool that allows upgrade of $PGDATA from some version to some version. What's the use case? Let's assume you have this 200GB database working as 8.3, and you'd like to go to 8.4 (or 9.0). Normal way is pg_dump + pg_restore – which will take some time. With pg-migrate/pg_upgrade it should be faster, and easier. So, let's play with it.
As some of you know, I've been working lately for OmniTI company. When doing things for them (PostgreSQL related of course :), I stumbled on very interesting problem.
One of our clients is working on PostgreSQL 8.2, and wants to upgrade to 8.3. This is generally trivial – pg_dump, pg_restore/psql, and you're done.
But, this situation was with a twist – we had only very limited time-frame to do the migration. To be exact – we had 4 hours that we can bring the website down for.
So, we had to dump database, transfer it to new server, and load. All within 4 hours. Simple? Sure. Database was ~ 200GB (after restore – around 130GB). How to do it?
Usually I write about new features in 8.4, but this time I'd like to write about feature that will be actually missing in 8.4. And thank God, it will be missing.
On Mon, 09 Mar 2009 11:22:47 -0400 Greg Sabino Mullane wrote mail to pgsql-hackers list with his patch that removes -d switch from pg_dump.
Later there was some discussion (20 mails) that extended the patch to remove also -D.
And now, today, Tom Lane committed:
Remove the -d and -D options of pg_dump and pg_dumpall. The functionality is still available, but you must now write the long equivalent --inserts or --column-inserts. This change is made to eliminate confusion with the use of -d to specify a database name in most other Postgres client programs. Original patch by Greg Mullane, modified per subsequent discussion.
This is great news. One less way a new user of pg (or one that doesn't read –help pages) can do himself harm, one less thing that is purely illogical.