Preface

1. What is PostgreSQL

  • Object-relational database management system
  • Developed at University of California at Berkeley Computer Science Department
  • Supported SQL Standard Features
    • Complex queries
    • Foreign keys
    • Triggers
    • Updatable views
    • Transactional intregity
    • Multi-version concurrency control
  • Can be extended by the user. Examples:
    • Data types
    • Functions
    • Operators
    • Aggregate functions
    • Index methods
    • Procedural languages

3. Conventions

  • Administrator: a person who is in charge of installing and running the server
  • User: anyone who is using, or wants to use, any part of the PostgreSQL system

Part 1: Tutorial

1. Getting Started

Architectural Fundamentals

  • Client/server model
  • Session consists of the following cooperating processes (programs):
    • Server process
      • Manages the database files
      • Accepts connections to the database from client apps
      • Performs database actions on behalf of clients
      • Server program is called postgres
    • Client application
      • An applicatoin that wants to perform database operations
  • Multiple concurrent connections
    • postgres process starts a new process for each connection
    • postgres process is always running
    • Clients' associated server processes come and g

Creating a Database

  • PostgreSQL user names are separate from operating system user accounts
  • When you connect to a database, you can choose which username to connect as
    • If you don't supply anything, it will default to taking the name of the user account currently logged into the operating system
  • There will always be a PostgreSQL user account that has the same name as the o/s user that started the server
    • This user always has permission to create databases

Accessing a Database

3 options:

  • Running the CLI, called psql
  • Using an existing graphical frontend tool like pgAdmin
  • Writing a custom application, using one of the several available language bindings

Part 2: The SQL Language

Part 3: Server Administration

18. Server Setup and Operation

The PostgreSQL User Account

  • It's advised to run the server daemon under a separate user account
    • This user account should only own the data that is managed by the server

Creating a Database Cluster

  • database cluster
    • A database storage area on disk which must be initialized before you can do anything
    • A collection of databases that is managed by a single instance of a running database server
    • SQL Standard calls this a catalog cluster
    • In pgAdmin this is displayed as a Server
    • Each cluster will contain a database named postgres
      • Default database for use by utilities, users and third party applications
      • Not required to exist, but many external utility programs assume it exists
    • Each cluster will contain a database named template1
      • A template for subsequently creating databases
  • In file system terms a database cluster is a single directory under which all data will be stored

Use the following command to create a new database:

The -D option specifies the file system location where the data will reside

initdb -D /usr/local/pgsql/data

  • File system
    • Recommended that the PostgreSQL user own not just the data directory, but its parent directory as well
    • Because the data directory contains all the data stored in the database, it is essential that it be secured from unauthorized access.
    • Group access:
      • read-only
      • enabling or disabling an existing cluster requires the cluster to be shut down
      • For clusters that allow access only by the owner:
        • 0700 for directories and 0600 for files
      • For clusters that allow reads by the group
        • 0750 for directories and 0640 for files

Starting the Database Server

  • Before anyone can access the database, you must start the database server
    • The name of the program is postgres
    • Must provide file system location of where data is stored. 2 Options:
      • -D option to let postgres know where the data is stored
        • postgres -D /usr/local/pgsql/data
      • PGDATA environment variable
      • If neither is provided, program will fail
  • Autostart scripts are o/s specific.
    • Examples for macos, freebsd and linux can be found here