PostgreSQL on Ubuntu from scratch

My notes on installing and configuring PostgreSQL on Ubuntu Linux.

PostgreSQL is used by many large web sites and services, including Skype, Reddit, Instagram, “.org” registerer, check longer list here Prominent Users

Best of all it isn’t owned by anyone, not Oracle nor anyone else!

• Installing PostgreSQL:

   $ sudo aptitude install postgresql
  

• If you want to allow access from outside, first you need to listen to all IP instead of “localhost” only:

    $ sudo vi /etc/postgresql/8.4/main/postgresql.conf 
    :
    listen_addresses = '*'
    :
  

  and allow connecting:

    $ sudo vi /etc/postgresql/8.4/main/pg_hba.conf 
    :
    host    all         all         10.0.0.0/24          md5
    :
  

• You can start, stop, restart using the commands:

    $ sudo /etc/init.d/postgresql-8.4 stop
  

• To connect as Postgres superuser, type:

    $ sudo -u postgres psql
    psql (8.4.11)
    Type "help" for help.
    postgres=#
  

  Notice the “#” which means superuser, superuser can do anything!

• From “psql” create a new database using:

    postgres=# CREATE DATABASE rayed;
    CREATE DATABASE
    postgres=# \c rayed
    psql (8.4.11)
    You are now connected to database "rayed".
    rayed=# 
  

  “\c” is used to switch database, notice the prompt changed to “rayed” the name of the database.

• Let’s create a new table:

    rayed=# CREATE TABLE users (
    "id" SERIAL,
    "email" TEXT,
    "firstname" TEXT,
    "lastname" TEXT,
    "password" TEXT,
    PRIMARY KEY ("id")
    );
  

  “SERIAL” is a short cut, it will create a database “sequence”, read about it is important, and if you are lazy it is an AUTO_INCREMENT field in MySQL. Let’s see what we have created so far:

    rayed=# \d
                    List of relations
    Schema |       Name        |   Type   |  Owner   
    --------+-------------------+----------+----------
    public | users             | table    | postgres
    public | users_user_id_seq | sequence | postgres
    (2 rows)
  

  Notice the auto created sequence, cool huh! Try also: “\d+”, “\d users”, “\d+ users”.

  To see a list of databse on the system try “\l”:

    rayed=# \l
                                    List of databases
    Name    |  Owner   | Encoding | Collation  |   Ctype    |   Access privileges   
    -----------+----------+----------+------------+------------+-----------------------
    postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
    rayed     | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
    template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres
                                                            : postgres=CTc/postgres
    template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres
                                                            : postgres=CTc/postgres
    (4 rows)
  

  You will notice we have 4 databases:

  • postgres: This is where the schema for everything is stored, I think! NEED CHECKING
  • rayed: the new database we created.
  • template1: This an empty template database, whenever you create a new database it will be created from this one, you can even change this database to include any custom objects to be copied to new databases.
  • template0: Another template DB, this one shouldn’t be touched, it will be used to create new DB for restoring backups.

• We will create user named “rayed”, and allow it to login:

    rayed=# CREATE ROLE rayed LOGIN;
    rayed=# \du
                List of roles
    Role name | Attributes  | Member of 
    -----------+-------------+-----------
    postgres  | Superuser   | {}
            : Create role   
            : Create DB     
    rayed     |             | {}
  

  Now I can login to “psql” directly from my account.

• User with a password:

    rayed=# ALTER ROLE rayed WITH PASSWORD 'my_pass_word';
  

  Now if you try to login from bash:

    $ psql
  

  Didn’t ask for a password!!! This because we set “pg_hba.conf” to use “ident” service and not passwords for local connections, to fix this we add:

    $ sudo vi /etc/postgresql/8.4/main/pg_hba.conf
    :
    local   all         postgres                          ident
    local   all         all                               md5
    :
    $ sudo /etc/init.d/postgresql-8.4 restart
    $ psql
    Password for user rayed:
  

  The changes we made to “pg_hba.conf” as follow, “postgres” is authenticated using “ident” service, other users should use “md5” password, order of the lines are important!

• Let’s try to insert data to our “users” table:

    rayed=> INSERT INTO users (email) VALUES('rayed@rayed.com');
    ERROR:  permission denied for relation users
  

  Permission is denied because we created the database “rayed” and the table “users” from “postgres” account, and by default the creator will be the owner, we need to change the owner, from superuser (i.e. postgres) type:

    rayed=# ALTER TABLE users OWNER TO rayed;
    ALTER TABLE
    rayed=# ALTER SEQUENCE users_user_id_seq OWNER TO rayed;
    ALTER SEQUENCE
  

  Try inserting again, voila!

• Connecting from PHP now, try the example from http://www.php.net/manual/en/pgsql.examples-basic.php, make sure you install PHP PostgreSQL module:

    $ sudo aptitude install php5-pgsql
  

  Make sure you change the database name, username, and password.

• Linux
• Postgres
• Ubuntu