Guest User!

You are not Sophos Staff.

Thread Info
  • State Not Answered
  • Locked Locked
  • Replies 7 replies
  • Subscribers 2 subscribers
  • Views 11809 views
  • Users 0 members are here
Options
Suggested
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Clean up PostgreSQL Database

GL@MO
Hi there,

is there a possibility to clean up my postgreSQL database without losing all data? (not the postgre rebuild-command [;)] )

I had some contact with Sophos support today which told me my db is about 4GB, too big.


This thread was automatically locked due to age.
  • 0
    Hi, you could change the reporting retention settings in the web interface.

    If the DB doesn't shrink after that, you could try running a PostGres Vacuum command.

    Barry
  • 0
    To add to what Barry said, the vacuum command runs automatically in the AMs, so after you reduce your retention rates, you should see a reduction in DB size the next morning.  The shorter the retention rates, the more space you'll save.
    __________________
    ACE v8/SCA v9.3

    ...still have a v5 install disk in a box somewhere.

    http://xkcd.com
    http://www.tedgoff.com/mb
    http://www.projectcartoon.com/cartoon/1
  • 0
    Thanks for your answers. I already changed the retention rates from 3 to 1 month for all logs. Support said 4Gb is too much (disk is not full, UTM220). I'll wait till tomorrow and have a look for the DB size again.

    ---

  • 0
    Hi, it's the Reporting Data that's in the DB, not the logs.

    Make sure you check the Reporting retention settings at
    Logging & Reporting -> Reporting Settings

    Barry
  • 0
    A starting point: 

    loginuser@home:/home/login > psql -U postgres -c '\l+'

                                                                        List of databases

       Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description

    -----------+-----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------

     epp       | epp       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7102 kB | pg_default |

     hotspot   | hotspot   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6534 kB | pg_default |

     pop3      | pop3      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6742 kB | pg_default |

     postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6470 kB | pg_default | default administrative connection database

     repmgr    | repmgr    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6470 kB | pg_default |

     reporting | reporting | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 379 MB  | reporting  |

     smtp      | smtp      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7870 kB | pg_default |

     template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6169 kB | pg_default | unmodifiable empty database

               |           |          |             |             | postgres=CTc/postgres |         |            |

     template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 6470 kB | pg_default | default template database

               |           |          |             |             | =c/postgres           |         |            |

    (9 rows)
  • 0 in reply to BarryG
    Hi, it's the Reporting Data that's in the DB, not the logs.


    sorry I did not write exactly what it is, you are right, it's reporting data.


    This morning the DB size shrinked to 1,9GB, a first step 

                                                                        List of databases

       Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description

    -----------+-----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------

     epp       | epp       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6518 kB | pg_default |

     hotspot   | hotspot   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6598 kB | pg_default |

     pop3      | pop3      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6742 kB | pg_default |

     postgres  | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6470 kB | pg_default | default administrative connection database

     repmgr    | repmgr    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 6470 kB | pg_default |

     reporting | reporting | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 1895 MB | reporting  |

     smtp      | smtp      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 15 MB   | pg_default |

     template0 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 6169 kB | pg_default | unmodifiable empty database

               |           |          |             |             | postgres=CTc/postgres |         |            |

     template1 | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 6470 kB | pg_default | default template database

               |           |          |             |             | =c/postgres           |         |            |

    ---

  • 0 
    psql reporting -U postgres -c '\dt+'
Share Feedback
×

Submitted a Tech Support Case lately from the Support Portal?

Unfiltered HTML