Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Location
    Hong Kong
    Posts
    25

    Unanswered: Need to move my database to another drive

    My database on my Redhat linux server is growing too large that my current drive can no longer support its size. I would like to move the whole PostgresSQL to another drive. Of course the proper way to do this
    is to export all the tables, re-install the PostgreSQL, and then import back the data. But I am thinkg of a simpler way:

    1.Stop running the PostgreSQL
    2.Simply mv the whole pgsql folder to another drive
    3.Create a soft link in the original drive
    4.Restart running the PostgreSQL

    I wonder if this can work??? If not, any other suggestion which requires
    least effort?

  2. #2
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49
    I'm fairly sure it will work.

    But remember also that you don't even need to use the symbolic link. The PostgreSQL daemon allows you to specify data directory and logging directory upon startup.

    For example, on my workstation, my startup script for PostgreSQL is:
    Code:
    su -l postgres -c "/usr/local/pgsql/bin/pg_ctl start -i -D /usr/local/pgsql/data -s -l /usr/local/pgsql/data/serverlog"
    Explanation:

    1. su to user postgres, which is the main owner of the PostgreSQL process.
    2. Execute the pg_ctl startup script
    3. Use the -i switch to allow remote network connections
    4. Use the -D switch to indicate data directory /usr/local/pgsql/data
    5. Use the -l switch to indicate the logging directory /usr/local/pgsql/data/serverlog

    This means you can easily move your data and logging directories anywhere you want, as long as you give them the right user and permissions. One good thing to do is split your logging onto a completely different physical disk from your data directory. This reduces throughput on one disk, and provides a certain amount of redundancy. This will be especially useful in PostgreSQL 7.4 because you can log to a different disk and use Point-in-Time-Recovery to rebuild your data from the log.

    Check out the pg_ctl manpages for more detail.

  3. #3
    Join Date
    May 2003
    Location
    Hong Kong
    Posts
    25
    oh, you're right, I will try to use your method~~ thx!

  4. #4
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    Post PGDATA

    you can also change the variable $PGDATA in your startup script.
    im my case, in the file /etc/rc.d/init.d/postgresql: i just copy my data folder to a certain location and edit $PGDATA=/some/location then start your postgresql.

    HTH
    --- Hey! its me!!! the MaleMan ---

  5. #5
    Join Date
    May 2002
    Location
    Florida, USA
    Posts
    49

    Thumbs up

    Kinda gives you the feeling that PostgreSQL might just be flexible, doesn't it ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •