Installation Notes
--prefix=/usr/local/pgsql
# default
1) ./configure --prefix=/home/chee/usr/pgsql --with-perl --with-python --enable-odbc --enable-syslog
2) gmake
3) gmake check
4) gmake install
5) add path to LD_LIBRARY_PATH, PATH, MANPATH
6) create user "postgresq" -> adduser postgres
7) Install database directory:
cd /home/chee/usr/pgsql/
mkdir data
chown postgres /home/chee/usr/pgsql/data # change owner of "data" to "postgres"
su - postgres # install db as user "postgres"
~/usr/pgsql/bin/initdb -D /home/chee/usr/pgsql/data # installing db
8) Starting the database:
/home/chee/usr/pgsql/bin/postmaster -D /home/chee/usr/pgsql/data
or
/home/chee/usr/pgsql/bin/pg_ctl -D /home/chee/usr/pgsql/data -l logfile start
This will start the server in the foreground. To put the server in the
background use something like
#### does not seem to work
nohup /home/chee/usr/pgsql/bin/postmaster -D /home/chee/pgsql/data \
</dev/null >>server.log 2>&1 </dev/null &
To stop a server running in the background you can type
### kill `cat /usr/local/pgsql/data/postmaster.pid`
kill `cat /usr/local/pgsql/data/postmaster.pid`
In order to allow TCP/IP connections (rather than only Unix domain
socket ones) you need to pass the "-i" option to "postmaster".
4. Create a database:
createdb testdb
Then enter
psql testdb
to connect to that database. At the prompt you can enter SQL commands
and start experimenting.
--with-includes=DIRECTORIES
"DIRECTORIES" is a colon-separated list of directories that will
be added to the list the compiler searches for header files. If
you have optional packages (such as GNU Readline) installed in a
non-standard location, you have to use this option and probably
also the corresponding "--with-libraries" option.
Example: --with-includes=/opt/gnu/include:/usr/sup/include.
--with-libraries=DIRECTORIES
"DIRECTORIES" is a colon-separated list of directories to search
for libraries. You will probably have to use this option (and the
corresponding "--with-includes" option) if you have packages
installed in non-standard locations.
Example: --with-libraries=/opt/gnu/lib:/usr/sup/lib.
--with-pgport=NUMBER
Set "NUMBER" as the default port number for server and clients.
The default is 5432. The port can always be changed later on, but
if you specify it here then both server and clients will have the
same default compiled in, which can be very convenient. Usually
the only good reason to select a non-default value is if you
intend to run multiple PostgreSQL servers on the same machine.
--with-CXX
Build the C++ interface library.
--with-perl
Build the Perl interface module. The Perl interface will be
installed at the usual place for Perl modules (typically under
"/usr/lib/perl"), so you must have root access to perform the
installation step (see step 4). You need to have Perl 5 installed
to use this option.
--with-python
Build the Python interface module. You need to have root access to
be able to install the Python module at its default place
("/usr/lib/pythonx.y"). To be able to use this option, you must
have Python installed and your system needs to support shared
libraries. If you instead want to build a new complete interpreter
binary, you will have to do it manually.
--with-tcl
Builds components that require Tcl/Tk, which are libpgtcl,
pgtclsh, pgtksh, PgAccess, and PL/Tcl. But see below about
"--without-tk".
--without-tk
If you specify "--with-tcl" and this option, then programs that
require Tk (pgtksh and PgAccess) will be excluded.
--with-tclconfig=DIRECTORY, --with-tkconfig=DIRECTORY
Tcl/Tk installs the files "tclConfig.sh" and "tkConfig.sh", which
contain configuration information needed to build modules
interfacing to Tcl or Tk. These files are normally found
automatically at their well-known locations, but if you want to
use a different version of Tcl or Tk you can specify the directory
in which to find them.
--enable-odbc
Build the ODBC driver. By default, the driver will be independent
of a driver manager. To work better with a driver manager already
installed on your system, use one of the following options in
addition to this one. More information can be found in the
Programmer's Guide.
--with-iodbc
Build the ODBC driver for use with iODBC.
--with-unixodbc
Build the ODBC driver for use with unixODBC.
--with-odbcinst=DIRECTORY
Specifies the directory where the ODBC driver will expect its
"odbcinst.ini" configuration file. The default is
"/usr/local/pgsql/etc" or whatever you specified as
"--sysconfdir". It should be arranged that the driver reads the
same file as the driver manager.
If either the option "--with-iodbc" or the option
"--with-unixodbc" is used, this option will be ignored because in
that case the driver manager handles the location of the
configuration file.
--with-java
Build the JDBC driver and associated Java packages. This option
requires Ant to be installed (as well as a JDK, of course). Refer
to the JDBC driver documentation in the Programmer's Guide for
more information.
--with-krb4[=DIRECTORY], --with-krb5[=DIRECTORY]
Build with support for Kerberos authentication. You can use either
Kerberos version 4 or 5, but not both. The "DIRECTORY" argument
specifies the root directory of the Kerberos installation;
"/usr/athena" is assumed as default. If the relevant header files
and libraries are not under a common parent directory, then you
must use the "--with-includes" and "--with-libraries" options in
addition to this option. If, on the other hand, the required files
are in a location that is searched by default (e.g., "/usr/lib"),
then you can leave off the argument.
"configure" will check for the required header files and libraries
to make sure that your Kerberos installation is sufficient before
proceeding.
--with-krb-srvnam=NAME
The name of the Kerberos service principal. postgres is the
default. There's probably no reason to change this.
--with-openssl[=DIRECTORY]
Build with support for SSL (encrypted) connections. This requires
the OpenSSL package to be installed. The "DIRECTORY" argument
specifies the root directory of the OpenSSL installation; the
default is "/usr/local/ssl".
"configure" will check for the required header files and libraries
to make sure that your OpenSSL installation is sufficient before
proceeding.
--with-pam
Build with PAM (Pluggable Authentication Modules) support.
--enable-syslog
Enables the PostgreSQL server to use the syslog logging facility.
(Using this option does not mean that you must log with syslog or
even that it will be done by default, it simply makes it possible
to turn that option on at run time.)
--enable-debug
Compiles all programs and libraries with debugging symbols. This
means that you can run the programs through a debugger to analyze
problems. This enlarges the size of the installed executables
considerably, and on non-GCC compilers it usually also disables
compiler optimization, causing slowdowns. However, having the
symbols available is extremely helpful for dealing with any
problems that may arise. Currently, this option is recommended for
production installations only if you use GCC. But you should
always have it on if you are doing development work or running a
beta version.
--enable-cassert
Enables assertion checks in the server, which test for many "can't
happen" conditions. This is invaluable for code development
purposes, but the tests slow things down a little. Also, having
the tests turned on won't necessarily enhance the stability of
your server! The assertion checks are not categorized for
severity, and so what might be a relatively harmless bug will
still lead to server restarts if it triggers an assertion failure.
Currently, this option is not recommended for production use, but
you should have it on for development work or when running a beta
version.
--enable-depend
Enables automatic dependency tracking. With this option, the
makefiles are set up so that all affected object files will be
rebuilt when any header file is changed. This is useful if you are
doing development work, but is just wasted overhead if you intend
only to compile once and install. At present, this option will
work only if you use GCC.
If you prefer a C or C++ compiler different from the one "configure"
picks then you can set the environment variables CC or CXX,
respectively, to the program of your choice. Similarly, you can
override the default compiler flags with the CFLAGS and CXXFLAGS
variables. For example:
env CC=/opt/bin/gcc CFLAGS='-O2 -pipe' ./configure
###############################################
Starting a session (assume database "testdb" already exist)
=>psql testdb
Information:
=>select current_user;
=>select current_timestamp;
System:
- case INSENSITIVE
- use ";" to end statement
- prompt "=>" is first prompt, "->" are subsequent prompts, ended with ";"
PHP connection:
1. In data/postgresql.conf
tcpip_socket=true
2. In data/pg_hba.conf:
# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE AUTH_ARGUMENT
local all trust
host all 127.0.0.1 255.255.255.255 trust
host all 129.94.176.241 255.255.255.255 trust
3. Run "postmaster -i ........"
User:
1. Creating user from Unix shell: createuser demouser1
2. Creating user from within pgsql:
a) start PostgreSQL by: psql
b) creating new user: CREATE USER demouser2;
3. Changing user permissions:
test=> ALTER USER demouser2 CREATEDB;
test=> CREATE GROUP demogroup WITH USER demouser1, demouser2;
test=> CREATE TABLE grouptest (col INTEGER);
test=> GRANT ALL on grouptest TO GROUP demogroup;
test=> \connect test demouser2
You are now connected to database test as user demouser2.
test=> \q
Commands:
; to end sentence
\g (go) to end sentence
\p (print) to display buffer contents
\r (reset) to erase or reset buffer
\q (quit)to exit pgsql
\l (list) to list databases in the system
\d to list all Tables in the database
ORACLE: select * from cat
\d TABLE to list all attributes or colums of the TABLE
\connect <DB> <USER> connect to database DB as user USER
\i FILE to read/run SQL script
\o FILE to print results to file called FILE
\o to switch output back to STDOUT
\t to switch off column titles when displaying query results
\z to see ownership of db objects
Data Types PostgresQL Oracle Description
----------------------------------------------------------------------
char string CHAR(n) CHAR(n) blank-padded string, fixed storage
VARCHAR(n) VARCHAR2(n) variable storage length
----------------------------------------------------------------------
number INTEGER integer, +/-2billion range
FLOAT float pt, 15-digit precision
NUMERIC(p,d) NUMBER(p,d) user-defined precision and decimal
----------------------------------------------------------------------
date/time DATE DATE date
TIME time
TIMESTAMP date and time
DATE - use
"show datestyle" or
"SET DATESTYLE TO 'ISO'|'POSTGRES'|'SQL'|'US'|'NONEUROPEAN'|'EUROPEAN'|'GERMAN'"
***** PostgresQL has even more data types than listed here.
***************
Creating table
CREATE TABLE friend (
firstname CHAR(15),
lastname CHAR(20) );
Inserting Values
INSERT INTO friend VALUES (
'Cindy',
'Anderson' );
Selecting Records
SELECT <attribute/column> FROM <relation/table>;
SELECT * FROM friend;
SELECT <attribute/column> FROM <relation/table> WHERE <attribute> <OP> <value>;
SELECT <attrib> FROM <relation> ORDER BY <attrib1, attrib2> DESC;
<OP> = {=, <, >, ........}
use "\t" in psql to omit the column titles.
Delete records
DELETE FROM <relation>; !!! delete all rows
DELETE FROM <relation> WHERE <attribute> <OP> <value>;
Update
UPDATE <relation> SET <attribute> = <value> WHERE <attribute> = <value>;
Destroying table
DROP TABLE <relation>
Input / Output data using COPY
NULL is displayed as \N
COPY table TO 'file' USING DELIMITERS '|';
COPY table FROM 'file';
COPY table FROM stdin;
COPY table TO stdout;
COPY table TO 'file' WITH NULL AS '\'; NULL as blanks
COPY table FROM 'file' WITH NULL AS '\'; NULL as blanks
COPY table FROM 'file' WITH NULL AS '?'; NULL as '?'
Copying across network
- use stdin, stdout or pgsql's \copy command
Posts
