Re: Can't make backup

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