move data directory postgresql

Directions on how to move data directory for PostgreSQL on Redhat 6.

Get the data directory from pgsql command line. This can be done by running the following command either in pgsql or pgAdminIII.

SHOW data_directory;

To access the pgsql command line you must fire the following command. Depending on where  Postgres is installed you must use the correct path.

[command]# ./usr/pgsql-9.1/bin/psql -U postgres dp_toolkit_core

dp_toolkit_core=# SHOW data_directory;

data_directory

-------------------------------------------------

/var/lib/pgsql/9.1/data

(1 row)

dp_toolkit_core=# \q

Now after we have the directory lets try the following, lets stop the postgres service to see the name of the service we can run the following command to see all the services running.

[command]# service --status-all

This will give us a list of all the services available if it is not available or you can find it.. since we know this version of postgres is 9.1 we can try the following command to see if gives us any information.

[command]# service postgresql-9.1 status

(pid 1234) is running…

So now lets stop the service with the following command

[command]# service postgresql-9.1 stop

Once the service is stopped lets copy the data to a different directory whichever is your choice.  For reference go to the following post http://stackoverflow.com/questions/16678872/how-can-i-move-postgresql-data-to-another-directory-on-ubuntu-over-amazon-ec2

Now in my case the reason for moving the data directory is because the original install location is running out of space. So we will move to a different partition that has more space. It should be simple based on the stackoverflow.com answer.

Im going to move my directory to a folder called /home by issuing the following command. Please note

-a = preserve current permissions

-R = recursive

-v = verbose  - show the progress of the function

also make sure directories are made before you try to move the data

[command]# cp -aRv /var/lib/pgsql/9.1 /home/pgsql/9.1/data

Lets rename the folder from data to data old so it doesn’t cause any problems and we can also have it there in case we need a back up

[command]# mv /var/lib/pgsql/9.1/data /var/lib/pgsql/9.1/data_old

now lets create a symlink to our new directory in the old directory, the flag -s means this is a soft link, make sure you are in the original pgsql directory first and then issue the symlink command

[command]# cd /var/lib/pgsql/9.1/

[command]# ln -s /home/pgsql/9.1/data datadir

Now that we are done lets start the db server again  and see if we get any errors

[command]# service postgresql-9.1 start

If you have trouble starting the service go back and retrace the steps and make sure everything was done in order.

Now double check and make sure inserts, selects and other db functions are working normally.

GET through URL Rewrite Lighty lighttpd

one quick post about handling this. it took me a few hours to figure this out. make sure you handle the URL rewrite in the lighttpd conf otherwise you will see your GET params being dropped in PHP at least that was my experience here is the link where I found the correct wayt to rewrite through lighttpd


http://redmine.lighttpd.net/projects/1/wiki/Docs_ModRewrite


and then also check it out github for Slim example which is the framework I was using


https://github.com/codeguy/Slim/issues/405

Lighttpd Permission denied on home directory Alias

I recently ran into problems trying to set up lighttpd on redhat and giving home directory access for alias URL's After chmod and chown on all folders and directories I could see the log throwing a permssion denied when trying to display contents from the home folder. I was confused and spent a lot of time trying figure out what the problem was. It turns out this Article helped me understand a little more about the problem I faced

http://wiki.centos.org/HowTos/SELinux this link showed me how to check if SELinux is working and also understand about permissions to different processes. After I ran the following command

chcon -Rv --type=httpd_sys_content_t /home/lighttpd

everything started working. It had the wrong type and it was not allowing lighttpd to read the contents of the directory. I hope this helps someone out if the run into this problem.

Lighttpd, Python and Django

Before I continue I would like to say there are a lot of   great tutorials out there for setting up Lighttpd, Python and Django. I guess my case is a little different maybe not by much. I went from developing all of my django apps in windows server IIS 7 so things were a little different. For example in windows I used Python 2.7 in Redhat it comes with Python 2.6 by default but needed to upgrade or install Python 2.7 for that I loaded the repo from Python27 and installed then installed PIP for Python 27 and started installing all the libraries I had in windows. I will do a full write down of my install as for me it was difficult to figure out permissions and getting them on the right files

Linux Redhat 5 and Lighttpd

While installing and configuring Lighttpd for Redhat. I came across a problem that took me hours to figure out. After a lot of research I found the answer.

Problem:
           While everything was setup correctly, I could serve php pages, I  could not connect to my database (Postgres). I checked everything I could to make it work. the error I was receiving was

Database error

Message: SQLSTATE[08006] [7] could not connect to server: Permission denied Is the server running on host "myserver" and accepting TCP/IP connections on port 9999?

to make matters a bit more difficult for me I had PGPOOL-II infront of the my db and had to troubleshoot both pg_hba.conf files from master db to PG_POOL which in my case were two different servers. After hours of trying to reconfigure my pg_hba.conf file I googled and came across this  post which saved my life.


Solution:
         If you try to install lighttpd make sure you give permissions to the process setsebool -P httpd_can_network_connect on otherwise you will wont be able to connect to postgres from php.