Got interested recently in speed of pg_dump. Specifically, if, over the years, it has became faster, and if yes, how much.
Couple of years ago I was in position where we needed to run pg_dump, and found some inefficiencies, which got later patched. This was around the version 12. So, how does the situation look now? Interestingly, not so great…
On 4th of April 2025, Andrew Dunstan committed patch:
Non text modes for pg_dumpall, correspondingly change pg_restore pg_dumpall acquires a new -F/--format option, with the same meanings as pg_dump. The default is p, meaning plain text. For any other value, a directory is created containing two files, globals.data and map.dat. The first contains SQL for restoring the global data, and the second contains a map from oids to database names. It will also contain a subdirectory called databases, inside which it will create archives in the specified format, named using the database oids. In these casess the -f argument is required. If pg_restore encounters a directory containing globals.dat, and no toc.dat, it restores the global settings and then restores each database. pg_restore acquires two new options: -g/--globals-only which suppresses restoration of any databases, and --exclude-database which inhibits restoration of particualr database(s) in the same way the same option works in pg_dumpall. Author: Mahendra Singh Thalor <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Reviewed-by: jian he <[email protected]> Reviewed-by: Srinath Reddy <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Discussion: https://postgr.es/m/[email protected]
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.