Thread: PG_DUMP not working

PG_DUMP not working

From

Dan99

Date:

24 September 2007, 21:30:09

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

Re: PG_DUMP not working

From

"Scott Marlowe"

Date:

25 September 2007, 14:32:14

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

Re: PG_DUMP not working

From

Alvaro Herrera

Date:

25 September 2007, 15:02:54

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)

Re: PG_DUMP not working

From

Dan99

Date:

26 September 2007, 15:09:52

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.

Re: PG_DUMP not working

From

Dan99

Date:

26 September 2007, 15:12:01

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?

Re: PG_DUMP not working

From

Alvaro Herrera

Date:

26 September 2007, 15:21:42

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