Postgres Configuration on RedHat

This will install Postgresql on a RedHat 6.4 or CentOS 6.4 system and will allow password-protected remote login access. 

# Open the firewall to allow remote postgres connections
sudo /sbin/iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

sudo /sbin/service iptables save
sudo /sbin/service iptables restart

# Install packages through yum
sudo yum install vim-enhanced
sudo yum install postgresql-server

# Initialize a new database directory
sudo service postgresql initdb 

# Start the database server
sudo service postgresql start 

# Add postgres to the default runlevel 
sudo chkconfig postgresql on
sudo chkconfig postgresql --list
sudo chmod 755 /home/idcuser/

# Edit the postgres.conf to allow connections from all ips

sudo vim /var/lib/pgsql/data/postgres.conf
listen_addresses="*"

# Edit the pg_hba.conf file to allow password auth for any ip

sudo vim /var/lib/pgsql/data/pg_hba.conf
host all all 0.0.0.0/0 md5

# Restart postgres
sudo /etc/init.d/postgres restart

# Configure a postgresql superuser and new schema. 
sudo -u postgres psql
psql> create role idcuser superuser createdb createrole inherit login;
psql> alter user idcuser with password 'password';
psql> create database sample;
psql> \c sample
psql> create schema sample;
psql> alter user idcuser set search_path to sample,public;

Different way to alter a Postgres table

Alter Postgres Tables via metadata 
(Thanks Louis R.) 

select atttypmod from pg_attribute where attrelid = 'TABLE1'::regclass and attname = 'title';
# then
update pg_attribute set atttypmod = 200+4 where attrelid = 'TABLE1'::regclass and attname = 'title';

http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

Postgresql Sequences

Create a new sequence:
CREATE SEQUENCE mytable_myid_seq;

Add the sequence as the incrementor for a table column:
ALTER TABLE mytable
ALTER COLUMN myid
SET DEFAULT NEXTVAL('mytable_myid_seq');

Update existing rows in table with the sequence:
UPDATE mytable
SET myid = NEXTVAL('mytable_myid_seq');

Display the sequence information:
Select * from mytable_myid_seq;

Update a sequence to be one greater than a table's id column:
select setval('mytable_myid_seq', (select max(id) + 1 from mytable));
http://railspikes.com/2009/3/6/duplicate-key-violates-unique-constraint (thanks Louis)
http://pointbeing.net/weblog/2008/03/mysql-versus-postgresql-adding-an-auto-increment-column-to-a-table.html

Query Django Admin Log

Show Django Admin Log entries


select u.username, u.first_name, u.last_name, l.action_time, c.name, c.app_label,
       l.object_id as "Object Modified", l.object_repr, l.action_flag, l.change_message
from (auth_user u inner join django_admin_log l on u.id = l.user_id )
      inner join django_content_type c on c.id = l.content_type_id
where l.object_id = '9'  /* filter by object id if desired */
       and l.action_time > to_date('2010-10-04 01:00','YYYY-MM-DD HH:MI')  /* filter by date range */
order by l.action_time;

Case Study Notes: install djangobb in virtualenv

Ubuntu 10.04 python 2.6 django 1.1

sudo apt-get build-dep python-psycopg2
sudo aptitude install python-dev

cd Sites
virtualenv --no-site-packages env
. ./env/bin/activate
pip install pil
pip install markdown2 Markdown
pip install django-registration
pip install djapian
pil install xapian
pip install psycopg2
easy_install -i http://downloads.egenix.com/python/index/ucs4/ egenix-mx-base
pip install http://code.djangoproject.com/svn/django/tags/releases/1.1.2/
hg clone http://hg.djangobb.org/djangobb/ djangobb
cd env/lib/python2.6/site-packages/
cp /usr/lib/python2.6/dist-packages/_xapian.so .
cp /usr/lib/python2.6/dist-packages/xapian.py .

# configure settings.py to use postgresql_psycopg2
# comment out the openid stuff

## http://www.saltycrane.com/blog/2009/07/using-psycopg2-virtualenv-ubuntu-jaunty/

