PostgreSQL

Pointers

• http://www.postgresql.org/
• http://www.postgresql.org/docs/

• UnixGeekTools - other great tools for unix geeks

Startup/shutdown

• osx: /sw/bin/pgsql.sh start
• freebsd: /usr/local/etc/rc.d/010.pgsql.sh start
• pg_ctl start -s -w
• pg_ctl stop -s -m fast
• pg_ctl restart -s -m fast
• pg_ctl reload
• pg_ctl status

First startup

• make data directory - mkdir /usr/local/pgsql/data
• chown to postgres user
• log in as postgres user
• initdb -D datadir

Database startup

• osx: sudo /sw/bin/pgsql.sh start
  • pg_ctl start
  • pg_ctl start -l $logfile -D $PGDATA
  • postmaster -D $PGDATA

Database shutdown

• pg_ctl stop

psql

• psql template1 pgsql = start psql, db=template1, user=pgsql
• \l = list databases
• \c dbname = connect to database
• \dt = list tables

Upgrade

• http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html

step 1 - back up data

• pg_dumpall > db.out
• one db: pg_dump dbname > db.out

step 2 - sofware install

• shut down old version
• bring up new version

step 3 - restore old data

• psql -f db.out template1
• one db: psql -d database -f db.out

JDBC

• jdbc:postgresql://develop.portal.iad.vt.edu:5432/portal
• jdbc:postgresql://localhost:5432/gcportal
• Driver: org.postgresql.Driver
• for postgres, must be running on a port, even if on local machine
• add the following line to postgresql.conf: tcpip_socket = 1

Database access

• /sw/var/postgresql/data/pg_hba.conf
  • trust
  • md5
• /usr/local/pgsql/bin/createuser
• /usr/local/pgsql/bin/dropuser
• passwords
  • ALTER USER myname WITH PASSWORD 'value';
• CREATE USER myname WITH PASSWORD 'value'; ???

Creating a new database

• createdb dbname

Connecting to the database

• psql -U pg template1

Create a new table

CREATE TABLE tablename (
                        columnname1   VARCHAR(16) NOT NULL,
                        columnname2   INTEGER,
                        columnname3   TIME NOT NULL,
                        columnname4   FLOAT,
                        PRIMARY KEY ( columnname1, columname2 )
);

Inserting data

    INSERT INTO testtable VALUES ( 'c', '3' );

Querying data

    select * from testtable;

Viewing table information

    \dt testtable

drop all databases and start all over

    pg_ctl stop
    rm -rf /sw/var/postgresql/data/*
    /usr/local/pgsql/bin/initdb -D /sw/var/postgresql/data

Environment

• ~pgsql/.profile
• PGDATA=/sw/var/postgresql/data
• PGHOST - override default host
• PGPORT - override default port
• PATH - /usr/local/pgsql/bin:$PATH
• MANPATH - /usr/local/pgsql/man:$MANPATH
• LD_LIBRARY_PATH - /usr/local/pgsql/lib