Andrew Yu, BSD style license, Illustra, Ingres, Jolly Chen, Michael Stonebraker, MySQL, Postgres95, SSH agent, SSH tunnelling, University of California at Berkeley
1 History
This all goes back to last century when people at the University of California at Berkeley developed
a database system called Ingres (1977-1985). Later, Ingres branched into a few different products:
Relational Technologies/Ingres Corporation, extended Ingres and produced one of the first commercially successful
relational database servers. On the other branch, still at UC Berkley, Michael Stonebraker
led a team and developed an object-relational database server called Postgres (1986-1994).
Illustra 6.2 took the Postgres code and developed it into a commercial product. Two Berkeley graduate students,
Jolly Chen and Andrew Yu , subsequently added SQL capabilities to Postgres, which was then called Postgres95
(1994-1995). The two later left Berkeley, but Chen continued maintaining Postgres95, which had an active mailing list.
In 1996, the name was changed from Postgres95 to PostgreSQL. PostgreSQL has been an open source software and has been
developed by a large community into one of the most powerful database servers.
[PostgreSQL] 2 Licensing
Different from MySQL which has a commercial version and a GPL-version licenses available,
PostgreSQL is licensed under BSD license only. BSD license is less restrictive compared with GPL.
For example, under BSD license, commercial users do not have to share their source code,
and at the same time, they do not have to pay anything either.
3 Installation on Fedora Core 4
Follow these steps to install postgreSQL
3.1 install postgreSQL
yum -y install postgresql-server
yum -y install postgresql
yum -y install postgresql-devel
3.2 Tell postgres where to store data
Before doing anything else, set PGDATA environment variable (the following example works with bash,
you might need to adjust a little bit depending on the shell you are using):
export PGDATA=/opt/pgdata
Alternatively, you could use -D pgdata-path with your command to tell postgres where to store data
and config files. So theoratically, you could have multiple instances of postgres running on the
same physical server.
Note that the pgdata directory has to be owned by the user who will maintain the database
service (init it, start it, stop it, etc.) People usually create a user called postgres.
3.3 initialize database server
Then change user to postgres, run "initdb", this will initialize the database.
Note that the default database service port is 5432, if you want, you could change this port
to anything by changing $PGDATA/postgres.conf.
3.4 start the server
To start postmaster in a shell (will quit after shell closes):
Run "postmaster start" or "pt_ctl start" to start the database server.
To run database server in the background, use "postmaster -D $PGDATA >logfile 2>&1 &".
If you want to start the database server at start up,
add the following to /etc/rc.local: "su -c '/usr/bin/pg_ctl start -l /var/log/pgsql.log' -u postgres"
3.5 create your first database
Use "createdb test" to create your first database.
3.6 try it out
psql is the postgreSQL client tool. Use "psql -Upostgres test" to connect to the database
and try it out.
If you get this error message:
LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
WARNING: could not create listen socket for "localhost"
The default port 5432 could have been taken by some applications. Check to make sure that you do not have
another instance of postgres running. In my case, it was because I used SSH tunnelling and
mapped port 5432 to a remote postgres server. If I run "ssh -L5432:pgsql.foo.bar.com:5432 remotehost",
and then run "postmaster start", I got the previous error message. I got out of the ssh logon,
and tried "postmaster start" again, everything worked fine.
4 postgreSQL vs. MySQL
Here is Unbiased comparison
between postgreSQL and MySQL . In short, postgreSQL is good at transaction support, while MySQL usually
has better performance.
5 Using pgAdmin administrate postgres database
install pgadmin. On fedora core, you could do this by running the following command
yum -y install pgadmin3
run pgadmin (on linux systems, run "pgadmin3")
click File->Add Server.
in the address field, put in the ip address or domain name of your postgresql server. If you are using SSH agent and SSH tunnelling , put in "localhost"
in the description field, put in some text briefly descrbing this database
leave the service field as it was
in the port field, put in the port number for your postgres instance. Usually it is 5432. If you are using SSH agent and SSH tunnelling , put in the local port number you mapped your postgres server onto.
leave the initial db field as it was
fill in your user name and password, note that if you are connecting to a newly installed database instance, use the username from which you started
the service, and leave the password field empty.
click OK
browse the database
Create a new database in pgAdmin: right click you database host in the list database servers in pgAdmin, then New Object -> Create database
Add a table to your newly created database: choose the database you just created, collapse layer by layer until you see the "tables" item,
then right click "Tables" and choose New Table.
Add columns to your new table. You can add new columns to your table by right click the table name and select "New Column".
To add a auto-increasing ID column, create a sequence "aaa_bbb_seq" and set default value to "nextval('public."employee_id_seq"'::text)".
If you run this query "select * from Employees;", and get this error "ERROR: relation "employees" does not exist",
try this query instead "select * from "Employees";", In my case, when I changed table names and column names to lower case,
everything turned out fine, I didnot have to use quotation marks.
5.1 Couldnot find my newly added data type in pgadmin3
Try to upgrade pgadmin to a higher version. I had this problem with version 1.0,
problem solved after I upgraded to version 1.2.2
5.2 An error has occured: ERROR: permission denied for relation xxx
This happens when you actually do not have the permission to do xxx, however, if you are sure
you have the correct permissions, then this could have been caused by a bug in pgAdmin (as of version 1.2.2).
To work around this bug, open a SQL command window by clicking the SQL icon or "Tools->query tool",
then try to run your command here. For example, if you tried to set a column attribute "not NULL', however,
you got the previous error. Run the following query in the query windows:
ALTER TABLE tablename ALTER COLUMN yourcolname SET NOT NULL;
It should work fine.
References [PostgreSQL] website