Postgresql Basic Commands

Login to postgresql:
psql -d mydb -U myuser -W
psql -h myhost -d mydb -U myuser -W
psql -U myuser -h myhost "dbname=mydb sslmode=require" # ssl connection

Default Admin Login:
sudo -u postgres psql -U postgres
sudo -u postgres psql

List databases on postgresql server:
psql -l [-U myuser] [-W]

Turn off line pager pagination in psql:
\pset pager

Determine system tables:
select * from pg_tables where tableowner = 'postgres';

List databases from within a pg shell:
\l

List databases from UNIX command prompt:
psql -U postgres -l

Describe a table:
\d tablename

Quit psql:
\q

Switch postgres database within admin login shell:
\connect databasename

Reset a user password as admin:
alter user usertochange with password 'new_passwd';

Show all tables:
\dt

List all Schemas:
\dn

List all users:
\du

Load data into posgresql:
psql -W -U username -H hostname < file.sql

Dump (Backup) Data into file:
pg_dump -W -U username -h hostname database_name > file.sql

Increment a sequence:
SELECT nextval('my_id_seq');

Create new user:
CREATE USER jjasinski WITH PASSWORD 'myPassword';
# or
sudo -u postgres createuser jjasinski -W

Change user password:
ALTER USER Postgres WITH PASSWORD 'mypass';

Grant user createdb privilege:
ALTER USER myuser WITH createdb;

Create a superuser user:
create user mysuper with password '1234' SUPERUSER
# or even better
create user mysuper with password '1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION;
# or
sudo -u postgres createuser jjasinski -W -s

Upgrade an existing user to superuser: 
alter user mysuper with superuser;
# or even better
alter user mysuper with SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN REPLICATION

Show Database Version:
SELECT version();

Change Database Owner:
alter database database_name owner to new_owner;

Copy a database:
CREATE DATABASE newdb WITH TEMPLATE originaldb;
http://www.commandprompt.com/ppbook/x14316

View Database Connections:
SELECT * FROM pg_stat_activity;

View show data directory (works on 9.1+; not on 7.x):
show data_directory;

Show run-time parameters:

show all;
select * from pg_settings;

Show the block size setting:
# show block_size;
 block_size
------------
 8192
(1 row)

Show stored procedure source:
SELECT prosrc FROM pg_proc WHERE proname = 'procname'

Grant examples:
# readonly to all tables for myuser
grant select on all tables in schema public to myuser;
# all privileges on table1 and table2 to myuser
grant all privileges on table1, table2, table3 to myuser;

Restore Postgres .dump file:
pg_restore --verbose --clean --no-acl --no-owner -h localhost -U myuser -d mydb latest.dump
source

Find all active sessions and kill them (i.e. for when needing to drop or rename db)
Source: http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it

# Postgres 9.2 and above
SELECT pg_terminate_backend(pg_stat_activity.pid) 

FROM pg_stat_activity 

WHERE pg_stat_activity.datname = 'TARGET_DB' 

 AND pid <> pg_backend_pid();

# Postgres 9.1 and below
SELECT pg_terminate_backend(pg_stat_activity.procpid) 

FROM pg_stat_activity 

WHERE pg_stat_activity.datname = 'TARGET_DB' 

AND procpid <> pg_backend_pid();


Re-read postgres config without dropping connections (i.e. if postgres.conf or pg_hba.conf changes)
Source: http://www.heatware.net/databases/postgresql-reload-config-without-restarting/

/usr/bin/pg_ctl reload
or
SELECT pg_reload_conf();

Per user query logging (logs to to postgres logs):
alter role myuser set log_statement = 'all';


Sources:
http://www.devdaily.com/blog/post/postgresql/log-in-postgresql-database
http://forums.devshed.com/postgresql-help-21/how-do-you-turn-off-more-scroll-lock-at-psql-174831.htm
http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
http://archives.postgresql.org/pgsql-general/1998-08/msg00050.php
http://stackoverflow.com/questions/876522/creating-a-copy-of-a-database-in-postgres
http://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database