Search Postgresql Archives

Re: Can't make backup

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





Albe Laurenz <laurenz.albe@xxxxxxxxxx> escribió:
Sebastián Baioni wrote:
>>>>> Every day we run a Windows Programmed pg_dump, it used to
>>>>> work fine with PostgreSQL 8.0, but since we installed the new
>>>>> version we are not able to make a backup. We tried to make a
>>>>> whole database backup and it never ends. We tried to backup
>>>>> table by table and it reachs a table of 50,000 rows and it
>>>>> keeps for hours in that table, we started it again and
>>>>> pg_dump backups that same table in seconds, but it stops in
>>>>> another different table.
>>>>> If we watch the server message.# we don't have any error,
>>>>> it's like some tables were locked and pg_dump would keep
>>>>> waiting for the table to be unlocked.
>>>
>>> We let running pg_dump 8.2.5 at 1:30 AM after an automatic
>>> reboot of the PostgreSQL service and it didn't finish. When I
>>> came to the office at 9:00 there were a lot of locks, but all
>>> of them were from the pg_dump conection.
>>>
>>> Is there any row in pg_locks that has a "false" in "granted"?
>>> That would be a wait for a lock to be released.

Is there, now?

Try
SELECT locktype, relation, pid, mode, granted FROM pg_catalog.pg_locks;

>> Does pg_dump consume any processor cycles?

Does it, now, while the dump hangs?

>> You say "Windows Programmed pg_dump" - what exactly is that?
>> Can you use a debugger to see where your program hangs?
>
> I don't know how it is "Tareas programadas" in english, I
> have Windows in spanish, but it's the tool where yuo set a
> work to execute in a determinated time. We call a .bat that execute
> pg_dump:
> "C:\Archivos de programa\PostgreSQL\8.2\bin\pg_dump.exe" -i -h 192.168.0.1 -p 5432 -U MyUser -F c -b -v -f "C:\back\pd_dump\si\si.backup" MyDB

That seems fine. Except that the "-i" is bad (I told you to use the same version of pg_dump as the server version).
And "-b" is without effect, and "-v" won't do anything for you. But no problem.

Maybe you could try "-F p" temporarily so that you can see where the output is stalled.

Also, when the dump hangs, what is the result of:

SELECT procpid, current_timestamp, query_start, current_query
FROM pg_catalog.pg_stat_activity;

Yours,
Laurenz Albe
Thank you everyone for your help, now backups works ok, I'm using the same pg_dump version than server and I took out the -i parameter.

Thank you.



Yahoo! Encuentros
Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros.
Visitá http://yahoo.cupidovirtual.com/servlet/NewRegistration

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux