[GENERAL] Can't make backup (again)
Hi, Some days ago we were having problems running pg_dump (v. 8.2.5.7260) from Windows XP SP2 to a database in a sever PostgreSQL 8.2.5 on amd64-portbld-freebsd6.2. We thought the problem was solved but we are having problems again. Now we got an error: cannot allocate memory for input buffer The command was: COPY public.sipat00 (sipasede, ... ) TO stdout; ¿Is this a memory problem? ¿From server (FreeBSD) or from client PC (Windows XP)? Thanks Sebastián Sebastián Baioni http://www.acomplejados.com.ar http://www.extremista.com.ar http://www.coolartists.com.ar - Yahoo! Encuentros Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros. Visitá http://yahoo.cupidovirtual.com/servlet/NewRegistration
Re: [GENERAL] Can't make backup (again)
--- Richard Huxton [EMAIL PROTECTED] escribió: Sebastián Baioni wrote: Hi, Some days ago we were having problems running pg_dump (v. 8.2.5.7260) from Windows XP SP2 to a database in a sever PostgreSQL 8.2.5 on amd64-portbld-freebsd6.2. We thought the problem was solved but we are having problems again. Now we got an error: cannot allocate memory for input buffer The command was: COPY public.sipat00 (sipasede, ... ) TO stdout; ¿Is this a memory problem? Well, yes you can't allocate memory. ¿From server (FreeBSD) or from client PC (Windows XP)? Did you see the error on the client PC or the server? What do the server logs show? What was the memory usage on the client when this happened? -- Richard Huxton Archonet Ltd The error was in the client PC, the command we used in a command line was: C:\Program files\pgAdmin III\1.8\pg_dump.exe -h mihost -p 5432 -U miuser -F c -v -b -f C:\back\db.backup db I contacted the Admin and told me there was no erros nor in the postgresql log nor in the server log. I don't know the memory usage on the client, I'll try to repeat the error and check it. Thank you Sebastián Yahoo! Encuentros. Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros http://yahoo.cupidovirtual.com/servlet/NewRegistration ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't make backup (again)
--- Sebastián Baioni [EMAIL PROTECTED] escribió: --- Richard Huxton [EMAIL PROTECTED] escribió: Sebastián Baioni wrote: Hi, Some days ago we were having problems running pg_dump (v. 8.2.5.7260) from Windows XP SP2 to a database in a sever PostgreSQL 8.2.5 on amd64-portbld-freebsd6.2. We thought the problem was solved but we are having problems again. Now we got an error: cannot allocate memory for input buffer The command was: COPY public.sipat00 (sipasede, ... ) TO stdout; ¿Is this a memory problem? Well, yes you can't allocate memory. ¿From server (FreeBSD) or from client PC (Windows XP)? Did you see the error on the client PC or the server? What do the server logs show? What was the memory usage on the client when this happened? -- Richard Huxton Archonet Ltd The error was in the client PC, the command we used in a command line was: C:\Program files\pgAdmin III\1.8\pg_dump.exe -h mihost -p 5432 -U miuser -F c -v -b -f C:\back\db.backup db I contacted the Admin and told me there was no erros nor in the postgresql log nor in the server log. I don't know the memory usage on the client, I'll try to repeat the error and check it. Thank you Sebastián I forgot to tell that the error was seen on the client but it was a response from the server: pg_dump: Mensaje de error del servidor: cannot allocate memory for input buffer pg_dump: El comando es: COPY public.sipat00 (sipasede, ...) TO stdout; pg_dump: *** se abort¾ por un error *Mensaje de error del servidor = Server's Error Message *El comando es: The command is *se abortó por un error: it was aborted because an error. Los referentes más importantes en compra/ venta de autos se juntaron: Demotores y Yahoo! Ahora comprar o vender tu auto es más fácil. Vistá ar.autos.yahoo.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Can't make backup
Albe Laurenz [EMAIL PROTECTED] 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
Re: [GENERAL] Can't make backup
I don't have any table called pg_locks in pg_catalog. 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. Thanks Sebastián Albe Laurenz [EMAIL PROTECTED] 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. If pg_dump is locked out, there should be entries in pg_catalog.pg_locks. Check when the dump hangs! Does the dump file keep growing or not? Yours, Laurenz Albe - Los referentes más importantes en compra/venta de autos se juntaron: Demotores y Yahoo!.Ahora comprar o vender tu auto es más fácil. Visitá http://ar.autos.yahoo.com/
Re: [GENERAL] 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. If pg_dump is locked out, there should be entries in pg_catalog.pg_locks. Check when the dump hangs! I don't have any table called pg_locks in pg_catalog. It is a view. 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. Does pg_dump consume any processor cycles? You say Windows Programmed pg_dump - what exactly is that? Can you use a debugger to see where your program hangs? Yours, Laurenz Albe -- 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 -- Erik Jones wrote: The pg_locks view most certainly exists. Here's the list of all of the system catalog tables and view: http://www.postgresql.org/docs/ 8.2/interactive/catalogs.html. If you haven't looked at pg_locks how do you know there were a lot of locks? With the Server Status in the pg Admin III. Thanks Sebastián - Los referentes más importantes en compra/venta de autos se juntaron: Demotores y Yahoo!.Ahora comprar o vender tu auto es más fácil. Visitá http://ar.autos.yahoo.com/
[GENERAL] Can't make backup
Hi, We are running PostgreSQL 8.2.5 on amd64-portbld-freebsd6.2. We have 2 databases, one is a small one (and the pg_dump works ok) and the other one with 14,553,106 rows (the larget table has 3,700,000 rows). 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. Do we need to configure the server to be able to make a backup as we used to do it with 8.0 version? Thanks. Sebastián - Yahoo! Encuentros Ahora encontrar pareja es mucho más fácil, probá el nuevo Yahoo! Encuentros. Visitá http://yahoo.cupidovirtual.com/servlet/NewRegistration