http://www.phpfreaks.com/postgresqlmanual/page/index.html
http://www.phpfreaks.com/postgresqlmanual/page/sql-revoke.html
===============================================================================
http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html
The PostgreSQL Database - Installation and Configuration:
Install (rpm -ivh package) or check for the following PostgreSQL RPMs (rpm -q package):
postgresql-7.1.3-2
postgresql-libs-7.1.3-2
postgresql-server-7.1.3-2
postgresql-perl-7.1.3-2
Other packages available:
postgresql-odbc
postgresql-jdbc
postgresql-tcl
postgresql-tk
postgresql-python
postgresql-devel
Install: rpm -ivh postgresql-7.1.3-2.i386.rpm postgresql-libs-7.1.3-2.i386.rpm postgresql-server-7.1.3-2.i386.rpm postgresql-perl-7.1.3-2.i386.rpm
The user "postgres" should have already been configured by the installation of the RPMs. Info:
User: postgres
Home directory: /var/lib/pgsql
Default shell: /bin/bash
A password will be missing. As root issue the command: passwd postgres to assign a password for user postgres.
Login as user postgres: su - postgres
This will execute the profile: /var/lib/pgsql/.bash_profile
PGLIB=/usr/lib/pgsql
PGDATA=/var/lib/pgsql/data
export PGLIB PGDATA
Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data
This creates a bunch of directories, a template directory and sets up the postgres configuration in the user directory /var/lib/pgsql/. Red Hat start command (service)/script (rc script) will perform this task if it has not already been performed. See next step - Starting the database.
Starting the database server: As root. (from most to least favorite method)
service postgresql start
(If the database has not already been initialized with initdb, this will be performed by the command)
OR
/etc/rc.d/init.d/postgresql start
(If the database has not already been initialized with initdb, this will be performed by the script)
OR
/usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
OR
/usr/bin/postmaster -D /var/lib/pgsql/data &
Notes:
Configuration file: /var/lib/pgsql/data/postgresql.conf
By default there is no network access. See the directive tcpip_socket. (Required for ODBC,JDBC) Also see the postmaster directive "-i". Logging and tuning parameters are specified here.
Host Access file: /var/lib/pgsql/data/pg_hba.conf
Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf
Create a database: createdb bedrock
(As Linux user postgres)
Connect to the database: psql bedrock
Execute command as Linux user postgres
You will now be at the PostgreSQL command line prompt.
[prompt]$ psql - or "psql bedrock"
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
bedrock=#
bedrock=# \c bedrock
You are now connected to database bedrock.
bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20));
CREATE
bedrock=# INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
INSERT 18733 1
bedrock=# INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
bedrock=# INSERT into employee values ('Barney Rubble','Sales','Neighbor');
bedrock=# INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
bedrock=# SELECT * from employee;
name | dept | jobtitle
----------------------+----------------------+----------------------
Fred Flinstone | Quarry Worker | Rock Digger
Wilma Flinstone | Finance | Analyst
Barney Rubble | Sales | Neighbor
Betty Rubble | IT | Neighbor
(4 rows)
bedrock=#
bedrock=# \q
More info:
Create a user:
Command line: [prompt]$ createuser dude
SQL: CREATE USER dude WITH PASSWORD 'supersecret';
Change with ALTER USER
Grant priveliges:
SQL: GRANT UPDATE ON table-name to dude
SQL: GRANT SELECT ON table-name to dude
SQL: GRANT INSERT ON table-name to dude
SQL: GRANT DELETE ON table-name to dude
SQL: GRANT RULE ON table-name to dude
-- odvzamem privilegij
ameba=# REVOKE INSERT ON ombrografi_vhodna FROM PUBLIC;
REVOKE
SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public
Delete a user:
Command line: [prompt]$ dropuser SuperDude
Delete a database:
Command line:
[prompt]$ destroydb bedrock
[prompt]$ dropdb bedrock
SQL: DROP DATABASE bedrock;
Create a database:
Command line: [prompt]$ createdb bedrock -U user-name -W
You will be prompted for a password. (or execute as Linux user postgres without -U and -W options)
SQL: CREATE DATABASE bedrock
Backup a database:
[prompt]$ pg_dumpall > outfile
[prompt]$ pg_dumpall -Fc dbname > outfile
Version Upgrades:
Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d
Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak
The table template1 is the default administrative database.
If one would like to generate a GUI interface to the database, I would recommend a web form to a Java servlet back-end or use a JAVA GUI program and JDBC. See the YoLinux tutorial on accessing PostgreSQL with JDBC in a Tomcat servlet example.
Links/Info:
PostgreSQL documentation:
/usr/share/doc/postgresql-7.1.3/html/index.html (local html)
/usr/share/doc/postgresql-7.1.3/ (local directory)
Resources: /usr/share/pgsql/
select x(geometry) as x_os from idmm_geom_tmp;
select astext(geometry) as xy from idmm_geom_tmp;
==================
select leto,mesec,dan,datum,round(k8_maksimalna_temperatura/10.0,1) as tmax,round(k9_minimalna_temperatura/10.0,1) as tmin,round(k10_minimalna_temperatura_5cm/10.0,1) as tmin5,CASE WHEN (k11_temperatura_suhi_7h>-999)and(k12_temperatura_suhi_14h>-999)and(k13_temperatura_suhi_21h>-999) then trunc(round((k11_temperatura_suhi_7h+k12_temperatura_suhi_14h+2*k13_temperatura_suhi_21h)/4.0)/10.0,1) else -999 end as tem_pov, CASE WHEN (k20_rel_vlaga_7h > -9 and k21_rel_vlaga_14h > -9 and k22_rel_vlaga_21h > -9) then round((k20_rel_vlaga_7h+ k21_rel_vlaga_14h+k22_rel_vlaga_21h)*1.0/3.0,0) else -9 end as vla_pov, round(k45_trajanje_sonca/10.0,1) as son,round(k49_padavine/10.0,1) as pad,k51_sneg_skupaj as ss,k52_sneg_novi as sn, round(k11_temperatura_suhi_7h/10.0,1) as ts7,round(k12_temperatura_suhi_14h/10.0,1) as ts14,round(k13_temperatura_suhi_21h/10.0,1) as ts21, k20_rel_vlaga_7h as rv7,k21_rel_vlaga_14h as rv14,k22_rel_vlaga_21h as rv21, k70_snezna_odeja as s,idmm,tip from klima_vhodna where postaja in(192,249,268,355,464) and datum between '1.10.2004' and '24.11.2004';
pg_dump -x -o -t amp_datum ameba |gzip >amp_datum.gz
pg_dump -x -o -t amp_datum -p 6543 ameba |gzip >amp_datum.gz
gunzip -c amp_datum.gz | psql miharadb
gzip ime
gunzip ime.gz
ssh base@virga
psql -p 6543 -U postgres ameba
mv uuuu virga_stare_tabele.txt
unix2dos uuuu
less uuuu
cd ~mihad/
[zvicar@virga aa]$ unix2dos a.a
unix2dos: converting file a.a to DOS format ...
-----------------------------------------------------------
Kako preimenujes fajle, das prozornost sliki, spremenis format jpg - gif
rename image iiko_xg *
for i in *.gif; do convert $i -transparent-color \#ffffff prozorne/$i ; done
for i in *.jpg; do convert $i prozorne/`basename $i .jpg`.gif ; done