Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
HOWTO : PostgreSQL (Mini)
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks
View previous topic :: View next topic  
Author Message
plastix
n00b
n00b


Joined: 27 Oct 2003
Posts: 11
Location: Boise, ID

PostPosted: Thu Jun 23, 2005 11:38 pm    Post subject: HOWTO : PostgreSQL (Mini) Reply with quote

Ok, I tried scouring the forums and couldn't find anything dealing with PostgreSQL and how to set it up, so I decided to write this small how-to. Please bear with me, as I am not generally a how-to writer. This post relates to version 8 of PostgreSQL

To begin with, emerge PostgreSQL:
Code:

[root] /home/plastix > emerge postgresql


When it has finished, you will get a message about needing to configure it :
Code:

[root] /home/plastix > ebuild /var/db/pkg/dev-db/postgresql-X/postgresql-X.ebuild config

Where X is the version number. These two parts should be relatively simple.

After the config portion, the database should be ready to be run:
Code:

[root] /home/plastix > /etc/init.d/postgresql start
[root] /home/plastix > rc-update add postgresql default


Hey, now you have PostgreSQL up and running, how do you use it? What you need to do is change to the PostgreSQL user, and add users into the database using createuser (The opposite function is dropuser if you need to remove access for a particular user). Start from root (you should still be there after the previous steps):
Code:

[root] /home/plastix > su postgres
postgres /home/plastix $ cd
postgres ~ $ createuser
Enter name of user to add: plastix
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER
postgres ~ $


Now we have a user that is allowed to add more users and databases. Continue by exiting the PostgreSQL user and logging into the account for the user you just created, and creating a new database using the createdb tool (Likewise there is also a dropdb tool to remove a database):
Code:

[plastix][~] > createdb
CREATE DATABASE
[plastix][~] > psql
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

plastix=#           


You might want to add a password for your users, in order to do this we need to throw down some sql:
Code:

[plastix][~] > psql
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

plastix=# alter user postgres with password '<some password>';
ALTER USER
plastix=# alter user plastix with password '<some other password>';
ALTER USER
plastix=# select * from pg_shadow;              << This will only be viewable by postgres operators
 usename  | usesysid | usecreatedb | usesuper | usecatupd |               passwd                | valuntil | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
 plastix  |      100 | t           | t        | t         | md5XXX                              |          |
 postgres |        1 | t           | t        | t         | md5XXX                              |          |
(2 rows)

plastix=#           

There is also a way to set s timeout for the password for each of the users, please see the PostgreSQL documentation for more information on that. As a side note, the pg_shadow table is the main user table in the database, only the operators should have access to it as it contains the password information.

If you would like to tighten down the database, you should modify the pg_hba.conf (Host Based Authentication) file (as postgres)
!!!Warning!!!
Make sure you add passwords to the users if you want to add password authentication, while it won't break the database, it is a pain to have to go and change the values and restart the database each time:
Code:

[root][/home/plastix] > su postgres
postgres /home/plastix $ cd
postgres ~ $ cd data/
postgres ~/data $ vi pg_hba.conf
postgres ~/data $ exit

I changed all the trust options under method to md5. You can also change it to a few other values, please refer to this page to learn more about different authentication methods.
Code:

[root][/home/plastix] > /etc/init.d/postgres restart


If you would like to access PostreSQL with PHP add PostgreSQL to your php use flag (as root) and rebuild php:
Code:

[root] /home/plastix > echo "dev-php/php postgres" >> /etc/portage/package.use
[root] /home/plastix > emerge -av php


Setting up php is not in the scope of this howto, so I will leave it up to the reader to look up information on how to set it up.

*** From here down will be examples for SQL and PHP (Possibly Python and Perl later) ***

*** This is a simple SQL example
This will take you through creating a table, listing it, inserting information into it, deleting the information and deleting the table from the database.

Code:

[plastix][~] > psql
Welcome to psql 8.0.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

