Solved

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

Posted on 2011-03-25
13
780 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
13 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
.lee file containing data 5 41
Currency in SQL? 2 29
corrupt Databases 9 62
average of calculation (TSQL) 4 26
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

791 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