PokerTracker

Index

Basics

How to...

Backing up PostgreSQL databases (Step by step in PGAdmin)

Database/PostgreSQL problems

How To: Update PostgreSQL (same major version)

What is PostgreSQL?
PostgreSQL is a powerful, open source relational database system. PT3 uses PostgreSQL to store all of the data that it generates from your hand histories. For more information on PostgreSQL, see http://www.postgresql.org.


What are the Housekeeping functions (Vacuum/Cluster/Analyse/Cache) for? How often should I do them?
These 'housekeeping' functions are to keep your databases running efficiently.
You will find information at http://www.postgresql.org, but PT3 does a little more than the basics.
The cache is an addition to the database which allows much faster retrieval of stats.
The recommendations are here: http://www.pokertracker.com/products/PT ... erformance


What version of PostgreSQL should I be running?
In general, the recommended version of PostgreSQL will be included in the "PT3 with PostgreSQL" download, so if you install that way you shouldn't need to worry about your PostgreSQL version.
The latest 8.3.x versions should work, and the first 8.4 version works too, although versions from 8.2 are supported.

Note: PT2 and PAHud do not fully support PostgreSQL 8.3, so if you use PostgreSQL databases in PT2 you should stick to 8.2.
It's not too serious, the cache doesn't work in PAH with 8.3, but it is possible to use PT2 with PostgreSQL 8.3.


How many hands can I import into a single database?
There isn't a limit to the number of hands a database can hold, other than disk space, but when your databases get very big you will start to lose performance. When this happens really depends on your system.

The recommendation for large scale datamining like this is to create a new database each month or 2 to import your observed hands into while importing all your 'played' hands into the active database (see the 'Databases' section on the auto import tab to configure where to import played and observed hands).
If you run housekeeping on your databases (as recommended here) they should stay pretty fast, and once you've finished importing into the 'september08' DB and moved onto 'october08' you will not need to re-cache it again now that you're not importing into it any more. Then after a few months you can remove old DBs from the HUD (or even delete them).


Can I delete the PostgreSQL log files?
The only files in the C:\Program Files\PostgreSQL\ directory that are safe to delete are the files in:
C:\Program Files\PostgreSQL\8.3\Data\pg_log. Those logs may be safely discarded. Everything else should be kept.

How do I make PostgreSQL run quicker?
Please see this tutorial: Tuning PostgreSQL.
Before making any changes to your PostgreSQL configuration please make a backup of the "C:\Program Files\PostgreSQL\8.3\data\postgresql.conf" file - if you make the wrong changes it can make performance worse and you may want to revert to the original settings.


Can I have a PostgreSQL database on another networked computer? How do I do that?
How To: Network PostgreSQL Database
See also this well-written post by Geert: PT3 on a Mac / PostgreSQL on a server (useful for non-mac setups too).


Can I have a PostgreSQL database on a separate drive in the same computer?
Details in this topic, or this one.
An alternative is to install PostgreSQL on another drive. You need to fully remove PostgreSQL from your PC (see below), then install to your chosen drive.


Can I have PostgreSQL installed on a hotswap drive to use on two different computers?
Yes. Full tutorial guide is located Here.
Or see this alternative method posted by a PT3 user: http://www.pokertracker.com/forums/viewtopic.php?f=17&t=20345&p=96577#p96460


How do I move my database to another drive?
Please see this post by a PT3 user: http://www.pokertracker.com/forums/viewtopic.php?f=17&t=20345&p=96577#p96460


How do I synchronize my databases on two computers?
There are a few options - the simplest is just to copy all the hand history files between computers after you play, and import them on both computers (or set up a shared folder and have both computers import the hand histories from it).
You could backup and restore the DBs, but that's quite long-winded.
If your computers are always networked, you could share a DB (but don't import from both at the same time). How To: Network PostgreSQL Database
Alternatively, you could set up a single DB on your laptop which would be shared by both (using the network link above), but then your laptop harddisk is probably smaller and slower, so this won't be so efficient.

You can also install PostgreSQL on a hot swap hard drive and then use it on two different computers (see here: Tutorial: 2 install of pt3, 1 database, no networking).


How do I completely uninstall PostgreSQL?
How to reinstall PostgreSQL


I have a copy of my PostgreSQL folder from before a harddisk failure or before I uninstalled PostgreSQL - how do I reconnect to my databases?
See How to reinstall PostgreSQL.
1.) Save your "data" folder. You can find it in C:\Program Files\PostgreSQL\8.3.
2.) Uninstall PostgreSQL completely and delete everything under C:\Program Files\PostgreSQL\8.3. EXCEPT the data folder.
3.) Install PostgreSQL again, but untick "Initialize Database cluster".
4.) click on "connect" again and it should work


