Pg_dumpall

I have cron execute a Python script as the database
administrator to vacuum and backup all databases.
Rather than dump all databases at once, however, the
script performs a 'pgsql -l' to get a current list of
databases. Each database is dumped and piped into
gzip for compression into its own backup file.

I should also mention that the script renames all
previous backup files, all ending in *.gz, to
*.gz.old; so that they survive the current pg_dump.
Of course, you could change the script to put the date
in the file name as to keep unlimited backup versions.

If you'd like the script, let me know.

Best of Luck,

Andrew L. Gould

--- Robert Fitzpatrick <robert@webtent.com> wrote:

On Sun, Jun 08, 2003 at 22:04:26 -0400,
Robert Fitzpatrick <robert@webtent.com> wrote:

I'm looking for an unattended (cron job) to dump all the databases. We
have both 7.3.3 and 7.0.2 (Cobalt RaQ) to do this for. I cannot grant a
certain user permission to dump the databases in 7.0.2 pg_hba.conf file
and, if I do this for our 7.3.3, does it mean anyone at the command
prompt can access the databases using the '-U' option and it not require
a password? What are most on this list doing for automated dumps?

In 7.3.3 you can use ident authentication for local (domain socket)
connections on some OS's. This is a good way to do this kind of thing.

I'm the only one with access to our database server, so I do a full trust.

Jon

On Tue, 10 Jun 2003, Bruno Wolff III wrote:

I have cron execute a Python script as the database
administrator to vacuum and backup all databases.
Rather than dump all databases at once, however, the
script performs a 'pgsql -l' to get a current list of
databases. Each database is dumped and piped into
gzip for compression into its own backup file.

I should also mention that the script renames all
previous backup files, all ending in *.gz, to
*.gz.old; so that they survive the current pg_dump.
Of course, you could change the script to put the date
in the file name as to keep unlimited backup versions.

FWIW, another good way to handle the last paragraph would be to use
logrotate. It would handle renaming files as *.1, *.2,... and you could
specify the number of days you wanted it to retain and you don't have to
go in periodically and delete ancient backups so that your drive doesn't
fill up.

(US$0.02)

~Berend Tober

--- btober@seaworthysys.com wrote:

I have cron execute a Python script as the

database

administrator to vacuum and backup all databases.
Rather than dump all databases at once, however,

the

script performs a 'pgsql -l' to get a current list

of

databases. Each database is dumped and piped into
gzip for compression into its own backup file.

I should also mention that the script renames all
previous backup files, all ending in *.gz, to
*.gz.old; so that they survive the current

pg_dump.

Of course, you could change the script to put the

date

in the file name as to keep unlimited backup

versions.

FWIW, another good way to handle the last paragraph
would be to use
logrotate. It would handle renaming files as *.1,
*.2,... and you could
specify the number of days you wanted it to retain
and you don't have to
go in periodically and delete ancient backups so
that your drive doesn't
fill up.

Thanks, I think I'll modify the script to manage a
declared number of backups as described above.

Logrotate sounds like FreeBSD's Newsyslog.conf. The
reason I don't use it is that I would have to
configure each database's backup file. The Python
script adds new databases and backup files to the
process automatically. This is one of those "if I get
hit by a bus" features. As my databases do not have
IS support, my boss insists on contingency planning.

Best regards,

Andrew Gould

I have the same thing in PHP if someone needs it. I wrote it originally
to get around the limits of 7.3's pg_dumpall to hit against our 7.2
production database, and then got carried away and did the same thing as
you, made it dump them into seperate files.

For folks with lots of databases (we create a different database for each
discrete application) it's nice to have individual database dumps instead
of the one huge file that pg_dumpall makes.

Now that it's getting re-written in C, it might be a good time to add the
ability to create a file per each database...

On Tue, 10 Jun 2003, Andrew Gould wrote:

On Fri, Jun 13, 2003 at 02:20:45PM -0600, scott.marlowe wrote:

hi,

I have the same thing in PHP if someone needs it. I wrote it originally
to get around the limits of 7.3's pg_dumpall to hit against our 7.2
production database, and then got carried away and did the same thing as
you, made it dump them into seperate files.

i'd be more interested in an schema builder in php, which generates all
CREATE statements from an hash array structure, checks for results,
can cope with schema changes (i.e. field rename or fields added)
and reports problems.

i'm currently using such array structures for my database abstraction
layer, which handles whole objects (instead of relations), allows syncing, ...

$class_desc { CLSID_PERSON } = array
(
type => inode,
table => 'person',
properties => array
(
firstname => array ( type => text, not_null => 1 ),
lastname => array ( type => text, not_null => 1 ),
gender => array ( type => char ),
birth => array ( type => date ),
death => array ( type => date )
score => array ( type => integer )
),
unique_keys => array
(
array ( firstname => 1, lastname => 1 )
)
);

the generated schema now could look like this:

CREATE TABLE person
(
"firstname" text,
"lastname" text,
"gender" character,
"birth" date,
"death" date,
"score" integer
)
INHERITS ( "_inode" ); -- this comes from type="inode"

CREATE UNIQUE INDEX person_ui0 ON person ( inode_id );
CREATE UNIQUE INDEX person_ui1 ON person ( firstname, lastname );

...

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux ITS
Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr.

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact@metux.de
cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de
---------------------------------------------------------------------
Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/