change postgresql's data directory

General Discussion
1

Hi all,

How can I change postgresql’s data directory?
I’ve just change mysql’s data directory easily but I don’t know how for postgreSQL.
I am using Virtualmin PRO on Debian 5.0.

Thank you very much

2

I find something here /etc/postgresql/8.3/main/postgresql.conf

data_directory = '/var/lib/postgresql/8.3/main' # use data in another directory

I change it to /home/postgresql;
I move files from /var/liv/postgresql/8.3/main
I chmod 755 and 777 after I see it doens’t start.

I get this error when I restart:

Failed to start database server :

Starting PostgreSQL 8.3 database server: mainError: could not exec /usr/lib/postgresql/8.3/bin/pg_ctl /usr/lib/postgresql/8.3/bin/pg_ctl start -D /home/postgresql -l /var/log/postgresql/postgresql-8.3-main.log -s -o -c config_file="/etc/postgresql/8.3/main/postgresql.conf" : failed! failed!
3

Changed ownership and it works :slight_smile:

4

out of curiosity…
would it not be better to create symlinks instead of moving files and risk breaking stuff?

5

How can I do?
It seems good :slight_smile:

Thank ronald

6

http://en.wikipedia.org/wiki/Symbolic_link
so this may work or else try full path
ln -s /var/lib/postgresql /home/postgresql

7

It created a link in /home/postgresql called main?
Is it right?
All files in /var/lib/postgresql/8.3/main will be moved to /home/postgresql, won’t it?

Thank you very much

8

you’re not actually moving the files, but you can reach and work with the files through the symlinks, so the original files are kept in place in case there are updates and such.

you can symlink complete directories, which may be the easiest way.

9

i think the best approach is the one Maurizio1230 mentioned but for the sake of safety and cleanliness, I would take the following approach:

  1. use initdb command to create a database cluster in the place of your choice. Refer to http://www.postgresql.org/docs/8.2/interactive/creating-cluster.html

  2. dump existing databases (create .sql files) before dropping them.

  3. make changes to postgresql.conf as mentioned above. you may have to create the following symbolic links in your chosen data directory (inside the …/pgsql/data folder) for server certificates.

ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
ln -s /etc/postgresql-common/root.crt root.crt
ln -s /etc/ssl/private/ssl-cert-snakeoil.key server.key

  1. restart postgres

  2. re-create databases using the sql files you created earlier.

It’s a length process but error-free :slight_smile:

Cheers