PDA

View Full Version : HOWTO: set up a linux postgresql server for use with Holdem Manager



Schrapp
01-13-2009, 08:29 AM
Hi,

I recently set up a PostgreSQL server so that I can use one HM database on multiple computers. When i searched the forums I saw that people had already inquired how to do that, but there had been no answers. So I figured, I'd share what I did with the forum. Maybe it will be useful for some people.

I'm assuming you already have a Linux box running on your local network. I used Debian. If you are using a different different distribution, you might have to change certain commands (for example, the packet manager your distribution uses, etc.).

Ok, let's get started.

1) INSTALL AND CONFIGURE POSTGRESQL

First, we want to become root by typing

$ su

Then we install postgresql. On Debian, I just typed:

# apt-get install postgresql

This will install postgresql to your machine. Use the appropriate packet manager for your distribution.

Then we add a new user by issuing the following commands:

# su postgres
$ createuser -P -s -R -d holdem
Enter password for new role: holdem
Enter it again: holdem
$ exit


This will create the user 'holdem' with password 'holdem'. Note that the password will not be displayed as you type it.

Next, we have to allow connections from other computers. Open /etc/postgresql/8.3/main/pg_hba.conf with your preferred editor (depending on your distribution this path may differ. In that case, issue 'updatedb && locate pg_hba.conf' to find the correct location) and append the following line:

host all all 192.168.0.0/24 md5

Of course, you have to change the subnet according to your local network. Make sure you save the changes you made to the file.

Next, open /etc/postgresql/8.3/main/postgresql.conf and find the line that reads as follows:

listen_addresses='localhost'

It might be commented out by default. If that is the case, uncomment it and change it like so:

listen_addresses='*'

Save the file and restart postgresql by issuing

# /etc/init.d/postgresql-8.3 restart

Again, the name of the init-script might differ. Find out the correct name by using auto complete (TAB).

That's it, we're done already. Let's test, if we can access the database. Open Holdem Manager and Select 'Options --> Database Management'. Change the Server Name to your server's IP address and Username/Password to 'holdem'. Then click connect. If you did everything right, you should be taken to the next screen where you can select existing databases (of course, there are none at this point) or create a new database. If you want to do the latter, go ahead. You can stop reading now.
If you want to copy your existing database from your Windows machine to the Linux server, keep on reading.

2) DUMPING AND RESTORING EXISTING DATABASES

Switch to your Windows machine and open the command line prompt. Type

cd "C:\Program Files\PostgreSQL\8.3\bin"

Again, change the path accordingly. Then issue the following command to dump all existing databases:

pg_dumpall.exe --username=postgres > holdem.dump

This may take some time. Now all databases should be dumped into the file holdem.dump.

Now let's restore the databases on the Linux box. Copy the file 'holdem.dump' to your Linux box and then issue

# su postgres
$ psql -f holdem.dump postgres
$ exit

Your databases have now been restored. Use Holdem Manager's Database Manager to connect to the Server again. This time, you should see all your databases. Make your current one the default and click Connect. Voila, you have successfully outsourced your Holdem Manager Database.

You can access the database server from as many machines as you want. You could even configure your firewall to forward port 5432, so that you could access the database away from home. I have not tested that myself though. Depending on your internet connection, you may get performance problems there.

Last but not least, I strongly recommend to put the database on a separate partition or Logical Volume, that is formatted with XFS and mounted with the option 'noatime'. This will greatly improve database performance. I will not explain this in detail, since it is beyond the scope of this tutorial. If you are interested in doing that and don't know how, just ask. I will then post more detailed instructions.

Ok, I hope this was useful to some people.

GL at the tables!
Schrapp

morny
01-13-2009, 11:47 AM
Thanks very much for this, ill put this in the FAQ now

Kley
07-30-2010, 02:30 PM
Hello!

I decided to move my database to my ubuntu server.

I have a problem though, when I type "psql -f holdem.dump postgres" I get the message: "holdem.dump: Access denied"

I should say too that when I dumped my database to the file holdem.dump I had to type my postgres password 4 times.

Everything else works fine, I can create new database and so on on the linux server.

Any suggestion ?

netsrak
07-31-2010, 06:59 AM
Please try sudo psql....

Kley
07-31-2010, 07:06 AM
Thanks, but I forgot to mention, I already tried that...

netsrak
08-01-2010, 05:49 AM
And what permissions does the file have? ls -l ...

Kley
08-01-2010, 07:00 AM
Ah, thank you, that was it!

Linux noob here =)

talvind
01-09-2011, 03:30 PM
Very useful. Thanks a lot.
I just want to ask. Did anyone else do this:

Last but not least, I strongly recommend to put the database on a separate partition or Logical Volume, that is formatted with XFS and mounted with the option 'noatime'. This will greatly improve database performance

MyCaptain
01-10-2011, 06:13 PM
Hi,

Do you know, in case I want to switch back to Windows, the command to dump the database on the Linux server please ?

Thanks

netsrak
01-11-2011, 04:36 AM
You can use pgadmin under linux too. If you start the dump from there you see the command line in the message window. I'm not at my Linux system at the moment so i can't copy it.

MyCaptain
01-11-2011, 10:36 AM
You can use pgadmin under linux too. If you start the dump from there you see the command line in the message window. I'm not at my Linux system at the moment so i can't copy it.

Ok, great. Thank you !

Varange
04-28-2011, 12:04 AM
the dump file seems monstrously big. It's at 1.2GB now and growing.

Is this normal?

Patvs
04-28-2011, 07:33 AM
Do you have a large database?
1 million hands = 10 GB
So it's very easy for a dump process to use a lot of disk space.

Varange
04-29-2011, 02:36 AM
Yes, it ended up being 1.4GB in size.

Anyway, then I ran into the problem of the import not working because the importer kept finding strange end-of-line characters (\n).

Anyway, here is how I managed to finally get the DB across.

1. Export the DB in the normal manner using either pg_dumpall or pg_dump. Patvs's description worked just fine for me.

2. Transfer the file to your linux box. DO NOT USE a CD, FLASH DRIVE, or any such media. That text file seems to go bad if it it transferred to a FAT or ISO filesystem. I used normal SFTP to do the transfer. You will need to have openssh running on your linux box (which is porbably the case anyway), and you will need Filezilla on your Windows box. SFTP uses port 22 when you connect to the linux box with Filezilla.

3. Import the file. If you only exported the holdemmanager database with pg_dump instead of the whole thing with pg_dumpall, you will need to create a new database first. There is an admin tool called PGAdmin3 on your windows machine, just fire that up and connect to the linux box. Create a new database, call it something like "hem_linux" and make postgres the owner of it. At the same time make sure the "postgres" account has a password - I kept mine at "postgrespass".

To do the actual import, issue the command pg_restore. Look at the manual first (man pg_restore). The command, when restoring only one database, is something like


pg_restore --host 192.168.1.150 --port 5432 --username postgres --dbname hem_linux hem_export.dump

where the name of the new database is hem_linux and the dump file is hem_export.dump.

If you did a dumpall, you won't need the dbname, of course.

The importer will ask you for a password for every database it imports. Normally four times for a dumpall.