change postgresql's data directory

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

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

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!

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

Changed ownership and it works :)

Fri, 04/03/2009 - 10:02 (Reply to #3)
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)

How can I do?
It seems good :)

Thank ronald

Fri, 04/03/2009 - 13:53 (Reply to #5)
ronald's picture
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'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

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)

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

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 :)


Topic locked