PDA

View Full Version : Moving postgresql to another drive



Mossy
03-05-2010, 12:57 AM
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

fozzy71
03-05-2010, 11:53 AM
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.



------------------------------------------------------------------------------
# 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 (http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database)
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands (http://faq.holdemmanager.com/questions/197/Purge+Hands)

Then you can vacuum/analyze - FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual)

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:

http://208.109.95.123/faq/files/Image/postgreServices.jpg

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:

http://208.109.95.123/faq/files/Image/2PCpostgresRegedit.jpg

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

http://208.109.95.123/faq/files/Image/2PCpostgresRegedit2.jpg

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:

http://208.109.95.123/faq/files/Image/2PCpostgres6.jpg

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.