gunzip postgresql-version.tar.gz
tar xvf postgresql-version.tar
./configure --prefix = // default path is /usr/local/pgsql
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
It is recommended to create a separate user to own the PostgreSQL files
and processes that will be installed. The user name is typically postgres.
By default, POSTGRESQL allows database access only to users logged into
the computer running the database server.
To enable network access, the postmaster must be started with the -i flag.
Many distributions use a configure command that allows users to choose
various options before compiling and installing the software.
PostgreSQL is usually distributed in source code format.
As a consequence, C source code must be compiled into a format that is
understood by the CPU. This process is handled by cc or gcc compiler.
When PostgreSQL is installed, it creates files in its home directory,
typically /usr/local/pgsql. This directory holds all the files needed
by PostgreSQL in various subdirectories:
PostgreSQL command-line programs, such as psql.
Configuration files and tables shared by all databases.
A subdirectory for each database.
Using the du and ls commands, administrators can display the
amount of disk space used by each database, table, or index.
PostgreSQL documentation.
Include files used by various programming languages.
Libraries used by various programming languages.
This subdirectory also contains files used during initialization
and sample configuration files that can be copied to /data and modified.
PostgreSQL manual pages.
Initialization creates a database called template1 in the PostgreSQL
home directory. This database is used to create all other databases.
Initdb performs this initialization step:
$PGSQL_DIR/bin/initdb -D $PGDATA
The "-D" option specifies the location where the data will be stored.
Just make sure that the server account can write to the directory
(or create it, if it doesn't already exist) before starting "initdb".
Once template1 is created, the database server can be started.
This step typically involves running the program called postmaster.
To start up the database server:
a). $PGSQL_DIR/bin/pg_ctl start -l logfile -D $PGDATA
b). in the foreground:
$PGSQL_DIR/bin/postmaster -D $PGDATA
c). in the background use:
$PGSQL_DIR/bin/postmaster -D $PGDATA >logfile 2>&1 &
To stop a server:
$PGSQL_DIR/bin/pg_ctl stop -l logfile -D $PGDATA
Once the database server is running, you can create databases by running
createdb from the operating system prompt:
$PGSQL_DIR/bin/createdb testdb
Initially, only the POSTGRESQL superuser can create new databases.
Other users can be given permission to create new databases.
The createdb program creates a new database by making a copy of the template1 db.
This database is created when POSTGRESQL is first initialized. Any modifications to
template1 will appear in subsequently created databases.
To see a list avalable databases use following command:
$PGSQL_DIR/bin/pg_ctl start -l
To connect to database testdb enter:
$PGSQL_DIR/bin/psql testdb
At the prompt you can enter SQL commands and start working with PostgreSql.
To switch to amother database:
testdb# \connect newdb;
Use the psql commands '\z' or '\dp' to list the access permissions on the open database,
or view a list of groups and its members by querying system tables or catalogs.
SELECT * FROM pg_shadow; -- displays all postres users
SELECT * FROM pg_group; -- displays all groups and the usesysid of its members
Client authentication is controlled by the file pg_hba.conf in the data directory:
$PGDATA/pg_hba.con.f
A default pg_hba.conf file is installed when the data area is initialized by initdb.
Separated from UNIX users, and global across a database cluster installation.
To create a user: CREATE USER name.
To remove an existing user: DROP USER name.
The psql program uses the -U command line option to indicate the user to connect as.
: a database superuser bypasses all permission checks. Also, only a superuser can
create new users. To create a database superuser: CREATE USER name CREATEUSER.
database creation: a user must be explicitly given permission to create databases
(except for superusers). To create such a user: CREATE USER name CREATEDB.
password: a password is only significant if password authentication is used for client authentication.
Database passwords are separate from operating system passwords.
To create user with password: CREATE USER name PASSWORD 'string'.
A user's attributes can be modified after creation with ALTER USER.
To create a group:
CREATE GROUP nameTo add users to or remove users from a group, use:
ALTER GROUP name ADD USER uname1, ...
ALTER GROUP name DROP USER uname1, ...
$ createuser demouser1
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 test
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
test=> CREATE USER demouser2;
CREATE USER
test=> ALTER USER demouser2 CREATEDB;
ALTER USER
test=> CREATE GROUP demogroup WITH USER demouser1, demouser2;
CREATE GROUP
test=> CREATE TABLE grouptest (col INTEGER);
CREATE
test=> GRANT ALL on grouptest TO GROUP demogroup;
CHANGE
test=> \connect test demouser2
You are now connected to database test as user demouser2.
test=> \q
$ createdb demodb1
CREATE DATABASE
$ psql test
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
test=> CREATE DATABASE demodb2;
CREATE DATABASE
test=> DROP DATABASE demodb1;
DROP DATABASE
test=> \connect demodb2
You are now connected to database demodb2.
demodb2=> \q
A database is then destroyed, and a connection made to a new database.
-------------------------------------
Catalog Name Purpose
pg_aggregate aggregate functions
pg_am index access methods
pg_amop access method operators
pg_amproc access method support procedures
pg_attrdef column default values
pg_attribute table columns ("attributes", "fields")
pg_class tables, indexes, sequences ("relations")
pg_database databases within this database cluster
pg_description descriptions or comments on database objects
pg_group groups of database users
pg_index additional index information
pg_inherits table inheritance hierarchy
pg_language languages for writing functions
pg_largeobject large objects
pg_listener asynchronous notification
pg_opclass index access method operator classes
pg_operator operators
pg_proc functions and procedures
pg_relcheck check constraints
pg_rewrite query rewriter rules
pg_shadow database users
pg_statistic optimizer statistics
pg_trigger triggers
pg_type data types
UPS/UPD Declaring and Building the product
To get info:
ups list -a postgres
ups list –a mysql
To see product in fnkits: ftp://ftp.fnal.gov/KITS/Linux/postgres/
Pre-build Checklist
Build the Product
add to the .k5login for "updadmin" on fnkits.fnal.gov a.k.a ftp.fnal.gov.
Then you can ssh/rsh/telnet as updadmin.
Declare the Product in UPS
1. create new dir for the version under > cd
/export/products/Linux/postgres/ (or SunOS, IRIX, etc.)
2. Copy all from working area: cp -r /usr/local/devel/…./* .
3. Give permission to group to read my dir chmod -R g+r var to execute:
chmod -R g+x var chmod g+x cdofprod/ mysql/ test
4. Check: group name should be products in all these dirs before declare
UPD!
5. Declare new product in UPS
shell> ups declare product version -f flavor [Linix/SunOS/IRIX]
Options: -c make this version current (t - test)
-r /path-to-product
-m name of table file
-M /path-to-table-file
-z /path-to-ups-database
6. If declare was O.K. but in case of other problems, just repeat configure:
> ups configure mysql v3_23_51 -f Linux+2
8. To change current from previous version:
> ups undeclare -c mysql -f Linux+2.2
To declare as test:
> ups declare -t mysql
9. In UPS there 2 files: .version and .table file
10. to verify that the product was declared properly run this and the new
product should be in the list
>ups list -a <product name>
Add the Product to UPD
Make sure all misc. files from testing have been
cleaned up.
chmod on the directories and files to make them all 755
Make sure all directories and files are owned by the products group
When we create kits in FNKITS:
ftp.fnal.gov/KITS/Linux/mysql/v3_23_51/
There should be: tar file and table file
> upd addproduct <product_name> v0_0_0 -f SunOS+5
If this is successful you will see the new product in upd
>upd list -a <product name>
INSTALL PRODUCT INTO AFS VOLUME (on FNALU):
(Do it on fsui02 machine where works upd_volrelease script)
upd install mysql_python v1_0_0 -f SunOS+5 -z /afs/fnal/ups/db
upd install mysql v4_0_18 -f SunOS+5 -z /afs/fnal/ups/db
If need to update:
cd /afs/fnal.gov/ups/db/mysql-python
add "." to vi and then upd_volrelease:
vi /afs/.fnal.gov/ups/db/mysql-python/v1_0_0.version
upd_volrelease /afs/.fnal.gov/ups/db/mysql-python/v1_0_0.table
Sometimes need: > ups do_usr_local k5arc
to copy the upd_volrelease script to /usr/local/bin on that system,
and you do a "upd install -z /afs/fnal/ups/db ..."
Trouble Shooting UPS/UPD
LogFile example:
---------------- Lock file "/tmp/.s.PGSQL.5432.lock" already exists. Is
another postmaster (pid 9843) using "/tmp/.s.PGSQL.5432"? /UPS_PROD_DIR/bin/postmaster:
cannot create UNIX stream port It means there is another postmaster with pid=9843
is running! Use: %kill -9 9843 OR shut down postmaster usung pg_ctl: %pg_ctl
-D $PGDATA -l logfile stop To stop postmaster with "pg_ctl" command, file
postmaster.pid should be available in PGDATA dir for this database cluster.
Then you can start postgres postmaster again! See Step 5.
Tailoring errors:
$PGDATA environment variable blank
$PGPORT environment variable blank
If you set up your local source to create these variables the tailoring
process cannot reset them to what you enter. You must remove these
variables from your source. Once they are removed the tailor will
successfully add and set the variables. Once you have edited the source
file you must exit and reconnect so the environment variables are cleared
out.
UPD errors:
When adding product: upd addproduct postgres v8_2_2 -f Linux+2.4
Error output of move_archive_file: rsh: relocation error: /usr/krb5/lib/libkrb4.so:
symbol errno, version GLIBC_2.0 not defined in file libc.so.6 with link time
reference
upd addproduct failed.
Sent error to helpdesk and Marc Mengel, awaiting resolution.
Mail
comments to:css-dsg@fnal.gov
Last Modified March, 14 2007