Re: pg_dump, shemas, backup strategy

On 24 Jul 2010, at 24:20, Michael A. Peters wrote:

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

Welcome, I hope you like it here :)

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

Correct.
It's not a very recent version (we're at 8.4.1 now), but at least it's up to date regarding bug and security fixes - it's not 8.1.2 or something, you'd be amazed with what versions people show up here sometimes :P.

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

Schema's in Postgres are similar to different databases in MySQL. They allow you to organise your tables in groups of tables belonging to similar functionality, for example. They have their own permissions too, which is nice if you need to restrict certain users to certain functionality. And of course you can access tables cross-schema, if you aren't denied the permissions.

In your case, you could move those "troublesome" tables into their own schema and adjust the search_path accordingly for the user your PHP application uses to connect to the DB.

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

There are several approaches to that actually:

You can do it from PHP by executing "SET search_path TO '...'" after you connect to the database (or when you first need tables from that schema, but that seems to overcomplicate matters).

You can ALTER the DATABASE to set the search_path to what you need.

You can ALTER the ROLE to set the search_path for a group of users or a single user.

Any of those options will work, pick which suits your needs best ;)

> 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?

You could move Sphyder's tables into a separate schema too, but... if you disallow the accompanying role (let's say 'sphyder') access to the public schema, then it can't read various system tables either. That can cause issues with looking up FK constraints and the like.
Mind that I've never been in a situation where I needed to disallow some roles to access to the public schema, I'm not 100% sure about this - a simple test case is easy to create though.

I'd probably just put most (or all) of my main database in a schema other than 'public' so that the sphyder role can still access the system tables it needs (and it won't be able to change those if that role is set up with sufficiently restrictive permissions).

As an aside; I'm not familiar with Sphyder, but Postgres' TSearch 2 is pretty good too. It's built into the main database since version 8.3, not in your version. For 8.1 there is an extension with largely the same functionality, in case you're interested. I'm not sure how easy that would be to upgrade to the builtin version once you get to 8.3 or newer though...

> Thanks for helping out a n00b.

You're welcome, we've all been there.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4c4ac73d286218533513805!