Help! Debian postgresql nightmare #2

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

Help! Debian postgresql nightmare #2

Postby fyl2u » Tue Sep 02, 2008 9:54 am

Hi all,

I'm stuck. I've been banging my head against a wall for a few days now and I just can't make it work at all any more.

I'm trying to run my PT3 database from my Debian server on my LAN.

I've been all the way through my original linux pgsql thread ( http://www.pokertracker.com/forums/viewtopic.php?f=17&t=7444&st=0&sk=t&sd=a ) 4 times, trying to recreate the way I got it working in the first place, but I just can't get it working.

After upgrading to PT3 B16, I was getting an error during Housekeeping that indicated that my Postgres version might not be the right one, so I checked it using dpkg -l, and lo and behold it said I was only running v.7.4.7, so immediately I set to trying to upgrade at least to version 8 so that PT3 would work.

I checked around and found that there was a testing version of 8.1 and an unstable release of 8.3 available, so I altered my sources.list to allow me to use the other servers and tried 8.3 first, but it failed. I altered my sources.list again and tried 8.1 but this failed as well. I decided to try installing the required packages individually and this gave me loads of errors, so I tried to downgrade back to the stable release, 7.4.7. but still I couldn't get PT3 to connect.

I got an email from Martin Pitt this morning (he apologised for the delay - my email had somehow been overlooked) regarding 8.3 and gave me the details for how to install it from the backport repository. Using his method, 8.3 seemed to install perfectly.

And so in total, I've upgraded, downgraded, installed, removed, installed, upgraded, updated, installed, removed, removed, and installed PostgreSQL.

I came to the conclusion that it's possible that the database just isn't initialised, so I've been trying to work out how to check it.

I found this command on a site somewhere.... and this is what it returns...

postgres@gate:/etc/init.d$ ps -ef | grep postgres

Code: Select all

root     25686 11666  0 13:34 ?        00:00:00 sshd: postgres [priv]
root     25689 25686  0 13:34 ?        00:00:00 sshd: postgres [priv]
postgres 25691 25686  0 13:34 ?        00:00:00 sshd: postgres@pts/2
postgres 25692 25691  0 13:34 pts/2    00:00:00 -bash
postgres 26235     1  0 14:01 ?        00:00:00 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf
postgres 26237 26235  0 14:01 ?        00:00:00 postgres: writer process                                                                                       
postgres 26238 26235  0 14:01 ?        00:00:00 postgres: wal writer process                                                                                   
postgres 26239 26235  0 14:01 ?        00:00:00 postgres: autovacuum launcher process                                                                           
postgres 26240 26235  0 14:01 ?        00:00:00 postgres: stats collector process                                                                               
postgres 26590 25692  0 14:38 pts/2    00:00:00 ps -ef
postgres 26591 25692  0 14:38 pts/2    00:00:00 grep postgres


...but I have no idea what any of it means :D

Right... now the thing is... when I type "dpkg -l" it says I've got BOTH 8.1 AND 8.3 installed. This is entirely possible due to the number of times I installed, uninstalled etc. as detailed above.

When I type "apt-get remove postgresql-8.1" it says "package postgresql-8.1 is not installed, so not removed".


When I type "createdb /usr/var/pgsql1" it says "createdb: could not connect to database postgres: could not connect to server: No such file or directory. Is the server running locally and accepting connections on Unix domain socket '/var/run/postgresql/.s.PGSQL.5432'?"

I've checked my pg_hba.conf against the working one from the other thread and I'm pretty sure that it is identical.

I just don't know what to do next. I've pushed and pushed and pushed myself to learn what's going on rather than just ask for help, but I just can't figure it out.

Please, can anyone help me?

I've tied myself in such a knot, and all I want is to be able to play poker with PT3 again.

I don't even care if I have to start a completely new database, just filled up with my HHs! That'd be fine, as long as PT3 is working again. (Though if anyone knows whether my old DB would still be kicking around my linux machine and knows how to find it and restore it, that would be fantastic!)

A thousand pleases... I am at your mercy!

Thanks in advance,

Phil, now with a flat forehead from banging it against the desk too many times.
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby kraada » Tue Sep 02, 2008 10:39 am

Phil,

It clearly looks like you do have 8.3 installed and there's nothing problematic about having 8.1 installed alongside it so let's not worry about that for now.

I personally run Debian also so I can attest that the 8.3 package does work, though I did a bunch of tweaking after the fact, and I never got the automated init scripts working properly, mostly because I'm too lazy to figure out what's wrong with it; I just start postgresql manually.

So, to start PostgreSQL manually, first kill the 8.3 postgres process running (pid 26235) then decide where you want the database to be located and create a directory that will only contain your database. Make sure that directory is owned by user postgres (chown postgres.postgres <directory>).

Then as user postgres (you can su into him from root), run /usr/lib/postgresql/8.3/bin/initdb -D <data directory>. If you have a spare hard drive to put the transaction log on that is distinct from the drive the data directory is on add "-X <xlog directory>" and this will speed things up considerably for you.

Once initdb finishes, edit the postgresql.conf and pg_hba.conf there to match the settings you desire and then run /usr/lib/postgresql/8.3/bin/postmaster -i -D <data directory> as user postgres in order to actually start PostgreSQL.

