Re: pg_dump in stand alone backend

Hi,

>> I would like to stop the postmaster every night and run
>>
>> vacuum
>> pg_dump
>> reindex
>>
>> in the stand alone backend.
>>
>> Vacuum and reindex seem to be quite easy, as I can setup a small
>> script with both commands. But what about pg_dump. That seems
>> "somewhat" more complex.
>
>
> Explain what exactly you are trying to do, why do you have to stop
> the postmaster ? If you request is due only to forbid the access then
> you can replace the pg_hba.conf with a "void" one and replace it again
> at the end of operations.
>
> BTW vacuum, pg_dump, reindex are operations that can be performed
> with the server up and running.

on my database server I have a lot of scripts running. Some of them are
not under my control and some of these don't really behave nice. So when
my "maintainance period" starts they will still be running for hours and
  take exclusive locks on 100,000+ rows in various tables. Which means
neither "vacuum full" nor "reindex" can get locks on these tables. Which
stalls these calls and delays them to a time when my well behaving but
heavy duty scripts start running again. Now these scripts will be
delayed be "vacuum" or "reindex" and when I get to my desk the next
morning the whole system is in overload.

I need to to disconnect any other users and do "vacuum full verbose
analyze" "reindex database" and reindex all tables. And for these I will
stop the postmaster and run a stand alone backend.

I figured that doing a nightly backup would be a good idea and running
it in stand alone mode will speed up the process drastically.

Ulrich