What is the syntax to move Postgres data directory to a separate logical volumn?

Experts,
 
       I'm using Postgres9.02 on RHEL5. I need to move the data directory located in /opt/comp/CD_DB/DATA to our /dev/mapper/VolGroup00LogVol02/data . How do I accomplish this? I tried using

logon as cd_db
PATH=/usr/pgsql-9.0/bin
pg_ctl stop
exit

logon as root/Test
mv /opt/comp/CD_DB/DATA/* /data
exit

logon as postgres
PATH=/usr/pgsql-9.0/bin
export PGDATA=/data
initdb
pg_ctl start -D /data

Now I cannot get PGAdmin to connect. I thought /data would be dev/mapper/VolGroup00LogVol02/data

Thanks, Missymadi
missymadiAsked:
Who is Participating?
 
MysidiaConnect With a Mentor Commented:
(1) Don't initdb,  if you want to preserve existing data.
Initdb is for creating a new (blank) database cluster.

(2) " /dev/mapper/VolGroup00LogVol02/data  "   is the path to a LVM logical block device.  You are in way over your head, if you're trying to administer a PostgreSQL install, and not familiar with what a block device is.

A block device is not a directory.   You cannot store files there, until a filesystem has been prepared, and that filesystem has been mounted.
/dev/mapper/VolGroup00LogVol02/data   represents a LVM volume which is a logical block device mapped on top of physical block devices  (such as  /dev/sda1  /dev/sdb, etc)

Basically, it needs to be prepared via mkfs  (if that has not already been done) and then mounted, somewhere.

For example
mkfs -t ext3  /dev/mapper/VolGroup00LogVol02/data

(NOTE)   MKFS  destroys _any_  existing contents  of the block device.    The  MKFS operation and editing of FSTAB should be left to an experienced system administrator.

mkdir /mydata

Edit /etc/fstab
to add something like....
/dev/mapper/VolGroup00LogVol02/data     /vol2data           ext3     defaults,acl,noatime,rw  3 3


Then
mount /vol2data

Once the 'df'  and 'cat /proc/mounts' commands show  /mydata,  
you know the filesystem has been mounted.

After that you could move the data directory for postgresql

(3)
Assuming stock postgresql90 install from pgdb

/etc/init.d/postgresql stop

mv /var/lib/pgsql/data  /vol2data

Edit  /etc/sysconfig/pgsql/postgresql-9.0

change
PGDATA=/opt/comp/CD_DB/DATA
to
PGDATA=/vol2data/data

/etc/init.d/postgresql start

*NOTES*
  /etc/sysconfig/pgsql/postgresql-9.0
Is based on pgdb install assuming the init script basename is named /etc/init.d/postgresql-9.0

You'll need to change the name in the correct file for your install.
Which might be different if your Postgres install is a different version,
already hacked up, or not using stock init scripts from the postgresql
Yum repository for RHEL 5,  or has a custom instance name.








0
 
data_n_pokerCommented:
can you make sure that the new data directory is owned by postgres.

Are you getting any error messages from pgAdmin?  if so, what are they?  If not, what is happening when you do try to connect with pgAdmin?
0
 
missymadiAuthor Commented:
I don't think that I was pointing to the correct partition. The absolute path is dev/mapper/VolGroup00LogVol02/data. I was trying to use /data but this is the wrong place. How do I point to the dev/mapper... partition?

I did the above steps , then tried to connect to the db and when I entered the database user's password it just went back to the database listed with a red x. no error listed. What log file can I look at to see exactly the error?

 Thanks!
Missymadi
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
data_n_pokerCommented:
To view the log for pgadmin, select File->options and then select the logging tab in the options window.  This will show you the location of the pgAdmin log, simpley navigate to that directory and check it out.  

use the full path to point it to the deb mapper partition

export PGDATA=/dev/mapper/VolGroup00LogVol02/data

Open in new window


Check the error log, and post it back here if there is anything at all in it
0
 
missymadiAuthor Commented:
Well, I found the pgadmin.log but it only says, "ERROR connecting to the server: FATAL. the database system is shutting down.

So the steps should be as follows:
logon as cd_db
PATH=/usr/pgsql-9.0/bin
pg_ctl stop
exit
logon as root/Test
mv /opt/comp/CD_DB/DATA/* /dev/mapper/VolGroup00LogVol02/data
exit
logon as postgres
PATH=/usr/pgsql-9.0/bin
export PGDATA=/dev/mapper/VolGroup00LogVol02/data
initdb
pg_ctl start -D /dev/mapper/VolGroup00LogVol02/data
How do I set the PATH=/usr/pgsql-9.0/bin to so I can stop typing it every time?

Thanks! Missymadi
0
 
data_n_pokerCommented:
vi into the .bash_profile which is located in the home directory of the user.  and add the lines:

PATH=$PATH:/usr/pgsql-9.0/bin:.
Export PATH

Open in new window


are you able to pog into postgres from the command line after starting it up?
0
 
missymadiAuthor Commented:
how do I log in from command line? I always use PGAdmin?
0
 
data_n_pokerCommented:
after you issue the pg_ctl start command, type:

psql postgres

Open in new window


this will log you into the temp admin database.  to log into one of the databases you have in the system use:

psql -d <database name>

Open in new window

0
 
Frank ContrepoisVP Technical SalesCommented:
/dev/mapper/... Files need to be formated and mounted to be able to host files.

/dev contains file that in windows can be seen as drivers ( yeah I know it's a really oversimplified idea)

You need to format the device first
Then
Mount /dev/mapper/.... /path/to/new/data/dir

Then stop pgsql, copy all the files in the old data to the new one

Chenge the PGDATA variable to point to the new datadir and voila

PGDATA is probably set on the /etc/init.d/postgresql file
0
 
missymadiAuthor Commented:
I'm a little confused at this point. I did find that the custom scripts that run actually delete the postgres user and create a user ABCDB and a db ABCDB. Path to data /opt/comp/CD_DB/DATA

I tried moving mv opt/comp/CD_DB/DATA to dev/mapper/VolGroup00LogVol02/data
but I'm getting an error "dev/mapper/VolGroup00LogVol02/data" is not a directory. Please provide the steps I should take to accomplish this task ...I'm confusted at this point.
ABCDB is the owner of  opt/comp/CD_DB/DATA .
0
 
missymadiAuthor Commented:
When I look at Logical volume management is lists the logical volume of VolGroup00LogVol02 as mounting as /data
Is /data what I should be moving the /opt/comp/CD_DB/DATA to?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.