Moving Postgresql Data Folder

September 10, 2008

We had a old server which ran out space for partition where postgres was running… we had to move the data folder to a bigger partition… and here is how I did it…

Step 0:  Stop the Postgres if running

$service postgresql stop

Step 1 :  Copy  the whole  data/  folder  to  new parition

say  we have  /usr/local/pgsql/data   and wants to move to /home2/ partition

$cp -R /usr/local/pgsql/data  /home2/postgres/data

Step 2 :  Modify the Startup script to point to correct data directory

In  /etc/init.d/postgresql  file,  change the value of  PGDATA  variable to new location  which is /home2/postgres/data

Step 3 : Start the db server

$ service postgresql start

Advertisements

6 Responses to “Moving Postgresql Data Folder”

  1. sean Says:

    this doesn’t work for postgres 8.3 on ubuntu. I don’t know the answer yet, but this comment in that init.d file verifies this:

    # Setting environment variables for the postmaster here does not work; please set them in /etc/postgresql/8.3//environment instead.

    • Mick T. Says:

      Sean, make a backup copy of postgresql.conf, and then edit the data_directory setting to point to the new data directory.

      You can find postgresql.conf under /etc/postgresql .

      Editing the startup script in /etc/init.d/ is generally not a good idea.


  2. A far better solution is to heed how the init script is designed allowing placing overriding config in /etc/sysconfig/pgsql/postgresql

    The directory should have been created by default but the file is not, but it’s just to create it and enter the modified config desired such as:

    PGDATA=/your/desired/path

    Finally, restart PostgreSQL. It worked like a charm for me.

    • Justin Says:

      Agreed.

      However, where that “overriding config” lives depends on the dist you’re using. I see you use Redhat, or some derivative.

      Gentoo’s way cooler :P and the config file lives here: /etc/conf.d/postgresql (same idea – it sets env var PGDATA).

      Otherwise, thanks for this post, I just had to put the db on another partition, myself, and this is what I found with a quick Google.

  3. Ur Says:

    since version 8.4 use TABLESPACE instead
    First mkdir for postgres-Account on the bigger partition, e.g.:
    /mnt/db_data

    CREATE TABLESPACE ts_data
    OWNER db_user
    LOCATION ‘/mnt/db_data’
    ;
    ALTER DATABASE mydb
    SET TABLESPACE ts_data
    ;

    also set temp and default tablespaces in postgresql.conf for new DB-Objects

  4. Jitendra Tiwari Says:

    It’s working for me……thanks


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: