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