Re: postgresql packages for potato

[mwooding@thecity.sfsu.edu (Miguel Wooding SF Ten.Union)]

I tried again to dump the 6.3 database using postgresql-dump, and once 
again it doesn't appear to be working quite as advertised.

(1) As you instructed, I chown'ed the dumpall directory.  

(2) Then, as I noted in my previous email, postgresql-dump (or
something it calls) checks to see whether db.out is being created
under $PGDATA, but it's not too smart about how it checks, and so
doesn't recognize that '$PGDATA/../' puts it into the parent
directory.

(3) Also, again as I noted earlier, you have to set PGDATA in
/etc/postgresql/postmaster.init to be the same location as in the
slink version, or else move your data in advance. (Slink had it in
/var/postgres/data; potato defaults to the data going into
/var/lib/postgres/data.  The upgrade procedure outlined in
Debian.migration doesn't mention this difference, nor does the new
postmaster.init, but it wasn't all that hard to find after comparing
the new and old versions of postmaster.init. Still, it might be worth
mentioning.)

(4) Then, if there is any process running that contains the string
postmaster (like, say, 'less /etc/postmaster.init' or some such), a
warning appears from the kill command in postgresql-dump.  The grep
used isn't particularly sophisticated:

pm_pid=`ps ax | grep -s postmaster | grep -v grep | awk '{print $1}'`
                if [ -n "$pm_pid" ]
                then
                        kill $pm_pid
                fi

Not a particularly big deal, I suppose.

(5) postgresql-dump doesn't actually conect to the back end.  I think
the problem here is that psql isn't told that postmaster is running on
a different port.  The problem here, I believe, is that there is no -p
command in the following (from postgresql-dump):

if [ -n "${verbose}" ]
                then
                        echo "Finding the default encoding" >&2
                fi
                /usr/lib/postgresql/bin/pg_encoding `${BINDIR}/psql -q -t -d template1 -c "select encoding from pg_database where datname = 'template1'"` >$ENCODINGFILE
                
If I run just the psql query, I get the message:
$ /usr/lib/postgresql/dumpall/6.3/psql -q -t -d template1 -c "select encoding from pg_database where datname = 'template1'"Connection to database 'template1' failed.
connectDB() failed: Is the postmaster running and accepting connections at 'UNIX Socket' on port '5432'?

The answer to the question posed in the error message is, of course,
no.  postmaster was set up to run on 5431, not 5432, and PGPORT isn't
set.  So I tried again, specifying the port:

$ /usr/lib/postgresql/dumpall/6.3/psql -p 5431 -q -t -d template1 -c "select encoding from pg_database where datname = 'template1'"
ERROR:  attribute 'encoding' not found

(6) So that seems to solve the problem of connecting with the
postmaster, but still doesn't let me move forward.  I also tried
writing a modified postresql-dump.maw with the port number specified,
and it also terminates with the error message about not finding the
'encoding' attribute. I can't find anything that contains the string
encoding in the template1 database.

Note that db.out is generated, as is default_encoding (the latter is a
0 byte file).

So I'm a bit stuck about how to make this method work, but I thought
this information might be useful to you.

--Miguel