Web based postgresql backup

[obsidian]

Well, I've recently been reading up on techniques to backup databases (both mysql and postgresql) through PHP and a web-based interface. So far, so good. I've found [url=http://archives.postgresql.org/pgsql-php/2005-12/msg00023.php]this script example[/url] that I've been toying with, but I can't seem to find a good way to ignore all the [i]system level[/i] tables. For instance, when I run the script, it works like a charm, but it returns tables such as 'sql_features', 'sql_implementation_info', 'pg_authid', 'pg_statistic', etc. Now, while these tables are by all means necessary to postresql running properly, I'm wondering if anyone has any genious input as to how I can restrict my results to [b]not include[/b] those system files. It wouldn't be a problem except for the fact I'm running into permission problems with accessing some of the features even though I'm connecting with a database administrator account.

I have gotten it to work with adding on a SUBSTRING() restriction for all tables with a 'sql_' or 'pg_' prefix, but I'm afraid that this might possibly be too limiting in some cases.

Does anyone have any experience backing up databases using this method, and if so, how did you avert the problem?

[btherl]

I don't do what you're doing, but I do select tables by namespace for calculating table size, in the following:

[code]ci=# \d total_rel_size
View "public.total_rel_size"
Column |  Type  | Modifiers
--------+--------+-----------
name  | name  |
tuples | real  |
gb    | bigint |
View definition:
SELECT pg_class.relname AS name, pg_class.reltuples AS tuples, pg_total_relation_size(pg_class.relname::text) / (1024 * 1024) AS gb
  FROM pg_class
  WHERE pg_class.relpages > 1024 AND pg_class.relnamespace = 2200::oid AND pg_class.relkind = 'r'::"char"
  ORDER BY pg_class.relname;[/code]

The part which may interest you is "pg_class.relnamespace = 2200::oid".  That's the namespace which contains all my tables (it may be different on your system, and schemas may affect it).  It doesn't include toast tables, but if you select all data from the main table then that will include toasted data.

This whole thing would be much easier if you can use pg_dump (the command line program).  That will take care of everything (though you might need to fix those permission problems).

[obsidian]

[quote author=btherl link=topic=108675.msg437665#msg437665 date=1158721938]
The part which may interest you is "pg_class.relnamespace = 2200::oid".  That's the namespace which contains all my tables (it may be different on your system, and schemas may affect it).  It doesn't include toast tables, but if you select all data from the main table then that will include toasted data.

This whole thing would be much easier if you can use pg_dump (the command line program).  That will take care of everything (though you might need to fix those permission problems).
[/quote]

thanks for the tip. as of now, i'm not using the relnamespace in my initial query, although i am using relkind. i'll have to play with that and see what i can come up with.

i agree: using pg_dump or pg_dumpall would be much more beneficial, but i'm actually trying to write a generic web based backup for postgres that could then be implemented on any system with the proper user permissions, so i have to assume that exec() won't be allowed.