Обсуждение: pg_dump, shemas, backup strategy
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/
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'snot 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 tablesbelonging to similar functionality, for example. They have their own permissions too, which is nice if you need torestrict certain users to certain functionality. And of course you can access tables cross-schema, if you aren't deniedthe permissions. In your case, you could move those "troublesome" tables into their own schema and adjust the search_path accordingly forthe 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 needtables 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 lookingup 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 stillaccess the system tables it needs (and it won't be able to change those if that role is set up with sufficiently restrictivepermissions). As an aside; I'm not familiar with Sphyder, but Postgres' TSearch 2 is pretty good too. It's built into the main databasesince version 8.3, not in your version. For 8.1 there is an extension with largely the same functionality, in caseyou're interested. I'm not sure how easy that would be to upgrade to the builtin version once you get to 8.3 or newerthough... > 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!
Michael A. Peters wrote: > 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. > That's a bad policy with PostgreSQL. I guarantee you that the problems you will run into because you're on PostgreSQL 8.1 are far worse than any you might encounter because you've updated from RedHat's PostgreSQL to the RPM packages provided by the PostgreSQL packagers. There are hundreds of known limitations in 8.1 you will absolutely suffer from as you expand your deployment that have been fixed in later versions. Yes, you can run into a packaging problem after upgrading to the PostgreSQL provided 8.3 or 8.4 that doesn't exist with the 8.1 they ship. But that's a *possible* issue, compared to the *guaranteed* limitations that are removed by using a later version of the database. Also, take a look at http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy 8.1 will be a frozen release no longer receiving bug fixes real soon now. You've already run into the first "It seems that my version of pg_dump does not accept..."; expect many more of those if you decide you must stay on 8.1. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
> On 24 Jul 2010, at 24:20, Michael A. Peters wrote: > *snip* > > 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. I spent last night playing with schemas and I must say, they absolutely rock. I especially like the fact that you can still do queries involving multiple schemas if you need to because they are still part of the same database, and pg_dump keeping track of the various user authentications granted to a schema and its tables is class. It's the right way to do things. *snip* > > 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). That's what I'm doing now. > > 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... I am going to look into that. ----- Michael A. Peters http://www.shastaherps.org/
> Michael A. Peters wrote: >> 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. >> > > That's a bad policy with PostgreSQL. I guarantee you that the problems > you will run into because you're on PostgreSQL 8.1 are far worse than > any you might encounter because you've updated from RedHat's PostgreSQL > to the RPM packages provided by the PostgreSQL packagers. There are > hundreds of known limitations in 8.1 you will absolutely suffer from as > you expand your deployment that have been fixed in later versions. Yes, > you can run into a packaging problem after upgrading to the PostgreSQL > provided 8.3 or 8.4 that doesn't exist with the 8.1 they ship. But > that's a *possible* issue, compared to the *guaranteed* limitations that > are removed by using a later version of the database. > > Also, take a look at > http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy > 8.1 will be a frozen release no longer receiving bug fixes real soon now. OK. I already maintain my own php RPMs because RHEL php is too old (I need the DOMDocument stuff) so maybe I need to add Postgresql to that. PHP is the only thing I currently have that links against postgresql anyway.
Michael A. Peters wrote: > I already maintain my own php RPMs because RHEL php is too old (I need > the DOMDocument stuff) so maybe I need to add Postgresql to that. > Note that you don't even have to build them yourself; the set at https://public.commandprompt.com/projects/pgcore/wiki/ are a straightforward drop-in replacement for the ones that RedHat provides. Subscribe to that yum repo just for the postgresql* packages and you can easily run 8.3 or 8.4 instead of the system 8.1. You might need to recompile your custom PHP against that afterwards, but you shouldn't have to build the database itself completely from source. And you'll still get security updates and bug fix point upgrades from that yum repo, continuing after the ones for 8.1 slow down. I've already started playing with the beta for RHEL6 just to avoid this whole stale package mess for a number of things. Will be nice when that ships, and the cycle of enterprise releases from them starts over with up to date packages again. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg Smith <greg@2ndquadrant.com> writes:
> Michael A. Peters wrote:
>> 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.
> That's a bad policy with PostgreSQL. I guarantee you that the problems
> you will run into because you're on PostgreSQL 8.1 are far worse than
> any you might encounter because you've updated from RedHat's PostgreSQL
> to the RPM packages provided by the PostgreSQL packagers.
Please note also that Red Hat has been shipping PG 8.4 for RHEL5 for
awhile --- it's the postgresql84-* package set. I would hope CentOS
has copied that by now.
regards, tom lane
Tom Lane wrote: > Please note also that Red Hat has been shipping PG 8.4 for RHEL5 for > awhile --- it's the postgresql84-* package set. I would hope CentOS > has copied that by now. > They have, as of CentOS's 5.5 back in May, and I keep forgetting its there. I'm not sure whether I like the trade-offs that come from using that packaging in every case yet though. The dependency issues with httpd are particularly weird: http://wiki.centos.org/Manuals/ReleaseNotes/CentOS5.5 (last item in "Known Issues"). I personally would rather just replace the system database with the newer version directly as the PGDG yums do, but you're right that some might prefer to use the system one instead. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
> Tom Lane wrote: >> Please note also that Red Hat has been shipping PG 8.4 for RHEL5 for >> awhile --- it's the postgresql84-* package set. I would hope CentOS >> has copied that by now. >> > > They have, as of CentOS's 5.5 back in May, and I keep forgetting its > there. I'm not sure whether I like the trade-offs that come from using > that packaging in every case yet though. The dependency issues with > httpd are particularly weird: > http://wiki.centos.org/Manuals/ReleaseNotes/CentOS5.5 (last item in > "Known Issues"). I personally would rather just replace the system > database with the newer version directly as the PGDG yums do, but you're > right that some might prefer to use the system one instead. I went with the upstream postgresql RPMs. They provided a compat package for CentOS stuff that links against older client libs, so it works out nicely. The reason I went with CentOS for server when I first started my project was because I wanted a system that had long term vendor maintenance and kept things stable rather than bleeding edge, a system that required minimal package maintenance on my part. The postgresql yum repo allows that. Since PostgreSQL has a 5 year commitment to support, even though it isn't vendor packaging I can pretty much guarantee that I'll have upgraded the server before that time limit expires, and even if they don't provide RPMs for that long, I can maintain the src.rpm for 8.4 series myself if I need to (which I hope I don't). The library version was not the issue with my php connection problem, though building against newer client libs was probably a good idea anyway. Still looking at it (yes I checked and double checked pg_hba.conf), I'll figure it out. ----- Michael A. Peters http://www.shastaherps.org/
> > The library version was not the issue with my php connection problem I needed to grant connect. I guess that must be new? Anyway I knew it was likely something simple. ----- Michael A. Peters http://www.shastaherps.org/
> Michael A. Peters wrote: >> I already maintain my own php RPMs because RHEL php is too old (I need >> the DOMDocument stuff) so maybe I need to add Postgresql to that. >> > > Note that you don't even have to build them yourself; the set at > https://public.commandprompt.com/projects/pgcore/wiki/ are a > straightforward drop-in replacement for the ones that RedHat provides. > Subscribe to that yum repo just for the postgresql* packages and you can > easily run 8.3 or 8.4 instead of the system 8.1. You might need to > recompile your custom PHP against that afterwards, but you shouldn't > have to build the database itself completely from source. And you'll > still get security updates and bug fix point upgrades from that yum > repo, continuing after the ones for 8.1 slow down. It looks like I might need to. I did the transition on my test machine and everything seems to be working well from the shell except it won't connect from php. I'm going out for week so I'll mess with it when I get back. Could be something else trivial too. ----- Michael A. Peters http://www.shastaherps.org/
25.Tem.2010 tarihinde 00:23 saatinde, "Michael A. Peters" <mpeters@shastaherps.org > şunları yazdı: > OK. I already maintain my own php RPMs because RHEL php is too old > (I need the DOMDocument stuff) IIRC, Centosplus repo has already Dom stuff. -- Devrim GÜNDÜZ PostgreSQL DBA @ Akinon/Markafoni, Red Hat Certified Engineer devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
> 25.Tem.2010 tarihinde 00:23 saatinde, "Michael A. Peters" > <mpeters@shastaherps.org > > Åunları yazdı: >> OK. I already maintain my own php RPMs because RHEL php is too old >> (I need the DOMDocument stuff) > > IIRC, Centosplus repo has already Dom stuff. I basically just rebuild the src.rpm from Fedora, which I think is similar to what they have in CentOS plus. ----- Michael A. Peters http://www.shastaherps.org/