Solved

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

Posted on 2011-03-25
13
778 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:missymadi
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

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

Expert Comment

by:data_n_poker
Comment Utility
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
Comment Utility
/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
Comment Utility
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
Comment Utility
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
Comment Utility
(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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Trying to make SNMP connection work 7 39
PL/SQL - Leading zeros 7 39
how to fix this error 14 44
sql calculate averages 18 21
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now