This is a log of information I consult when working with Postgres

Packages

Before going through the document, it is necessary to understand what the requisite packages do.

Following definitions are from https://packages.debian.org.

  1. postgresql - This metapackage always depends on the currently supported PostgreSQL database server version. This is the actual database server.

  2. postgresql-client - This metapackage always depends on the currently supported database client package for PostgreSQL. Among other utilities, it installs psql which allows us to connect to the local and remote instances of Postgres.

  3. postgresql-common - The postgresql-common package provides a structure under which multiple versions of PostgreSQL may be installed and/or multiple clusters maintained at one time.

  4. postgresql-client-common - The postgresql-client-common package provides a structure under which multiple versions of PostgreSQL client programs may be installed at the same time. It provides a wrapper which selects the right version for the particular cluster you want to access (with a command line option, an environment variable, /etc/postgresql-common/user_clusters, or ~/.postgresqlrc).

  5. postgresql-contrib - Adds some additional utilities and functionality.

Metapackages on debian are simply a collection of packages to be installed.

Uninstallation

sudo apt-get --purge autoremove postgresql-common postgresql-client-common postgresql-doc postgresql-contrib

Installation

sudo apt install postgresql postgresql-client postgresql-doc postgresql-contrib

Configuration

Reset the password of the postgres user

The default superuser, called ‘postgres’, does not have a password by default. We need to add one:

$ sudo su postgres -c psql template1
template1=# ALTER USER postgres with PASSWORD 'password'; //_pg@123
template1=# \q

Where ‘password’ is your password.

Allow local users to access the server

The default PostgreSQL installation in Ubuntu requires that a PostgreSQL user must also be a unix user. To do this, we need to change the configuration in the pg_hba.conf file:

$ sudo geany /etc/postgresql/9.1/main/pg_hba.conf

Replace out the line (comment it out by adding a ‘#’ in front of it):

local all all ident sameuser

With this line:

local all all md5

After that you will need to restart the server:

$ sudo /etc/init.d/postgresql restart

Now you’re all set. All you need to do is to create a new user, and a database and start coding.

Creating a new PostgreSQL user $ sudo -u postgres createuser -d -R -P

Create a new database owned by$ sudo -u postgres createdb -O

Executing an SQL file $ psql -f

Removing a database $ dropdb -U

Removing a PostgreSQL user $ sudo -u postgres dropuser

Test Run

26.08.2012 What I've done after:

createdb test_db2

psql test_db2

test_db2=>select version();
test_db2=>select current_date;
test_db2=>\h
test_db2=>\q

26.08.2012 i tried connecting to postgres from a python program and succeeded

Connecting to the database.

import psycopg2

try:
    conn = psycopg2.connect(dbname='testdb2', user='ab', host='')
    print "Able to connect to the database."

except:
    print "Unable to connect to the database."

Checking the database version.

import psycopg2
import sys

#con = None

try:
    con = psycopg2.connect(database='testdb', user='ab')
    cur = con.cursor()
    cur.execute('select version()')
    ver = cur.fetchone()
    print ver

except psycopg2.DatabaseError, e:
    print 'Error %s' % e
    sys.exit(1)

finally:
    if con:
        con.close()

Inserting data

import psycopg2
import sys

con = None

try:
    con = psycopg2.connect(database)
except Exception, e:
    raise e
finally:
    pass

to be able to insert data in next program, i first need to create some table that I could use.

createdb blogapp-testdb

blogapp-testdb=> create table entries (
blogapp-testdb(> entry_id serial,
blogapp-testdb(> entry_date date,
blogapp-testdb(> entry text)
blogapp-testdb-> ;
NOTICE:  CREATE TABLE will create implicit sequence "entries_entry_id_seq" for serial column "entries.entry_id"
CREATE TABLE
blogapp-testdb=> insert into entries VALUES (1,now(),'i guess this is my first entry.');
INSERT 0 1

blogapp-testdb=> DROP TABLE entries ;
DROP TABLE

blogapp-testdb=> create table entries (
blogapp-testdb(> entry_id int, entry_date timestamp, entry text);
CREATE TABLE

blogapp-testdb=> create table entries (entry_date timestamp, entry text);
CREATE TABLE

blogapp-testdb=> insert into entries VALUES (now(), 'this is my first entry here!');
INSERT 0 1

Services

Stop postgres service
    $ sudo /etc/init.d/postgresql stop
    Stopping PostgreSQL 8.4 database server: main.
Remove this service from system startup
    $ sudo update-rc.d -f postgresql remove
    update-rc.d: using dependency based boot sequencing
Check if service is stopped and removed from system boot
    $ service postgresql status
    Running clusters: