Is pg_dump supposed to work against prior version databases? I was
moving data from an old server (running 7.2devel from several months
ago) to a new server (running 7.2b3), using pg_dump on the *new* server,
and got the following message:

# pg_dump -h 172.16.1.84 -U postgres -t el_names -i lt_lcat > el_names.dmp
Password: <types in password>
pg_dump: query to obtain list of tables failed: ERROR:  Attribute
'relhasoids' not found

Not that I need to do this, but it was convenient and I thought it was
supposed to work. Is this a problem, or am I trying to do something
unsupported?

On a side note, I was *very* happy when I was able to load a table with
~40 million rows about 30 minutes (compared to about a day+ on the old
hardware, Red Hat 6.2, and early 7.2devel Postgres). And that was 
without changing the default postgresql.conf, because I forgot to do it 
before I started the copy ;-). Memory usage according to 'top' never 
exceeded about 5 MB.

Also worth noting, I installed successfully from an RPM which I built
from the source RPM.

Thank you to *everyone* involved in getting PostgreSQL to where it is 
today! It is truly an awesome product.

-- Joe



Wed, 28 Nov 2001 20:48:39 -0800
Joe Conway <joseph.conway@home.com>
7.2b3 pg_dump, general 7.2b3 comments
Joe Conway <joseph.conway@home.com> writes:
> Is pg_dump supposed to work against prior version databases?

Since 7.1.1 we've tried to make it do so.

> # pg_dump -h 172.16.1.84 -U postgres -t el_names -i lt_lcat > el_names.dmp
> Password: <types in password>
> pg_dump: query to obtain list of tables failed: ERROR:  Attribute
> 'relhasoids' not found

<scratches head>  Odd.  There's only one query in pg_dump that touches
relhasoids, and it's set up to only be used when "remoteVersion >= 70200".
It seems to work here, too: I can dump from a 7.1 or even 7.0 server
with current pg_dump.  Would you burrow in there and see what's screwing
up the version-check code?

BTW, what happens if you leave off -i?  It shouldn't be necessary.

			regards, tom lane

Thu, 29 Nov 2001 00:36:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.2b3 pg_dump, general 7.2b3 comments 
Joe Conway <joseph.conway@home.com> writes:
> Is pg_dump supposed to work against prior version databases? I was
> moving data from an old server (running 7.2devel from several months
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> ago) to a new server (running 7.2b3), using pg_dump on the *new* server,
> and got the following message:

> # pg_dump -h 172.16.1.84 -U postgres -t el_names -i lt_lcat > el_names.dmp
> Password: <types in password>
> pg_dump: query to obtain list of tables failed: ERROR:  Attribute
> 'relhasoids' not found

Oh, never mind: I see it.  pg_dump's test is on whether the server calls
itself 7.2 or not.  You've evidently got a copy from back before the
changes to make OIDs optional.  The versioning code is not set up to
deal with intermediate development versions, so it gets it wrong about
what query to use.  You'll need to use the pg_dump of the same vintage
as the 7.2devel server.

You're a brave man to be putting production data on CVS-tip servers.
I wouldn't recommend it ;-)

			regards, tom lane

Thu, 29 Nov 2001 00:46:17 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.2b3 pg_dump, general 7.2b3 comments 
Tom Lane wrote:

> Joe Conway <joseph.conway@home.com> writes:
> 
>>Is pg_dump supposed to work against prior version databases? I was
>>moving data from an old server (running 7.2devel from several months
>>
>                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> 
>>ago) to a new server (running 7.2b3), using pg_dump on the *new* server,
>>and got the following message:
>>
> 
>># pg_dump -h 172.16.1.84 -U postgres -t el_names -i lt_lcat > el_names.dmp
>>Password: <types in password>
>>pg_dump: query to obtain list of tables failed: ERROR:  Attribute
>>'relhasoids' not found
>>
> 
> Oh, never mind: I see it.  pg_dump's test is on whether the server calls
> itself 7.2 or not.  You've evidently got a copy from back before the
> changes to make OIDs optional.  The versioning code is not set up to
> deal with intermediate development versions, so it gets it wrong about
> what query to use.  You'll need to use the pg_dump of the same vintage
> as the 7.2devel server.
> 
> You're a brave man to be putting production data on CVS-tip servers.
> I wouldn't recommend it ;-)
> 
> 			regards, tom lane
> 


