Moving database to another drive (Split from Low Diskspace thread)

[TexDanny]

Hi,

I'm sorry to come to this thread but I have a very similar question so I might as well just post it here.

I've recently changed my ( C: )OS to an 150gb SSD because I thought that it would be enough for my db. However, my database is almost that size so I created a new database and have been using it ever since.

I want to have all my hands in one database so I think that the best way to do it is to 1. Back up my current DB 2. Move postgresql to my storage hdd (D: 1TB) 3. Restore my old database and import new db's hands. (Did you finish this faq? :P)

I guess I lose the SSD performance but I really dont want to purge hands because I'd like to be able to replay the old hands.

Also, I dislike the idea of having my big db sitting in a unrealiable storage drive and I know that you use a very nit backup system, can you tell me what piece of software do you use?

[fozzy71]

Your method for moving it will work. There are a couple easier/faster methods that I would try first. I will post them in separate posts, after this reply. You should still make a .backup before trying the following steps, in case anything goes wrong.

I have 2 layers of backup. I use AllWay Sync to do daily sync backups to my external drive. This software lets you setup multiple sync jobs. You pick the 2 folders to sync (source and backup) and it will analyze then only copy/delete the new/changed files since the last backup. I backup my entire A drive (data partition with all my hand archives and my postgresql \data folder) as well as my poker programs on C drive and any other important files/folders, like your Desktop, etc. I backup my C:\Users directory (to backup the User Desktops) and my AppData folder, which has my Pokerstars settings and Hand History folder.

I also use carbonite.com to backup all my important personal data and graphics files. I do NOT backup my PostgreSQL database or media files (music/video) because it is a lot of data and would take a long time to upload and update. This works 'on the fly' so I pretty much set it and forget it. The AllWay sync backups are scheduled for every 6 hours, but it still requires some attention from me to clear any warnings, etc.

[fozzy71]

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. Go to start > Control Panel > Administrative Tools (Use classic View) > Services. On Vista/W7 you can get there by going to 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.

[fozzy71]

For reinstalling SQL to the same DB, follow these steps:

1) Uninstall PostgreSQL from the Windows Control Panel.
2) If you are moving the DB to a different drive/partition, manually cut or copy the \Postgresql\8.x\data directory to it's new location.
3) run the command - start > programs > accessories > command prompt > right-click > run as administrator

net user postgres /delete
4) reboot.
5) Install the same version of PostgreSQL as your current DB using these instructions - FAQ - Hold'em Manager Poker Tracking Software :: Reinstall PostgreSQL Preserving Old Database (http://faq.holdemmanager.com/questions/71/Reinstall+PostgreSQL+Preserving+Old+Database)
6) If you have moved/installed your DB to a non-default location, please make sure to change the 'Data Directory' path during the installation.

[good2cu]

Fozzy,

I moved my database to a new harddrive as you outlined and did the regedit. When I open hold em manger I get the following error Hold em manger can not connect to 127.0.0.1. When I switch the reg files back to the old hard drive (I copied didn't cut), hold'em manger again works fine. I'm on windows XP. Can you help?

[morny]

Hi,

Please email me and we can do a teamviewer session, just download teamviewer from TeamViewer - Free Remote Access and Remote Desktop Sharing over the Internet (http://www.teamviewer.com) and let me know a suitable time GMT in advance. Teamviewer allows me to access your computer to fix the problem.

When we agree a suitable time you just need to send me the ID and password that Teamviewer creates to get access to your PC


support@holdemmanager.net

Thanks
Morny

[jenkulle]

Fozzy,

I moved my database to a new harddrive as you outlined and did the regedit. When I open hold em manger I get the following error Hold em manger can not connect to 127.0.0.1. When I switch the reg files back to the old hard drive (I copied didn't cut), hold'em manger again works fine. I'm on windows XP. Can you help?


you got to restart the service since you stop it


go in start menu -- all programs - postgre sql - start service

retry

[pampaseba]

Hello:
I cut and copy the files to new directory as shown in the example, and postgress cant restart.
"Failed to establish connection to 127.0.0.1

Please help me

[fozzy71]

Did you also make the registry changes? And then rebooted?

If you did all that and it still won't connect, I would suggest you reinstall the same version of postgresql and pick the new \data folder location during installation.

Please update to the latest beta - http://www.holdemmanager.com/downloads/HmBetaUpdate.exe

For reinstalling SQL to the same DB, follow these steps:

1) Uninstall PostgreSQL from the Windows Control Panel.
2) If you are moving the DB to a different drive/partition, manually cut or copy the \Postgresql\8.x\data directory to it's new location.
3) run the command - start > programs > accessories > command prompt > right-click > run as administrator

net user postgres /delete
4) reboot.
5) Install the same version of PostgreSQL as your current DB using these instructions - FAQ - Hold'em Manager Poker Tracking Software :: Reinstall PostgreSQL Preserving Old Database (http://faq.holdemmanager.com/questions/71/Reinstall+PostgreSQL+Preserving+Old+Database)
6) If you have moved/installed your DB to a non-default location, please make sure to change the 'Data Directory' path during the installation.

[GrndStneCboy]

ugggggggggggggg!

I have followed every step on here because I ran out of space on my C: drive and I have been working for 4 hours trying to get Holdem Manager to run again.
:mad:

Once I changed the initial location information that fozzy listed, the service would not start back up.
I uninstalled PostgreSQL, reinstalled and chose the new location on the larger harddrive. Changed my Login and password, hit connect and get a Connection Error: FATAL: 28000: no pg_hba.conf entry for host "127.0.0.1"..........

[morny]

Hi,

Please email me and we can do a teamviewer session, just download teamviewer from TeamViewer - Free Remote Access and Remote Desktop Sharing over the Internet (http://www.teamviewer.com) and let me know a suitable time GMT in advance. Teamviewer allows me to access your computer to fix the problem.

When we agree a suitable time you just need to send me the ID and password that Teamviewer creates to get access to your PC


support@holdemmanager.net

Thanks
Morny

...

[pampaseba]

Is imposible install Postgress on hard D.
By default, is instaled in the same disk C. There are not options for the destination folder

[GrndStneCboy]

okay,
I got it running by installing HOLDEM MANAGER again, but this time installing it on my alternate harddrive(where I had moved my postgresql files(data folder)).
I imported with what I had exported from my previous version as well.

I used the Combo installer and it installed PostgreSQL 8.4. My previous version was 8.3. I wasn't given the option of selecting where I could install 8.4, it just defaulted to C drive.
I would like to have this new version where I had placed 8.3, but as of right now...I am not gonna struggle with it any longer.

what a day.
I will work on trying to get it to function correctly another time.

thanks for your offers to help out mods!
GrndStneCboy

[LowWater]

Presently, I have executable v1.11.01b along with the v8.3 database on HDD C, my only hard drive with an operating system (Windows XP SP3) and all my Program Files. This includes Holdem Manager and PostgreSQL.

HDD C is one of four identical HDDs (Seagate Barracuda 7200.10 SATA 3.0Gb/s 320GB HDD). The other three HDDs, D, E and F, are strictly for data. The four HDDs are not in any RAID array.

I wish to move the datablase for PostgreSQL from HDD C to HDD F and dedicate that data drive to my poker database.

Is this possible?

[morny]

See this FAQ FAQ - Hold'em Manager Poker Tracking Software :: Move Database to Another Hard Drive or Partition (http://faq.holdemmanager.com/questions/368/Move+Database+to+Another+Hard+Drive+or+Partition)

[LowWater]

Thanks Morny.

Honestly, I looked myself to try and be the self-sufficient, self-help kind of guy - guess I missed it.

Take Care