[GENERAL] Can I pause the pg_dump or pg_restore
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? Thanks KevinChen
Re: [GENERAL] 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 r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_restore
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_restore
2009/5/20 Grzegorz Jaśkiewicz : > 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_restore
On Wed, May 20, 2009 at 2:53 PM, Chen, Dongdong (GE Healthcare) <
dongdongc...@ge.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
kill -CONT
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 ' 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[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
Re: [GENERAL] Can I pause the pg_dump or pg_restore
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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_restore
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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_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/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_restore
On 2009-05-20, Chen, Dongdong (GE Healthcare) 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I pause the pg_dump or pg_restore
On 2009-05-20, Sam Mason 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
