Postgres Archival Logging generating error after database rebuild to clear log generated disk space

I'm a little confused, Scott - are you trying to archive log files or PostgreSQL databases?  What version are you running?  What was the installed version if not your current version?  What command did you use to repair your PostgreSQL databases?

Cheers - Bob

Hi Bob,

I resolved the problem on my own last week. I have recently deployed in AWS,  the Sophos HA-UTM 9 platform in two separate VPC's, one with Linux based EC2 instances (web-application servers) and an Windows (VPC) with corporate domain controller, VPN's and RDP services. 

In the Linux VPC where this problem originated, I have the Sophos UTM with one EIP as my main entry point. Then have multiple AWS internal ELB's listening on port 443/80 monitoring EC2 instance pairs within AWS Auto Scaling Groups for each web site. And AWS RDS DB's on the backend.

On the Sophos UTM in the Linux VPC, I have a 100GB EBS volume. After performing the initial Sophos HA-UTM deployment through AWS Cloudformation, I noticed after a week or so, that my disk space allocation was over 90%. Then I got under the hood, SSHing into the UTM.

To make a long story short, it appears that after I initially ran the Postgres rebuild script, without specifying "killall REPCTL" before executing "/etc/init.d/postgres -rebuild and then REPCTL after, it removed a WAL log file and was stuck in a loop looking for the specfic 16MB log file.

To resolve the Postgres DB corruption, I first terminated the Warm Standby EC2 instance and AWS automatically built a new one with the Cloud Formation template or AMI. Then I performed the same process on the Master instance. Once the Master booted and synced up with the Warm Standby everything was fine and has been ever since.

All I had to do then, was upload my Sophos UTM backup configuration and everything as been "Hunky Dory" every since.

Thanks for reaching out to me and offering assistance. The Sophos UTM 9 platform is new territory to me from a security appliance perspective, but am catching on fast, as I have a lot of Cisco/Snort IDS/IPS expertise.

I noticed you are the most dedicated individual in these Sophos forums and I look forward to collaborating more with you in the near future, as I am going into production with 5 corporate websites and switching DNS in Godaddy.

Best Regards,

Scott Spangler

Hi Bob,

I resolved the problem on my own last week. I have recently deployed in AWS,  the Sophos HA-UTM 9 platform in two separate VPC's, one with Linux based EC2 instances (web-application servers) and an Windows (VPC) with corporate domain controller, VPN's and RDP services. 

In the Linux VPC where this problem originated, I have the Sophos UTM with one EIP as my main entry point. Then have multiple AWS internal ELB's listening on port 443/80 monitoring EC2 instance pairs within AWS Auto Scaling Groups for each web site. And AWS RDS DB's on the backend.

On the Sophos UTM in the Linux VPC, I have a 100GB EBS volume. After performing the initial Sophos HA-UTM deployment through AWS Cloudformation, I noticed after a week or so, that my disk space allocation was over 90%. Then I got under the hood, SSHing into the UTM.

To make a long story short, it appears that after I initially ran the Postgres rebuild script, without specifying "killall REPCTL" before executing "/etc/init.d/postgres -rebuild and then REPCTL after, it removed a WAL log file and was stuck in a loop looking for the specfic 16MB log file.

To resolve the Postgres DB corruption, I first terminated the Warm Standby EC2 instance and AWS automatically built a new one with the Cloud Formation template or AMI. Then I performed the same process on the Master instance. Once the Master booted and synced up with the Warm Standby everything was fine and has been ever since.

All I had to do then, was upload my Sophos UTM backup configuration and everything as been "Hunky Dory" every since.

Thanks for reaching out to me and offering assistance. The Sophos UTM 9 platform is new territory to me from a security appliance perspective, but am catching on fast, as I have a lot of Cisco/Snort IDS/IPS expertise.

I noticed you are the most dedicated individual in these Sophos forums and I look forward to collaborating more with you in the near future, as I am going into production with 5 corporate websites and switching DNS in Godaddy.

Best Regards,

Scott Spangler

Hi Scott,

Thanks for the details, I am only starting to use Sophos so I got stuck at the "postgres rebuild" step. Can you please point me to the docs on how to do that properly?

Thanks and regards,

Efren

Hi Efren and welcome to the UTM Community!

I'm not sure what your question is.  Are you looking for the following command?

/etc/init.d/postgresql92 rebuild

Cheers - Bob

Thanks Bob,

That was the one. Unfortunately I can not find any documentation explaining what it will do or what to consider when running it.

I also contacted support and they suggested the same, but sadly, the issue remains. It looks like the rebuild worked, but it can not log anyway and the disk usage keeps growing.

Efren, If first-level Support can't help you, you should request escalation - someone needs to take a closer look.

The rebuild just deletes the history in graphs and Reporting, rebuilds some databases like the SMTP Quarantine and Queue and does not touch the log files.

Cheers - Bob