[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-03-25
13
Medium Priority
?
795 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Cron is one of the most popular and basic utilities found on Unix systems. Combined with other tools, cron makes it exceptionally easy to automate a broad range of tasks on your server.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

612 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