PG_DUMP not working

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, 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?

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.

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