Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-25
13
Medium Priority
?
789 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:missymadi
11 Comments
 
LVL 1

Expert Comment

by:data_n_poker
ID: 35219082
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
 

Author Comment

by:missymadi
ID: 35219158
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
 
LVL 1

Expert Comment

by:data_n_poker
ID: 35219198
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:missymadi
ID: 35219248
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
 
LVL 1

Expert Comment

by:data_n_poker
ID: 35219293
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
 

Author Comment

by:missymadi
ID: 35219347
how do I log in from command line? I always use PGAdmin?
0
 
LVL 1

Expert Comment

by:data_n_poker
ID: 35219358
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
 
LVL 9

Expert Comment

by:fcontrepois
ID: 35219747
/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
 

Author Comment

by:missymadi
ID: 35227817
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
 

Author Comment

by:missymadi
ID: 35228025
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
 
LVL 23

Accepted Solution

by:
Mysidia earned 2000 total points
ID: 35237572
(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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question