PostgreSQL OpenSource database product under name "postgres" is created in fnkits
for Linux+2.2(redhad release 6.x), Linux+2.4(redhat release 7.x), SunOS+5.6,
SunOS+5 and IRIX+6.5.
To install and start using it on your local machine you do not have to be a root user.
Initially there is no data directory in the postgres product instance and the users
will have a choice to configure PGDATA directory to create their own database cluster.
1. Postgres product installer should be in a postgres groupname.
2. If postgres installer is a root user then it has to be postgres groupname
on this machine: ups tailor will change groupname root to postgres to access
postgres databases by postgres server.
3. If you are going to install postgres product on AFS space be sure you have
the rights to work with AFS Read-only system! It is not recommended to create
postgres databases on AFS space! Postgres server will not work on AFS.
4. The base installation with an empty postgres databases requires about 20MB.
5. Directory you are going to use to install postgres databases
will be created, if not exists, or should be empty!
1. Execute: newgrp products
2. Set $PRODUCTS to point to specific ups database on your machine.
2a. newgrp postgres2c. setup postgres version -f flavor
3. Install postgres product in ups database:
upd install postgres Version -f Flavor
4. Configure postmaster and build database cluster:
ups tailor postgres version -f flavor
5. Start postgres server: ups start postgres
6. Create user database: ups createdb postgres
7. Start postgres client: ups client postgres
8. Stop postgres server: ups stop postgres
There are also some ups functions you can use:
ups createuser postgres - add a new user
ups dropuser postgres - remove username
ups dropdb postgres - drop existing database
ups showdb postgres - list available databases
ups vacuum postgres - prompt to vacuum database or specific table
ups tailor postgres
Ups tailor section is designed to configure postmaster (postgres server)
and build postgres database cluster.
There are following options user can to consider:
The default data directory = /usr/local/postgres
Is this the place you want to configure postgres database cluster (y/n)?
n
Enter your path:
/.../"data"
NOTE: "data" directory will be created if not exists or should be empty!
Default username for postmaster = postgres
You can type another username or press Enter to accept the default.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
"The files belonging to this database system will be owned by user [....].
This user must also own the server process."
creating directory /(path-to-data)/data... ok
creating directory /(path-to-data)/data/base... ok
creating directory /(path-to-data)/data/global... ok
creating directory /(path-to-data)/data/pg_xlog... ok
creating directory /(path-to-data)/data/pg_clog... ok
creating template1 database in /(path-to-data)/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
creating system views... ok
loading pg_description... ok
vacuuming database template1... ok
copying template1 to template0... ok
Success. You can now start the database server using:
PGSQL_DIR/bin/postmaster -D /(path-to-data)/data &
or
PGSQL_DIR/bin/pg_ctl -D /(path-to-data)/data -l logfile start
NOTE: During installation process user will be prompted to set superuser password.
"Postmaster successfuly configured" - means the end of configuration process.
NOTE: Group ownership for files in configured database cluster should belong to postgres
group name. GID reserved for postgres is xxxx.
If postgres group name does not exist on user's machine, there will be message with
request to add an entry for postgres group to the user's system /etc/group file or
NIS group map.
If there are going to be multiple databases to be managed by different
UNIX acccounts, these account names should be added to the group entry also.
In postgres data directory you can find:
1. postgresql.conf - configuration file that user can edit.
2. pgsql.port - flat file with port number for ups table file to use.
3. pg_hba.conf - control file user can edit the access options to postgres databases.
4. logFile - postmaster log file
5. README file - user can add additional notes regarding postgres commands and etc.
fo rhis own use.
The postgres database cluster name will be saved in UPS_THIS_DB/postgres/config/hostname. file.
List configured postgres servers for specific machine will be created in
UPS_THIS_DB/postgres/config/hostname.postgres-servers.
If for some reason, you would like to remove specific server from your machine, then
- shutdown this server;
- edit hostname.postgres-servers file in UPS_PROD_DB/postgres/config directory
removing all records related to this server;
- remove your postgres datadir;
ups tailor postgres
There will be additional prompts to described above:
Postgres server with server_id = 1 was already configured on [hostname] machine.
Would you like to configure the next server on this machine (y,n)?
ups start/stop -O "user-option".
Enter: any name you would like to use as your identifier. the identifier is normally your application name, example d0_hdw
When Postmaster successfuly configured use: ups start postgres -O 'identifier'
to start postmaster for additional postgres server.
The user-defined datadir name will be saved in UPS_THIS_DB/postgres/config/hostname.identifier file,
and records related to this server will be added to hostname.postgres-servers file.
shell> ups start postgres [-O "user option]
shell> ups stop postgres [-O "user option]
Postgres logfile, postmaster.pid and pg_hba.conf are created in PGDATA dir;
In case of problem executing "pg_ctl" or "psql" check logfile first.
To configure remote postgres client:
1 Install postgres product in ups database:
upd install postgres Version -f Flavor
2. Setup postgres product environment:
setup postgres
3. Start postgres client:
shell>psql [dbname] -U user -p port -h socket -W password
Database name, port number and socket name for the specific server should be provided
by postgres server sysadmin for corresponding postmaster.
To start postgres client on local node:
shell> ups client postgres [-O "user option]
Please, enter user name and database name as "username"@"dbname"
Password: "password"
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
testdb=# \q
At the prompt you can enter SQL commands and start working with PostgreSql.
To switch to amother database: testdb# \connect newdb;
1. Show list available databases in database cluster:
----------------------------------------------------
shell> ups showdb postgres [-O "user option]
Please, enter your username: postgres
List of databases
Name | Owner
-----------+----------
template0 | postgres
template1 | postgres
test | postgres
test1 | postgres
test2 | postgres
testdb | postgres
(6 rows)
2 Create a new user: shell> ups createuser postgres [-O "user option]
-----------------------------------------------------------------------
Please, enter user name to create and superuser name as: newuser@superuser.
"newuser"@postgres
Shell a password be assigned for the new user? (y/n)y
Shell the password be encrypted? (y/n)y
Enter password for user "newuser":
Enter it again:
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) y
Password:
DROP USER
4. Create new database: shell> ups createdb postgres [-O "user option]
-------------------------------------------------------------------------
Please, enter user name and database name you wish to create as username@dbname.
postgres@test3
Password:
CREATE DATABASE
5. Drop database: shell> ups dropdb postgres [-O "user option]
---------------------------------------------------------------
Please, enter user name and database name to create as username@dbname.
postgres@test3
Password:
DROP DATABASE
6. Compress database: shell> ups vaccuum postgres [-O "user option]
-----------------------------------------------------------------------
Please, enter user name and database name you wish to vacuum as username@dbname.
postgres@test2
Vacuum all databases (y,n)?
n
Please, enter table name to vacuum specific table only,
Or press enter to vacuum test2 database.
"table name"
Shell be full vacuuming (y,n)?
y
Password:
VACUUM
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.conf
CREATE GROUP and USER:
----------------------
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
CREATE DATABASE:
----------------
test=> CREATE DATABASE demodb2;
CREATE DATABASE
test=> DROP DATABASE demodb1;
DROP DATABASE
test=> \connect demodb2
You are now connected to database demodb2.
demodb2=> \q
PostgreSql System Catalogs
-------------------------------------
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
Prepare PostgresStart & PostgresStop scripts depending on postgres deployment on box.
Example for fnalpgsdev box :
at startup <fnalpgsdev.fnal.gov> more postgresStart . /usr/local/etc/setups.sh su postgres -c "setup postgres -O content; ups start postgres -O content" su postgres -c "setup postgres -O content_int; ups start postgres -O content_int" su postgres -c "setup postgres -O minerva_hardware; ups start postgres -O minerva_hardware" su postgres -c "setup postgres -O nimi_int; ups start postgres -O nimi_int" su postgres -c "setup postgres -O osgim; ups start postgres -O osgim" at stop more postgresStart . /usr/local/etc/setups.sh
su postgres -c "setup postgres -O content; ups stop postgres -O content" su postgres -c "setup postgres -O content_int; ups stop postgres -O content_int" su postgres -c "setup postgres -O minerva_hardware; ups stop postgres -O minerva_hardware" su postgres -c "setup postgres -O nimi_int; ups stop postgres -O nimi_int" su postgres -c "setup postgres -O osgim; ups stop postgres -O osgim"
created the scripts in the /etc/init.d directory for start and stop. put links in for stop in rc6.d, rc3.d, rc1.d, and rc0.d and start links in rc5.d and rc3.d. SPECIAL NOTES: Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use rc.d directories. Whatever you do, the server must be run by the PostgreSQL user account and not by root or any other user.
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.
Mail comments to:css-dsg@fnal.gov
Last Modified March 13, 2007