Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Architecture Components

 I.Postgres Architecture Components
             a.Describe the Postgres Server Architecture and its main components.
Postgres is formed from:
                          1.A Database system which forms the backbone of the database and consists of a : directory structure, database files where data will live, shared catalog tables,etc.
                          2. postmaster process which manages every incoming connection destined to the Database system.
                          3.Server Processes , which interact directly with every request, once thepostmaster process has finished its work.
                          4.libpq libraries :The libpq libraries are used by every client application to connect to the postmaster process,compared to Oracle these are very similar to the OCI libraries used to establish communication to the database.
                          5.Client side : The client side application can be composed of a command execution environment like psql (similar to SQL*Plus in Oracle), or a special driver to be used with a web-server, these clients must be desinged to "hook" into the libpq libraries which will eventualy communicate with the postmasterprocess.
             b.List the Structures involved in connecting a user to Postgres Server Process
                          A.The client ("psql" or "web-server driver") makes a call through the psql libraries to an established TCP/IP port (5432 default),to a database from the database system. (Oracle comparasion: When a connection is established through a descriptor like:sqlplus daniel/rubio@server1:1521 )
                         B.The postamaster process is listening on a certain TCP/IP port (5432 default), and when it sees the request from a client, it reads the connection descriptor, and passes this connection directly to a server process.(In oracle this would be the Listener process)
                         C.The Server Process will be in charge of manipulating the information in the database specified in the client descriptor, it should be noted that when the Server Process is invoked, the communication with the client is made directly, that is, the postmasterprocess abandons the communication circle.
                         D.The number of server process that can be invoked on any database system is 32 by default, but this can be as high as 1024 if the system supports it.
                         E.It should also be noted that since the libpq does not support multiple threads, it limits thepostmaster and server process to operate on the same machine (Oracle is not limited in this sense), however it is still possible to have a client (Web-server driver) operating on a different machine, since this is done through TCP ports.
              c.List the Stages in processing:queries,DML statements, COMMITS.
II.List the Stages in processing:queries,DML statements, COMMITS.
              b.Install the database
After untaring the Postgres file just type the following commands:
shell# .configure
shell# gmake
shell# gmake install
The previous steps install Postgres under the default /usr/local/pgsql directory. Also in order to have a working database system (in consequence databases ), you will need to execute theinitdb command which creates a database system.
            c.Setup operating system and password authentication
Postgres will need only one operating system account to make an installation, although this account can be any name, it is recommended that this user be named postgres , it is this user who will: create the database system, be the owner of the postmaster process and own thedatabase system files which compose the core of the system (database files, system catalogs...), this user is commonly called the postgres superuser and would be analoguous to the oracle/dba user in Oracle8i.
Within every database system there is a file called pg_hba.conf, this text file controls accessto every piece of the database system, it is possible to specify a great deal of access controls like : IP Address origin, kerbros authentication, database..etc.(Note: It mentions database, recall that a databasesystem is composed of various databases).
Examples of this file:
#This following line allows any user on the local host to connect to any database using any username.
 host         all     trust    
#This next line indicates that any user (client) coming from IP address 192.168.93.x
#is allowed access to the trabajos database, (note that this is only  a  database, not the database system),
#and will be authenticated via the password file name passwd located under the database system  home ($PGDATA).
host         trabajos password passwd
The passwd file
As mentioned earlier the pg_hba.conf can make use of a password file that will reside in thedatabase system home ($PGDATA), this file by convention only is named passwd and has entries similar to the /etc/passwd on *nix systems. While you can edit this file with a text editor, Postgres provides the pg_passwd to manipulate this file,in order to use this utility you must be in your database system home directory ($PGDATA),as pg_passwd attempts to write to the current directory.
Depending on the client you are using to connect to postgres (psql or web-driver), you will need to feed parameters in a certain manner(e.g.: psql uses the -U flag to indicate which user to connect as.)
The pg_shadow table
The pg_hba.conf provides a first level of access control, however there is a table namedpg_shadow which contains an extra level of granularity for the whole database system, this granualrity comes in the form of letting a user create databases in the database system , create other users to connect to a database; this would be the DBA_USERS table in Oracle with only two types of access privileges(create new user,create database).
To add a new user to the pg_shadow table the command createuser is used, this command can be issued directly from the shell if the user has the appropriate privileges; another possbilitiy to add a user to the pg_shadow table is with the create user in psql , this allows for finer settings for the user like: password expiration, specific userid and group membership.                                            
III. Managing the Postgres Postmaster process
             a.Create the Parameter File
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-timemodification 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).
             b.Startup and open the database
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 thepostmaster.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 thepostmaster 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 -iwhich 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 
# 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 topg_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 postmasterprocess is available.
              c.Close the Database and Shutdown the Instance
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 systemhome.):
kill -SIGTERM 3432
You could also use the pg_ctl stop mentioned previously to avoid this problem.
             d.Get and set parameter values
