Thread: Problems with new data location

Right after a new installation (7.1.1) and building my first table with the
data directory being the normal /usr/local/pgsql/data, I decided I wanted to
put my data directory on a different partition, for space reasons (room for
database growth) - I chose /var/pgdata.  At first I tried just copying the
whole data directory, but I got some sort of error (I don't remember it
now).  Then I tried creating /var/pgdata fresh and doing initdb -D
/var/pgdata, and that was okay until I tried to create the database again,
when it said a database of that name already existed.  Yes, I have changed
my startup script to reflect the new path.  The next thing I tried was to
change the name of /usr/local/pgsql/data to /usr/local/pgsql/data.old (to
hide it), erase the stuff in /var/pgdata, and then go through the initdb,
pg_ctl, createdb process again.  Now createdb complains:
   pgsql: Missing or erroneous pg_hba.conf file, see postmaster log for
details
   createdb: database creation failed
I have a perfectly good /var/pgdata/pg_hba.conf file.  Is it still looking
for stuff in /usr/local/pgsql/data?  How can I determine where it is
looking?  I don't where the "postmaster log" is - the log file I specify
with pg_ctl -l (/var/pgdata/serverlog) is not showing anything about this
(just one older error about a lock file).  What is the right way to move a
data directory or to start over with a new one? (It would be gravy to keep
my already created database, but starting over is no big deal - there is no
data in it yet.)

Thanks for your help.

--------------------------------
Karen Ellrick
S & C Technology, Inc.
1-21-35 Kusatsu-shinmachi
Hiroshima  733-0834  Japan
(from U.S. 011-81, from Japan 0) 82-293-2838
--------------------------------

"Karen Ellrick" <k-ellrick@sctech.co.jp> writes:
> ... Then I tried creating /var/pgdata fresh and doing initdb -D
> /var/pgdata, and that was okay until I tried to create the database again,
> when it said a database of that name already existed.  Yes, I have changed
> my startup script to reflect the new path.

But ... um ... did you restart the postmaster with the new path argument?
Or only change the script?

BTW, it should work fine to move a data-directory tree from one place to
another (with cp -p -r or tar/untar according to preference).  But you
have to shut down the old postmaster *first*.

            regards, tom lane

Tom Lane wrote:
> But ... um ... did you restart the postmaster with the new path argument?
> Or only change the script?

Well, now I can't remember the exact sequence I used originally - I probably
tried to restart only after all the changes had taken place.  I do remember
getting the error "can't find /usr/local/pgsql/data/postmaster.pid - is
postmaster running?", and dummy me, I assumed it wasn't, without checking
ps.  Anyway, restarting doesn't seem to work if I actually move the
directory rather than copy it.  But after I got your message, I checked ps
and discovered that indeed, postmaster was still running.  So I put the
directory back in its old place, successfully ran "pg_ctl -D
/usr/local/pgsql/data stop", then moved the directory, and then successfully
ran "pg_ctl -D /var/pgdata stop".

> BTW, it should work fine to move a data-directory tree from one place to
> another (with cp -p -r or tar/untar according to preference).  But you
> have to shut down the old postmaster *first*.

Yup, the key is that both those methods copy (rather than move) the
directory.  If you copy, you can use restart (I assume - I didn't try it).
If you want to simply move the directory, you have to (1) stop, (2) move,
(3) start.

Thanks for pointing me in the right direction, and also giving me the
assurance that a data directory can indeed be moved without redoing initdb.

Karen

"Karen Ellrick" <k-ellrick@sctech.co.jp> writes:
> Yup, the key is that both those methods copy (rather than move) the
> directory.  If you copy, you can use restart (I assume - I didn't try it).

Just for the benefit of the archives: copying the directory tree while
the postmaster is live is exactly what I *don't* recommend you do.
There's too much risk of copying an inconsistent fileset if any
backends, checkpoint processes, etc happen to be making changes while
you do the copy.  You might get away with it ... or you might not.

You can't 'mv' the $PGDATA directory while it's live either, but for
a different set of reasons (the backends use absolute pathnames to
access database files).

            regards, tom lane