Thread: Problem with pg_dump

I seem to be having a problem with pg_dump in 8.1.2, it's not dumping indexes at all.  Is this a known problem, should I just do an upgrade?

Thanks,

Alex Turner
Mint Pixels

am  Thu, dem 09.11.2006, um 13:04:31 -0500 mailte Alex Turner folgendes:
> I seem to be having a problem with pg_dump in 8.1.2, it's not dumping indexes
> at all.  Is this a known problem, should I just do an upgrade?

I can't see a necessity to dump a index. But, i hope, and i'm sure,
pg_dump dumps the definition for a index...


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

A. Kretschmer wrote:
> am  Thu, dem 09.11.2006, um 13:04:31 -0500 mailte Alex Turner folgendes:
>
>> I seem to be having a problem with pg_dump in 8.1.2, it's not dumping indexes
>> at all.  Is this a known problem, should I just do an upgrade?
>>
>
> I can't see a necessity to dump a index. But, i hope, and i'm sure,
> pg_dump dumps the definition for a index...
>
>
The dump includes the sql statements ro build the indices as part of the
restore process, the contents of the indices are not dumped. At least in
my experience.

Brent Wood

Well yes - thats what I mean, the definition for the index.  It's not dumping the index defs.  It also looks like it's not dumping roles fully either.  I was missing grants from the dump too.

Alex

"Alex Turner" <armtuk@gmail.com> writes:
> Well yes - thats what I mean, the definition for the index.  It's not
> dumping the index defs.  It also looks like it's not dumping roles fully
> either.  I was missing grants from the dump too.

[ raised eyebrow... ]  Let's see a test case.

            regards, tom lane

On Fri, 2006-11-10 at 11:23 -0500, Tom Lane wrote:
> "Alex Turner" <armtuk@gmail.com> writes:
> > Well yes - thats what I mean, the definition for the index.  It's not
> > dumping the index defs.  It also looks like it's not dumping roles fully
> > either.  I was missing grants from the dump too.
>
> [ raised eyebrow... ]  Let's see a test case.

Alex, what Tom is saying here is... this is likely user error :). Can
you provide the exact pg_dump command you are using?

Sincerely,

Joshua D. Drake



>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Sure thing, I hope it's as simple as user error!

#!/bin/sh
export DATE=`date +%Y%m%d`

/usr/local/pgsql/bin/pg_dump -Upostgres -hlocalhost trend > /backup/trend.dump.$DATE

Thats pretty much it repeated for each database.  I just upgraded to 8.1.5 to see if that would fix the problem, but it didn't

Alex

I would be happy to point someone to the dump file, it's about 500Meg though

Alex

Alex Turner <armtuk@gmail.com> schrieb:

> Sure thing, I hope it's as simple as user error!
>
> #!/bin/sh
> export DATE=`date +%Y%m%d`
>
> /usr/local/pgsql/bin/pg_dump -Upostgres -hlocalhost trend > /backup/
> trend.dump.$DATE
>
> Thats pretty much it repeated for each database.  I just upgraded to 8.1.5 to
> see if that would fix the problem, but it didn't

I can't believe that. Please, create a new, simple database, create a
table with a index in this db, make a dump and paste this on
http://rafb.net/paste/ and tell us the link.

Something like this:
http://rafb.net/paste/results/8NHxOb70.html
Line 62 creates the INDEX.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Friday 10 November 2006 08:37, "Alex Turner" <armtuk@gmail.com> wrote:
> Sure thing, I hope it's as simple as user error!
>
> #!/bin/sh
> export DATE=`date +%Y%m%d`
>
> /usr/local/pgsql/bin/pg_dump -Upostgres -hlocalhost trend >
> /backup/trend.dump.$DATE
>
> Thats pretty much it repeated for each database.  I just upgraded to
> 8.1.5to see if that would fix the problem, but it didn't
>

If you're going to backup each database separately, you also need to run a
pg_dumpall -g to get global objects (roles, etc.), and restore that first.

--
They laughed at Columbus, they laughed at Fulton, they laughed at the
Wright brothers.  But they also laughed at Bozo the Clown." -- Carl Sagan

Ok - it is user error, my bad.  It looks like something bad happened during the restore, and indexes didn't get created.  I forgot that pg_dump creates indexes as upper case CREATE INDEX, and I was grepping for 'index', not 'INDEX'.

sorry for the confusion, to be honest I panicked when the thought occurred to me that I didn't have a good backup ;)

Alex.