Systems Status

Announce RSS Feed RSS

Blog RSS Feed RSS

Help Topics

Deschutes Facilities

Campus Facilities

User Account Info

Roundcube Mail

Contact Systems

ipv6 ready

Introduction

The standard PostgreSQL install is run as a self-contained DBMS that needs an administrator to add users and grant access. For instructional purposes we've written some scripts that allow you to run your own PostgreSQL server (under your own user id), listening on its own socket/port, with its own database area, and with the database tables, permissions, and personal configuration all maintained by you. This is done with the command postgresctl.

postgresctl install

The command postgresctl install creates a postgresql data directory (~/postgres-data) and creates the initial tables. When prompted enter a database password of your choosing. Remember this password as you will need it to run all the postgresql (psql) commands.

jdash@ix: ~ 16$ postgresctl install
This script is designed to setup an individual postgresql server
When asked for a password, do NOT use your unix password.
This password is to control your database.
Enter new superuser password:
Enter it again:
jdash@ix: ~ 17$

The postgresctl install command also creates a ~/postgres-data/config.sh file. This contains Bourne (or bash) style commands for setting the postgresql environment variables. You can source this file to when you need to execute psql commands.

jdash@ix: ~ 18$ . postgres-data/config.sh
jdash@ix: ~ 19$ echo $PGDATA
/home/users/jdash/postgres-data

postgresctl start

The command postgresctl start starts the server.

jdash@ix: ~ 17$ postgresctl start
Started postgresql server on ix:4403
jdash@ix: ~ 18$

postgresctl stop

The command postgresctl stop will stop your postgresql server.

jdash@ix: ~ 21$ postgresctl stop
Stopping postgres server on ix:4403 in /home/users/jdash/postgres-data

Check the running server

You can use postgresctl status for a quick check of your running server.

jdash@ix: ~ 18$ postgreqctl status
postgres (pid 17271) is listening on ix:4403

If the server is not running the results will look like this:

jdash@ix: ~ 22$ postgresctl status
server is hung

You can also use pg_ctl to check the status of your server.

jdash@ix: ~ 19$ pg_ctl status
pg_ctl: server is running (PID: 17330)
/nfs/local/solaris-local/apps/Databases/postgresql-8.2.1/bin/postgres

Make a new database for your application

The organizational concept each postgresql server works under is that it has a number of databases, and each database will in turn have a number of possibly related schemas, that consist of related tables. Access rights can be different for different databases or schemas. As set up, there is a database named for your username, containing the default schema "public". You can see the tables with the command:

jdash@ix: ~ 19$ . postgres-data/config.sh
jdash@ix: ~ 20$ psql
Password:
Welcome to psql 8.2.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

jdash=# \l
        List of databases
   Name    |  Owner   | Encoding  
-----------+----------+-----------
 postgres  | jdash    | SQL_ASCII
 jdash     | jdash    | SQL_ASCII
 template0 | jdash    | SQL_ASCII
 template1 | jdash    | SQL_ASCII
(4 rows)

So to start with, you'll want to create a new database:

jdash=# create database my_db;
CREATE DATABASE

then give it one or more tables:

jdash=# \c my_db
my_db=# CREATE TABLE names ( first_name text, last_name text );
CREATE TABLE
my_db=# \dt
        List of relations
 Schema | Name | Type  |  Owner  
--------+------+-------+----------
 public | names| table | jdash

my_db=# \d names
     Table "public.names"
   Column   | Type | Modifiers
------------+------+-----------
 first_name | text |
 last_name  | text |

Now, to add some names to the names table (note that single quotes are important):

my_db=# insert into names values ( 'Jo Don', 'Dash' );
INSERT 0 1
my_db=# select * from names;
 first_name | last_name
------------+-----------
 Jo Don     | Dash
(1 row)

That's enough for basic access to your database.

Other stuff to do...

The details of maintaining a PostgreSQL server are beyond the scope of this document. However, the official PostgreSQL docs are actually fairly understandable, accurate, and up to date.

The postgresql database software is installed on CS machines in /local/apps/postgresql. Included there is the database server daemon, and some utility programs and files.

Manual Pages

 createdb (1)- create a new PostgreSQL database
 createuser (1)- define a new PostgreSQL user account
 dropdb (1)- remove a PostgreSQL database
 dropuser (1)- remove a PostgreSQL user account
 pg_config (1)- retrieve information about the installed version of PostgreSQL
 pg_dump (1)- extract a PostgreSQL database into a script file or other archive file
 pg_restore (1)- restore a PostgreSQL database from an archive file created by pg_dump
 psql (1)- PostgreSQL interactive terminal

External Links

Edited: November 05, 2014, at 10:50 AM
Copyright © 2017, University of Oregon, All rights reserved
Privacy Policy