change postgresql's data directory

9 posts / 0 new
Last post
#1 Fri, 04/03/2009 - 05:41
Maurizio1230

change postgresql's data directory

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

Fri, 04/03/2009 - 09:01
Maurizio1230

I find something here /etc/postgresql/8.3/main/postgresql.conf
<div class='quote'>
data_directory = '/var/lib/postgresql/8.3/main' # use data in another directory</div>

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 :
<div class='quote'>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=&quot;/etc/postgresql/8.3/main/postgresql.conf&quot; : failed!
failed!</div>

Fri, 04/03/2009 - 09:09 (Reply to #2)
Maurizio1230

Changed ownership and it works :)

Fri, 04/03/2009 - 10:02 (Reply to #3)
ronald
ronald's picture

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

Fri, 04/03/2009 - 13:23 (Reply to #4)
Maurizio1230

How can I do?
It seems good :)

Thank ronald

Fri, 04/03/2009 - 13:53 (Reply to #5)
ronald
ronald's picture

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

Sat, 04/04/2009 - 23:27 (Reply to #6)
ronald
ronald's picture

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.

Sat, 04/04/2009 - 07:10
Maurizio1230

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

Thu, 05/06/2010 - 08:24 (Reply to #8)
umairdojki

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

4) restart postgres

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

It's a length process but error-free :)

Cheers

Topic locked