Re: Can I pause the pg_dump or pg_restore

On Wed, May 20, 2009 at 2:53 PM, Chen, Dongdong (GE Healthcare) <
DongdongChen(at)ge(dot)com> wrote:

> In my application, when press button "Backup" in UI, it invokes pg_dump
> to backup the database. It may take few minutes for the whole process. If I
> want to pause the process, what should I do. And also how to resume the
> process? Can I pause while pg_restore?
>

I don't know if the following would be recommended, but did some research
and it seems that one can use STOP and CONT signals on Linux/Unix to
pause/continue a process. There are equivalent things you can do on NT based
Windows too, for eg. by using ProcessExplorer's suspend/resume menu options
for a process.

kill -STOP <pid>
kill -CONT <pid>

So, you have a choice of pausing/resuming the clients (like
psql/pg_dump/pg_restore) or if you have access to the DB host, then you can
pause/resume the backend process that is performing the dump/restore. I
myself generally wouldn't recommend mucking with a backend process.

>
Although it seems safe, it does come with some implications if not
excercised properly. If the process being paused has taken any DB level
locks, you'll see contention because of those locks. You might also see long
running '<IDLE> in transaction' session, which hinder in Vacuum process.

As I said, I am not sure if it would be recommended by experts, so would
recommend waiting for someone else to respond to this approach.

Best regards,
--
Lets call it Post-gres

EnterpriseDB http://www.enterprisedb.com

gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device