2 ways to store DB on different drive to PostgreSQL

[Mullin]

Firstly, if an expert could help me here I'd consider donating a testicle (not that it would be much use to you, but it illustrates the dire straits that I'm in)

viewtopic.php?f=17&t=13300


On my travels trying to figure this out for myself using old posts it appears I've come across two fundamentally different ways to keep your DB on a different drive to the PostgreSQL installation:

viewtopic.php?f=17&t=526&start=0pny#p3436
As far as I can tell this method uses TABLESPACE to store the data folder only on the second drive while keeping the vast majority of config and other files and folders on the main drive.

viewtopic.php?f=17&t=3463#p16720
(specifically referring to the second point mentioned... paragraph starting "Alternatively...")
As far as I can tell this method copies the vast majority of files and folders (including the data folder) to the new drive, leaving a config file (postgresql.conf) on the original drive to point the program to all the goodies on the second drive.



Are both of these posts valid ie. do they work?

If so, how would the methods compare to each other with regards to:

Speed of operation (I'd guess post 1 - only the data is stored on the secondary drive, the program itself is intact on the original drive)
Ease of recovery in case of HD/program (I'd guess post 2 - all settings would be stored with the data ie. the treasure chest AND the key)


Advice here would be appreciated... there seems to be a lack of direction as to the workings of PostgreSQL... obviously its a stand-alone product and not part of PT3, however it's required for PT3 so you'd think we'd be able to get definitive advice as to how to use it correctly. As is we have to trawl through old posts hoping someone else had the same problem (assuming the solutions worked then and/or still work now), or wait for a postgreSQL expert to answer posts (which can't be expected to be answered promptly as I'm assuming these experts are not part of the PT team, given postgreSQL isn't part of PT3)

I guess that's another thing to tack on the end of the world's biggest to-do list. In the mean time if a postgreSQL expert is out there answers to the above questions would be greatly appreciated.

Mullin

[kraada]

Mullin,

I've been doing some digging for you, so don't think you've been ignored

To explain the difference between using a tablespace and not:

If you use a tablespace, only the data from the database is put in the tablespace location. The transaction log isn't written there as well. This improves performance over writing all of the data to the other drive in at least one way: the transaction log needs to be written to the hard drive regularly and that takes time. If that's happening on a different drive than at times of heavy load you'll see better performance because the only thing your spare drive is writing is the actual data in the actual database. With the other method, you move the entire data directory -- with transaction log -- to the spare drive.

Now, in your other thread where you've had a hard drive crash we have a problem: your transaction log (the pg_xlog) directory is on the dead hard drive. Now, depending on the manner in which the drive crashed, I'm not sure how easy recovery is going to be. If PostgreSQL was off at the time, you should just be able to point your postgresql.conf at the data directory you have, and fire PostgreSQL up and everything should be fine. If postgres was running when the aforementioned drive gave up the ghost, so to speak, then you may have more trouble. Exactly how much I'm not sure, hence I've been digging to see what I can find. I haven't (as of yet) found a whole lot.

What I'd say you should do for your problems at this point is to make a copy of your data directory, call it data2 or some such, try starting that with that directory as the data directory in the postgresql.conf and see what happens. It may complain about a missing pg_xlog so you may need to create that subdirectory (make sure the permissions on it match the permissions on the other subdirectories also; permissions matter for PostgreSQL).

I'm looking into what else can be done if your xlog is gone but your database is mostly intact and I'll let you know what I can find.

I hope that helps clear up a few things, at least.

[Mullin]

Thanks a lot kraada, appreciate your help.

Re: the circumstances of the crash, I believe its worst case scenario.

Pretty sure I was datamining and auto-importing at the time of the crash


Anyhow, looking forward to what you come up with, and thanks again for your help.

Mullin

[kraada]

Well, the first thing you should do (assuming you have the hard drive space) is make another copy of the database for us to try recovery on. That way in case something gets screwed up we still have the original data. Once you have the copy, only work from the copy for the rest of my suggestions until we get something working or need to delete our failed attempt and try again (hopefully that won't happen, but better safe than sorry).

So, step one: install PostgreSQL on the machine, and point the PostgreSQL data directory at your copy of the backup. Edit the postgresql.conf, and uncomment "data_directory" (under File Loactions) by removing the # at the beginning of the line, set it equal to the path of the directory with the copy of the backup.

Attempt to restart the PostgreSQL server, see how badly it chokes. If it works well, great, fire up PT3 and export all of the hand histories you have in all of the databases you an access.

Then go create a new database elsewhere, and reimport those hands in Beta 19 just to make sure that your data is consistent.

I don't expect that to work that well, but hey it's possible

Assuming that fails and you get an error like

Code: Select all

2002-10-22 14:53:17 [2247]   DEBUG:  database system was interrupted at 2002-10-17 23:24:45 PST
2002-10-22 14:53:17 [2247]   DEBUG:  ReadRecord: out-of-sequence SUI 220 (after 221) in log file 4, segment 111,
offset 450560
2002-10-22 14:53:17 [2247]   DEBUG:  invalid primary checkpoint record
2002-10-22 14:53:17 [2247]   DEBUG:  open of /pg_xlog/000000040000006E (log file 4, segment 110) faile
d: No such file or directory
2002-10-22 14:53:17 [2247]   DEBUG:  invalid secondary checkpoint record
2002-10-22 14:53:17 [2247]   FATAL 2:  unable to locate a valid checkpoint record
2002-10-22 14:53:17 [2247]   DEBUG:  proc_exit(2)
2002-10-22 14:53:17 [2247]   DEBUG:  shmem_exit(2)
2002-10-22 14:53:17 [2247]   DEBUG:  exit(2)
2002-10-22 14:53:17 [2244]   DEBUG:  reaping dead processes
2002-10-22 14:53:17 [2244]   DEBUG:  startup process (pid 2247) exited with exit code 2
2002-10-22 14:53:17 [2244]   DEBUG:  aborting startup due to startup process failure
2002-10-22 14:53:17 [2244]   DEBUG:  proc_exit(1)
2002-10-22 14:53:17 [2244]   DEBUG:  shmem_exit(1)
2002-10-22 14:53:17 [2244]   DEBUG:  exit(1)

it indicates that postgres is complaining about the lack of a transaction log. That's more along the lines of what I expect, and the first thing to do here is to reset the transaction log and hope not too much data was corrupted. You do this using the command pg_resetxlog from the command line (pg_resetxlog lives in C:\Program Files\PostgreSQL\8.3\bin\). The command is hopefully just pg_resetxlog <data directory>. Here's where it can start to get a bit hairy. Depending on the state of the database pg_resetxlog might fail for a variety of reasons, for which there are various command line switches to tell it things like "use this as the next OID/transaction ID/transaction ID's epoch" etc.

So, if pg_resetxlog fails please post what it says when it fails and we'll go from there and work through what flags you need and what values make sense for them. If it succeeds, then you should be able to restart the database. Again, once you have the database running, export the hand histories and go create a new clean consistent database elsewhere and once you've gotten your new database up and running you can delete the copy and the original backup.

[Hicham]

I was sent here by the bikeriding avatar dude ( bad memory for names and to lazy to check now).

I recently had to reinstall windows. I copied the things I needed -like my PT database- to another HD. When I recently installed Build 3, it didn't find my 'old' database. I tried to look for it in PT3, but I find no way to really browse my HD.

Note that I am have no experience whatsoever with databases, so unfortunately the posts befoe me went straight over my head

Couple of questions:
- How to make PT3 recognize my old database?
- Can I merge the old with the new one?
- How to make it so I don't lose speed?

Thanks!

[WhiteRider]

That would be me..
Here is the original post, just for reference.
viewtopic.php?f=16&t=13413&p=62778#p62685

[Hicham]

Sorry White Rider

Another question :

- I think PT3 automatically puts the database on C, which happens to be my smallest partition. I have a huge drive for data. How to move my database there without slowing something down?

[WhiteRider]

Setting up postgres on another drive is explained in the Database / Postgres FAQ.

[Hicham]

So, I am trying to make sense of this.

First I need to backup my database. Then I need to uninstall PT3 and Postgresql. Then I need to import my saved database.

I have saved my database from before the windows reinstall, but it which folder do I need to import (or is it export?) to have it show up in my latest PT3 install? Is it the procesed folder in PoerTracker3 or something under Postgresql folder? Sorry if this is explained in detail somewhere else, but most of the explanations here seem to have the presumption that you have a basic knowledge of how PT3 and PostgreSQL work, which I don't.

Thanks for the patience.

[WhiteRider]

If you still have all your original hand history files, the simplest way will be to create a new database on your other drive, and then just run a Manual Import.
See the How To in the postgres FAQ: viewtopic.php?f=45&t=4098&p=56993#p56990

If you don't then you will need to copy the folders from your old computer onto your other drive, and reconnect as per kraada's post.