Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: postgresql - How to get it running / usable

  1. #1
    Join Date
    Jul 2008
    Location
    Netherlands
    Posts
    1,140

    Default postgresql - How to get it running / usable

    How do I get postgresql configured so I can actually use it?

    I followed the wiki page here:
    Postgresql - openSUSE
    Then I created a user (without a password) and attempted to connect via psql -U username which results inpsql: FATAL: Ident authentication failed for user "username"

    So I searched some more and found:
    /dev/loki: PostgreSQL on openSUSE
    Thought stopping postgresql and adding the md5 authentication methode might help. It did not.

    Search some more and found:
    Install and Configure PostgreSQL in openSUSE 11.0 | SUSE & openSUSE
    So I looked in the /var/lib/pgsql directory, expecting some files.. but the directory is empty.

    What gives? And more importantly what do I need to do to create a database and be able to connect to it? Need it up and running to connect a java application to it.
    Special effects <- KDE 4.2 Showing off
    More KDE -> PINK KDE

  2. #2
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: postgresql - How to get it running / usable

    It's been a while since I set up postgresql but IIRC you need to configure authentication first before you can login to create databases. The config file to edit is somewhere in the postgresql data directory. ident means it's checking your username against the user database on the local machine, you need an identd server running for this method. You can also use passwords, that's what the md5 method is for. Look for some postgresql tutes, the info should be pretty much distro independent.

    PS: remember there is a postgresql package and also a postgresql-server package. Like mysql you need the server running.

  3. #3
    Join Date
    Jul 2008
    Location
    Netherlands
    Posts
    1,140

    Default Re: postgresql - How to get it running / usable

    Yeah it's mentioned in the first link above and do have both installed.
    Seems to be running to, and the files do seem to be in /var/lib/pgsql just chmodded to something I can't see as a normal user.

    Guess I shall continue to poke around.
    Special effects <- KDE 4.2 Showing off
    More KDE -> PINK KDE

  4. #4
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: postgresql - How to get it running / usable

    Obviously those files have to be edited as root since they are not visible to normal users as they contain sensitive config data. However be careful not to change the ownership or permissions of those files because postgresql runs in its own account so it needs to be able to read those files.

  5. #5
    Join Date
    Jul 2008
    Location
    Netherlands
    Posts
    1,140

    Default Re: postgresql - How to get it running / usable

    I'm used to at least seeing the files in my filebrowser, needing the proper rights to edit them is a given.

    Well I finally managed to connect (via phpPgAdmin, hope my own program is soon to follow) although I'm not sure what I've been doing, nor do I care atm as my priority is having it run.. security and everything else are of no importance at all (for now).

    If anyone else is having problems,
    How to install and maintain PostgreSQL with phpPgAdmin | NMS
    By far the most useful page I've read while trying to find a solution.
    Special effects <- KDE 4.2 Showing off
    More KDE -> PINK KDE

  6. #6
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: postgresql - How to get it running / usable

    It's not just security. Sometimes things won't work properly with the wrong permissions. Obviously the cure to not being sure what you are doing is to get informed. It could save you wasted effort.

  7. #7
    Join Date
    Jul 2008
    Location
    Netherlands
    Posts
    1,140

    Default Re: postgresql - How to get it running / usable

    With this having to work pretty much on monday on mysql, postgresql and derby I really don't have the time to be reading up on things. It was ment more as a warning than anything else as I've no clue if the article I linked to is actually a good one to follow, seemed a bit 'too simplistic' to me.
    But I'm in no position to judge.

    Once I've time and the need to set it up properly I'll see about making a wiki page about it as the current article is severely lacking useful information.
    Special effects <- KDE 4.2 Showing off
    More KDE -> PINK KDE

  8. #8
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    1,770

    Default Re: postgresql - How to get it running / usable

    If you want to connect to postgres from Java, you need to make it listen on a TCP port.
    Make sure you edit the postgresql.conf file and enable the entries as follows:

    Code:
    listen_addresses = "*"
    post = 50000
    max_connections = 100
    (You may specify the listen_addresses parameter to the network interface of your choice if you don't want it to listen on all the interfaces. Also, choose the port number as per your choice).

    In the JDBC driver, this port number has to be specified.

    Also, make sure that you have edited the pg_hba.conf (go towards the end of the file) for allowing connection to the server from other machines.
    An entry like the following will allow connections from other machines:
    Code:
    host    all         all         127.0.0.1/32          password
    Please note that the default installation DOES NOT listen on any TCP ports.
    openSUSE 12.3 (x86_64) with Kernel 3.7.10-1.16-desktop and KDE 4.11.2 on MacBook Pro
    Latest MS Windows version used: Win95

  9. #9
    Join Date
    Apr 2009
    Location
    Annapolis, MD
    Posts
    204

    Default Re: postgresql - How to get it running / usable

    Are you running postgres locally or remotely?

    I got this book "Beginning Databases with PostgreSQL" from Appress that has helped me a great deal. They provide examples of all the GUI development tools (PgAdminIII,etc) as well as entire chapters on connecting with Java, C, PHP, C#, and PERL.

    Recently have used Embedded SQL with C for executing commands to the database.

    I find myself using the command-line tool psql both for performance reasons and to become familiar with the postgres commands.

    Did you install from the SUSE distro or from RPMs? It makes a difference because SUSE tends to distribute the installation into separate places.

    For example, the databases are stored at /var/lib/pgsql/data. The postgres executable is stored in /usr/bin.

    Not sure if this helps...what are you having trouble with?

  10. #10
    Join Date
    Apr 2009
    Location
    Annapolis, MD
    Posts
    204

    Default Re: postgresql - How to get it running / usable

    The first two steps I took after I installed postgres was to ensure the postgres account was the owner of the data directory and initializing the database with initdb.

    The book guides you through all this. The authors recommend you assume the identity of the postgres account in order to run the commands.

    su - postgres

    If you already know this, slap me and call me silly.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •