Tag: pg_restore

Command line tools? In XXI century? No way! Yes way!

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!

Waiting for 9.1 – EXTENSIONS

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

Continue reading Waiting for 9.1 – EXTENSIONS

Waiting for 9.0 – pg_upgrade

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.

Continue reading Waiting for 9.0 – pg_upgrade

Speeding up dump/restore process

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?

Continue reading Speeding up dump/restore process

Waiting for 8.4 – parallel restoration of dumps

On 2nd of February Andrew Dunstan committed his patch (with editing by Tom Lane) that:

Log Message:
-----------
Provide for parallel restoration from a custom format archive. Each data and
post-data step is run in a separate worker child (a thread on Windows, a child
process elsewhere) up to the concurrent number specified by the new pg_restore
command-line --multi-thread | -m switch.

Continue reading Waiting for 8.4 – parallel restoration of dumps