Bits And Pix

postgresql: Set the default schema name (like using or use)

Saturday, May 21st, 2011

SET search_path TO myschema;

Posted in Postgresql | No Comments »

Postgresql: Postgresql Commands Cheat-Sheet

Sunday, September 19th, 2010

PG Tools

pg_dump dbname > backupfile: Backup as a sql file
psql dbname < backupfile : restore

Login

su - postgres
psql
#or
psql mydb

Create Db

su - postgres
createdb DBTest

PSQL Commands

\h : SQL help
\? : psql help
\q : quit
\l : show databases
\d : show tables
\d tablename : show columns
\c dbname : connect to db
\! : switch to OS shell mode (very useful on Mac OS where you start on the SQL shell)

SQL Commands

DROP DATABASE MYDB;

Backup & Restore Database

Backup:  $ pg_dump {source_db} -f {dumpfilename.sql}

Restore: $ psql -d {desintation_db} -f {dumpfilename.sql}

Note: on Mac OS X, the command prompt need to be started from the application folder or from pgAdminIII, and then, type “\!” to switch to command shell, and then, go to /Library/PostgreSQL/8.4/bin to find the psql and pg_dump scripts

How to backup and restore postgresql

Postgresql: Failed to start on Windows (used to work)

Sunday, April 25th, 2010

Somehow, my Postgresql 8.4.3 failed to start on windows. After some digging around, I found the following in the windows event log:

FATAL:  bogus data in lock file "postmaster.pid": ""

Removing the file did the trick (it is located at the root folder of your database store)

Posted in Postgresql | No Comments »

Hibernate: annotations for PostgreSQL sequence id

Tuesday, March 30th, 2010

@Table(name = "mySchema.user")
@javax.persistence.SequenceGenerator(
    name="SEQ_STORE",
    allocationSize=1,
    sequenceName="mySchema.user_id_seq"
)
public class User {
....
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_STORE")
    public Long getId() {
        return id;
    }
}

Posted in Hibernate, Postgresql | No Comments »

Postgresql: Installing Postgresql 8.4.x on CentOS

Tuesday, March 23rd, 2010

Installing with YUM

Get the YUM repository

$ wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-2.noarch.rpm
$ rpm -ivh pgdg-centos-8.4-2.noarch.rpm

Filter YUM postgresql

yum list | grep postgresql

If you see postgresql from other repositories besides pgdg84, then you need to exclude postgresql from coming from other repositories by following the below instructions excerpted from PostgreSQL How to Yum

As root, cd /etc/yum.repos.d
Edit distro’s .repo file:
- On Fedora, edit fedora.repo and fedora-updates.repo, [fedora] sections
- On CentOS, edit CentOS-Base.repo, [base] and [updates] sections.
- On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section.

Add to the bottom of the section:

exclude=postgresql*

Yum Install

yum install postgresql-server

Init database

service postgresql initdb

Make it autostart

chkconfig postgresql on

Create the first DBTest

su - postgres
createdb DBTest

SQL to DBTest

su - postgres
psql DBTest

All DB files are at

Root directory: /var/lib/pgsql/

Config file: /var/lib/pgsql/data/postgresql.conf