Wednesday, October 16, 2013

PostgreSQL - Getting started

Here are some steps to getting started with PostgreSQL:

0. Install PostgreSQL:

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Note: you also need to change password of the database superuser (postgres) created when installing postgres:

$ sudo passwd postgres

1. Create a Linux user, myuser:

$ sudo adduser myuser

(This will prompt you to enter password for this user.)

2. Login as database superuser, postgres:

$ su - postgres

3. Connect to the database server:

postgres@trinh-pc:~$ psql template1

4. In the Postgres console, create database user name myuser:

psql (9.1.9)
Type "help" for help.

template1=# CREATE USER myuser WITH PASSWORD 'mypassword';

5. Create database name mydb:

template1=# CREATE DATABASE mydb;

6. Grant all privileges on mydb to myuser:

template1=# GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

7. Test user myuser login:

trinh@trinh-pc:~$ su - myuser

myuser@trinh-pc:~$ psql -d mydb -U myuser
psql (9.1.9)
Type "help" for help.