I want to upgrade to a newer version of PostgreSQL.
If you are upgrading between major versions (e.g. 8.2 to 8.3) and you have existing databases, you will need to backup and restore any existing databases.
Until these functions are added to PT3, see this reply (below).
Then follow the upgrade instructions with your new PostgreSQL installer.


I already have PostgreSQL installed by another application, can I use that with PT3?
Yes, if you already have PostgreSQL installed by another application, you should be able to use it with PT3.
Most applications will have created the default user "postgres", but if yours didn't you will need to create the "postgres" user which PT3 needs.


To create the postgres user.
You will need to know the username and password that the application created.

Open the PostgreSQL command line: Start -> Programs -> PostgreSQL -> Command Prompt
..and enter:

Code: Select all

dropuser -U [third party PostgreSQL username] -W postgres

Code: Select all

createuser -s -d -r -U [third party PostgreSQL username] -W postgres

Make sure you make a note of the password you choose as you will need it.
This will create the "postgres" user which should enable PT3 to connect to PostgreSQL.

FYI: http://www.postgresql.org/docs/8.3/inte ... euser.html


How do I Purge and re-import all my hands?
First; make sure you have all your hand history files. You may have them in the 'processed' folder, but if not then you must export them; Database -> Database Management -> Export.

After this, if you have a registered copy then you can create a new database: Database -> Database Management -> New.
If not, you will have to empty your single DB. Database -> Database Management -> Purge.

Now you can run a Manual Import and import all your existing hand histories.
Tutorial: Exporting Sessions
Tutorial: Purging Sessions
Database Management & Maintenance Guide
Tutorial: Using Manual Import

Build 5.3+ / Beta 29+
From Beta 29 onwards PT3 has database backup and restore functions built in.

These are on the Database > Database Management window.

Backup

Select the database you wish to backup and click the Backup button and choose where to save the file.

Restore

You can only restore into an empty database.
If you click Restore when a non-empty database is selected PT3 will offer to create a new empty database for you, or if an empty database is selected then the database will be restored into that.

Build 5.2 / Beta 28 and Lower (OLD)
For reference I'll leave the old instructions here too, although in normal use these are no longer needed.

Backing up a PostgreSQL database using PGAdmin III
With PT3 closed, run PGAdmin: Start -> Programs -> PostgreSQL -> PGAdmin.
Double-click PostgreSQL Database Server on the left hand side, and open the Databases list.
Right click the database and choose "Backup...".
Make sure you choose the Compress option, as Plain won't restore properly.
See this post for more info: viewtopic.php?f=17&t=12153&view=unread#p57016

To restore you will need to use PT3 to create a new database, then use the above method but choose "Restore..." to restore the backup into the new database.

Click here for detailed steps with images.

Backing up a PostgreSQL database from the command line

Go Start -> Programs -> PostgreSQL -> Command Prompt. Run the following command:

Code: Select all

pg_dump -E SQL_ASCII -f "filename" -F p -h "host_name" -p "port" -U "username" "database"

(replace yourdatabasename with the name of your database)

This will backup your database into your C:/Program Files/PostgreSQL/bin folder. You should move it elsewhere, away from the PostgreSQL folders to store it.

To restore the database, start PT3 to create a new database, then move your backed-up file back to C:/Program Files/PostgreSQL/bin and go Start -> Programs -> PostgreSQL -> Command Prompt. Run the following commands:

Code: Select all

createdb -E SQL_ASCII -U "username" "database"
psql -a -d "database" -h "host_name" -p "port" -U "username" -f "filename"

