postgres maintenance

I've read the docs on the postgres maintenance and I'm disappointed that
the best Novell could do is point me to the PG website.

Does anyone with more PG experience have canned scripts that could be
used (barring the passwords/PG user) to do the Postgres vacuums /
maintenance on dedicated DS/Mobility systems?

Perhaps it would be good to post it to the DS wiki for others as well.

thanks
Brad
Parents
  • bbecken;2027736 wrote:
    I've read the docs on the postgres maintenance and I'm disappointed that
    the best Novell could do is point me to the PG website.

    Does anyone with more PG experience have canned scripts that could be
    used (barring the passwords/PG user) to do the Postgres vacuums /
    maintenance on dedicated DS/Mobility systems?

    Perhaps it would be good to post it to the DS wiki for others as well.

    thanks
    Brad


    Agreed, I too would like some Novell branded documentation for PG maintenance.
  • Bump! I agree and wanted to know if anyone has a recommended maintenance routine?
  • jkillebrew;2052610 wrote:
    Bump! I agree and wanted to know if anyone has a recommended maintenance routine?


    I too would like to see something from Novell. Since it is installed with the product, perhaps they can write the scripts and put it in the next release??
  • On 08/12/2010 20:39, tschwartzniu wrote:

    > jkillebrew;2052610 Wrote:
    >> Bump! I agree and wanted to know if anyone has a recommended maintenance
    >> routine?

    >
    > I too would like to see something from Novell. Since it is installed
    > with the product, perhaps they can write the scripts and put it in the
    > next release??


    Let me ask Novell ...
    --
    Simon
    Novell Knowledge Partner (NKP)

    ------------------------------------------------------------------------
    Do you work with Novell technologies at a university, college or school?
    If so, your campus could benefit from joining the Novell Technology
    Transfer Partner (TTP) program. See novell.com/ttp for more details.
    ------------------------------------------------------------------------
  • On 12/9/2010 8:30 AM, Simon Flood wrote:
    > On 08/12/2010 20:39, tschwartzniu wrote:
    >
    >> jkillebrew;2052610 Wrote:
    >>> Bump! I agree and wanted to know if anyone has a recommended maintenance
    >>> routine?

    >>
    >> I too would like to see something from Novell. Since it is installed
    >> with the product, perhaps they can write the scripts and put it in the
    >> next release??

    >
    > Let me ask Novell ...

    Did anything ever come of this? I am at the point where I would like to
    start running regular maintenance, but it is quite daunting for someone
    with limited postgres experience.
    Thanks,
    Dan
  • Hi,

    I thought I'd share my experiences regarding this. I would prefer a Novell documentation or for Novell to turn on auto vacuum or say that it is ok to turn on.

    According to the postgresql site pretty much the only maintenance that needs to be done is run the vacuum command.

    I built a test server first to test for any negative effects, how long it would take and to play with the commands. It was instant on my server for 5 users running for a few weeks. I also checked if the latest build has autovacuum turned on but it does not. I have 3 servers with a db of about 70GB, basically the servers have been at capacity since release last year.

    So I logged on in the middle of the night, stopped the mobility then gw connectors, stopped the datasync services. logged in to mobillity database, and ran a vacuum on only one relation. It took a few seconds. I did a few and was comfortable enough timewise to run it for all relations. I think it took a little over 20 minutes per database. 2 databases per server and 3 servers I was done in a few hours. I did not do them concurrently as I tested each one worked afterward. I did not have any reports that anything broke afterwards.

    This could definitely be scripted especially if it's ok to run vacuum while the db is active which postgresql seem to think so. Even better though would just be to turn on autovacuum and never have to run anything at all.

    I too have limited experience so am not comfortable providing the commands. Novell need to step up here.

    Cheers,
    Zisis
Reply
  • Hi,

    I thought I'd share my experiences regarding this. I would prefer a Novell documentation or for Novell to turn on auto vacuum or say that it is ok to turn on.

    According to the postgresql site pretty much the only maintenance that needs to be done is run the vacuum command.

    I built a test server first to test for any negative effects, how long it would take and to play with the commands. It was instant on my server for 5 users running for a few weeks. I also checked if the latest build has autovacuum turned on but it does not. I have 3 servers with a db of about 70GB, basically the servers have been at capacity since release last year.

    So I logged on in the middle of the night, stopped the mobility then gw connectors, stopped the datasync services. logged in to mobillity database, and ran a vacuum on only one relation. It took a few seconds. I did a few and was comfortable enough timewise to run it for all relations. I think it took a little over 20 minutes per database. 2 databases per server and 3 servers I was done in a few hours. I did not do them concurrently as I tested each one worked afterward. I did not have any reports that anything broke afterwards.

    This could definitely be scripted especially if it's ok to run vacuum while the db is active which postgresql seem to think so. Even better though would just be to turn on autovacuum and never have to run anything at all.

    I too have limited experience so am not comfortable providing the commands. Novell need to step up here.

    Cheers,
    Zisis
Children
  • You should post the command line (less passwords) you used.
    I used to maintain a different application running postgres 8.1 and these where two types of vacuums.

    In Postgres 8.1:
    A postgres Simple vacuum could be done most anytime.
    A postgres Full vacuum requires exclusive DB access and thus locks the database during the operation. So the Application would need to be down during the vacuum.

    I've not played with the autovacuum process as the application changed to MS-SQ.

    Either way, I am still waiting for Novell's official response.