Two Databases - Each in a different drive

[leviathan74]

I have two databases, each around 60GB. However, I am running out of space on drive C. Since I ve got 114gb in Drive D, I am thinking of moving there.

Can I do it?

Thanks

[Flag_Hippo]

I will make sure that the appropriate staff who can answer your query will reply on Monday.

[WhiteRider]

The simplest way is to Backup your databases, uninstall PostgreSQL and reinstall it on your other drive, then restore your backups. See this guide for uninstall/reinstall directions.
If you install the same major version of PostgreSQL again then you could just move the data folder across to your new drive once you've reinstalled as the data folders are not deleted by the uninstaller and if it's the same version of PostgreSQL it will be able to read them.

[leviathan74]

I am sorry, I wasn't clear. I want one database on C drive, the other on D drive.

[kraada]

You have two options:
(1) Install PostgreSQL twice, on different ports. Put one install in each location, set one to port 5432 (default) the other 5433 (a common secondary port) and just instruct PokerTracker to create the databases you want in the proper locations by setting the port at creation time. This is probably easiest and the closest to foolproof method.

(2) Install a TableSpace - this tells PostgreSQL to put the data table for one particular database in a different physical location on disk. You can do this and set it up to go to a secondary hard drive - see this post for the details.

[leviathan74]

You have two options:
(1) Install PostgreSQL twice, on different ports. Put one install in each location, set one to port 5432 (default) the other 5433 (a common secondary port) and just instruct PokerTracker to create the databases you want in the proper locations by setting the port at creation time. This is probably easiest and the closest to foolproof method.

(2) Install a TableSpace - this tells PostgreSQL to put the data table for one particular database in a different physical location on disk. You can do this and set it up to go to a secondary hard drive - see this post for the details.

Sorry for the late reply, I was just swamped. I just tried going with the second option, but I am stuck in this step:

Click Start --> Programs --> PostgreSQL --> psql to template1

I am on windows 7 and I can't perform this step. Is there a way perhaps of doing this through the pgadmin?

As for the first method, that would be easiest, but I already have two 60gb databases to begin with, is there a way to just install a second postgres version and move one of the two databases to the second installation?

[WhiteRider]

Kraada is on vacation this week and I'm not personally familiar with the method described so I'm not sure what you need to do at that step.
However:

As for the first method, that would be easiest, but I already have two 60gb databases to begin with, is there a way to just install a second postgres version and move one of the two databases to the second installation?

You can install a second version of PostgreSQL to a new location and specify a different port number, then you can run both services at once and choose which to connect to by specifying the appropriate port number. Now you can make a Backup of a database from the old service and restore it to the new service (and once you're happy it's working you can delete it from the old one).

[leviathan74]

Kraada is on vacation this week and I'm not personally familiar with the method described so I'm not sure what you need to do at that step.
However:

As for the first method, that would be easiest, but I already have two 60gb databases to begin with, is there a way to just install a second postgres version and move one of the two databases to the second installation?

You can install a second version of PostgreSQL to a new location and specify a different port number, then you can run both services at once and choose which to connect to by specifying the appropriate port number. Now you can make a Backup of a database from the old service and restore it to the new service (and once you're happy it's working you can delete it from the old one).

I suspect that when he wrote that post, there wasn't a pgadmin app. By using the query tool, I was able to add a tablespace, but I really don't feel comfortable with postgresql to move to the next step. I really need an idiotproof step by step guide for that.

As far as installing a second postgresql version, I wonder if that's going to have any effect on speed. As things stand, I cannot backup anything, because I don't have the free space in my C drive to do so.

[kraada]

You can run the query in question through PGAdmin's query tool once you're connected to either the template1 database or the postgres system database (they changed the name at one point, so which you see will depend on the version you have installed).

Having a second PostgreSQL installation shouldn't affect performance considerably as far as PT4 is concerned.

[leviathan74]

Ok, this is how my pgadmin page looks. There is a postgres database, but I don't have to connect to it per se, I just connected to the server. I am not sure whether it makes a difference to which database I am connected when I use the query tool.



Now, I see the commands you say I need to perform:

Code: Select all
CREATE TABLESPACE spare_disk LOCATION 'D:\\pg_data';



and hit enter. Please note there are two backslashes. Then type:

Code: Select all
CREATE DATABASE pt3_newdb TABLESPACE spare_disk;



and hit enter. Then type:

Code: Select all
\connect pt3_newdb;



hit enter and the console will change to show that we are now connected to pt3_newdb. Once the console changes, type:

Code: Select all
\cd D:


hit enter and then type:

Code: Select all
\i schema.postgres.sql

I am guessing I will type those commands on the query tool and I will select execute. Question. Do I need to type select all before each command, or just type the command bellow it? Also, do I need to type the semicolon after those commands?

Thanks