Postgres Architecture and Administration (2 of 3)

  1. Managing the Postgres Postmaster process

    1. Create the Parameter File

      2. 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:

       
verbose=2
query=4

      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).

    2. Startup and open the database

      3. 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

      Using the postmaster command: 

       
postmaster -D /u01/database

      The -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:

       
postmaster -S

      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 pg_ctl utility

      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.

       
pg_ctl start

      Starts the 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:

       pg_ctl stop

      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 pg_ctl is 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 $PGDATA

      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.

    3. Close the Database and Shutdown the Instance

      4. 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.

    4. Get and set parameter values

      5. As mentioned previously, the parameters are specified as plain text in postmaster.opts.defualt .

    5. Manage Sessions

      6. 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 postmaster.opts

  2. Creating the Database

    1. Prepare the Operating System

      2. 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.
    2. Prepare the parameter file :

      3. Defaults on the postmaster.opts are reasonable.

    3. Create the Database :

      4. 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 postgres ( 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.