Thanks, but I'm not that brave. This has been my proof-of-concept server 
for the past 6 or so months. The new server is intended to become a 
production, relatively high volume data collection server on our factory 
floor. I've finally been successful at selling our upper management on 
using PostgreSQL in lieu of commercial databases for this purpose (we 
already have multiple instances of brand O and brand M). In fact, given 
the current economy, they're enthusiastic about it ;-)

In any case, I've already moved the data, just wanted to report the 
possible issue. It sounds like it shouldn't affect any but the highly 
adventurous!

BTW, after your first reply, I started to load the new pg_dump into gdb 
and discovered it had no debug symbols (recall I installed from RPM). Is 
there a way to install the RPM with additional configure options without 
rebuilding it? Is there any significant downside (performance or 
otherwise) to having --enable-debug on a production server?

-- Joe





Wed, 28 Nov 2001 22:17:50 -0800
Joe Conway <joseph.conway@home.com>
Re: 7.2b3 pg_dump, general 7.2b3 comments
Joe Conway <joseph.conway@home.com> writes:
> BTW, after your first reply, I started to load the new pg_dump into gdb 
> and discovered it had no debug symbols (recall I installed from RPM). Is 
> there a way to install the RPM with additional configure options without 
> rebuilding it?

Don't know; certainly you'd have to recompile, but I dunno if you have
to modify the source RPM or not.  Lamar?

> Is there any significant downside (performance or 
> otherwise) to having --enable-debug on a production server?

If you're compiling with gcc then I believe the only cost is the disk
footprint of the debug info.  On some other compilers, --enable-debug 
disables most compiler optimizations, which can mean a significant
speed penalty.  We currently have the following in the installation
guide:

     --enable-debug

         Compiles all programs and libraries with debugging
         symbols. This means that you can run the programs through a
         debugger to analyze problems. This enlarges the size of the
         installed executables considerably, and on non-GCC compilers it
         usually also disables compiler optimization, causing
         slowdowns. However, having the symbols available is extremely
         helpful for dealing with any problems that may
         arise. Currently, this option is considered of marginal value
         for production installations, but you should have it on if you
         are doing development work or running a beta version.

     --enable-cassert

         Enables assertion checks in the server, which test for many
         "can't happen" conditions. This is invaluable for code
         development purposes, but the tests slow things down a
         little. Also, having the tests turned on won't necessarily
         enhance the stability of your server! The assertion checks are
         not categorized for severity, and so what might be a relatively
         harmless bug will still lead to server restarts if it triggers
         an assertion failure.  Currently, this option is not
         recommended for production use, but you should have it on for
         development work or when running a beta version.

Perhaps "marginal value" is too lukewarm an assessment, at least for
gcc users.  Comments?

			regards, tom lane

Thu, 29 Nov 2001 01:27:26 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.2b3 pg_dump, general 7.2b3 comments 
Tom Lane wrote:

> Perhaps "marginal value" is too lukewarm an assessment, at least for
> gcc users.  Comments?
> 

Well, if I have problems on my production machine, and I post a question 
here, the first thing I'll be asked for is a backtrace, preferably with 
symbols, right ;-)

So ISTM, that if there is no penalty except (a relatively trivial amount 
of) disk space with gcc, then that ought to be the preferred 
configuration for gcc. The "value" for a production installation is 
significant if it helps get a problem fixed faster.

-- Joe


Wed, 28 Nov 2001 22:42:24 -0800
Joe Conway <joseph.conway@home.com>
Re: 7.2b3 pg_dump, general 7.2b3 comments
On Thursday 29 November 2001 01:27 am, Tom Lane wrote:
> Joe Conway <joseph.conway@home.com> writes:
> > BTW, after your first reply, I started to load the new pg_dump into gdb
> > and discovered it had no debug symbols (recall I installed from RPM). Is
> > there a way to install the RPM with additional configure options without
> > rebuilding it?

> Don't know; certainly you'd have to recompile, but I dunno if you have
> to modify the source RPM or not.  Lamar?

Hmmm. You know, it would be a good idea, IMHO, to enable debugging symbols in 
the beta RPMs anyway.  So, I will do that for the next beta (or release 
candidate -- although, with that report from Jan, I wonder what the next 
release will be).

