LinuxQuestions.org - Change Postgresql data directory

Quote:

Originally Posted by houdelou (Post 3187503)

You have to create another database cluster. To do this :

>mkdir your_new_directory
>chown postgres_user your_new_directory

login as postgres user

>initdb -D your_new_directory

You have to specify this new cluster when you start postmaster with the -D option too or you can change it directly in the startup script like /etc/rc.d/rc.pgsql if you have one.

Thanks houdelou :D

This is what I did to get it to work (This was on CentOS 5.0)...

Shutdown postgres (if it's running)

Code:

root@host# service postgresql stop

Edit the start-up script

Code:

root@host# vi /etc/rc.d/init.d/postgresql

Change the PGDATA and PGLOG variable to wherever you want (changes in red)...

Code:

# Set defaults for configuration variables

PGENGINE=/usr/bin

PGPORT=5432

PGDATA=/path/to/pgdata

if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ]

then

        echo "Using old-style directory structure"

else

        PGDATA=/d2/pgdata

fi

PGLOG=/path/to/pgdata/pgstartup.log



# Override defaults from /etc/sysconfig/pgsql if file is present 

[ -f /etc/sysconfig/pgsql/${NAME} ] && . /etc/sysconfig/pgsql/${NAME}



export PGDATA

export PGPORT

Create the directory...and set the permissions

Code:

root@host# mkdir -p /path/to/pgdata

root@host# chown postgres:postgres /path/to/pgdata

Initialize the database...

Code:

root@host# su - postgres -c "initdb -D /path/to/pgdata"

Now you can start postgres

Code:

root@host# service postgresql start

Now you can check this with the ps and you'll notice (in red) that the pgdata dir is where the database is

Code:

root@host# ps aux | grep post

postgres 11140  0.0  0.1 122556  3372 ?        S    10:41  0:00 /usr/bin/postmaster -p 5432 -D /path/to/pgdata

postgres 11142  0.0  0.0 111736  672 ?        S    10:41  0:00 postgres: logger process                

postgres 11144  0.0  0.0 122692  1168 ?        S    10:41  0:00 postgres: writer process                

postgres 11145  0.0  0.0 112736  672 ?        S    10:41  0:00 postgres: stats buffer process          

postgres 11146  0.0  0.0 111864  848 ?        S    10:41  0:00 postgres: stats collector process        

root    11186  0.0  0.0  61120  724 pts/1    S+  10:58  0:00 grep post