Thread: Backup automation

Hi good folks !

I've been backing up my databases (25 of them now, simple ones, not more than
three tables in any one of them, max 3000 rows and a dozen fields or so, I
try to keep everything as simple as possible) manually via ssh with pg_dump,
works fine, yes, good and great.

There is a way to make the machine do this for me daily/weekly/whatever at a
given time ? With a script, scheduled job or something, preferably to a text
file ?

I tried searching for this in the docs, manual and archives but maybe with
wrong search words.

Feeling still newbie both to linux and PG, could you please point me in the
right direction to do this ?

Running RH 7.1 / 2.4.9-kernel (up2date agent is great !) with PG 7.03.

Again, thanking in advance,

aarni

Aarni,

> There is a way to make the machine do this for me
> daily/weekly/whatever at a
> given time ? With a script, scheduled job or something, preferably to
> a text
> file ?

Yes, there is, and it's surprisingly easy and flexible.  You'll need two
things:

1. Write a SHELL SCRIPT to make a remote connection to or from the
database server and back up all databases.

2. Use the command-line program CRON to schedule this backup for
appropriate times.

You may find that you simplfy your life also if you use PG_DUMPALL
instead of backing up each database individually, but this depends on
whether you will want to restore them individually or all at once.

Unfortunately, shell scripts and cron are more than I want to get into
in an e-mail.  I learned this all myself by reading two books from
O'Reilly: Running Linux (for how to use Cron) and Learning the Bash
Shell (for how to write the script).

Perhaps someone will be kind enough to post their scripts on TechDocs (
( http://techdocs.postgresql.org/ ).  Unfortunately, I don't have time
to clean mine up ...

> Running RH 7.1 / 2.4.9-kernel (up2date agent is great !) with PG
> 7.03.

You will want to upgrade as soon as convenient.   In addition to the
added SQL commands, 7.1.x and 7.2 beta include improved backup and
restoration programs.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Is there a way to count the number of columns in a view?

Nevermind,

Answered my own question.  select relnatts from pg_class where relname =
'myviewnamehere';



-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Travis Hoyt
Sent: Tuesday, December 11, 2001 3:59 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] counting columns


Is there a way to count the number of columns in a view?