Jump to content

Archived

This topic is now archived and is closed to further replies.

obsidian

Web based postgresql backup

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.