I am glad that you have your code working but I am confused. What does the current values of STDIN, STDOUT and STDOUT have to with opening a pipe or the pipe system call in general?

If I run this code, it works exactly as expected, i.e. there is a file /tmp/err, containing the output of the ps command.

#!/usr/bin/perl
# vim: sw=4

use strict;
use warnings;

close STDIN;
close STDOUT;
close STDERR;
open STDIN, "</dev/null";
open STDOUT, ">/dev/null";
open STDERR, ">/dev/null";

open my $pipe, "ps aux |" or die "pipe failed:$!\n";
open my $err, ">", "/tmp/err" or die "couldn't open /tmp/err :$!\n";
while (<$pipe>) {
    print $err $_;
}
close $err;
[download]

I realise that the original code had re-opened STDERR to the apache error log.

To answer the question regarding the need to close STDIN, STDOUT and STDERR. This usually done when you want to create a daemon or process that is intended to run in the background. I have never seen someone do it in the parent before. Normally it is done in the child so that the child will be owned by init etc.
Here is a URL for a bit more info on writing a daemon in Perl if you are curious.