Managing PostgreSQL on Windows
Outline
1. The pgAdmin III 2. Parts of the PostgreSQL system 3. Practices Creat a new application
localhost Port: 5432 Account: postgres Password: admin
1. The pgAdmin III
The pgAmin III
Any function you need to perform on your
PostgreSQL system you can do from within the pgAdmin III graphical interface Location: ~bin\pgadmin3.exe Default:
localhost
port: 5432
Add new connect: FileAdd server Connect server: right click Connect
5
2. Parts of the PostgreSQL system
Two-tier Database Server Architecture
Database System Structure
Database Server Database Cluster
Database Database Schema User Defined Public pg_database, pg_role
Tablespace System Catalog
10
Database Object Definition (table, trigger, function, permission)
Database Server
Database server A computer program that provides database services to other programs (client applications) that access the server via a network A database server divides a client application onto: A front end that runs on a users computer and typically performs simple tasks as displaying results and A back end that runs on the server computer and performs:
User authentication, Transaction control, Query optimization, Database access
11
Database Cluster
A database cluster is a directory on disk where all
database data will be stored (data area) Data in a cluster is stored as a collection of databases Cluster databases are managed by a single database server After initialization, a database cluster contains:
A database named postgres, A database named template1 A database named template0,
12
5 basic components
Tablespaces Databases Schemas (listed under each individual database) Group Roles Login Roles
13
Tablespaces (1/2)
Table spaces allow a DBA to define locations in
the file system where the files representing database objects can be stored Advantages of using table spaces:
If the partition or volume on which the cluster was
14
initialized runs out of space and cannot be extended, a table space can be created on a different partition and used until the system has been reconfigured Table spaces allow a DBA to optimize performance by placing mission critical database objects (like indexes) on highly reliable and fast devices
Tablespaces (2/2)
After initialization, two default tablespaces created: pg_default: the default location for all database objects pg_global: hold PostgreSQL system catalogs, containing internal Data Dictionary information When new database objects are created, you must
specify which tablespace area they are stored in Creating a new tablespace:
must point to an empty directory on the system creat
15
directory first postgres must have permission to write to the directory default: postgres is a normal account grand permission to directories
Databases (1/2)
The core objects in PostgreSQL Each client connection to the server can access the
data in only one database To access data in more than one database a client must make more connections The default database created during the PostgreSQL installation is postgres:
contains the default system tables for handling the internal
PostgreSQL Data Dictionary
template0 and template1 (NOT shown in pgAdmin III)
are used to create new databases
template1 can be modified
16
Databases (2/2)
Each database object contains 4 types of objects: Casts: control how Postgres casts from one datatype to another (NOT view in pgAmin III) Languages: these are the languages you can define stored functions, aggregates and triggers in (NOT view in pgAmin III) Schemas: the most important objects within the database, containing the tables, triggers, functions, views, and other objects for handling data Replications: define copies (or replicas) of the PostgreSQL database in a fault-tolerantboperation.
17
18
Template
CREATE DATABASE actually works by copying an
existing database Default, it copies the standard system database template1 There is a second standard system database named template0
the same data as the initial contents of template1 never be changed
19
Schemas (1/2)
The most important objects within the database A database contains one or more schemas, which
contain database object (table, data type, domain, function, trigger) definitions While users can only access objects within one database at a time, they can access all of the schemas within that database, if it has permissions Unlike databases, schemas are not rigidly separated
20
Schemas (2/2)
21
Catalogs
A PgAdmin catalog is a schema Hold meta data information and built-in Postgres
objects 2 types:
system catalog: pg_catalog
information catalog: information_schema
22
The System Catalog
Every database system must have a meta-database
of information on the schema which it contains.
The names of the relations in the schemas
The names of the columns of each relation.
The data type of each column. The integrity constraints on the relations. Information about indices on the relations. The access privileges for the elements of the schema.
This database is often called the system catalog.
23
The System Catalog Schema
Each PostgreSQL database contains a pg_catalog schema Normaly, it is copied from the template1 database The pg_catalog schema contains tables with information about
database objects like:
24
Schemas (pg_namespace) Tables, indexes, sequences, and views (pg_class), Data types (pg_type), Functions and procedures (pg_proc), Table columns (pg_attribute), Check, unique, primary key, and foreign key constraints (pg_constraint), Aggregate functions (pg_aggregate), Triggers (pg_trigger), Planner (optimizer) statistics (pg_statistics), and Many others
Information_schema and pg_catalog
The pg_catalog schema is the standard PostgreSQL
meta data and core schema. The information_schema is part of the ANSI standard, but is not quite so standard. Oracle and DB2 evidentally still don't support A lot of this information overlaps with information found in the information_schema and pg_catalog, but the information_schema is much easier to query Although not named explicitly in the search_path, pg_catalog is the first schema to be searched
25
SELECT * FROM pg_tables SELECT * FROM pg_catalog.pg_tables
Group Roles
Create access permissions for groups of users
While you can grant an individual user account access
directly to a database object, the preferred method is to use Group Roles pgAdmin III only allows you to grant Group Roles access to database objects Default, public group role:
applies to all users on the PostgreSQL system
NOT able to remove any user account from the public
26
Group Role does not appear in the pgAdmin III Group Roles listing
Login Roles (or user accounts)
Are roles that are allowed to log into the PostgreSQL
server Each database user should have an individual account for logging into the PostgreSQL system That account is then assigned as a member of the appropriate Group Roles that grant privileges to the database objects required Allows you to easily change access for database objects without having to touch hundreds (or even 27 thousands) of individual user Login Roles
3. Practices Creat a new application
28
Practices Creat a new application
Create a database test Customer Product Order
Create two Group Roles Salesman Group Role: write permission on the Customer and Order, only read permission on the Product Accountant Group Role: write permission on the Product and Order, read permission on the Customer Create two Login Roles salesman - Barney 29 accountant - Fred
Creating a New Database
30
Creating a New Schema
31
Creating the Tables
32
Customer Table Columns
33
34
Common PostgreSQL Data Types
35
The Product Table Columns
36
The Columns for the Order Table
37
New Foreign Key window for the Order table
38
Entering and Viewing Data
39
THE pgADMIN III QUERY TOOL
40
WORKING WITH USER ACCOUNTS
41
42
pgAdmin Object Privilege Codes
43
Creating Login Roles
44
Testing
log in using the test database and the fred Login Role psql test fred test=>INSERT into store."Product" VALUES
('LAP001', 'Laptop', 'TakeAlong', 'Acme', '500.00', 100); test=>
45
DATABASE MAINTENANCE
46
47
BACKUPS AND RESTORES
48
49
50