Like I said, I don't know off the top of my head how to get the proper init scripts working so when I have to reboot I just su to postgres and rerun that last command and everything works quite well for me.

If you get stuck just say where and I'll be glad to help you get through it :)
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Help! Debian postgresql nightmare #2

Postby fyl2u » Wed Sep 03, 2008 6:49 am

kraada wrote:Phil,

It clearly looks like you do have 8.3 installed and there's nothing problematic about having 8.1 installed alongside it so let's not worry about that for now.

I personally run Debian also so I can attest that the 8.3 package does work, though I did a bunch of tweaking after the fact, and I never got the automated init scripts working properly, mostly because I'm too lazy to figure out what's wrong with it; I just start postgresql manually.

So, to start PostgreSQL manually, first kill the 8.3 postgres process running (pid 26235) then decide where you want the database to be located and create a directory that will only contain your database. Make sure that directory is owned by user postgres (chown postgres.postgres <directory>).

Then as user postgres (you can su into him from root), run /usr/lib/postgresql/8.3/bin/initdb -D <data directory>. If you have a spare hard drive to put the transaction log on that is distinct from the drive the data directory is on add "-X <xlog directory>" and this will speed things up considerably for you.

Once initdb finishes, edit the postgresql.conf and pg_hba.conf there to match the settings you desire and then run /usr/lib/postgresql/8.3/bin/postmaster -i -D <data directory> as user postgres in order to actually start PostgreSQL.

Like I said, I don't know off the top of my head how to get the proper init scripts working so when I have to reboot I just su to postgres and rerun that last command and everything works quite well for me.

If you get stuck just say where and I'll be glad to help you get through it :)


Hi!

Thanks for your help, I really appreciate it!

I've followed your instructions, then tried to "Browse databases" on the server's i.p. address, and posgres is saying...

FATAL: no pg_hba.conf entry for host "19.168.70.159", user "postgres", database "postgres", SSL off.
FATAL: no pg_hba.conf entry for host "19.168.70.159", user "postgres", database "template1", SSL off.

and PT3 is saying...

Unable to login to database (template1). Reason: FATAL: no pg_hba.conf entry for host "19.168.70.159", user "postgres", database "template1", SSL off.
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby fyl2u » Wed Sep 03, 2008 7:43 am

I had "/pt3-postgresqldb/" instead of "pt3-postgresqldb" in the postmaster line, so I've stopped it and tried again.

I still get the same error - now using the "connect" button instead of the "browse databases" link in PT3 so that it says...

FATAL: no pg_hba.conf entry for host "19.168.70.159", user "postgres", database "pt3-postgresqldb", SSL off
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby fyl2u » Wed Sep 03, 2008 8:03 am

my pg_hba.conf has the line...

host all postgres 192.168.70.159 255.255.255.0 trust


Ooops, my mistake, I didn't know there was a different pg_hba.conf for each database!

Right, I've altered the one inside the database directory now... /pt3-postgresqldb/pg_hba.conf
...as oppose to the /etc/postgresql/8.3/main/pg_hba.conf

Now it says database pt3-postgresqldb does not exist.

Hang on.
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby fyl2u » Wed Sep 03, 2008 8:10 am

Ah, never mind, I'm in!

I had the "database already exists" box ticked.

I thought we'd already created it from the debian command line but I guess it's a different thing.

It seems to be working now! Thanks so much for your help!

I couldn't've done this without you!
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby fyl2u » Wed Sep 03, 2008 8:59 am

Oh.

When I say "in"... I mean.. er...

[ img ]
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby kraada » Wed Sep 03, 2008 9:05 am

Glad I could help get you this far :)

Could you please enable logging in PT3 (Configure --> Options --> Logging Enabled) and restart PT3 and then get this error message one more time then paste the entire error from the C:\Program Files\PokerTracker 3\PokerTracker.log file? Unfortunately my eyes aren't quite good enough to make out all the details and the full error message will be useful in detailing exactly what is going on.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Re: Help! Debian postgresql nightmare #2

Postby fyl2u » Wed Sep 03, 2008 9:41 am

Ah, I restarted PT and it's working! Yay!

It still thinks I've played some tourneys in 1969 though :lol:

Ah, not quite working... the "starting hands" page on the "hands" tab says "Unable to load query. Reason: Unable to load Query. Reason: Unknown token encountered: cnt_p_raise_first_in"
fyl2u
 
Posts: 187
Joined: Tue Mar 11, 2008 5:15 pm

Re: Help! Debian postgresql nightmare #2

Postby kraada » Wed Sep 03, 2008 11:15 am

Database --> Housekeeping, create a cache on your database and that should fix that issue.

Also, are you running Beta 17 yet? Beta 17 also fixes a few issues, particularly one with that stat.

ETA: though you'll need to recreate the cache after upgrading to beta 17 for that particular issue, so it's fastest to upgrade first, then run housekeeping.
kraada
Moderator
 
Posts: 54430
Joined: Wed Mar 05, 2008 2:32 am
Location: NY

Next

Return to PostgreSQL [Read Only]

Who is online

Users browsing this forum: No registered users and 0 guests