ABOUT YOUR WORK ON POSTGRESQL under LINUX
How I created the databases
I created users group1, group2, group3 on the linux machine
from the console, using
. They belong to a newly created group. Also, they belong to postgres
Then, I started postgres server on the console
, using GUI
I logged on as postgres user.
As postgres user, I ran:
Also, I created databases group1, group2, group3 to maximally simplif
y login procedure.
As postgres user, I ran the following in linux:
Then, as postgres user, I logged on postgresql:
I created three users
create user group1;
create user group2;
eate user group3;
Since I use IDENT authentication, (see also bellow), I had to create postgresql users with
the same names as
linux users. This way, they do not need to type passwords when
U gateadmin template1
psql: FATAL: I
DENT authentication failed for user "gateadmin"
It looks like you would rather use password authentication than the
based auth (it's default in Debian distro of postgres
anyway). IDENT will only let you in when you do *not* use
our postgres user name is the same as your Unix user name. Change
this in pg_hba.conf, and don't forget to SIGHUP or restart the
regards, tom lane
Just modify pg_hba.conf like this (change to trust)
local all trust
127.0.0.1 255.255.255.255 trust
Then, I loaded company schema
to each user.
I logged on linux as user group1, then I used
the following (it went to the default database
of each user, so I did not need to specify
d after psql!)
I repeated this for users group2 and group3.
How to log on linux
Your database is located on a linux server. The server name IP address is
Every group has an account opened on this machine.
F YOU CANNOT LOG ON THIS MACHINE. I might need to send you another
IP, because it is dynamic.
To access the server the database is located, you need to have ssh client installed at your
machine. If you do not have one, you can use puTTY.
To get puTTY, go
and download the windows .zip version
A .ZIP file containing all the binaries (except PuTTYtel), and al
For Intel x86:
(or by FTP)
If you are at the Department, you can
download puTTY on your personal drive.
Once you downloaded putty
.zip, extract it on your personal drive (right click zipped
folder, it will prompt you to create putty folder and unzip into that).
Then, launch putty by left clicking on the icon for SSH, Telnet and Rlogin client.
If you are prompted, whether you are
sure to run the software, press Run (see bellow).
You’ll see the configuration menu
The host name should be set to
The port should be set to 22, and the service name is ssh.
Click on Open. You might be prompted about the server’s ho
st key. You should click
After that, you’ll get the command prompt (“what’s that black screen?”).
[You’ll see the host name in the window. It is gibran in this example,
it will be
Type your user name. Press enter.
e your password. Press enter.
Now, if everything is OK, you are on linux!
CONGRITULATIONS. WELCOME TO A REAL AND ROBUST OPERATING
The first time you log on, you MUST change your password.
in this command!) and press enter. Typ
e your current password.
Press enter. Type new password. Press enter. Retype your new password. Press enter.
Now, you have your new password set.
To log off:
On command line, type
and press enter.
How to log on DBMS
gresql is a versat
ile DBMS that can run under linux or windows platform. To support
multiuser environment, we run postgresql on a linux server. Hence, to be able to work in
postgresql, you should have a linux account, from which you can log on DBMS. The
login procedure in l
inux is described in previous Section.
To log on the database
Since DBMS used IDENT authentication method, once you are on linux, you’ll simply
You’ll get something like:
As you can see, your command prompt says that you are logged on g
In this database, I loaded (see previous Section) tables from Company database.
To see these tables, type
You’ll get something like this:
To log off the DBMS
To log off the
in DBMS prompt.
As you can see,
you will be again at linux prompt.
Main commands of the DBMS
Main commands of the DBMS are:
Lists all the tables (demonstrated above)
lists the properties of a particular table
lists brief help for an S
For instance, let’s see what does it say for ALTER TABLE
As we can see, we can add or drop column (with specified name and datatype),
alternate column (e.g., to become not null or to set default)), to rename the table
or to add/drop constr
aint (primary/foreign key).
lists all DBMS commands with brief explanations.
exits DBMS prompt. After typing
q we are back in linux prompt.
Creation and dropping the tables
1. Log on the linux server and on the database
2. Use CREATE TAB
LE command we learned at class. E.g., to create table test_table
containing one integer field key, you’ll type
create table test_table(
After pressing ENTER, the table will be created, and you’ll see the messages like thi
d test_table we can see that the table now exists and examine its
3. To drop the table, use the following (WITH CAUTION!!!)
drop table test_table;
As we can confirm by
d, the table test_table does not exist anymore.
Other useful commands for creating and dealing with tables and other objects in
To add or drop a column in the table or add/drop primary/foreign key
To create an index to enhance the perf
ormance (you won’t use this in
the project, but you’ll need that later in your life
. You may want to create index for
foreign keys frequently used in queries, etc).
CREATE OR REPLACE FUNCTION
To create a function (e.g., if you want to
compute the square
root of the sum of squares of the values in the column, you need to
create a function, since there is no automatic function that will do that; you will not need
this in the project!)
Triggers are special functions that are executed befor
e or after
insertion/deletion/update of particular columns in specified tables. You will not need that
for your project
OR REPLACE VIE
Views are virtual tables. E.g, if you would like to have
a table that contains ONLY first and last names of al
l the students, you may execute
AS select fname, lname
ORDER by lname, fname;
Observe that views do not TRULY exist, so there is no violation of “no replication” rule
for the database
s. You will not need views for your project.
More or less, any object that can be created can be altered or dropped.
Hence, there are commands DROP VIEW, DROP INDEX, DROP FUNCTION.
Of course, there is a command DROP DATABASE, but be VERY cautious with th
You can see all the commands by typing
h from postgresql prompt:
More information can be found at postgresql documentation page:
7. How to backup the datab
To backup the database, you’ll use:
pg_dump > <filename>
pg_dump > 12_5_2006.txt
will dump all the data into the file 12_5_2006.txt
The file will be stored on your home directory.
You can (using secure copy) subsequently download these dat
a on your machine.