plastix=# create table hello
plastix-# (
plastix(#   ID integer,
plastix(#   MSG varchar(255)
plastix(# );
CREATE TABLE
plastix=# \dt
        List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+---------
 public | hello | table | plastix
(1 row)

plastix=# insert into hello (id,msg)  values(1,'Hello World');
INSERT 17236 1
plastix=# select * from hello;
 id |     msg
----+-------------
  1 | Hello World
(1 row)

plastix=# delete from hello;
DELETE 1
plastix=# \dt
        List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+---------
 public | hello | table | plastix
(1 row)

plastix=# drop table hello;
DROP TABLE
plastix=# \dt
plastix=# \q
[plastix][~] > 


*** A Simple PHP example :
This example will pop up a simple login form and when you press the button will do a sql query of the pervious hello table (You will probably have to re enter it if you deleted the information and dropped the table)

File: login.html
Code:

<form action="test.php" method="post">
        <p>Database: <input type="text" name="db" /></p>
        <p>Username: <input type="text" name="user" /></p>
        <p>Password: <input type="password" name="pass" /></p>
        <p><input type="submit" /></p>
</form>


File: test.php
!!!Warning!!! I am not the best PHP developer, and this code is not very safe, use it as an example only as anybody who has access to your webserver can echo the username and password for your database.
Code:

<html>
<body>
<?php
$dbdb = $_POST['db'];
$dbpass = $_POST['pass'];
$dbuser = $_POST['user'];
$db = pg_connect("dbname=$dbdb user=$dbuser password=$dbpass");
$query = "SELECT * FROM hello";
$result = pg_exec($db, $query);

if (!$result)
{
   printf ("ERROR");
   exit;
}

$row=0;
$numrows = pg_numrows($result);

do
{
   $myrow = pg_fetch_row ($result,$row);
   printf ("ID : %s Msg: %s<br>\n", $myrow[0], $myrow[1]);
   $row++;
} while ($row < $numrows);

pg_close();
?>
</body>
</html>


*** This is a python example:
In order to use this, you must emerge pypgsql:
Code:

[root] /home/plastix > emerge pypgsql


File: login.html
Code:

<form action="cgi-bin/test.py" method="post">
        <p>Database: <input type="text" name="db" /></p>
        <p>Username: <input type="text" name="user" /></p>
        <p>Password: <input type="password" name="pass" /></p>
        <p><input type="submit" /></p>
</form>


File: test.py
Code:

#!/usr/bin/env python

from pyPgSQL import PgSQL
import cgi

class pytest:
        def __init__(self,db,un,ps):
                self.db = PgSQL.connect(database=db,user=un,password=ps)
                self.cur = self.db.cursor()
                self.cur.execute("select * from hello")
                self.res = self.cur.fetchone()

        def __str__(self):
                self.output = "<html><body>\n"
                while self.res:
                        self.output = self.output + "ID: %d Msg: %s<br>\n" % (self.res[0], self.res[1])
                        self.res=self.cur.fetchone()
                self.output = self.output + "</body></html>\n"
                return self.output

form = cgi.FieldStorage()

p=pytest(form['db'].value,form['user'].value,form['pass'].value)

print "Content-type: text/html\n"
print p



Hope that this helps anybody trying to install PostgreSQL, if you need more information, please feel free to let me know.


Last edited by plastix on Fri Jun 24, 2005 8:52 pm; edited 1 time in total
Back to top
View user's profile Send private message
rusty
Apprentice
Apprentice


Joined: 07 May 2004
Posts: 181
Location: St.Louis

PostPosted: Fri Jun 24, 2005 12:20 am    Post subject: Reply with quote

cool
Back to top
View user's profile Send private message
robbyjo
Guru
Guru


Joined: 06 Apr 2003
Posts: 462

PostPosted: Fri Jun 24, 2005 2:00 am    Post subject: Reply with quote

Can you extend this to include php-pgsql? Thanks :)
Back to top
View user's profile Send private message
taiger
Tux's lil' helper
Tux's lil' helper


Joined: 25 Nov 2003
Posts: 112

PostPosted: Thu Jul 14, 2005 7:18 am    Post subject: Reply with quote

I need to include a module in postgres.
This module is "db_link" in the "contrib" directory.

Is possible include in postgres this module during emerging...

... I must modify the ebuild script?

thanks in advance...
Back to top
View user's profile Send private message
plastix
n00b
n00b


Joined: 27 Oct 2003
Posts: 11
Location: Boise, ID

PostPosted: Thu Aug 11, 2005 11:59 pm    Post subject: Reply with quote

Sorry for the late reply. The db_link file you are asking about couldn't possibly be called dblink.so? If so, I believe it is installed by defualt under /usr/lib/postgresql/dblink.so.
Back to top
View user's profile Send private message
rrok
n00b
n00b


Joined: 30 Aug 2005
Posts: 48
Location: Slovenia

PostPosted: Wed Sep 07, 2005 5:49 pm    Post subject: Reply with quote

Hello!

Does anyone know how to fix that error?
Quote:
postmaster cannot access the server configuration file "/var/lib/postgresql/dat$
Back to top
View user's profile Send private message
MrSums
Apprentice
Apprentice


Joined: 25 May 2003
Posts: 283
Location: UK

PostPosted: Sun Sep 11, 2005 4:12 pm    Post subject: Reply with quote

I have had postgresql 7.4.6 working fine with sql-ledger and with emerge nagging me to upgrade postgresql, I bit the bullet, did some checking, backed up my databases and upgraded to 8.0.3.

On the way. emerge also I think emerged libpq, but I am afraid I wasn't paying attention - kids screaming to play etc. Emerge postgresql required I remove the directory /var/lib/postgresql/ which I duly did and the emerge seeemed to go fine (unattended due to kids etc). When I got back, I had a few problems until I found out about recreating the "template1" database, then restored my backups, which also seemed to go ok.

Now I cannot connect to sql-ledger, as I get an apache error:

Internal Server Error: The server encountered an internal error or misconfiguration and was unable to complete your request.

On reviewing the apache access_log file I see:
Quote:

127.0.0.1 - - [10/Sep/2005:15:37:04 +0100] "POST /sql-ledger/login.pl HTTP/1.1" 500 619 http://barnum/sql-ledger/login.pl" "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.10) Gecko/20050716 Firefox/1.0.6"
127.0.0.1 - - [10/Sep/2005:15:37:04 +0100] "GET /favicon.ico HTTP/1.1" 404 294 "-" "Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.10) Gecko/20050716 Firefox/1.0.6"

and the error_log:
Quote:

[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] install_driver(Pg)
failed: Can't load
'/usr/lib/perl5/site_perl/5.8.6/i686-linux/auto/DBD/Pg/Pg.so' for module DBD::Pg: libpq.so.3: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.6/i686-linux/DynaLoader.pm line 230., referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] at (eval 7) line 3, referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] Compilation failed in require at (eval 7) line 3., referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] Perhaps a required shared library or dll isn't installed where expected, referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] at SL/User.pm line 117, referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] Compilation failed in require at /usr/local/sql-ledger/login.pl line 91., referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] Premature end of script headers: login.pl, referer: http://barnum/sql-ledger/login.pl
[Sat Sep 10 15:37:04 2005] [error] [client 127.0.0.1] File does not exist: /var/www/localhost/htdocs/favicon.ico


