Re: Scheduling a backup job (was: pgAdmin III

From: Dave Page <dpage(at)vale-housing(dot)co(dot)uk>
To: Antoine <melser(dot)anton(at)gmail(dot)com>
Cc: Christian Hozee <Christian(dot)Hozee(at)rivm(dot)nl>, <pgadmin-support(at)postgresql(dot)org>
Subject: Re: Scheduling a backup job (was: pgAdmin III
Date: 2006-07-20 19:26:54
Message-ID: C0E5978E.F706%dpage@vale-housing.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

On 20/7/06 18:29, "Antoine" <melser(dot)anton(at)gmail(dot)com> wrote:

> I imagine he is talking about scheduled jobs à la Oracle Enterprise
> Manager. I have to admit it would make postgres admin à lot more
> attractive for bosses like mine - they want point and click... I have
> set up a postgres database which (apart from stupid design and
> implementation mistakes) runs damn well. The only problem is that
> there aren't any open source tools that perform the same range of
> functions as any of the big three Enterprise Managers.
> Would it be that hard to integrate a ssh client and a basic gui
> interface to cron/postgres? Sure *nix only (I'm sure a windows module
> could be developped also) but who would run postgres on a windows
> machine anyway!?!

Why on earth would we want to? Christian was asking about pgAgent (I
believe) which runs on Windows and *nix and allows you to create and manage
jobs with multiple SQL or shell/batch steps, each with as many different
schedule definitions as you like. You can also run agents on multiple
servers allowing you to distribute jobs on a first-come-first-served, or
node targetted basis.

It's essentially the same as the Microsoft SQL Server Agent, but more
flexible.

> I know that is one of the major reasons that is stopping our company
> from using postgres for most of our internal projects - I am basically
> the only one with a real knowledge (and even that is stretching the
> truth somewhat!) of the *nix command line and no one at all knows
> about cron and whatnot.

Well you certainly don't need to know cron, but you do need to know a little
shell/batch with pgAgent. The script language can be whatever you like on
*nix - just start the script with the appropriate #! line. Alternatively (or
additionally) you can write SQL steps to perform other maintenance (I have
date related partial indexes built on some large tables for example).

The one thing you cannot do that SQL Server can (I don't know about DB2 or
Oracle as I never admined them) is point and drool configuration of
automated backups. This is because PostgreSQL doesn't allow us enough access
to the host system to be able to reliably create the scripts to do the
backup - for example, we don't even necessarily know what platform the
server is running on, never mind be able to browse the filesystem for backup
directories etc. This type of job does require you to write the appropriate
pg_dump/pg_dumpall command manually (and realistically, I think that anyone
who cannot figure out that sort of simple command should really not have
admin/dba privileges on a business system - at least not if the company
wants to stay in business!!).

Please see http://www.pgadmin.org/docs/1.4/pgagent.html for more info.

Regards, Dave

In response to

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Benjamin Krajmalnik 2006-07-20 20:26:00 Problem setting custom auto-vaccum settings
Previous Message Antoine 2006-07-20 17:29:52 Re: Scheduling a backup job (was: pgAdmin III v1.4.3 released)