Create new db in a different drive

[ozora tsubasa]

I have a few db's on my C drive. Since they are starting to occupy too much space, I'd like to create my new db's in another drive. How can I do this? Can I leave the exisitng db's where they are and just create the new ones in a different drive? or do they have to be at the same drive. If so, how do I move the existing db's?

[APerfect10]

You can but you will need to modify the table space. We plan on adding some features to make this a little easier. In the mean time, I suggest sending _dave_ a PM. He might be able to assist you better than I can.

Best regards,

Derek

[_dave_]

BE VERY CAREFUL - FOLLOW THESE INSTRUCTIONS TO THE LETTER - BACKUP ANYTHING YOU VALUE!!!

This is done using the PostgreSQL command TABLESPACE - documentation here: http://www.postgresql.org/docs/8.2/stat ... paces.html

Let us go with the original request, and presume we already have postgres up and running perfectly, installed to our C:\ drive. OP does not specify, so I'm just going to assume he has a brand new empty partition, called D:\. Here is where he wishes to create new databases.

These instructions apply to Windows XP PRO - it may vary for XP Home, I have no idea. At a guess I'd assume the File system permissions bit isn't needed / doesn't exist - someone please inform us if so. Same goes for Vista, I have no idea how ACL works on those.

You will need "Simple File Sharing" disabled, so you can set folder-level ACL settings.

You will need to be logged in as Administrator.


Preparation:

1. Using Windows Explorer, create a "New Folder" in the root of D:\ - let us call it "pg_data". You can choose what you want, remember it.

2. Right-click on this folder, choose Properties. Select the "Security" tab. Here we see a list of users allowed to access our new folder. We must add the database superuser "postgres" to this list. So click "Add", type in the little box "postgres" (without quotes), then hit the "Chack Names" button. It should fix the name to correct format (for me, it became A64\postgres). If it does similar, good. Hit OK. User "postgres" should now appear in the "groups or user names" list on the "Security" tab. Select it, then tick the box in the panel below to "Allow/Full Control" All the boxes should be ticked. If so, good, Apply/OK, and we are done with this part.

3: For conveniences sake, we should copy the database schema to save typing later. This is optional, but do it anyway. Using Windows Explorer, navigate to "C:\Program Files\PokerTracker3\Data\Schemas". There should be a single file in here, "schema.postgres.sql" - copy it, and paste in the root of D:\.

Now you should have two things in D:\ - a folder (with postgres user having full access control) - and a copy of the PT3 schema. This is the end of preparation, let us now get it done!


Execution:

4. Open a command console to the database. You can probably do this in pgAdmin, but I didn't. Do "Start -> Programs -> PostgreSQL 8.2 -> psql to template1" you should get a console.

BE VERY CAREFUL TO TYPE EVERYTHING EXACTLY AS WRITTEN!
note there are TWO backslashes in the 'D:\\pg_data' location command.

5: type the following commands - [Enter] after each line, any error messages, stop!

Code: Select all

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

Code: Select all

CREATE DATABASE pt3_beta3_newdb TABLESPACE spare_disk;

Code: Select all

\connect pt3_beta3_newdb

The prompt should change to reflect the new database we have connected the console to - if it doesn't, stop!

Code: Select all

\cd D:

Code: Select all

\i schema.postgres.sql

It should run through a load of commands to build the pt3 database structure now. we are done with the console.

Code: Select all

\q

and it should go away.

Now in to PokerTracker 3! We need to add our newly created database to it's configuration.

6. Select "Database -> Database Management". Hit the "New" button. Check the host settings are correct, and hit the "Browse Databases" link. You should see in the list, the newly created "pt3_beta3_newdb" - select it, then OK. It should have auto-ticked "this database already exists", but if it didn't - tick it. Change the name at the top to something friendlier if you wish. Hit Connect, it should succeed. We are done - Set it as the Default if you wish, close database management, and switch active databases to the new one - and import some hands, make sure all is OK!

FINISH

Hopefully this is useful to some, there is more fun to be had with tablespaces, but this should do us until something is built in to PT3's database management utilities

dave.

[_dave_]

Oh, I'm out of edit time

This is advanced and potentially dangerous - so make sure you are happy working won a console etc. if you are gonna attempt it.

As an advertisement for TABLESPACE tho, my import speed doubled and retrieve speed is much improved once I did this and moved my DB off the C: drive and on to a hard disk all of it's own Mainly to do with the log and xlog being now on a separate disk to the data itself imo.

[APerfect10]

Looks very good dave. Thank you very much!

Best regards,

Derek

PS. How much improvement have you squeezed out moving the data to a separate disk from pg_log & pg_xlog?

[_dave_]

no scientific results as yet. Need to test things on the "real" database server yet

Import speed increased hugely. on a Win32 postgres 8.0 server I went from ~150h/s to >350h/s, and while there are no easy ways to tell, queries *seem* faster when changing between tabs etc. - but this may just be due to using a faster / non-system disk drive.

[WhiteRider]

Hmm, hopefully it will be possible to build all this into PT3?
I normally (ie on PT2) have PT installed on c: and my DBs on another disk. Will this be possible from within PT3, or should I just install it on my other drive? So far I've just taken the default install on c: for simplicitiy of testing.

[ozora tsubasa]

Awesome guide dave! I followed it all the way and everything seems to be working as far as I can see. PT3 is happily auto-importing hands, and I can view stats of players etc.

I noticed some errors while executing the commands from postgres.sql though:

[quote="_dave_"2dq]
[code2dq]\i schema.postgres.sql[/code2dq]
[/quote2dq]

Few are shown here, and there were couple more of the same/similar before these:
[url2dq]http://www.karidegiliz.biz/publicpoker/2p2feb1.jpg[/url2dq]

Should I be worried about these?

[Josh]

Yes, we will be adding tablespace support into PT3, so this will be easily done in the future.

[hotwing]

hi guys, I need some clarifications please.

I have completed the software install with SQL included. Everything is ready to go, but I have not gone any further because I want to set up a database on an external drive first.

I am running XP home.

I have created a new file on the external drive, opened the properties and since the is no security tab I selected the "web sharing" tab.

clicked- share this folder
entered- under alias "postgres"
clicked- all boxes under "Access Permission"
clicked- Execute, under "Application Permissions"
clicked- OK
clicked- OK, again

copied and pasted "schema.postgres.sql", to said drive, but not in this new folder.

Now when I try to find by your directions the file "psql to template1"
What I do see when I follow the path that you suggested is something that is called "psql to 'postgres' " is this the same or where I should go.

Also, when I select- psql to 'postgres' a Command prompt does indeed show, and askes for a user postgres password. I have the password, but is this the direction I should be headed?



thanks