PostgreSQL on Ubuntu from scratch

By Rayed

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.
    • comments powered by Disqus