In the meantime, Joe, if you can rebuild the RPM from source, here's what to 
do:
1.)	rpm -i the source RPM.
2.)	If you've never built from a source RPM before, see 
/usr/share/docs/postgresql-7.2b3/README.rpm-dist for some more information.
3.)	Edit the spec file (on Red Hat, that would be in /usr/src/redhat/SPECS, 
name of 'postgresql.spec'), adding the following line near the top:
%define __os_install_post /usr/lib/rpm/brp-compress
4.)	Add the configure option.  The rpm build process by default runs strip on 
the binaries.....
5.)	rpm -ba postgresql.spec, wait a few minutes for the build, and pick up 
your RPMs in /usr/src/redhat/RPMS/i386.

Or you can just wait until I upload a set with that line in it..... :-)

-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Thu, 29 Nov 2001 19:59:04 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: 7.2b3 pg_dump, general 7.2b3 comments
On Thursday 29 November 2001 07:59 pm, Lamar Owen wrote:
> Or you can just wait until I upload a set with that line in it..... :-)

Said set is now being uploaded to ftp.postgresql.org. Look for the 0.3PGDG 
sbinary et in /pub/binary/beta/RPMS/redhat-7.2, and the source in 
......./SRPMS

My my, --enable-debug and disabling stripping in the build sure does inflate 
the package size :-O

For reference: The entire binary set for 7.2b3-0.2PGDG weighs in at 7,036KB. 
The debug-enabled 7.2b3-0.3PGDG set weighs in at 11,240KB. The biggest 
increase is in the postgresql-contrib package, which increases from 1,192KB 
to 2,940KB.

This build also has --enable-cassert.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Thu, 29 Nov 2001 20:48:04 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: 7.2b3 pg_dump, general 7.2b3 comments
Lamar Owen wrote:

> Hmmm. You know, it would be a good idea, IMHO, to enable debugging symbols in 
> the beta RPMs anyway.  So, I will do that for the next beta (or release 
> candidate -- although, with that report from Jan, I wonder what the next 
> release will be).
> 
> In the meantime, Joe, if you can rebuild the RPM from source, here's what to 
> do:
> 1.)	rpm -i the source RPM.
> 2.)	If you've never built from a source RPM before, see 
> /usr/share/docs/postgresql-7.2b3/README.rpm-dist for some more information.
> 3.)	Edit the spec file (on Red Hat, that would be in /usr/src/redhat/SPECS, 
> name of 'postgresql.spec'), adding the following line near the top:
> %define __os_install_post /usr/lib/rpm/brp-compress
> 4.)	Add the configure option.  The rpm build process by default runs strip on 
> the binaries.....
> 5.)	rpm -ba postgresql.spec, wait a few minutes for the build, and pick up 
> your RPMs in /usr/src/redhat/RPMS/i386.
> 
> Or you can just wait until I upload a set with that line in it..... :-)
> 


Thanks for the instructions, Lamar -- that helps quite a bit. I'll give 
this a try next week.

Joe





Fri, 30 Nov 2001 17:50:15 -0800
Joe Conway <joseph.conway@home.com>
Re: 7.2b3 pg_dump, general 7.2b3 comments
On Friday 30 November 2001 08:50 pm, Joe Conway wrote:
> Lamar Owen wrote:
> > Or you can just wait until I upload a set with that line in it..... :-)

> Thanks for the instructions, Lamar -- that helps quite a bit. I'll give
> this a try next week.

While you're certainly welcome to do the work yourself, you are also equally 
welcome to download the latest RPMset, which has debugging symbols enabled, 
AFAIK.
-- 
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Sat, 1 Dec 2001 21:34:30 -0500
Lamar Owen <lamar.owen@wgcr.org>
Re: 7.2b3 pg_dump, general 7.2b3 comments
Lamar Owen wrote:

>>Lamar Owen wrote:
> While you're certainly welcome to do the work yourself, you are also equally 
> welcome to download the latest RPMset, which has debugging symbols enabled, 
> AFAIK.
> 


Thanks, I saw that, but I'd like to know how to do these kinds of 
adjustments to RPMs anyway. I normally prefer to install from source 
tarballs, or even CVS, but for production servers our network admins get 
a warm fuzzy from RPMs. It gives them more confidence they can reproduce 
the server setup if they need to after I hit the Lotto and retire ;-)

Joe





Sat, 01 Dec 2001 22:30:04 -0800
Joe Conway <joseph.conway@home.com>
Re: 7.2b3 pg_dump, general 7.2b3 comments