As mentioned previously, the parameters are specified as plain text in postmaster.opts.defualt .
             e.Manage Sessions
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
IV.Creating the Database
             a.Prepare the Operating System
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 database system 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.
         b.Prepare the parameter file :
Defaults on the postmaster.opts are reasonable.
         c.Create the Database :
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 initdbthat 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.

 I.Create System Catalogs (Data Dictonary Views) and the Standard Packages.
             a.Construct the Data Dictonary views
As mentioned in the previous sections, when the command initdb is executed to create theDatabase system, the System Catalogs are also created under the $PGHOME directory, as well as under the special database named template1 which will form the skeleton for other databases created under the databasesystem.
             b.Prepare and Install the PLPGSQL,PLTCL,PLPERL environment
To install a procedural language in Postgres the createlang command available from the shell is used, in order to use this command it is recommended that the PGLIB environment variable be set, this variable should point to the: /usr/local/pgsql/lib directory (the Postgres libraries), the variable can also be set with a flag (-L) at createlang execution. It should be noted that by default no procedural language is active on any database,
NOTE: active; the difference betweenactive and installed is that the installation refers to the installation-library level, while the activation is done at the more granular database level, this will be made clearer in the following sections.
Install (Activate) PLPGSQL
In order to activate the PLPGSQL (Procedural Language PG(Postgres)SQL) the commandcreatelang will require at a mininmum two parameters, the library location (provided with the $PGLIB variable or -L flag) and the database (provided with the $DBNAME or -d flag), upon execution, the language should be typed in at the prompt:
[postgres@servidor1 db]$ createlang -d workload
Language to install in database workload: plpgsql
[postgres@servidor1 db]$
Install (Activate) PLTCL & PLPERL
To activate these procedural languages the process is a little more involved than for PLPGSQL, this is due to the fact that they need to be installed in the system ; these procedural languages requiere the shared libraries pltcl.so and plperl.so , the default installation/compile does not create these shared libraries, so they will have to be installed in the /usr/local/pgsql/libdirectory. To do this there are two possibilities:
                          1.Recompile and reinstall postgres with the --with-tcl flag. (This in no  way affects existingdatabasesystems or databases)
                          2.Go into the source tree of your intial compilation and under the directory /src/pl locate the perl and tcl directories, these will contain the source (.c) for obtaining the pltcl.so andplperl.so libraries, these should then be placed in the /usr/local/pgsql/lib library.
To successfully install the pltcl.so and plperl.so correctly, the system you are installing Postgres on will have to have the appropriate Tcl Interpreter and Perl Interpreter , this is necessary because Postgres does not come with a procedural language environment so in order for procedural languages to be executed it will rely on the "Host's" installed packages.
Activate Languages by Default
In order to activate languages by default on a new database, this can be done by activatingthe desired languages in the template1 database located under the $PGDATA/base directory, this is made possible because every newly created database generates its base tables from thistemplate database
              c.Administer installed languages and stored procedures.
Every database in a databasesystem contains a table named: pg_language, this table contains al the active procedural languages for the database. This table can be queried directly from the shell with the createlang -d workload -l command, it would display the following:
[postgres@servidor1 postgres]$ createlang -d workload -l
     Procedural languages
  Name   | Trusted? | Compiler
 plpgsql | t        | PL/pgSQL
 pltcl   | t        | PL/Tcl
