Create new db in a different drive

[omaha]

WARNING ALL

IF YOU ARE HAVING PROBLEMS WITH SPADEYE HANGING AND CRASHING, MAKE SURE YOU PUT SPADEYE AND PT AND THE DATABASE ON THE SAME DRIVE.

I DIDNT, AND SPADEYE JUST KEPT STUFFING UP AND CRASHING AFTER I HAD A FEW MILLION HANDS

i NEVER SHOUT IN CHAT, BUT THIS IS REALLY IMPORTANT

DAVE, COULD YOU PLEASE EDIT YOUR PREVIOUS POST SO THAT OTHERS WILL NOT HAVE THE SAME TROUBLE?

I then put spadeye on the same drive, and it is unreal!

Thanks esp for dave for showing us how to use our spare drives

[_dave_]

I can't edit old posts, only for 30 mins after posting or something.

This is REALLY strange - there is no reason whatsoever spadeeye should have to be on the same drive as a postgres database. Tablespace is invisible to other applications.

I suspect whatever problems you were having with spadeeye were fixed by doing something else (like reinstalling it) that coincided with moving to the same drive.

If Mr_Snood says something about this, I'd like to see why spadeeye would have issues - ask him to post here (or link pls)

[Timid Flower]

I am having the same problem someone was having earlier; when I attempt to create a new tablespace I get the message:

An error has occured: ERROR: could not set permissions on directory "D:\pg_data": Permission denied

How do I give it permission? I am running Windows XP Pro SP2. When I R. click and go to Properties the only tabs I have available are General, Sharing, and Properties.

[Timid Flower]

Alright I was able to do it and get my DB on other disk.
http://www.cactushop.com/support/No-sec ... _591__.htm

[omaha]

I can't edit old posts, only for 30 mins after posting or something.

This is REALLY strange - there is no reason whatsoever spadeeye should have to be on the same drive as a postgres database. Tablespace is invisible to other applications.

I suspect whatever problems you were having with spadeeye were fixed by doing something else (like reinstalling it) that coincided with moving to the same drive.

If Mr_Snood says something about this, I'd like to see why spadeeye would have issues - ask him to post here (or link pls)

Yep, i thought it was weird too.

Basically, i started using HEM(checked out the competition, as i was sick of reimporting all my datamining when a new db had to be done), got a huge db, and started having problems. THought it might have been HEM , so went back to pt3 (where i had no problems at all with the smaller db).

Things were really, really, really bad. Scanning the empty tables (ie four or five tables) worked, but SE just couldnt get through a scan of the full ones (where there would be 20 or 40 tables to scan) nine out of ten times. Sometimes it would if you clicked on a table, but it was just soooooo painfully bad. Tried reinstalling SE (on C drive), HEM (E drive), and even PT3 (on the E drive) with the updates. Nothing worked.

Then, i just installed SE to the E drive (where my db is). First go, SE and PT3 worked SEAMLESSLY and flawlessly.

WEird, but thought I would post it here, and also i have posted in the SE post in the software forum on 2+2

[zedjr]

am asking dave do u know how much performance gain i would get if i separated the postgresql installation files and the actual db. RIght now i have them both installed on a separate drive other than c. have a scsi drive that is just dedicated to the postgresql 8.3 installation. I dont want to go thru all the trouble of reinstalling and reimporting hands back into pt3 if the gain is very minimal. ALso my restore process for 8.3 db takes forever for some reason so am trying to avoid doing this again. again dont misunderstand and i think i have installation and data on c drive. HAve both on separate drive and wasnt even aware i could install postgresql on c drive and keep data on separate drive or i would of done so. also is there a way to do this thru the installation of postgresql as i would jsut prefer if it is worth it to just do a clean reinstall and reimport. thanks for any info that might help. btw u all rock. u are keeping up with pats customer service in pt2. am surprised and dont know how yall do it.

[_dave_]

The potential gain depends a lot on your configuration, in this case. It will unlikely be very large, unless you have posgres "Log All" enabled. ~If you do - and don't want to turn it off for some reason - there are easier ways to make it log to a drive other than the SCSI drive you have postgresql installed on, just edit the settings for log_dir in postgresql.conf.

Otherwise, the only benefit you are going to get is probably a small speed increase on bulk imports, as the trandsaction log will be going to a different drive - but this could actually make things slower, if the SCSI drive is significantly faster than the others. IDK, you'd have to run tests to be sure - but the difference isn't likely to be huge... I'd just leave it as you are (other than moving logs if they are enabled), a seperate drive for the whole lot is not much different from the tablespace method here.

[Perrygarl]

I have started the "pqsl" to, to open up the command prompt:, but as I place"

CREATE TABLESPACE spare_disk LOCATION 'D:\\pg_data';

into the command prompt, I get this message

ERROR: Could not set permissions on directory "F:/pg_data": Permission denied postgres=#

I am using Windows XP Home,

Has anyone had this problem before, and does anybody know how to solve this problem, as I really would like to place the databases onto my external hard drive, as soon as possible.

[vbpro7]

I have recently changed my DB to a different 2nd HD using the guidelines here, same names and everything just on drive E:\. I went to make another DB(08DEC) and it made it on my C:\ drive. How do i add another DB to my spare drive E:\? I tried retyping everything just with a new DB name and got an error that said Tablespace spare_disk already in use or something along the lines of that. Do i need to make a different folder other than pg_data in root E:\?

[kraada]

Instead of creating the tablespace again, you'll want to create your table using the tablespace:

CREATE TABLE NewDB USING TABLESPACE WhateverYouCalledItEarlier;

Then run the schema the same way as previously described.