How to connect to postgresql database.doc

bossprettyingData Management

Nov 28, 2012 (4 years and 7 months ago)

229 views

12/5/2006


ABOUT YOUR WORK ON POSTGRESQL under LINUX


D. Pokrajac


1.
How I created the databases


I created users group1, group2, group3 on the linux machine

from the console, using
GUI
. They belong to a newly created group. Also, they belong to postgres
group.


Then, I started postgres server on the console
, using GUI
.


I logged on as postgres user.


su postgres


As postgres user, I ran:


createdb group1

createdb group2

createdb group3


Also, I created databases group1, group2, group3 to maximally simplif
y login procedure.

As postgres user, I ran the following in linux:


createdb group1

createdb group2

createdb group3


Then, as postgres user, I logged on postgresql:


psql

d group1


There,


I created three users


create user group1;

create user group2;

cr
eate user group3;


Since I use IDENT authentication, (see also bellow), I had to create postgresql users with
the same names as
the
linux users. This way, they do not need to type passwords when
login in.




$ psql
-
U gateadmin template1


psql: FATAL: I
DENT authentication failed for user "gateadmin"

Why?

It looks like you would rather use password authentication than the

default IDENT
-
based auth (it's default in Debian distro of postgres

anyway). IDENT will only let you in when you do *not* use
-
U, ie,

y
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

postmaster afterwards.

regards, tom lane

Solution:

Just modify pg_hba.conf like this (change to trust)

local all trust

host all

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!)


cat
CompanyDatabaseInPos
tgreSQL.txt
|psql >ddd


I repeated this for users group2 and group3.



2.
How to log on linux


Your database is located on a linux server. The server name IP address is
71.200.16.212.

Every group has an account opened on this machine.

PLEASE INFORM
ME I
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

to:

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html


and download the windows .zip version


A .ZIP file containing all the binaries (except PuTTYtel), and al
so the
help files

For Intel x86:

putty.zip

(or by FTP)

(RSA sig)

(DSA
sig)


If you are at the Department, you can
still
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
71.200.16.212


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
yes.


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
71.200.16.212

in yours).



Type your user name. Press enter.



Typ
e your password. Press enter.


Now, if everything is OK, you are on linux!


CONGRITULATIONS. WELCOME TO A REAL AND ROBUST OPERATING
SYSTEM



The first time you log on, you MUST change your password.

Type
passwd

(no
r

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

logout

and press enter.


3
.
How to log on DBMS


Post
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
type:


psql


You’ll get something like:





As you can see, your command prompt says that you are logged on g
roup1 database.


In this database, I loaded (see previous Section) tables from Company database.

To see these tables, type


\
d


You’ll get something like this:





To log off the DBMS


To log off the

DBMS, type


\
q


in DBMS prompt.




As you can see,
aft
er this,
you will be again at linux prompt.


4.
Main commands of the DBMS


Main commands of the DBMS are:


\
d


Lists all the tables (demonstrated above)


\
d <tablename>
lists the properties of a particular table




\
h <command>


lists brief help for an S
QL command.


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.




\
q
exits DBMS prompt. After typing
\
q we are back in linux prompt.


5.
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(

key integer,

primary key(key)

);


After pressing ENTER, the table will be created, and you’ll see the messages like thi
s:




By using
\
d and
\
d test_table we can see that the table now exists and examine its
properties




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.







6.
Other useful commands for creating and dealing with tables and other objects in
the database



ALTER TABLE


To add or drop a column in the table or add/drop primary/foreign key
constrains;


CREATE INDEX


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!)


CREATE TRIGGER


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


CREATE
OR REPLACE VIE
W


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
something like


CREATE
OR REPLACE
VIEW
emp_fname_lname

AS select fname, lname

FROM employee

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
at.


You can see all the commands by typing
\
h from postgresql prompt:



More information can be found at postgresql documentation page:


http://www.postgresql.org/docs/











7. How to backup the datab
ase


To backup the database, you’ll use:


pg_dump > <filename>


E.g.,


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.