I've been using MySQL for years. I switched (er, mostly) to PostgreSQL
recently because I need to use PostGIS. It is all working now for the most
part, and PostGIS is absolutely wonderful.
I run CentOS 5.x and I do not like to upgrade vendor supplied packages. My
version of pg_dump is from postgresql-8.1.21-1.el5_5.1 - I'm assuming the
8.1.21 is the important part.
In writing my backup cron job, I ran into a small problem. It seems that
my version of pg_dump does not accept the -T option for excluding tables.
There are a couple tables that never need to be included in the backup (IE
php session data). Since I prefer not to upgrade pgsql at this time, I was
wondering if this is where schemas might help? IE can I put those few
tables into a different schema and then tell pg_dump to only dump the
public schema? Schema is kind of a new concept to me.
For my code, I use the php pear::MDB2 wrapper (which made moving from
MySQL to PostgreSQL much easier, just had to fix some non standard SQL I
had). If I move stuff out of the public schema, am I going to have tell
MDB2 how to find which schema it is in? I guess that may be better suited
for php list, but hopefully someone knows.
Finally, the one part of my site that is NOT moved over to PostgreSQL is
the site content search engine, which is sphyder. I would like to move
that over as I do not see a need to run two databases if one will suffice.
Sphyder also does not use a database layer or prepared statements (and I
love prepared statements for security aspect), so to move it over it looks
like what I should do is first port it to use MDB2 with prepared
statements and then fix any sql that causes it to break in PostgreSQL.
When everything was MySQL - I ran sphyder in its own database so that a
bug in sphyder code could not be exploited to hack my main database.
However, I'm wondering if that is an area where schema would be better. IE
create a schema called sphyder and only give the sphyder user permission
to select from the sphyder schema. Is that what the concept of schemas is
for?
Thanks for helping out a n00b.
-----
Michael A. Peters
http://www.shastaherps.org/
