Обсуждение: PG_DUMP not working
Hi, I found out this morning that I cannot get pg_dump to work at all on my database. It refuses to create a dump and instead just freezes. When using the verbose option (-v) i get the following output and then it stops (it at one point ran for days on end before i even noticed) pg_dump: saving encoding pg_dump: saving database definition pg_dump: reading schemas pg_dump: reading user-defined types pg_dump: reading user-defined functions pg_dump: reading user-defined aggregate functions pg_dump: reading user-defined operators pg_dump: reading user-defined operator classes pg_dump: reading user-defined tables I think this problem is somehow related to a VIEW problem that I have. I created a VIEW of semi-large tables, which did not come back with any errors. However, when I go to view it, it never finishes loading. Also, I cannot drop this view as this as well never finishes. PGSQL version: 7.4.2 Any help with this would be greatly appreciated. Thanks, Dan
On 9/18/07, Dan99 <power919@gmail.com> wrote: > Hi, > > I found out this morning that I cannot get pg_dump to work at all on > my database. It refuses to create a dump and instead just freezes. > When using the verbose option (-v) i get the following output and then > it stops (it at one point ran for days on end before i even noticed) > > pg_dump: saving encoding > pg_dump: saving database definition > pg_dump: reading schemas > pg_dump: reading user-defined types > pg_dump: reading user-defined functions > pg_dump: reading user-defined aggregate functions > pg_dump: reading user-defined operators > pg_dump: reading user-defined operator classes > pg_dump: reading user-defined tables > > I think this problem is somehow related to a VIEW problem that I > have. I created a VIEW of semi-large tables, which did not come back > with any errors. However, when I go to view it, it never finishes > loading. Also, I cannot drop this view as this as well never > finishes. > > PGSQL version: 7.4.2 Two things. What's in the postgresql logs (if you're not logging pgsql output, then turn it on and watch it while you're running pg_dump. Update your pgsql. 7.4.2 is old in two ways. the 7.4 branch is pretty old. plan an upgrade as soon as you can get this backup to work. Secondly, pg 7.4 is up to a number near 20 now, i.e. 7.4.18. There are over two years of bug fixes you're missing, and one of them could well be the solution to your problem. Upgrading from 7.4 to 8.2 requires a dump and reload, but 7.4.2 to 7.4.18 is just an rpm -Uvh or apt-get update away
Dan99 escribió: > Hi, > > I found out this morning that I cannot get pg_dump to work at all on > my database. It refuses to create a dump and instead just freezes. > When using the verbose option (-v) i get the following output and then > it stops (it at one point ran for days on end before i even noticed) > > pg_dump: saving encoding > pg_dump: saving database definition > pg_dump: reading schemas > pg_dump: reading user-defined types > pg_dump: reading user-defined functions > pg_dump: reading user-defined aggregate functions > pg_dump: reading user-defined operators > pg_dump: reading user-defined operator classes > pg_dump: reading user-defined tables > > I think this problem is somehow related to a VIEW problem that I > have. I created a VIEW of semi-large tables, which did not come back > with any errors. However, when I go to view it, it never finishes > loading. Also, I cannot drop this view as this as well never > finishes. Perhaps somebody has a lock on a table or view. Try select relation::regclass, database, transaction, pid, mode, granted from pg_locks; Do you see anything related to the view you created? -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "This is a foot just waiting to be shot" (Andrew Dunstan)
On Sep 25, 11:02 am, alvhe...@commandprompt.com (Alvaro Herrera) wrote: > Dan99 escribi�: > > > > > Hi, > > > I found out this morning that I cannot get pg_dump to work at all on > > my database. It refuses to create a dump and instead just freezes. > > When using the verbose option (-v) i get the following output and then > > it stops (it at one point ran for days on end before i even noticed) > > > pg_dump: saving encoding > > pg_dump: saving database definition > > pg_dump: reading schemas > > pg_dump: reading user-defined types > > pg_dump: reading user-defined functions > > pg_dump: reading user-defined aggregate functions > > pg_dump: reading user-defined operators > > pg_dump: reading user-defined operator classes > > pg_dump: reading user-defined tables > > > I think this problem is somehow related to a VIEW problem that I > > have. I created a VIEW of semi-large tables, which did not come back > > with any errors. However, when I go to view it, it never finishes > > loading. Also, I cannot drop this view as this as well never > > finishes. > > Perhaps somebody has a lock on a table or view. Try > > select relation::regclass, database, transaction, pid, mode, granted > from pg_locks; > > Do you see anything related to the view you created? > > -- > Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J > "This is a foot just waiting to be shot" (Andrew Dunstan) > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match I think I fixed my problem. I waited until the weekend when practically nobody was going to be using my site and then just restarted the server. After that I could delete the view and pg_dump worked.
On Sep 25, 10:32 am, scott.marl...@gmail.com ("Scott Marlowe") wrote: > On 9/18/07, Dan99 <power...@gmail.com> wrote: > > > > > Hi, > > > I found out this morning that I cannot get pg_dump to work at all on > > my database. It refuses to create a dump and instead just freezes. > > When using the verbose option (-v) i get the following output and then > > it stops (it at one point ran for days on end before i even noticed) > > > pg_dump: saving encoding > > pg_dump: saving database definition > > pg_dump: reading schemas > > pg_dump: reading user-defined types > > pg_dump: reading user-defined functions > > pg_dump: reading user-defined aggregate functions > > pg_dump: reading user-defined operators > > pg_dump: reading user-defined operator classes > > pg_dump: reading user-defined tables > > > I think this problem is somehow related to a VIEW problem that I > > have. I created a VIEW of semi-large tables, which did not come back > > with any errors. However, when I go to view it, it never finishes > > loading. Also, I cannot drop this view as this as well never > > finishes. > > > PGSQL version: 7.4.2 > > Two things. > > What's in the postgresql logs (if you're not logging pgsql output, > then turn it on and watch it while you're running pg_dump. > > Update your pgsql. 7.4.2 is old in two ways. the 7.4 branch is > pretty old. plan an upgrade as soon as you can get this backup to > work. Secondly, pg 7.4 is up to a number near 20 now, i.e. 7.4.18. > There are over two years of bug fixes you're missing, and one of them > could well be the solution to your problem. > > Upgrading from 7.4 to 8.2 requires a dump and reload, but 7.4.2 to > 7.4.18 is just an rpm -Uvh or apt-get update away > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend I currently am running apache + pgsql 7.4.2 + php 4.2. Do you think I would have any troubles when upgrading to pgsql 8.2 ~ ie. existing php queries not working?
Dan99 escribió: > > Update your pgsql. 7.4.2 is old in two ways. the 7.4 branch is > > pretty old. plan an upgrade as soon as you can get this backup to > > work. Secondly, pg 7.4 is up to a number near 20 now, i.e. 7.4.18. > > There are over two years of bug fixes you're missing, and one of them > > could well be the solution to your problem. > > > > Upgrading from 7.4 to 8.2 requires a dump and reload, but 7.4.2 to > > 7.4.18 is just an rpm -Uvh or apt-get update away > > I currently am running apache + pgsql 7.4.2 + php 4.2. Do you think I > would have any troubles when upgrading to pgsql 8.2 ~ ie. existing php > queries not working? There could be. However, upgrading to 7.4.18 (?) should work without issues, so short-term it is a very good idea to upgrade to that. Then you can plan an upgrade to 8.2 or 8.3 in a longer-term future. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support