Alternative method for database on USB drive

PostgreSQL is the database server used to store information. Do you have a question or are you having problem with PostgreSQL? If so, post them here.

Moderator: Moderators

Alternative method for database on USB drive

Postby mikew » Mon Jun 15, 2009 7:42 pm

Suppose you want to synchronize your database between your main computer and your laptop. One way is to save all your hands you play, transfer them to the other computer, and then re-import on your other PT3 install. If that is too tedious for you (like it is for me), then you might consider putting your database on a hotswap USB hard drive, and plugging it into the respective computer each time you want to play.

If you go to the FAQ, you'll find this: Tutorial: 2 installs of pt3, 1 database, no networking!

Here, I will list an alternative way to accomplish this, based off the competing product's FAQ. User prajna wrote the tutorial above and his method was to install postgresql directly to the usb drive. Here, I will have postgresql installed to each computer, but have only the 'data' directory on the usb drive. I do not claim that this method is better in any way than the previous one outlined. In fact, I encourage any responses in that regard for all of our clairty.

Assumptions:
- You already have PT3 and Postgre installed and in use on your main computer.
- You want to be able to use and play with the db on a laptop.
- You want to plug the drive back into the main computer and have all laptop play up-to-date in the db with no other importing.

Steps: (main computer first, then laptop)

  1. As you should always do before messing with your db, MAKE A BACKUP, just in case stuff goes wrong. Follow the guide for backup here: Backup/Restore with PGAdmin. Put the backup file somewhere safe.
  2. Stop the postgres service on the main computer. Go to: Control Panel -> Administrative Tools -> Services. Find the PostgreSQL service, double click, and Stop it.
  3. Copy the 'data' directory to the usb drive. First, navigate to your usb drive (lets assume windows makes it E: for now) and create the folders "Program Files", inside that "PostgreSQL", and inside that "8.3". Leave it open, and navigate another window to your main computers hard drives postgres installation at "C:\Program Files\PostgreSQL\8.3". Copy the "data" directory to the hard drive. Now when you navigate to "E:\Program Files\PostgreSQL\8.3\data" you should see the same contents.
  4. Make windows assign a consistent drive letter to the usb drive. Control Panel -> Administrative Tools -> Computer Management -> Disk Management. Find the usb drive (we assumed E:), right click, change drive letter and path. I changed it to Z: and we'll use that for this tutorial. After this, no matter what you plug into the computer, even if you have 5 usb drives, this particular drive will always be Z:
  5. Tell Postgres your new 'data' location. Open the registry editor on the main computer. Start menu -> Run, then type in "regedit". BE CAREFUL HERE, YOU COULD SCREW UP YOUR COMPUTER. In the left pane, navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\pgsql-8.3. When you find it, in the right pane, open the "ImagePath" entry. Its probably too long for the box for you to read, but, it should 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 the end part is what needs to be changed. The part after the "-D" tells postgres where to find the data directory. Simply change the C to Z (or whatever letter you assigned in Step 4) so the end part reads "Z:\Program Files\PostgreSQL\8.3\data\" and the new full ImagePath entry should be "C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe" runservice -w -N "pgsql-8.3" -D "Z:\Program Files\PostgreSQL\8.3\data\"
  6. Restart Postgres on main comp. Start the service the same way you stopped it in Step 2. The status should say "Started". If it doesn't, something went wrong. You can check for errors in Control Panel -> Administrative Tools -> Event Viewer -> Application.
  7. Test PT3 on main comp. PT3 will still be configured for your database on the main hard drive. You will get an error on PT3 startup and need to change it. In the PT3 menu, go to Database -> Database Management. Click "New". Click "This database already exists" and then "Browse databases". Select your db. Now you should have PT3 working on the main comp, reading from your database off the external hard drive.
  8. Repeat Steps 4 - 7 on your laptop. If you don't have PT3 and postgres installed on your laptop, do it. Download the full installer with postgres and install as normal. Then repeat the steps above.