The main problem seems to be with libpq.so.3 and searching through the system, I only have libpq.so.4 so has the emerge somehow borked (with no doubt some help from me) the original config.

I am pretty sure I originally installed DPD::pg through CPAN, should I reinstall it? do I have to unemerge something else first?

Thanks

MrSums
_________________
If you dont ask, you wont get
Back to top
View user's profile Send private message
agenteo
n00b
n00b


Joined: 16 Sep 2005
Posts: 40
Location: Castiglione delle Stiviere (MN)

PostPosted: Wed Sep 21, 2005 2:02 pm    Post subject: Reply with quote

Do you know where is the postgres log file? Do I need to set its location in the config file?
thanks
Back to top
View user's profile Send private message
agenteo
n00b
n00b


Joined: 16 Sep 2005
Posts: 40
Location: Castiglione delle Stiviere (MN)

PostPosted: Wed Sep 21, 2005 3:50 pm    Post subject: Reply with quote

agenteo wrote:
Do you know where is the postgres log file? Do I need to set its location in the config file?
thanks

locate helps :-)
I've found:
/usr/portage/dev-db/postgresql/files/postgresql.conf
Back to top
View user's profile Send private message
MrSums
Apprentice
Apprentice


Joined: 25 May 2003
Posts: 283
Location: UK

PostPosted: Wed Sep 21, 2005 5:52 pm    Post subject: Reply with quote

Solved my problem - it is an u/g issue with libpq.so.3, where new version is libpq.so.4. Got it all working by simlinking, then just reinstalled DPD::pg and all works just dandy.

Robert
_________________
If you dont ask, you wont get
Back to top
View user's profile Send private message
Seppi
n00b
n00b


Joined: 31 Oct 2004
Posts: 11

PostPosted: Wed Sep 28, 2005 12:40 pm    Post subject: Re: HOWTO : PostgreSQL (Mini) Reply with quote

I have got troubles connecting to my postgres database from other machines although I added following line to pg_hba.conf:

Code:
host    all         all         192.168.0.4/32        trust 


There seems to be some problem because I get following error message when I start postgres:

Code:
su: Authentication service cannot retrieve authentication info.



But Im still able to connect from the local machine
Back to top
View user's profile Send private message
d0wn_under
Guru
Guru


Joined: 13 Mar 2003
Posts: 300
Location: Sheffield, England

PostPosted: Thu Dec 29, 2005 6:46 pm    Post subject: Reply with quote

It has been a while since your post so you've probably solved this by now but the solution seems to be to add a missing line to your /etc/shaddow file for the postgres user. That will remove the authentication error. Not sure about getting other machines connecting though.
_________________
This sig left intentionally blank.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Documentation, Tips & Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum