Skip site navigation (1) Skip section navigation (2)

Re: pg_dump in stand alone backend

From: Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se>
To: Gaetano Mendola <mendola(at)bigfoot(dot)com>,pgsql-general(at)postgresql(dot)org
Subject: Re: pg_dump in stand alone backend
Date: 2004-08-24 08:27:31
Message-ID: 412AFBF3.10403@relevanttraffic.se (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Michael SeeleDate: 2004-08-24 09:28:24
Subject: replace array values in a select statement
Previous:From: Paul ThomasDate: 2004-08-24 08:20:43
Subject: Re: Connection to a PG 8.0 Beta 1 win32 server

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group