Sunday, November 6, 2011

Postgresql Server Management 

[pgsql-9.0] cd bin
[bin] cd /usr/local/pgsql-9.0/bin
[bin] initdb /Users/javapro/dev/database/postgres_data/petstore
The files belonging to this database system will be owned by user "javapro".
This user must also own the server process.
. . . 
Success. You can now start the database server using:

    postgres -D /Users/javapro/dev/database/postgres_data/petstore
or
    pg_ctl -D /Users/javapro/dev/database/postgres_data/petstore -l logfile start

[bin] pg_ctl -D /Users/javapro/dev/database/postgres_data/petstore -l logfile start
server starting
Manage the server using other options of pg_ctl 
pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c]
pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
pg_ctl restart [-w] [-t seconds] [-s] [-D datadir] [-c] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]
pg_ctl reload [-s] [-D datadir]
pg_ctl status [-D datadir]
pg_ctl kill signal_name process_id
pg_ctl register [-N servicename] [-U username] [-P password] [-D datadir] [-w] [-t seconds] [-s] [-o options]
pg_ctl unregister [-N servicename]


To start the console client, enter
[postgresql] psql -h localhost
psql (9.1.9, server 9.1.4)
Type "help" for help.

javapro=# \l
                           List of databases
   Name    |  Owner  | Encoding | Collate | Ctype |  Access privileges
-----------+---------+----------+---------+-------+--------------------
 javapro   | javapro | UTF8     | en_US   | en_US | 
 postgres  | javapro | UTF8     | en_US   | en_US | 
 template0 | javapro | UTF8     | en_US   | en_US | =c/javapro        +
           |         |          |         |       | javapro=CTc/javapro
 template1 | javapro | UTF8     | en_US   | en_US | =c/javapro        +
           |         |          |         |       | javapro=CTc/javapro
(4 rows)

javapro=# create database bookstore;
CREATE DATABASE
javapro=# \c bookstore;
psql (9.1.9, server 9.1.4)
You are now connected to database "bookstore" as user "javapro".
bookstore=# create table books (book_id SERIAL PRIMARY KEY, title VARCHAR(50), pages SMALLINT, isbn CHAR(17));
NOTICE:  CREATE TABLE will create implicit sequence "books_book_id_seq" for serial column "books.book_id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books"
CREATE TABLE
bookstore=# \dt
 public | books | table | javapro

bookstore=# insert into books (title, pages, isbn) VALUES ('Java Generics and Collections', 451, '978-0-596-52775-4');
INSERT 0 1
bookstore=# select * from books;
       1 | Java Generics and Collections |   451 | 978-0-596-52775-4

bookstore=# \i ~/Desktop/postgres_bookstore.sql 
DROP TABLE
DROP TABLE
DROP TABLE
. . .
CREATE TABLE
CREATE TABLE
















at