vsbabu.org

PostgreSQL in Mandrake 9.1

PostgreSQL is my choice for a SQL database while using Linux. If you are in a big hurry to get PostgreSQL going in your shiny new Mandrake Linux 9.1 machine, here is how to do it quickly.

These notes don’t address security or backup and restore. I explain how to start the database server, create a user and database, create a table in the database, add some data and query it back.

Things to note: vsbabu is my login in this machine - toshiba. Wherever you see vsbabu, you need to use your login.

 1  [vsbabu@toshiba vsbabu]$ su - root
 2  password: <enter root password here>

 3  [root@toshiba root]# /etc/rc.d/init.d/postgresql restart
 4                                                                  [  OK  ]
 5  Starting postgresql service:                                    [  OK  ]
 6  [root@toshiba root]# su - postgres
 7  -bash-2.05b$ createuser vsbabu

 8  Shall the new user be allowed to create databases? (y/n) y
 9  Shall the new user be allowed to create more new users? (y/n) n
10  CREATE USER
11  -bash-2.05b$ createdb vsbabu
12  CREATE DATABASE

13  -bash-2.05b$ exit
14  [root@toshiba root]# exit
15  [vsbabu@toshiba vsbabu]$ psql
16  Welcome to psql 7.3.2, the PostgreSQL interactive terminal.
17
18  Type:  \copyright for distribution terms

19         \h for help with SQL commands
20         \? for help on internal slash commands
21         \g or terminate with semicolon to execute query
22         \q to quit
23
24  vsbabu=> create table states (state_code varchar(2) primary key, state_name varchar(30) not null);

25  CREATE TABLE
26  vsbabu=> insert into states(state_code, state_name) values ('VA','Virgina');
27  INSERT 16981 1
28  vsbabu=> insert into states(state_code, state_name) values ('MD','Maryland');
29  INSERT 16982 1

30  vsbabu=> select * from states;
31   state_code | state_name
32  ------------+----------
33   VA         | Virgina
34   MD         | Maryland

35  (2 rows)
36
37  vsbabu=> \q
38  [vsbabu@toshiba vsbabu]$

Explanation

• lines #3-#5: Make sure the database service (called Postmaster, in PostgreSQL) is started. You can permanently enable the service by setting it to start automatically using Mandrake Control Center.
• lines #6-#13: My login in this machine is vsbabu. So, I create a user called vsbabu first and then a database called vsbabu. Default authentication is set to use unix authentication. In short, I’m creating a private database for my login.
• line #15: I’m back to my login, vsbabu. psql is an interactive SQL client you can use to execute queries against the database. Running psql without any arguments will automatically connect it to the default database as default user - both being vsbabu in this case.
• lines #24-#35: I create a table, insert 2 rows, select the data from the table and exit from psql.

Now, you are all set to explore PostgreSQL documentation, learn some SQL and try out your database.

1. Hi
   thanks a million for your instruction. I tryed to run it sincs 20 houres without a result. After i found your site i takes me 10 minutes. Easy to follow what to do. Well done.

   stefan

   Posted by: Stefan on May 21, 2003 08:56 AM

2. Hello, and thanks for this quick guide, I'm a newbie in PostgreSQL and it helped me to know how to start.
   

   Posted by: Fabrizzio on June 8, 2003 11:10 AM

3. thx alot this note save me alot, thx again

   Posted by: fany on June 9, 2003 01:35 AM

4. thanks alot

   Posted by: ruiming zhang on June 10, 2003 04:50 AM

5. Why is it the first few steps are always the hardest..

   Thanks!

   Posted by: gustchaser on July 8, 2003 02:42 AM

6. You've saved me hours(days, weeks?) of teeth gnashing. The instructions on www.postgresql.org detail none of this.. The biggest obstacle though is that you won't have a working postgresql implementation out of the box, and doing an urpmi postgresql won't give it to you either. You have to ask urpmi for postgresql-server. (doh!) Then everything is jake:)

   Posted by: aiiee on August 18, 2003 12:33 PM

7. RE (lines #3-#5: Make sure the database service (called Postmaster, in PostgreSQL) is started. You can permanently enable the service by setting it to start automatically using Mandrake Control Center)

   How exactly is this done????

   Posted by: Tim on October 28, 2003 11:43 PM

8. Sorry guys ive worked that out now, and if anyone else is stuck delete whats already in /usr/local/pgsql/data (only if you know no ones using it and stuff!) then make it again with

   mkdir /usr/local/pgsql/data
   then
   chown postgres /usr/local/pgsql/data

   then
   su - postgres

   then
   initdb -D /usr/local/pgsql/data

   and your away, wasnt working for me until i cleared out the contents of /usr/local/pgsql/data after install

   Posted by: Tim on October 29, 2003 12:11 AM

9. Ok I have tried that and it works on some computer. But there are those once when you run ./postgresql restart, is says failed. What then, what is wrong with that database ?

   Posted by: Joshua on November 14, 2003 06:31 AM

10. Hi,

    I saw your site has providing with the useful info. & like to submited my problem which I found in my mandrake9.2 machine when I started psql program, the error show below:
    "psql: FATAL: database "john" does not exit inthe system catalog." , do you have any idea ? please help...

    Thank you.

    Best Regard
    John

    Posted by: John on January 26, 2004 10:06 PM

11. Hi..i tried what is said above but this is the error i get..is there something i am missing. Please let me know ..thanks in adavance...

    [kbis@localhost kbis]$ psql
    psql: FATAL: Database "kbis" does not exist in the system catalog.
    

    Posted by: Vishnu on March 22, 2004 08:13 PM

12. Hi, can anyone tell me how to set up a pgsql server with this postgres that comes with mandrake 9.1

    Posted by: Rod on May 8, 2004 04:17 AM

13. Thanks a lot buddy.........

    Posted by: Rijo Varghese on June 9, 2004 05:23 PM

14. This worked a dream on Mandrake 10. I used the services dialog to startup postgresql. Easy as pie, thanks a BUNCH!

    Posted by: Joshua on July 26, 2004 05:26 AM

15. I am trying to set up plpgsql on Mandrake 10.1 Community, I tried to install postgresql-devel-7.4.5-1mdk.i586.rpm:

    [root@localhost tmp]# urpmi postgresql-devel-7.4.5-1mdk.i586.rpm
    Some package requested cannot be installed:
    postgresql-devel-7.4.5-1mdk.i586 (due to unsatisfied devel(libcrypto))
    Continue? (Y/n)

    Any suggestions? Thanks! :)

    Posted by: Gene on October 26, 2004 09:15 PM