Revisiting PostgreSQL

offbeatlossData Management

Nov 22, 2012 (4 years and 11 months ago)

367 views

Revisiting PostgreSQL
Ruiwen Chen
rchen052@uottawa.ca
Office: SITE 4066
CSI 3130 - Lab 1
Contents
Introduction to PostgreSQL
Install PostgreSQL in Linux
Download source code
Compile and install
Initialize and run
Basic Unix/Linux Commands
ls, cd, gcc, gmake, ...
Install PostgreSQL in Windows
binary installer
source codes

Introduction to PostgreSQL
The most advanced open source database
Website
www.postgresql.org
Documents:
http://www.postgresql.org/docs/8.3/static/index.html
Installation: Chap. 15
Installation on Windows: Chap. 16
Internals: Chap. 43-45
Source code
http://www.postgresql.org/developer/coding
Code tree:
Source Code Browser
Internals:
A Tour of PostgreSQL Internals


Contents
Introduction to PostgreSQL
Install PostgreSQL in Linux
Download source code
Compile and install
Initialize and run
Basic Unix/Linux Commands
ls, cd, gcc, gmake, ...
Install PostgreSQL in Windows
binary installer
source codes

Install PostgreSQL on Linux
For different versions of Unix/Linux, the binary packages are
usually incompatible.
It's better to download source codes of PostgreSQL,
compile and install by yourself.

Steps:
download source codes
a stable version, or
the current version
configure
make
make install
Download source codes
The latest stable version
Download (via ftp/http)
http://www.postgresql.org/ftp/source/v8.3.3/
postgresql-8.3.3.tar.gz

Decompress

tar xvf postgresql-8.3.3.tar.gz
The current version
Download (via
cvs
)
www.postgresql.org/docs/current/static/anoncvs.html

cvs commands:
cvs -d :pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot login
cvs -z3 -d :pserver:anoncvs@anoncvs.postgresql.org:/projects/cvsroot co -P pgsql
Make by yourself
Steps:
(
www.postgresql.org/docs/8.3/static/install-procedure.html
)
1.
configure
configure source codes with system information
cd postgresql-8.3.3
./configure --prefix=/home/infofa/h/users/rchen052/csi3130/pgsql
"--prefix":
set the dir to be installed, must be full path
2.
make
compile the whole source tree
make check
: run regression tests after make
make install
: install files to the dir specified in
configure
make clean
: clean up immediate files
Initialization
The installed dir include:
bin
: executable programs
lib
: static and dynamic libraries
doc
: documents
include
: header files
share
: templates for initialization
... ...
Initialization
build up the data dir to contain the database files
cd pginstall/bin
./initdb -D ../data

Run
Start the database server

./postgres -D../data

log information will appear following this command
Start a client connection
./psql postgres

SQL commands can be executed now
Some default setting:
postgres
is the default database created in
initdb

5432
is the default port number
A user, which is the same as the linux account, is
created by default with no password.
Almost all parameters are in the file:
data/postgresql.conf
Command history
$ cd csi3130
(Assume the tar file is in this folder)
$ tar xvf postgresql-8.3.3.tar.gz

$ cd postgresql-8.3.3
$ ./configure --
prefix=/home/infofa/h/users/rchen052/csi3130/pginstall
$ make check
$ make install

$ cd ..
$ cd pginstall/bin
$ ./initdb -D../data

$ ./postgres -D ../data
(Server side)

$ ./psql postgres
(Client side. You should open another command window)







Server and Client commands
Server:
$ ./postgres -D../data
LOG: database system was shut down at 2008-09-13 20:40:21 EDT
LOG: autovacuum launcher started
LOG: database system is ready to accept connections

Client:

$ ./psql postgres
Welcome to psql 8.3.3, 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
postgres=#
create table students(sno int, sname varchar(20));
CREATE TABLE
postgres=#
\d

(list all tables)
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | students | table | rchen052
(1 row)
Contents
Introduction to PostgreSQL
Install PostgreSQL in Linux
Download source code
Compile and install
Initialize and run
Basic Unix/Linux Commands
ls, cd, gcc, gmake, ...
Install PostgreSQL in Windows
binary installer
source codes

Unix Shell commands
cd:
enter a dir
ls:
list files in a dir
pwd:
display current path
history:
list your command history
make/gmake:
compile source codes with
Makefile
cc/gcc
: c compiler

Use
--help
to get help on your command, such as "
ls --
help
".
Contents
Introduction to PostgreSQL
Install PostgreSQL in Linux
Download source code
Compile and install
Initialize and run
Basic Unix/Linux Commands
ls, cd, gcc, gmake, ...
Install PostgreSQL in Windows
binary installer
source codes

Install PostgreSQL on Windows
We install binary version directly, instead of source codes.

Download the latest version (binary):
http://www.postgresql.org/ftp/binary/v8.3.3/win32/
postgresql-8.3.3-2.zip
(with installer)
Decompress
Install
Click
postgresql-8.3.msi
to install
Service configuration
Account name
,
Account password
Initialize database cluster
Port number, Superuser name, Password
After Installation
Installed Folder
C:\Program Files\PostgreSQL\8.3
The same dir tree:
bin
: commands of postgresql
data
: data files of the database
doc
: documents
A
service
installed:
Control Panel -- Administrative Tools -- Services
PostgreSQL Database Server 8.3
This service is started automatically
A user is created (default:
postgres
)
Run: Server & Client
The server is started as a service automatically.
Client:
psql.exe
Command line:
psql -Upostgres postgres
Specify
user name
and
database name


C:\Program Files\PostgreSQL\8.3\bin>
psql -Upostgres postgres
Password for user postgres:
(enter password)
Welcome to psql 8.3.3, 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
postgres=#
create table student(sno int, sname varchar(20));
CREATE TABLE

GUI client --
pgAdmin3.exe

Install source codes on Windows
Consult:
http://www.postgresql.org/docs/8.3/static/install-
win32.html


Compile source codes with:
Visual C++ 2005, Perl, and several other tools, or
MinGW
Resources
Unix/Linux resources in SITE:
http://www.site.uottawa.ca/local/labinfo/unix.shtml

PostgreSQL documents:
http://www.postgresql.org/docs/8.3/static/index.html
PostgreSQL internals introduction:
http://www.postgresql.org/developer/coding

(Presentations)
PostgreSQL mailing lists:
http://www.postgresql.org/community/lists/
pgsql-committers
: submitted new features
pgsql-hackers
: discussions by developers