Jump to content


Photo

Web based postgresql backup


  • Please log in to reply
2 replies to this topic

#1 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 19 September 2006 - 07:06 PM

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 this script example that I've been toying with, but I can't seem to find a good way to ignore all the system level 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 not include 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?
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#2 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 20 September 2006 - 03:12 AM

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

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;

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).

#3 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 20 September 2006 - 01:06 PM

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).


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.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users