Change the default PostgreSQL data directory on Windows
Our database which is storing every minute telemetry data is now getting bigger and need to be relocated to another newly mounted drive. So, I need to relocate the old data to a new disk. Actually, I don’t need to do this if my drive is a dynamic drive.
As we already know, PostgresSQL for Windows installs the PGDATA directory by default into “C:\Program Files\PostgreSQL\8.3\data”. This mini-HOWTO explains how to change the default PGDATA directory to another location. Note that 8.3 is the version number of my current PostgreSQL installation. It could be varied based on your installed version.
Step 1: Stop The PostgreSQL Service
Close all applications that are currently connected to your database, then go to Windows Services Management and stop the PostgreSQL service:
Start->Settings->Control Panel->Administrative Tools->Services
You should check the task manager to see if any postgresql.exe instances are still running. If so, DO NOT TERMINATE them, instead close all applications that are still connected to the database. Sometimes services like webservers keep persistent connections. In this case you also should stop these services.
Step 2: Change Registry Values
Start the Windows Registry Editor (regedit.exe) and navigate to: “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.3″
. Double click on “ImagePath” and change the diectory after the “-D” option to your new location. If the path to your new location contains spaces, you should then enclose it with quotes.
Registry Editor
Step 3: Move the data folder to the new location
Move your current PGDATA directory to the directory you just specified in Step 2.
Please note that you should do this as Administrator on server machines.
Step 4: Restart The PostgreSQL Service
Before you can start your database you must close and re-open the Windows
Services Management window. This refreshes the path you just changed in the registry without the need to restart your system.
After closing and re-opening the Services Management window, you should check the “path to executable” information of the PostgreSQL Database service. The path after “-D” option should be pointing to your new directory now.
Service properties
Now that the PGDATA directory has been re-located and the service startup parameter in the registry has been modified. The new directory should be granted permissions to PostgreSQL service to access it.
Directory Properties Dialog
Add username which PostgreSQL service use.
And then, set some required permissions PostgreSQL operations.
Now, you can go ahead and start the service.
Tags: database, pgsql.exe, postgreSQL, relocate database directory, relocate directory, Telemetry, windows
February 22nd, 2010 at 7:10 am
great post,however i have some difficulty in understanding the final paragraph, can you please describe a bit more?
March 10th, 2010 at 6:08 pm
gzuGgp Excellent article, I will take note. Many thanks for the story!
March 28th, 2010 at 3:53 am
Very super information.
April 12th, 2010 at 2:33 am
Awesome Post. I add this Blog to my bookmarks.
April 12th, 2010 at 3:19 am
Hi There! I ran into your site absolutely by mistake, and it turned out to being a blessing. You bring a lot of interesting things to the table and I will be back for more
Thanks!
April 14th, 2010 at 2:03 pm
Thanks For This Blog, was added to my bookmarks.
April 17th, 2010 at 6:04 pm
Intimately, the post is actually the best topic on this related issue. I fit in with your conclusions and will eagerly look forward to your approaching updates. Just saying thanks will not just be sufficient, for the phenomenal clarity in your writing. I will immediately grab your rss feed to stay abreast of any updates.
May 7th, 2010 at 7:18 pm
Cool post.
May 13th, 2010 at 2:15 am
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material
May 18th, 2010 at 10:53 pm
Great info, thank you for making this available on your blog.
May 31st, 2010 at 1:03 am
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material
June 19th, 2010 at 11:12 am
So why you do this?
June 21st, 2010 at 5:06 am
For some reason only half of the submit is being displayed, is it my browser or the website?
June 28th, 2010 at 3:57 pm
I just visited your site through Facebook… would you be interested in getting weblog partners?
June 28th, 2010 at 8:25 pm
I just visited your website via Facebook… would you be interested in getting weblog partners?
July 9th, 2010 at 12:43 am
Hi Webmaster, If you’re interested in making money from your blog and driving thousands of free visitors to your website then i will show you a secret way to build a Blog Empire that earned over $1,107,693! Just check it out now: http://tinyurl.com/new-blogging-system
July 22nd, 2010 at 12:09 am
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
July 27th, 2010 at 6:50 am
I just sent this post to a bunch of my friends as I agree with most of what you’re saying here and the way you’ve presented it is awesome.
July 29th, 2010 at 12:40 am
I’ve been visiting your blog for a while now and I always find a gem in your new posts. Thanks for sharing.
July 30th, 2010 at 11:47 pm
You certainly have some agreeable opinions and views. Your blog provides a fresh look at the subject.
August 8th, 2010 at 7:58 pm
Awesome Post. I add this Post to my bookmarks.
August 17th, 2010 at 2:17 pm
Hi administrator I wallow in w/ ur article . may i copy this know-how for my academic check ? thanks
August 17th, 2010 at 6:08 pm
Hi administrator I rejoice in w/ ur content . can i copy this knowledge being my college test ? thanks
August 17th, 2010 at 8:43 pm
Hello admin , i want how to ask question about A New content. is this okay ?(1st)Whats the meaning for which content? but i enjoy using Your New url. (2nd) are you have facebook or twitter > end users may add A New facebook badge just so i can like Your web site. like my blog Thank end usersbest Regards admin as for (sorry,if my english is bad. iam an italian :p)
August 21st, 2010 at 9:51 am
Hello dude,i like Your site ideal much. attain u have on suggestion being my site? thanks being Your attention
August 21st, 2010 at 4:30 pm
Hello dude,i likes Your website very a lot. do u have on suggestion as my homepage? thanks as A New attention
August 23rd, 2010 at 2:22 am
Hello dude,i like this Your blog very a lot. achieve u have suggestion for my site? thanks for Your New attention
August 24th, 2010 at 7:52 pm
Awesome Blog. I add this Blog to my bookmarks.
August 25th, 2010 at 8:04 am
You certainly have some agreeable opinions and views. Your blog provides a fresh look at the subject.
August 25th, 2010 at 1:56 pm
You certainly have some agreeable opinions and views. Your blog provides a fresh look at the subject.
August 27th, 2010 at 8:47 pm
Very informative post. Thanks for taking the time to share your view with us.
August 28th, 2010 at 5:43 am
You certainly deserve a round of applause for your post and more specifically, your blog in general. Very high quality material
August 29th, 2010 at 7:02 pm
You certainly have some agreeable opinions and views. Your blog provides a fresh look at the subject.
August 30th, 2010 at 12:51 am
Thanks For This Post, was added to my bookmarks.
August 30th, 2010 at 3:37 am
I find myself coming to your blog more and more often to the point where my visits are almost daily now!
August 30th, 2010 at 3:05 pm
Hello dude,i like Your blog ideal much. achieve u have on suggestion for my website? thanks for Ones New attention
August 31st, 2010 at 12:00 am
Hello dude,i like Your site ideal a lot. accomplish u have suggestion for my homepage? thanks being A New attention
August 31st, 2010 at 9:57 am
Hello dude,i liking Ones homepage in truth a lot. do u have suggestion as my site? thanks being A New attention