PostgreSQL Troubleshooting

PokerTracker 4 version 4.0.x
October 25, 2013
  • PostgreSQL How To...
    • Make PostgreSQL Run Faster

      To improve performance of your PostgreSQL server we recommend considering a hardware  upgrade.  Software upgrades and tuning might result in a small performance improvements, but the primary focus should be on increasing hardware specs if you are concerned with PostgreSQL speed. 

      1. The greatest performance increase will be earned by switching to a dedicated Solid State Drive (SSD). 7200 RPM drives are acceptable for use, but 5400 RPM drives are not recommended.  Prior to the 2013 most Macs came with a 5400 RPM drive built in, most new Mac computers now include the option to have an SSD drive installed.  Macbook Air and Retina Macbook Pros all contain SSD drives by default. 
      2. Consult the Recommended Specs FAQ for all other hardware consideration, we recommend an recent i7 multi-core 64 Bit processor, a minimum of 4 gigabytes of RAM and a 64Bit native OS (Windows 7 64 or greater / Mac OS X Lion or greater)
      3. Use the 64 Bit version of PostgreSQL 9.3.x
    • Update PostgreSQL - Same Major version

      PostgreSQL is a third party database application and is continually being updated. Versions 8.3.9 or later, 8.4, and 9.0 to 9.3 are supported by PokerTracker 4, if you are using a 64 bit processor and operating system with at least 4 Gigs of RAM then we advise upgrading to the latest version of PostgreSQL 9.3.x 64 Bit edition. 

      When PokerTracker 4 was initial developed 8.3.4 was in use, since that time several issues have been fixed and performance has been improved, if you are using 8.3.x but lower than 8.3.9 you should update to the latest 8.3.x version available at a minimum.

      The same applies to updating 8.4.x - at the time of writing 8.4.4 is current and working fine, but if you have one of the early versions (less than 8.4.2, say) we would suggest updating.

      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 much more involved process, please consult with ethe PokerTracker support team before attempting to update. Do NOT update from 8.3 to 8.4 (or 8.4 to 9.0) using these directions - it will not work!

      1. Before you start use the backup tool in PokerTracker 4 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 folder, which is located in the PokerTracker 4 data folder, too, so that you have a copy of the original hand history files if you ever need to recreate your database.
      2. Close PokerTracker 4.
      3. Go to the PostgreSQL Download page and click on your operating system in the "Binaries" section. On the following page click the download link.

        If you are using PostgreSQL 8.3 as installed by PT4 use the PGInstaller. Do NOT use the One Click Installer.  For all other versions of PostgreSQL please select the One Click Installer.

        Click on the latest version (highest .x number) of the major version you are using (e.g. the highest 8.3.x version if you are using 8.3) and then on the link for your operating system.
        Click to download the main installer version - this will have a filename like "postgresql-8.3.11-1.zip". Save this file to your computer.
      4. If you downloaded the PGInstaller to upgrade to the latest version of 8.3.x then continue with Step 5.  If you downloaded the On Click Installer for 8.4.x or 9.0.x then run the installer and then skip to step 8. 
      5. Unzip those files to their own folder.
      6. Before proceeding make sure steps 1 and 2 are completed.
      7. Double-click the file named "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, then please follow the directions to uninstall and re-install PostgreSQL in the PostgreSQL Database Guide. This method will not cause any data loss, it is perfectly safe.  Please skip to step 9.
      8. Reboot your computer if requested by the PostgreSQL installation process.
      9. Upon reboot run PokerTracker 4.  If you are still having issues, please contact PokerTracker support
    • Backup and Restore in pgAdmin III

      PokerTracker 4 has backup and restore functionality built into the application.  In some rare situations these built in tools may not work for your needs, such as when PokerTracker 4 is installed in a VM but PostgreSQL is installed on the host OS.  Use the instructions below in the event you must use an external tool to backup or restire your database. 

      Before you proceed using the pgAdmin II method, we suggest you consult the tutorial Backing up and Restoring databases.

      • Using pgAdmin III to Backup a Database
        1. Open pgAdmin III
          Windows: Start -> Programs -> Postgres -> pgAdmin III
          Mac OS X: \Applications\PostgreSQL x.x\pgAdmin III
        2. Double click "PostgreSQL Database Server" to connect. If it asks for a password, the default password is dbpass, another commonly used password is postgrespass.
        3. Open (Click the + next to) PostgreSQL Database Server, then Databases.
        4. Right click the database you want to back up, and select "Backup...".

          Backing Up A PokerTracker 4 Database
           
        5. 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.

          Backing Up A PT4 DB
           
        6. Click "OK"  to select the standard options.
          The backup should complete successfully once you see "Process returned exit code 0."

          Final Backed Up PT4 DB
      • Using pgAdminIII to Restore a Database
        1. First we have to create a new database.
          In pgAdminIII right click "Databases" and choosing "New Object" then "New Database...".

          New 8.4 PostgreSQL DB
           
        2. On the New Database window enter a name and set Encoding to UTF8 (if you are using PostgreSQL 8.2 set Encoding to "SQL_ASCII"). Click OK and the database will be created.

          Naming A Restored PT4 DB
        3. We can now restore the backup into the new database.
          Right click the new database and select "Restore...".

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

          Final Restoration Of PT4 DB
        5. 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."

          How To Restore A Poker Tracker 4 Database
      • Add a Manually Restored Database
        1. Open the Database Management window (Database > Database Management) and click "New".
        2. Enable the option "This database already exists" and click "Browse Databases" to select the newly created and restored database.

          Configure A Restored PT4 DB
    • Problems
      • Unable to Login to the Database
        • If you get an error message which looks like this:
        unable to login to database (PT4_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?

        The first thing to try is to restart the PostgreSQL service.  The simplest way to accomplish this is by restarting your computer.  Some Windows users can also try stopping the service manually by selecting Start -> Programs -> PostgreSQL -> Stop Service, but this option is not always available If you are able to stop the PostgreSQL service using this method, you can attempt to search your computer for the postmaster.pid file in /Program Files/PostgreSQL/X.X/data, if this file is found you can safely delete it then restart your computer.  This will often, but not always solve the problem. Do not attempt to delete the postmaster.pid file unless the PostgreSQL service has stopped.

        Replace X.X with the PostgreSQL version number, this is usually 8.3, 8.4, or 9.0. 
        • If you get an error message which looks like this:
        unable to login to database (PT4 DB). Reason: FATAL: no pg_hba.conf entry for host "::1", user "postgres", database "PT4 DB", SSL off

        Edit the pg_hba.conf file.

        Windows:

        You must use a text editor while acting as an administrator when editing the pg_hba.conf file. To accomplish this task, we suggest you run Notepad by right clicking on the icon, then select Run As Administrator. Click File > Open, then select the pg_hba.conf file which is normally found at C:/Programs Files/PostgreSQL/X.X/data/pg_hba.conf.

        Scroll to the bottom, and add this line exactly as you see it here:

        host all all ::1/128 md5

        Save the file and quit NotePad. To enable the changes you must either restart your computer or click Start > Programs > PostgreSQL > Reload Configuration.

        Mac OS X:

        1. Run the Terminal application found at /Applications/Utilities/Terminal.
        2. Type sudo nano /Library/PostgreSQL/X.X/data/pg_hba.conf. Type in your OS X administrator password if requested. This will start the Nano text editor inside of the Terminal window. 
        3. Use the down arrow key to scroll to the bottom of the file, and add this line exactly as you see it here: host all all ::1/128 md5
        4. Click CTRL+O to save the file, then CTRL+X to exit the text editor. 
        5. Restart your computer to enable the changes. 
        Replace X.X with the PostgreSQL version number, this is usually 8.3, 8.4, or 9.0
      • Trouble Installing or Running PostgreSQL

        Often problems with installing, running or networking PostgreSQL are permissions issues caused by security software so please check our Firewall Troubleshooting Guide to make sure you have things configured correctly to allow PokerTracker 4 and PostgreSQL full access.

      • Will Not Connect - Wrong User or Password

        If you already had PostgreSQL installed on your computer before using PokerTracker 4 then you will need to use the password which was set up when it was installed.  For example if you have tried another tracker try the password postgrespass, or if you previously had a different version of PokerTracker installed then the default password may be 'dbpass'.

        If you cannot remember your password and the default password options do not work, then you can configure PostgreSQL to not require a password when accessing the database from your local computer by following these steps:

        Passwords will still required for server access from anywhere besides your local computer.

        Windows:

        You must use a text editor while acting as an administrator when editing the pg_hba.conf file. To accomplish this task, we suggest you run Notepad by right clicking on the icon, then select Run As Administrator. Click File > Open, then select the pg_hba.conf file which is normally found at C:/Programs Files/PostgreSQL/X.X/data/pg_hba.conf.

        Scroll to the bottom of the document, then replace all instances of the word md5, with the word trust.

        Save the file and quit NotePad. To enable the changes you must either restart your computer or click Start > Programs > PostgreSQL > Reload Configuration.

        Mac OS X:

        1. Run the Terminal application found at /Applications/Utilities/Terminal.
        2. Type sudo nano /Library/PostgreSQL/X.X/data/pg_hba.conf. Type in your OS X administrator password if requested. This will start the Nano text editor inside of the Terminal window. 
        3. Use the down arrow key to scroll to the bottom of the file to replace all instances of the word md5, with the word trust.
        4. Click CTRL+O to save the file, then CTRL+X to exit the text editor. 
        5. Restart your computer to enable the changes. 
        Replace X.X with the PostgreSQL version number, this is usually 8.3, 8.4, or 9.0
      • Unable to Import Hand

        If you see an error that looks like this:

        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..." )

        ..or this:

        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 common reasons for this kind of error.

        The first possible reason is a result of data corruption from a computer crash that requires reindexing your database.  You can reindex your database from the Database > Database Management window.  Select the affected database, then click Housekeeping > Reindex.  After the housekeeping is complete, we recomend that you Rebuild the Cache to assure your database speed performs as expected. 

        Reindex Database

         

        The second possible reason for this error is a result of faulty hardware.

        Although we do not advise this option, you may chose to set PostgreSQL to ignore these errors by editing the postgresql.conf file.

        Windows:

        You must use a text editor while acting as an administrator when editing the postgresql.conf file. To accomplish this task, we suggest you run Notepad by right clicking on the icon, then select Run As Administrator. Click File > Open, then select the postgresql.conf file which is normally found at C:/Programs Files/PostgreSQL/X.X/data/postgresql.conf.

        Scroll to the bottom of the document, create a new line and then add the text zero_damaged_pages = on

        Save the file and quit NotePad. To enable the changes you must either restart your computer or click Start > Programs > PostgreSQL > Reload Configuration.

        Mac OS X:

        1. Run the Terminal application found at /Applications/Utilities/Terminal.
        2. Type sudo nano /Library/PostgreSQL/X.X/data/postgresql.conf. Type in your OS X administrator password if requested. This will start the Nano text editor inside of the Terminal window. 
        3. Use the down arrow key to scroll to the bottom of the file to the bottom of the document, create a new line and then add the text zero_damaged_pages = on
        4. Click CTRL+O to save the file, then CTRL+X to exit the text editor. 
        5. Restart your computer to enable the changes. 
        Replace X.X with the PostgreSQL version number, this is usually 8.3, 8.4, or 9.0

        IMPORTANT: If you continue to have problems with your hard drive, you should consider replacing it with a new 7200 RPM or faster speed hard drive or SSD.

    • PostgreSQL on a Networked Computer

      Please consult the PostgreSQL Hosted on a Local Network tutorial to learn how to access PostgreSQL on a Networked Computer.  Please be aware that this method is not officially supported, since it is not recommended we cannot guarantee help in the event that a problem occurs. Since PokerTracker 4 was not developed to support remote database access, you will find some features will not work unless the PostgreSQL database is located on the same computer such as Backup, Restore, database tuning and database maintenance. 

    • PostgreSQL on a Separate Drive

      Please see these forum posts for details for creating a database on a separate drive.

      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.

      • PostgreSQL on a Hotswap Drive

        This guide is to help an individual set up their database on a hot swappable hard drive.
        By doing this, you can take your hard drive with you to another computer and use the same database. This gets around the networking issues some have with PostgreSQL and allows you to use your database outside your local network.

        This guide assumes you already have PokerTracker 4 installed on two computers but have separate databases for the two installations. It is written from the point of view of a Microsoft Windows Vista user, but it does tell some of the similarities of XP and Vista. We do not recomend this option for Mac OS X users at this time. 

        Before you start please make a backup of your database(s) and store it somewhere safe.

        • Prepare the Hotswap Drive

          Go to Start -> Computer.
          You will come to this window many times in this part of the guide (we’ll call it the “My Computer window”).
          Right click on your Hotswap drive and choose Rename and give it some name in reference to the fact it’s your database (for instance, we named ours pg_8_3_4).

          Go back to the My Computer window, right click on your Hotswap drive and choose Format.
          Make sure it is set to NTFS and a block size of 4096.
          Choose Quick Format, then click on Start.

          Now, we need to assign this a drive letter that is available on both computers.
          Go to Start -> right click on -> Computer -> Manage.
          Now click on “Disk Management” .
          Right click your hotswap drive and choose Change Drive Letter and Paths.
          Click on Change…, then click on “Assign the following drive letter “ and select “P” from the drop down menu (P is for PostgreSQL in this case). Ignore the warning (yes, you want to continue).

        • Uninstall PostgreSQL

          Go to Start -> Control Panel.
          Select Programs and Features (Add/Remove Programs for XP).
          Wait for this list to populate.
          Right click on PostgreSQL and choose Uninstall.
          Take a note of the version number you see.

          In Vista, click on Start -> All Programs -> Accessories -> right click on -> Command Prompt -> Run as Administrator.
          In XP, just click on Command Prompt in the same Start folder you have.
          At the command prompt type net user /delete postgres.
          If it did not say “The command completed successfully” then you did not do this step correctly (which is important).

          If you get something along the lines of “user access denied”, in Vista this means you are not running the command prompt as an administrator, try running the command prompt in administrator mode again.
          If in XP, you probably are using a limited account to do this. You need to change accounts to an administrator account. Once completed type “exit”.

          You can now go to Start -> Computer -> C: -> Program Files -> right click on -> PostgreSQL -> Delete.
          Before doing this make sure you have all your data backed up and secured.

        • Download and Install PostgreSQL

          Download the version of PostgreSQL you wish to install - you should generally get the lastest version within the same major update as you were already running; for instance if you had 8.3.4 before you could install 8.3.8 now - and get PostgreSQL.


          Once downloaded, extract EVERYTHING to a temp folder, double click on postgresql-x.x (x.x refers to the version of PostgreSQL installed on your computer). If you get an error that says, “please use the main MSI file”, you double clicked on the wrong one. Try the other install file you see.

          When you get to this screen, select browse and install it on your P: drive at P:\PostgreSQL\x.x (click on the new folder icon in the upper right to create a new folder).

          Click on next, then click on it again. It will tell you it generated a random password.
          Ignore this, click on OK and you will be presented w/this screen:

           

          dbpass Password For PokerTracker 4

           

          Use the password dbpass for the two boxes highlighted here. Now click on next until it installs.

        • Restore Databases

          Restore your backed up databases using the same method that you used to backup.

        • PostgreSQL shutdown and startup

          It is time to move to the other computer and get it synched up to this hotswap drive.

          You will and MUST do this EVERY TIME you move the hard drive from one system to the other or you risk losing the database server.

          On your main/first computer:
          Close PokerTracker 4, then go to Start -> right click on -> Computer -> Manage.
          Go to Applications and Services -> Services.
          Find PostgreSQL in the list, right click -> Stop Service. You may now safely remove your hard drive.

          When you move the hard drive from one computer to the other, you always need to stop the PostgreSQL service before unplugging the hard drive.

          You should also make sure the PostgreSQL service is NOT running on the other computer before you attach the hotswap drive.  When you have it plugged into the new computer, start the PostgreSQL service there.  To do this, go to the same place you went to stop PostgreSQL, but select Start Service instead of Stop.

        • Second Computer

          If PostgreSQL was installed on this computer, follow these guides to uninstall it.

          Now plug in your hard drive and the steps found here to change it to drive P: as you did on the first computer.

          Now download postgresql again (the same version you used before).  Run the installer on the new computer, and again set it to install to P:\PostgreSQL\x.x (x.x refers to the version of PostgreSQL you used before).

          Again let it create the password for the service, but when it gets to the screen where you would put in the password “dbpass”, UNCHECK INITIALIZE DATABASE. Complete the install.

          Once installed, go to Start -> Computer -> C: -> Program Files -> Pokertracker 4 -> Data -> Config and edit the Pokertracker.cfg file here (like you did on the first computer).
          Remove all database information from it and save it.

          Now run PokerTracker 4.

          When the configuration screen comes up to configure PostgreSQL, make sure you have in the correct password (“dbpass”). Click on connect.
          Now it wants to create a database.
          Check “This Database Already Exists”, then click on Browse. Select your database, then click on connect.

          You now have the same database available on each computer!

          Note: Remember to shut down the PostgreSQL service when moving your hard drive or it will fail!!!

          When you plug it into the new computer, remember to start the service (click on Start -> Programs -> PostgreSQL -> Start Service).