Thread: Can I pause the pg_dump or pg_restore

On 20/05/2009 10:23, Chen, Dongdong (GE Healthcare) 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 presume you're using pgAdmin III.....if so, than AFAIK you can't pause
them, though you may get a better answer on the pgadmin-support list.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

restore you can, kind of pause, by pausing data feed to it. (assuming
you have a control over pipe, and you are using text dump format).
as for pg dump, I don't think there's a way.

2009/5/20 Grzegorz Jaśkiewicz <gryzman@gmail.com>:
> restore you can, kind of pause, by pausing data feed to it. (assuming
> you have a control over pipe, and you are using text dump format).
> as for pg dump, I don't think there's a way.
>

pg_dump should be pauseable, if you use your OS's job-handling tools.
For instance, in Linux, you can press "Ctrl+Z" while running a
program, and then run run "fg" later to resume it. You can also script
this with various command-line utilities.

On Wed, May 20, 2009 at 06:22:46PM +0530, Gurjeet Singh wrote:
> 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.

As David noted; sending a STOP is exactly what your shell does for you
when you hit the Ctrl+Z key combination.  It'll subsequently send a CONT
when you put it into the background (by typing "bg") or resume it in the
foreground (when using "fg").

Note that when used on the pg_dump process all you're doing is stopping
it from writing out the backup.  The server process will still be
running and waiting for the backup to finish writing the data.  It will
thus hold the transaction open and any other state needed to keep things
going.  This should be fine for temporary pauses, but it wouldn't be
recommended to pause the backup for days at a time.

--
  Sam  http://samason.me.uk/

Sam Mason wrote:
> On Wed, May 20, 2009 at 06:22:46PM +0530, Gurjeet Singh wrote:
>
>> 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.
>>
>
> As David noted; sending a STOP is exactly what your shell does for you
> when you hit the Ctrl+Z key combination.  It'll subsequently send a CONT
> when you put it into the background (by typing "bg") or resume it in the
> foreground (when using "fg").
>
> Note that when used on the pg_dump process all you're doing is stopping
> it from writing out the backup.  The server process will still be
> running and waiting for the backup to finish writing the data.  It will
> thus hold the transaction open and any other state needed to keep things
> going.  This should be fine for temporary pauses, but it wouldn't be
> recommended to pause the backup for days at a time.
>
>
Just curious.... why would you want to pause a backup/restore?

On Wed, May 20, 2009 at 03:39:39PM +0100, Howard Cole wrote:
> Sam Mason wrote:
> >Note that when used on the pg_dump process all you're doing is stopping
> >it from writing out the backup.  The server process will still be
> >running and waiting for the backup to finish writing the data.  It will
> >thus hold the transaction open and any other state needed to keep things
> >going.  This should be fine for temporary pauses, but it wouldn't be
> >recommended to pause the backup for days at a time.
>
> Just curious.... why would you want to pause a backup/restore?

Yes, it seems a little perverse.  There seem to be valid use cases,
disk/cpu time need temporarily elsewhere being one.  As the poem goes;
"ours not to reason why"... (hum, I think it's supposed to be "theirs
not to..", ah well).

--
  Sam  http://samason.me.uk/

On 2009-05-20, Chen, Dongdong (GE Healthcare) <DongdongChen@ge.com> wrote:
> This is a multi-part message in MIME format.
>
> 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?

POSIX has SIGSTOP and SIGCONT. you could send them to the pg_dump task
using kill() if you have permission (invoker does) and its PID.

as others have you can also force the process to pause by throttleing it's
I/O.

if you invoke pg_dump using popen() (or similar)
when you stop reading the output pg_dump will (after filling the
buffer) also stop and wait for you to resume reading.

the converse is true when restoring.

Windows may have similar features available, no doubt with different
names and APIs.

On 2009-05-20, Sam Mason <sam@samason.me.uk> wrote:
> On Wed, May 20, 2009 at 03:39:39PM +0100, Howard Cole wrote:
>> Sam Mason wrote:
>> >Note that when used on the pg_dump process all you're doing is stopping
>> >it from writing out the backup.  The server process will still be
>> >running and waiting for the backup to finish writing the data.  It will
>> >thus hold the transaction open and any other state needed to keep things
>> >going.  This should be fine for temporary pauses, but it wouldn't be
>> >recommended to pause the backup for days at a time.
>>
>> Just curious.... why would you want to pause a backup/restore?
>
> Yes, it seems a little perverse.  There seem to be valid use cases,
> disk/cpu time need temporarily elsewhere being one.  As the poem goes;
> "ours not to reason why"... (hum, I think it's supposed to be "theirs
> not to..", ah well).

In that case just invoke it with a sufficiently low priority and let the O/S deal
with that issue.