Moving postgresql to another drive
Results 1 to 2 of 2
  1. #1
    Junior Member
    Join Date
    Feb 2010
    Posts
    2

    Default Moving postgresql to another drive

    Hi,

    I searched the FAQ and have found "reinstall Postgresql preserving old database", but I am unsure if it is even necessary for me to reinstall since I just want to move it to another drive and free up some space on my C drive (it's taking up 8gigs atm!). Can someone please tell me how to move postgresql from C drive to D drive without causing any problems with HEM and my current databases?

    Thanks in advance,

    Kyle

  2. #2
    *** HM3! *** fozzy71's Avatar
    Join Date
    Jun 2005
    Location
    HM Support
    Posts
    32,803

    Default

    Logging:

    I would suggest you turn off logging on your new SQL installation as well as these other performance improvements in my signature - Top 10: HEM performance increase tips


    To turn off logging, follow these instructions. This is the first thing I do anytime I install SQL for someone.

    - Start > My Computer > Tools > Folder Options > View > UnCheck 'Hide Extensions for known file types' > OK

    - C:\Program Files\PostgreSQL\data\postgresql.conf > Right-Click > Open With.. > NotePad > Check 'Always use this program'

    - Scroll about half way down to ~Lines 245 - 260:

    - The last line of this block of code, for the 'logging_collector', must be changed from On to Off.


    HTML Code:
    ------------------------------------------------------------------------------
    # ERROR REPORTING AND LOGGING
    #------------------------------------------------------------------------------
    
    # - Where to Log -
    
    log_destination = 'stderr'        # Valid values are combinations of
                        # stderr, csvlog, syslog and eventlog,
                        # depending on platform.  csvlog
                        # requires logging_collector to be on.
    
    # This is used when logging to stderr:
    logging_collector = off        # Enable capturing of stderr and csvlog

    File > Save; File > Close

    Start > Programs > PostgreSQL 8.3 > Reload Configuration.


    - Start > My Computer > C:\Program Files\PostgreSQL\data\pg_log, or C:\Program Files (x86)\PostgreSQL\data\pg_log, if you have 64 bit Vista. Delete all files in that folder. And it might be a good idea to empty your recycle bin at that point, or use a 3rd party utility like CCleaner to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.



    Regarding database maintenance:

    You should regularly purge mined hands in your database, after purge vacuum / full analyze the database via pgadmin.
    FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands from Database
    FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands

    Then you can vacuum/analyze - FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database

    Before you defragment you should stop the PostgreSQL service:

    Start > Programs > PostgreSQL 8.x > Stop Server

    Use your favorite defrag tool. I recommend Smart Defrag.

    After defrag I recommend you reboot your PC. If you need to access the DB before rebooting you can start the PostgreSQL service.

    Start > Programs > PostgreSQL 8.x > Start Server



    To move the DB to another drive w/o reinstalling:


    If you have PostgreSQL installed on the C drive where Windows is installed, but you want to move the Data folder i.e Database to a different partition or external hard drive to reclaim the space on your C drive, it can be done without having to reinstall.

    We need to close down the PostgreSQL service while doing this.

    Windows XP
    Start > Control Panel > Administrative Tools (Use classic View) > Services

    Windows XP
    Start > Search > Services

    This will bring up the following window:



    Right click this and choose Stop to stop the service.

    Then create the following folders in the external hard drive. Program Files and inside that folder create a PostgreSQL folder and inside that create a data folder. So if your external hard drive is on the I drive you would have created a path I:\Program Files\PostgreSQL\data

    Then go to where you have the data folder which will be c:\program files\PostgreSQL\8.3\data by default unless you changed it. Copy (or Cut) all the files in here to the new data folder we just created above on the external hard drive. If this is a big database this may take some time.

    When you've done that we need to tell PostgreSQL where the new data folder is located and to this we need to edit the registry.

    Windows XP
    Go to Start > Run and type the following in the textbox: regedit and then press enter and a new window should appear like in the screenshot below:

    Windows Vista/W7
    Go to Start > All Programs > Accessories > Run then type the following in the textbox: regedit and then press enter and a new window should appear like in the screenshot below:



    Navigate the folders to this path: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.3 and you should then see the following screen:



    In the image above ive highlighted the key we need to change. Go to image path and right click and choose modify and you should see the following:

    "C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\"

    Now we need to only change the bit thats in bold. This is the location that PostgreSQL looks for the database files but weve moved them to the external hard drive so we must tell it the location. Make sure you get the path exactly correct. The easiest way is to navigate to the actual Data folder on the external hard drive and right click any file inside that folder and choose properties and youll see this:



    As you can see for location it shows the actual path, in my case its the I drive but yours maybe different so just copy and paste it. So if i was creating the key based on that screenshot my old key would be:

    "C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "C:\Program Files\PostgreSQL\8.3\data\"

    And i should change it to this:
    "C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "I:\Program Files\PostgreSQL\8.3\data\"

    That now has everything in place so we should close everything down and make sure to reboot the PC.


    If the moved DB is working fine you can delete the original \data folder on the C drive, if you didn't use the Cut option earlier, to free up the space on your C drive.

Similar Threads

  1. Replies: 15
    Last Post: 06-09-2010, 06:08 AM
  2. Moving a postgresql database to another drive?
    By remiremi in forum Manager General
    Replies: 1
    Last Post: 12-17-2009, 03:02 PM
  3. Moving data base to an external hard drive
    By gring000h in forum Manager General
    Replies: 11
    Last Post: 11-27-2009, 12:32 PM
  4. Moving DB from C Drive
    By szabby in forum Manager General
    Replies: 1
    Last Post: 08-06-2009, 06:06 AM

Posting Permissions

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