The default parameters that are used by Postrgres (the
postmaster process ) at startup are found in a file named
postmaster.opts under the database system to be started. These parameters can be overriden by : environment variables, and flags passed to the postmaster command; this could be considered a washed down version of the
init(SID).ora file used in Oracle, as it takes few parameters, like: port to bind,number of server process, debugging level,etc...In postgres there is also an optional parameter file named:
pg_options which is also located under the databasesystem home directory ($PGDATA), this file enables run-time modification to trace files and other server process parameters. This file contains simple text,like the following:
There are approximatly 30 options for this configuration file, one of the advantages of this configuration file is that, its not necesary to kill the postmaster process for the changes to take effect on the current server process , the postmaster process can be given a SIGHUP signal and all the parameters will take effect on the active server processes (a.k.a.Sessions in Oracle).
A Postgres administrator needs to watch one process , this process is called postmaster , and it may be executed directly from the shell or via the
pg_ctl utility (used to start,stop and restart) , in whatever manner the postmaster process is started it must be under the postgres superuser typically named
postgres , it cannot be run under root
postmaster -D /u01/database
-D flag indicates the location of the databasesystem, it should be noted that if the $PGDATA environment variable is available the command
postmaster will do.
The previous command starts the postmaster process in the foreground,this is very useful for debugging purposes,but in order to run the
postmaster process in the background,there are two posiblities:
postmaster -d > postgres.log 2gt;&1 &
The previous command runs
postmaster in the background and pipes the standard error to the file named
postgres.log one other possibility is:
This also runs postmaster in the background (no need for &, this is done automatically) , however the standard error is lost, its piped out to /dev/null/ by default.
Other posibilities include specifying the port on which postmaster will receive connections (Analoguous to Oracle's listener on port 1521), this is done with the
-p flag, also available as environment variable PGPORT (Postgres defaults to port 5432).
NOTE: If no flags or environment variables are provided, Postgres reads values from the
postmaster.opts file located under $PGDATA ( database system), still at a mininmum the $PGDATA env.variable or -D flag must be provided.
The utility named
pg_ctl is used to automate the startup,stopping,and restarting of the
postmaster process , this utility is analogous to the
mount, startup,shutdown commands rolled up into one and provided from "svrmgr" in Oracle.
postmaster process, this command makes use of the $PGDATA environment variable, once this is read it sources the default startup postmaster parameters at
$PGDATA/postmaster.opts.default , these defaults (as mentioned earlier) can be overridden by environment variables or flags.
With a default installation the
pg_ctl start implies
pg_ctl -S which indicates silent mode this is not recommendable since all log information is sent to
/dev/null , you should at least modify the parameters in
$PGDATA/postmaster.opts.default to pipe standard error to logs, this is done with:
>>postgres.logfile 2>&1 &
The previous line instructs Postgres (
postmaster process) to send all the Standard Error to a file named
postgres.logfile , be aware that this file will be placed in the PWD("Present Working Directory") if not given a full path; other parameters that may be included in this file are
-i which is extremly important if you plan to connect from an external application as this allows TCP/IP communication.
To stop the
postmaster process Postgres database, you can issue:
This shutdowns the database appropriatly, this command can also take the flags
-m with the parameters
smart,fast or immediate
pg_ctl -m smart stop #Waits for all users to logout (Oracle analagous to Shutdown) pg_ctl -m fast stop # All active transactions get rolled back (Oracle analagous to shutdown transactional) pg_ctl -m immediate stop #Waits # Immediate shutdown will need recovery next time up (Oracle analoguos shutdown abort)
And the final option for
restart , this command can also take the flag
-m available to
pg_ctl stop , keep in mind that
pg_ctl needs to know the home of the database system, this can be provided with the -D flag or through the environment variable
When the postmaster process is started the database system and the databases composing it are open automatically, Postgres has no notion of mounting and opening a database (as Oracle), every database in the database system becomes available once the postmaster process is available.
In order to kill the Postgres database you should use the SIGTERM, SIGHUP or SIGINT process, you should not use the default SIGKILL (-9) signal , as this will prevent the
postmaster process from freeing all shared resources on the system, so if the process number for
postmaster is 3432 (available through the
ps -aux command or the
postmaster.pid file under the database system home.):
kill -SIGTERM 3432
You could also use the
pg_ctl stop mentioned previously to avoid this problem.
As mentioned previously, the parameters are specified as plain text in
To limit the number of server process in postgres the parameter
-N (for maxNbackends) may be used at postmaster startup , this parameter can also specified by a flag or provided at the default startup file
You will need to create a postgres superuser and have access to gmake in order to compile postgres.
Postgres also makes use of environment variables, the ones to be aware of are :
PGDATA: The directory where the databasesystem will reside.(Can be overriden by the -D flag) PGPORT: The TCP/IP port where postgres (postmaster process) will be listening on, or the TCP/IP that will be used by client to connect to. (Available through the -p flag) PGDATABASE: The name of the database in the databasesystem to connect to. PGLIB: The location of the postgres librariries (By default:
/usr/local/pgsql/lib) PGUSER: The name of the user used to connect to the postgres database.(Available with the -U flag) PGHOST: The name of the Host that the Postgres server resides on.
Defaults on the
postmaster.opts are reasonable.
In order to create a Database, Postgres must first create what is called a Database system , this Database system consists of creating a directory structure, database files where data will live, shared catalog tables,etc. (Analoguos to Oracle would be in the same order: OFA (Oracle Flexible Arcquitecture),Data Files,Data Dictonary)
To create a database system in Postgres you need to execute the following:
mkdir /u01/database chown postgres /u01/database su -postgres /usr/local/pgsql/bin/initdb -D /u01/database
The previous statement first creates a directory (
/u01/database) and changes its ownership to the
postgres user, then as user
su ) it executes the
initdb command; it is
initdb that creates the underlying structure of the Database system under the directory specified by the
-D parameter .
NOTE: That the directory and command execution must be under the
postgres superuser , these cannot be executed as root.
It may be convenient to define the PGDATA environment variable, this will avoid the need to pass any flags
-D at database system creation, (Analoguous to Oracle: consider PGDATA the ORACLE_SID environment variable mixed with ORACLE_HOME & ORACLE_BASE, it indicates where to find information the datafiles,and configuration information)
In order to create a database within the Databasesystem previously created, you must either issue the
creatdb command from the shell or the
create database from psql, both parameters take environment variables, flags or default values; the default action when a database is created is to place it in the
$PGDATA/base directory of the databasesystem in question; it should be noted that every database created will contain approximatly 60 tables , these tables are copied from a special database called
template1 (also under the $PGDATA/base), the name is pretty self-explanatory, it allows every database to contain a minimum amount of configuration and default values, these tables are called System Catalogs and are prefixed with the letters
" pg " , these are similar to Oracles Data Dictionary tables.