perpetual dump/restore problem

wu_zhong_min@vrane.com wrote:

Dear List,

As many of you know every time one wants to upgrade to a major server
version, as I want to do now, there is a need for pg_dumpall and
restore. This is a huge undertaking and requires lots of testing and
planning. I do hope that in the future this requirement will be
dropped.

There is currently some discussion about an upgrade tool, but there has
been discussion before. It's not a simple problem, and it won't reduce
the need for testing, just the dump/restore.

I am now upgrading from 8.1.9 to 8.2.4 and pg_dumpall gives a dump
file which has a command "\connect template0"

OK. If you're not in a hurry, and can wait a few months 8.3 will be out.

Normally template0 does not accept connection but I found a way to
make that happen in the documentation on postgresql website. But is
it generally safe to accept the connection for template0 all the
time? Why won't pg_dump include a line to accept connection for
template0 just before it writes "\connect template0" and then remove
the permission after it is done with template0?

Might be a bug, but first; did you dump using 8.2's pg_dumpall?

--
Richard Huxton
Archonet Ltd

OK. If you're not in a hurry, and can wait a few months 8.3 will be out.

I am moving the database to a new physical server and it makes sense to move to the latest version now.

for template0 all the
time? Why won't pg_dump include a line to accept connection for
template0 just before it writes "\connect template0" and then remove
the permission after it is done with template0?

Might be a bug, but first; did you dump using 8.2's pg_dumpall?

Yes. I tried both 8.1 and 8.2 pg_dumpall many times. I think that, with one try with 8.1, it does not give "connect template0".

wu_zhong_min@vrane.com writes:

time? Why won't pg_dump include a line to accept connection for
template0 just before it writes "\connect template0" and then remove
the permission after it is done with template0?

AFAICS it would not be issuing that unless you'd made template0
connectable on the source database. That is not a good thing;
undo it.

regards, tom lane

You are right. I have now corrected that and
I believe that should fix the problem.

On Jul 5, 2007, at 2:49 AM, Richard Huxton wrote:

wu_zhong_min@vrane.com wrote:

Dear List,
As many of you know every time one wants to upgrade to a major server
version, as I want to do now, there is a need for pg_dumpall and
restore. This is a huge undertaking and requires lots of testing and
planning. I do hope that in the future this requirement will be
dropped.

There is currently some discussion about an upgrade tool, but there
has been discussion before. It's not a simple problem, and it won't
reduce the need for testing, just the dump/restore.

One shot at that would be the pg_migrator tool that Enterprise DB has
put up on pgfoundry (http://pgfoundry.org/frs/?
group_id=1000235&release_id=763). It does have it's caveats,
though, so make sure to carefully read the documentation in the
download.

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com