skip to main content

Postgres Notes

Notes that I consult whenever I work with postgres or start afresh.

Packages

Before going through the document, it is necessary to understand what the requisite packages do. (From https://packages.debian.org)

  1. postgresql
  2. postgresql-client
  3. postgresql-doc

    These are metapackages that always depend on the currently supported PostgreSQL database, client and documentation versions respectively.

  4. 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.

  5. 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).

  6. postgresql-contrib

    Adds some additional utilities and functionality?

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.

After this we need to modify the password of the postgres UNIX user:

$ sudo passwd -d postgres $ sudo su postgres -c passwd //_pg@123

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: