# rpm -ihv postgresql-7.0.2-17.i386.rpm
# rpm -ihv postgresql-server-7.0.2-17.i386.rpm
# rpm -ihv postgresql-devel-7.0.2-17.i386.rpm
# rpm -ihv postgresql-odbc-7.0.2-17.i386.rpm
# rpm -ihv php-4.0.1pl2-9.i386.rpm
# rpm -ihv mod_php-4.0.1pl2-9.i386.rpm
# rpm -ihv php-pgsql-4.0.1pl2-9.i386.rpm
# rpm -ihv php-manual-4.0.1pl2-9.i386.rpm
# su - postgres
$ cd data
$ chmod u+w pg_hba.conf
...
# The above allows any user from any host with IP address 192.168.0.x to
# connect to database template1 as the same username that ident on that host
# identifies him as (typically his Unix username).
#host all 192.168.0.1 255.255.255.255 reject
host all 0.0.0.0 0.0.0.0 trust
# The above would allow anyone anywhere except from 192.168.0.1 to connect to
# any database under any username.
...
# /etc/init.d/postgres start
# ps ax | grep postmaster
537 ? S 0:00 /usr/local/pgsql/bin/postmaster -S -D/usr/local/pgsql
# su - postgres
$ createuser dbuser
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
% createdb tmpdb
% psql tmpdb
\? -- help
\a -- toggle field-alignment (currently on)
\C [<captn>] -- set html3 caption (currently '')
\connect <dbname|-> <user> -- connect to new database (currently 'hoge')
\copy table {from | to} <fname>
\d [<table>] -- list tables and indices, columns in <table>, or * for all
\da -- list aggregates
\dd [<object>]- list comment for table, field, type, function, or operator.
\df -- list functions
\di -- list only indices
\do -- list operators
\ds -- list only sequences
\dS -- list system tables and indexes
\dt -- list only tables
\dT -- list types
\e [<fname>] -- edit the current query buffer or <fname>
\E [<fname>] -- edit the current query buffer or <fname>, and execute
\f [<sep>] -- change field separater (currently '|')
\g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or pipe]
\h [<cmd>] -- help on syntax of sql commands, * for all commands
\H -- toggle html3 output (currently off)
\i <fname> -- read and execute queries from filename
\l -- list all databases
\m -- toggle monitor-like table display (currently off)
\o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or pipe
\p -- print the current query buffer
\q -- quit
\r -- reset(clear) the query buffer
\s [<fname>] -- print history or save it in <fname>
\t -- toggle table headings and row count (currently on)
\T [<html>] -- set html3.0 <table ...> options (currently '')
\x -- toggle expanded output (currently off)
\w <fname> -- output current buffer to a file
\z -- list current grant/revoke permissions
\! [<cmd>] -- shell escape or command
tmpdb=> create table emp (
tmpdb=> empono int, ename varchar(20), job varchar(10));
CREATE
tmpdb=>
create table emp (
empono int,
ename varchar(20),
job varchar(10)
);
tmpdb=> \i emp.sql
create table emp (
empono int,
ename varchar(20),
job varchar(10)
);
CREATE
EOF
tmpdb=>
tmpdb=> insert into emp values ( 1003, 'hoge bar','Manager');
INSERT 17451 1
tmpdb=> select * from emp;
empono|ename |job
------+----------------+-------
1003|hoge bar |Manager
(1 row)
#!/usr/bin/perl -w
use Pg;
$dbname = 'tmpdb';
$SIG {PIPE} = sub { print "broken pipe \n" };
$conn = Pg::connectdb ("dbname=$dbname");
$result = $conn-> exec ("SELECT * FROM emp");
while (@row = $result-> fetchrow) {
print " $row[0] $row[1] ";
print ("\n");
}
% chmod u+x emp01.pl
$ ./emp01.pl
1003 hoge bar
#!/usr/bin/perl -w
use Pg;
$dbname = 'tmpdb';
$SIG {PIPE} = sub { print "broken pipe \n" };
$conn = Pg::connectdb ("dbname=$dbname");
$result = $conn-> exec ("UPDATE emp SET ename = 'Wangdi' WHERE empono=1003");
if ( $ result-> resultStatus == PGRES_COMMAND_OK ){
print "OK\n"
}
else {
print "NG: $result-> resultStatus \n"
}
$ ./emp02.pl
OK
$ createuser
Enter name of user to add: nobody
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
$ psql tmpdb
tmpdb=> grant select, update on emp to nobody;
#!/usr/bin/perl -w
# use PostgreSQL perl library
use Pg;
# database name
$dbname = 'tmpdb';
$SIG {PIPE} = sub { print "broken pipe \n" };
$conn = Pg::connectdb ("dbname=$dbname");
$result = $conn-> exec ("SELECT * FROM emp;");
print "Content-type: text/html\n\n";
print "<html>\n";
print "<head><title>Sample</title></head>\n<body>\n";
while (@row = $result-> fetchrow) {
print " $row[0] $row[1] $row[2] <br>\n";
print ("\n");
}
print "</body></html>";
<html>
<body>
<?php
// Connect to Database
$conn = pg_connect("dbname=tmpdb user=nobody");
// Select
$result = pg_exec("select * from emp");
$rows = pg_numrows($result);
$columns = pg_numfields($result);
echo "<table border=1>";
for ($r=0;$r<$rows;$r++) {
print "<tr>";
print "<td>" . pg_result($result,$r,0);
print "<td>" . pg_result($result,$r,1);
print "<td>" . pg_result($result,$r,2);
print "</tr>\n";
}
echo "</table>";
// Close Database
pg_close($conn);
?>
</body>
</html>
|
... |
#tcpip_socket = false
tcpip_socket = true
[hoge@server hoge]$ psql
hoge=# alter user hoge with password 'hogeword';
|
#!/bin/bash |
|
$ pg_dumpall > backup_file_name |
|
$ pg_dumpall -c > backup_file_name |
|
$ pg_dumpall -D > backup_file_name |
|
$ createdb dbname |
Sets the number of shared-memory disk buffers for the postmaster to allocate for use by the backend server processes that it starts. This value defaults to 64 buffers, where each buffer is 8k bytes (or whatever BLCKSZ is set to in src/include/config.h).
PGOPTS="-B 1025 -i -S -D/usr/local/pgsql/data"
Sets the number of sort buffers. This value defaults to 512 buffers, where each buffer is 512k bytes.
PGOPTS="-o '-S 1024' -i -S -D/usr/local/pgsql/data"
PGOPTS="-o '-F' -i -S -D/usr/local/pgsql/data"
Copyright © [- SroNey / JohN -]. All rights reserved