This post (viewtopic.php?f=16&t=11866&view=unread#p55876) shows how to do it on a linux box.

I can't connect to PostgreSQL, the user or password is wrong.
The default user is 'postgres' and password 'dbpass' (no quotes), so try that unless you changed it yourself.
If you can't remember your password, or can't get it to work, you can configure PostgreSQL to not require a correct password.
How To: Remove PostgreSQL Password


unable to login to database (PT3_xxxx_xx_xx_xxxxxx) - reason:could not connect to server:connection refused(0x0000274D/10061) Is the server running on host "localhost" and accepting TCPT/IP connection on port 5432?
First thing to try is to restart the PostgreSQL service.
Start -> Programs -> PostgreSQL -> Start Service.
If this doesn't help, try a reboot.
Update: try this;
Try stopping the service (Start -> Programs -> PostgreSQL -> Stop Service), running a search on your computer for postmaster.pid and then deleting that file, then restarting the service and see if that helps.
Note that you may need to turn on 'View hidden files and folders', and the file seems to be in the folder './Program Files/PostgreSQL/<version>/data'
See also: http://www.pokertracker.com/forums/viewtopic.php?f=17&t=6332#p38285


If you continually get this same issue every time you start PT3
Please see this post: http://www.pokertracker.com/forums/viewtopic.php?f=17&t=6332&view=unread#p52444


unable to login to database (PT3 DB). Reason: FATAL: no pg_hba.conf entry for host "::1", user "postgres", database "PT3 DB", SSL off
Click Start --> Programs --> PostgreSQL --> Edit pg_hba.conf
Scroll to the bottom, and add this line exactly as you see it here:
host all all ::1/128 md5
Then save and exit the file, and Start --> Programs --> PostgreSQL --> Reload Configuration and things should then work properly.


Unable to import hand (#18202934050). Reason: Unable to execute query: COPY tourney_holdem_hand_summary FROM STDIN;; Reason: Fatal Error (ERROR: could not read block 0 of relation 1663/16438/16742: Invalid argument CONTEXT: COPY tourney_holdem_hand_summary, line 1: "54551 100 736 3 971 18202934050 2008/06/17 20:55:42 2008/06/17 17:22:44 9 2 2 2 1500.00 1500.00 1500..." )
Please see this topic: http://www.pokertracker.com/forums/viewtopic.php?f=16&t=7893#p37362


2008/08/15 15:06:40: Full Tilt: Unable to import hand (#7651813545). Reason: Unable to execute query: COPY holdem_hand_player_statistics FROM STDIN;; Reason: Fatal Error (ERROR: index "hhps:idx5-id_session" contains unexpected zero page at block 0 HINT: Please REINDEX it. CONTEXT: COPY holdem_hand_player_statistics, line 1: "70112 696 696 0 2 8819 2008/08/15 04:05:59 8 f f 0 f 0 f f f f f 0 f 0 t f f t f 0 f 0 t f t t f 0 f..." )
There are two main reasons for this kind of error. The first is a result of data corruption from a computer crash that requires reindexing your database. You can reindex your database from the PostgreSQL command prompt. Click Start --> Programs --> PostgreSQL --> Command prompt. At the prompt that appears, type “reindexdb -a” and hit enter. Be aware that if you have large databases this may take a while, so be patient and wait for it to finish.
(If the re-index doesn't work because you are not logged in as user 'postgres' enter: "reindexdb -U postgres -a").

The second reason for this error is a result of faulty hardware. You can set PostgreSQL to ignore these errors by clicking Start --> Programs --> PostgreSQL --> Configuration Files --> Edit postgresql.conf. In the configuration file that appears, go to the bottom of the file and add the text “zero_damaged_pages = on” on a new line and then save and close the file. Click Start --> Programs --> PostgreSQL --> Reload Configuration and PostgreSQL will ignore the damaged data. If you continue to have problems with that hard drive, you should consider replacing it.

Note: The PostgreSQL.conf file isn't available through the Start menu for PostgreSQL versions after 8.3 but you can open it manually (using Notepad or any other plain text editor) from the folder:

C:\Program Files (x86)\PostgreSQL\8.4\data

If you use Windows 7 or Vista you will need to open Notepad by right clicking and choosing Run As Administrator in order to be able to save your changes.


Unable to begin feeder import process
Please see this topic: http://www.pokertracker.com/forums/viewtopic.php?f=16&t=7347#p33709


Unable to import hand (#123456789). Reason: Unable to execute query: COPY holdem_hand_summary FROM STDIN;; Reason: Fatal Error (ERROR: duplicate key value violates unique constraint "holdem_hand_summary_primary_key" CONTEXT
That's not really a problem, it's just an indication that the hand was already in the database.

Stop all your poker sites.
Stop importing.
Make sure you have the 'move processed files to' option enabled on the auto import tab, and that it is configured to move them to somewhere completely separate from where you are importing from.
Start auto import and let it run until all the counts stop incrementing; then stop it. This should move all the old/processed hand history files away.
Open you hand history folders and check for old HH files - if there are any left behind, move them to the 'processed' folder yourself.
Start auto import again - it should report no files found.
Then start playing again.


Error: "PSQL failed to start verify that you have suficient priveledges to start system services."
This can be a problem with anti-virus software. See this topic:
http://www.pokertracker.com/forums/viewtopic.php?f=17&t=6902


Problems with initdb and privileges on Windows XP
See this post: How to install initdb manually on Win XP
Note: this is written by a user, and not tested personally.


Trouble installing/running/networking PostgreSQL
The problem quite often seems to be to do with anti-virus/firewall/anti-spam software or other stuff that prevents things from running. See here:
http://www.pokertracker.com/forums/viewtopic.php?f=15&t=7179&p=33158#p33111


Further help on these issues:
http://www.pokertracker.com/forums/viewtopic.php?f=17&t=7284

Note: From Beta 29 onwards PT3 has backup and restore functionality built in, so you no longer need to use these directions - I'll leave then here for reference only.

To backup a PT3 database using PGAdmin, follow these steps.

1. Open PGAdmin: Start -> Programs -> Postgres -> PGAdmin III.

2. Double click "PostgreSQL Database Server" to connect. If it asks for a password, the default is "dbpass" (no quotes).
Open (Click the + next to) PostgreSQL Database Server, then Databases.

3. Right click the database you want to back up, and select "Backup...".



4. On the Backup Database window choose the filename of the backup file and where you want to save it.
You can either type the path and filename or click the browse button ( "..." ) to the right.



5. Click "OK" - no other options need to be changed.
The backup should complete successfully and you will see "Process returned exit code 0."



To restore a database from a backup using PGAdmin, follow these steps.

First we have to create a new database.

6. In PGAdmin right click "Databases" and choose "New Database...".



7. On the New Database window enter a name and set Encoding to "SQL_ASCII".
Click OK and the database will be created



We can now restore the backup into the new database.

8. Right click the new database and select "Restore...".



9. On the "Restore Database" window enter (or browse to) the backup file we created earlier.



10. Click "OK" and the database will be restored - no other changes need to be made.
The restore should complete successfully and you will see "Process returned exit code 0."



To use a restored database in PT3 database.

11. Open the Database Management window (Database > Database Management) and click "New".

12. Enable the option "This database already exists" and click "Browse Databases" to select the newly created and restored database.

Upgrading PostgreSQL

PostgreSQL is a third party database application and is continually being updated. Versions 8.2 onwards are supported by PT3, but at least 8.3 is recommended.
When PT3 was launched 8.3.4 was in use but that is very old now and a lot of issues have been fixed and performance has been improved, so if you are using 8.3.x but not 8.3.23 you must update to version 8.3.23.
The same applies to updating 8.4.x or early versions of 9.0.x.

This guide only explains how to update within the same major version 8.3.x to 8.3.y (or 8.4.x to 8.4.y) - updating from 8.3.x to 8.4.y is a more involved process.

Do NOT update from 8.3 to 8.4 (or higher) using these directions - it will not work!

1) Before you start use the backup tool in PokerTracker to backup your databases.

In the case of any problems during the upgrade process this is vital so that you are able to recover your databases. You should make regular backups of your databases, as you would for any important data.
It might also be a good time to archive your Processed hands folder too, so that you have a copy of the original hand history files if you ever need to recreate your database.

Close PokerTracker.

2) PostgreSQL 8.3 has two types of installers and when updating you will need to use the same type as the original installation used. Only the same type of installer will work - the other type won't update properly, so if you're not sure you can try both. (If you are updating 8.4 or later then there is just one type of installer - the One Click installer.)

If PostgreSQL 8.3 was originally installed using PT3 (or an early version of PT4) then it would have used the pgInstaller.
If you installed manually you probably used the One Click installer.
PostgreSQL 8.3 is no longer being updated or supported, but you can still download the last version (8.3.23).

In most cases (when PostgreSQL 8.3 was installed by PokerTracker) you will want the pgInstaller, which you can download from this page.
Click to download the main installer version - the file you want is "postgresql-8.3.23-1.zip". Save this file to your computer.

If you installed using the One Click installer go here and select the appropriate 8.3.23 installer for your operating system.
Run the installer that you downloaded and skip points 3 to 5 - go to #6.

3) Unzip those files to their own folder. (Right-click unzip to folder usually works or use the wizard built-in to Windows.)

4) Double-click the "UPGRADE.BAT".
It will run at least two installers and both of them require a click or two. Just click "Next" or "Finished" until they are both complete.
If the updgrade.bat fails for any reason you will need to follow the directions to uninstall and re-install PostgreSQL in the Documentation. This will not lose any data so is perfectly safe.

5) Reboot when asked.

6) Upon reboot run PT3 and do full Database > Housekeeping with all 6 options ticked, for all databases.
This will take some time for large databases so please schedule accordingly.
You may want to run it overnight, for instance, if you have very large databases.