(2 rows)
            d. Administer stored procedures and packages
Information about the defined functions in a database can be queried with the \df (regexp)command in PSQL (Postgres's SQL*Plus), the \df+ (regexp) can also be used to display more detailed information like language (plperl,pltcl..) and description. If no (regexp) is provided allfunctions will be returned, if specified it returns those functions matching the regexp.
        e.List the types of database event triggers
A trigger is executed implicitly whenever the triggering event happens,and the trigger doesnt accept arguments. Triggers are used to:
                        a.  Maintain complex integrity constraints not possible through declarative constraints enabled on the table.
                        b.  Auditing information in a table, by recording the changes made and who made them.
                        c.  Automatically signalling other programs that action needs to take place, when changes are made to a table.
The general syntax is :
CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT| DELETE | UPDATE] triggering_event
ON table_reference FOR EACH [ROW]
EXECUTE PROCEDURE procedure (args);
Where triggering_event specifies when the trigger fires, table_reference is the table for which the trigger is defined, and procedure (args) is the main code for the trigger.
Compared to Oracle, Postgres cannot define the trigger event or action directly in the declaration, Postgres depends on creating a separate function to be executed on the trigger, this has the disadvantage of splitting the trigger logic in various parts, but it has the advantage of calling any triggering action (function) within Postgres support: Perl, Tcl or built-in Sql.
The triggering event determines the type of the trigger. Triggers can be defined for INSERT, UPDATE, or DELETE operations. They can be fired before or after the operation, in the current version Postgres (7.0) they can only be fired on row operations (Oracle allows statement operations). The values for the statement, timing and level determine the type of the trigger. There are a total of 6 possible types: 3 statements,2 timing and 1 level.
Defines which kind of DML statements causes the trigger to fire
Defines wether the trigger fires before the statement is executed or after the statement is executed
Row (Statement not currently supported)
If the trigger is a row-level trigger, it fires once for each row affected by the triggering statement. If the trigger is a statement-level trigger (not supported) it fires once , either before or after the statement. A row-level trigger is identified by the FOR EACH ROW clause in the trigger definition
 DROP TRIGGER [name] on [table name] <trigger_name>You can drop the trigger with the following commands:
NOTE: There is no command for enabling|disabling a trigger in Postgres like Oracle.To display the triggers defined in a database you have to query the object descriptors with the argument "triggers", from PSQL: \dd triggers
Maintaining the Control Files
To the extent the author knows, there is nothing similar to a control file in Postgres , all information regarding the physical structure (file location) of a database is located in the system catalogs.

Maintaining the Redo Log Files
  • Other than the fact that Postgres complies with the "readers wait for writers" database adage, that is, readers will consistently see only commited data , and writers must wait for any other data that is locked or under transaction, it is not clear if the transaction history made on the database is or can be piped out to a particular file and later reapplied , to conform what would in "Oracle-speak" be called a Redo-Log file, facilitating the rollback to a certain transaction or time functionality.
  • This issue brings up another interesting question, since all uncommited data is kept in memory,what happens if a long query/transaction is executed and the memory is exhausted or overwritten by another transaction, what happens if a user tries to query this overwritten data, will a SNAPSHOT TO OLD appear like Oracle, is there any manner to write this out to a file before its overwritten ? Basically creating an online redo-log file ? If you know how, please email me so I can add it here.

 Managing Tablepace and Data Files
There is no such logical structure in a Postgres database, everything is kept directly in system files, this would be similar to having Oracle data files , but without any extra logical divisions (a.k.a tablespaces)
  •   Storage Structure and Relationships
  •    Managing Rollback Segments
  •    Managing Tables
  •    Managing Indexes
  •    Maintaining Data Integrity
  •    Loading Data
  •    Reorganizing Data
  •    Managing Password Security and Resources
  •    Managing Users
  •    Managing Priviliges
  •    Managing Roles
  •    Using National Language Support


Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL