Postgres Architecture and Administration (1 of 3)

  1. Postgres Architecture Components

    1. Describe the Postgres Server Architecture and its main components.

      2. 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. A postmaster process which manages every incoming connection destined to the Database system.
        3. Server Processes , which interact directly with every request, once the postmaster 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 postmaster process.
    1. List the Structures involved in connecting a user to Postgres Server Process
        1. 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 )
        2. 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)
        3. 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 postmaster process abandons the communication circle.
        4. 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.
        5. It should also be noted that since the libpq does not support multiple threads, it limits the postmaster 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.
    2. List the Stages in processing:queries,DML statements, COMMITS.

  1. List the Stages in processing:queries,DML statements, COMMITS.

    1. Install the database
      2. 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 the initdb command which creates a database system.

    2. Setup operating system and password authentication

      3. 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 the database 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 access to 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         127.0.0.1    255.255.255.255     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   192.168.93.0 255.255.255.0 password passwd

      The passwd file

      As mentioned earlier the pg_hba.conf can make use of a password file that will reside in the database 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 named pg_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.