Обсуждение: Re: Scheduling a backup job (was: pgAdmin III v1.4.3 released)
Please post support request to the list, and don't hi-jack innappropriate subjects! > -----Original Message----- > From: Christian Hozee [mailto:Christian.Hozee@rivm.nl] > Sent: 20 July 2006 10:55 > To: Dave Page > Subject: Re: [pgadmin-support] pgAdmin III v1.4.3 released > > Dear Dave, > > Thanks for your reply. I have downloaded and installed > Pgadmin 1.4.3 but I > can't figure out how to schedule a backup job once a week. I > have searched > Pgadmin.org but I can't figure it out. I hope you can help me out. On Unix? Simply create a job with an appropriate schedule, and add a batch/shell step to do the work. You can either just call an external script, or write the script into the step itself, e.g. #!/bin/sh /usr/local/pgsql/bin/pg_dumpall -h 127.0.0.1 -U postgres -p 5432 > /var/backups/mydbdump.sql On Windows, just use batch syntax instead of shell, and in either case, specify the complete set of options the pg_dumpall (or pg_dump) requires to dump the required cluster. Regards, Dave.
On 20/07/06, Dave Page <dpage@vale-housing.co.uk> wrote: > Please post support request to the list, and don't hi-jack > innappropriate subjects! > > > -----Original Message----- > > From: Christian Hozee [mailto:Christian.Hozee@rivm.nl] > > Sent: 20 July 2006 10:55 > > To: Dave Page > > Subject: Re: [pgadmin-support] pgAdmin III v1.4.3 released > > > > Dear Dave, > > > > Thanks for your reply. I have downloaded and installed > > Pgadmin 1.4.3 but I > > can't figure out how to schedule a backup job once a week. I > > have searched > > Pgadmin.org but I can't figure it out. I hope you can help me out. > > On Unix? Simply create a job with an appropriate schedule, and add a > batch/shell step to do the work. You can either just call an external > script, or write the script into the step itself, e.g. > > #!/bin/sh > > /usr/local/pgsql/bin/pg_dumpall -h 127.0.0.1 -U postgres -p 5432 > > /var/backups/mydbdump.sql > > On Windows, just use batch syntax instead of shell, and in either case, > specify the complete set of options the pg_dumpall (or pg_dump) requires > to dump the required cluster. 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!?! 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. Just some thoughts, Cheers Antoine -- This is where I should put some witty comment.
On 20/7/06 18:29, "Antoine" <melser.anton@gmail.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
Hi,
should te PgAgent be installed on the postges Database or on the database I
want to schedule my backup on? In this case "staphtypeDb"
Met vriendelijke groet,
Christian Hozee
Systeembeheerder LIS
RIVM Bilthoven
Tel: 030-274 2284
Interne RIVM-mail naar I&A: LIS-IA@RIVM.NL
|-------------------------------------->
| Dave Page |
| <dpage@vale-housing.co.uk>|
| Sent by: |
| pgadmin-support-owner@post|
| gresql.org |
| |
| |
| 20-07-06 21:26 |
|-------------------------------------->
>------------------------------------------------------------------------------------------------------------------||
| |
| |
To| |
Antoine<melser.anton@gmail.com> | |
cc| |
ChristianHozee <Christian.Hozee@rivm.nl>, <pgadmin-support@postgresql.org> | |
Subject| | Re:
[pgadmin-support]Scheduling a backup job (was: pgAdmin III | |
|
>------------------------------------------------------------------------------------------------------------------|
On 20/7/06 18:29, "Antoine" <melser.anton@gmail.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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
> -----Original Message----- > From: pgadmin-support-owner@postgresql.org > [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of > Christian Hozee > Sent: 24 July 2006 10:21 > To: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Scheduling a backup job (was: > pgAdmin III > > Hi, > > should te PgAgent be installed on the postges Database or on > the database I > want to schedule my backup on? In this case "staphtypeDb" I would install it centrally on the postgres database and then use a single instance of pgAgent for all of your maintenance tasks. Regards, Dave.