*I will put in the same warning as from the original tutorial, and that is, you need to stop the postgres service like in Step 2 each time you remove the usb hard drive. I use the Safely Remove Hardware icon (grey rectangle with green arrow) in the tray next to the clock, and WinXP actually won't allow me to stop the drive before removing it. If you just yank out the USB cable, you may run into problems if you don't stop the postgres service first.

I am not a member of PT3 team, just a user who wrote this. I don't check this forum very frequently. Put discussion in this thread.

Feel free to add this to FAQ if you deem worthy.
mikew
 
Posts: 56
Joined: Sat Jan 03, 2009 12:41 am

Re: Alternative method for database on USB drive

Postby mikew » Tue Jun 16, 2009 2:38 pm

I've found an easier way to do Step 5 that doesn't involve the registry.

Step 5: Tell Postgres your new 'data' location. Start menu -> Programs -> PostgreSQL -> Command Prompt. Make sure you type this in exactly how I have it here.

Code: Select all
sc config pgsql-8.3 binPath= "\"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe\" runservice -w -N \"pgsql-8.3\" -D \"Z:\Program Files\PostgreSQL\8.3\data\""


This should accomplish the same thing.
mikew
 
Posts: 56
Joined: Sat Jan 03, 2009 12:41 am

Re: Alternative method for database on USB drive

Postby rianmullins » Sat Oct 31, 2009 11:10 pm

You may also need to give permissions to postgres for the new data folder. At least I did.

Right click your new data folder on the new drive, choose properties, then security, then press the "Add" button, and enter "postgres" in the "Enter the object ..." box, press "Check Names", press "OK", and you should be done.
rianmullins
 
Posts: 3
Joined: Sun May 11, 2008 10:40 pm

Re: Alternative method for database on USB drive

Postby Nichiei » Wed Nov 10, 2010 2:39 pm

mikew wrote:I've found an easier way to do Step 5 that doesn't involve the registry.

Step 5: Tell Postgres your new 'data' location. Start menu -> Programs -> PostgreSQL -> Command Prompt. Make sure you type this in exactly how I have it here.

Code: Select all
sc config pgsql-8.3 binPath= "\"C:\Program Files\PostgreSQL\8.3\bin\pg_ctl.exe\" runservice -w -N \"pgsql-8.3\" -D \"Z:\Program Files\PostgreSQL\8.3\data\""


This should accomplish the same thing.


Hi,

I'm trying to move my PT3 DB from my C: partition to just another partition on the same disk. Am I right in thinking I can just follow the instructions in the OP substituting the drive letters and that should do the job?

I'm using PT v3.07.6 and PostgreSQL v9.0.1-1.

For the instructions quoted above I just change the 8.3 to 9.0 or is there more to it due to the newer version of PostgreSQL?

Many thanks in advance!
Nichiei
 
Posts: 2
Joined: Wed Aug 27, 2008 8:16 pm

Re: Alternative method for database on USB drive

Postby kraada » Wed Nov 10, 2010 2:58 pm

That should just work with the substitutions you've mentioned as far as I'm aware.
kraada
Moderator
 
Posts: 54435
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Alternative method for database on USB drive

Postby Nichiei » Wed Nov 10, 2010 6:22 pm

kraada wrote:That should just work with the substitutions you've mentioned as far as I'm aware.


Hi,

I gave it a go but I got a bunch of SQL errors when trying to use the shell to update the location.

I ended up using the registry route in OP and it all worked fine.

Cheers :)
Nichiei
 
Posts: 2
Joined: Wed Aug 27, 2008 8:16 pm

Re: Alternative method for database on USB drive

Postby Peekaboo » Mon Nov 15, 2010 3:11 pm

Just to let anyone know, in windows 7 with the drive I had, I had to give "myself" full security control.

Right click on the drive. >> Properties. Security Tab. Then set "full control" for all users (or ones you want).
Peekaboo
 
Posts: 1
Joined: Thu Jul 16, 2009 6:16 pm


Return to PostgreSQL [Read Only]

Who is online

Users browsing this forum: No registered users and 3 guests

cron