EnterpriseDB PostgreSQL Exercises

From Artesano
Jump to: navigation, search


Module 4: Configuration

Lab Exercise 1

Changes is server parameter file:

- Server must allow up to 200 connected users

max_connections = 200

- Server should reserve 10 connections slots for DBA

superuser_reserved_connections = 10

- Maximum time to complete client authentication will be 10 seconds

authentication_timeout = 10s

Note: The change to max_connections forced me to increase the kernel variable shmmax and shmall (shared memory).

HowTo:

In the shell (temporarily):

$ sysctl -w kernel.shmmax=134217728
$ sysctl -w kernel.shmall=2097152

Or

$ echo 134217728 >/proc/sys/kernel/shmmax
$ echo 2097152 >/proc/sys/kernel/shmall

To make the change permanent:

Add these lines to /etc/sysctl.conf

kernel.shmmax=134217728
kernel.shmall=2097152

Lab Exercise 2

Go through server parameters for logging and implement the following:

- Save all error messages in a file inside pg_log folder in your cluster data directory

logging_collector = on -- Needed for redirecting the stderr to a file log_directory = '/var/lib/postgresql/9.1/main/pg_log' -- Absolute because PGDATA is not set

- Log all queries and their time which are taking more than 5 seconds to execute

log_min_duration_statement = 5s -- or using the default unit ms log_min_duration_statement = 5000

- Log the users who are connecting to the database cluster

log_connections = on

- Make changes and verify them. Here is the verification:

$ sudo service postgresql restart
 * Restarting PostgreSQL 9.1 database server
$ sudo ls -al /var/lib/postgresql/9.1/main/pg_log
[...]
-rw-------  1 postgres postgres  789 Feb 14 13:02 postgresql-2013-02-14_130159.log
$ psql charles
psql# SELECT pg_sleep(6);
psql# SELECT pg_sleep(4);
psql# SELECT pg_sleep(7);
psql# \q
$ sudo view /var/lib/postgresql/9.1/main/pg_log/postgresql-2013-02-14_130159.log
2013-02-14 13:26:43 CET LOG:  database system was shut down at 2013-02-14 13:26:41 CET
2013-02-14 13:26:43 CET LOG:  autovacuum launcher started
2013-02-14 13:26:43 CET LOG:  database system is ready to accept connections
2013-02-14 13:27:30 CET LOG:  connection received: host=[local]
2013-02-14 13:27:30 CET LOG:  connection authorized: user=charles database=charles
2013-02-14 13:27:50 CET LOG:  duration: 6002.420 ms  statement: SELECT pg_sleep(6);
2013-02-14 13:28:10 CET LOG:  duration: 7001.457 ms  statement: SELECT pg_sleep(7);

Note: Alternatively it is possible to restart the server using:

$ sudo -u postgres pg_ctl restart -D /var/lib/postgresql/9.1/main/ -m fast
waiting for server to shut down.... done
server stopped
server starting

Additional note: The first trying to start the database using pg_ctl brought an error, because the server was looking for the postgresql.conf file in the data directory. On my (Ubuntu) installation the file is located in /etc/postgresql/9.1/main/. For the server to start up I added a simlink in the data directory pointing to the conf file:

$ sudo ln -s /etc/postgresql/9.1/main/postgresql.conf /var/lib/postgresql/9.1/main/postgresql.conf

Module 5: Creating and managing databases

Lab exercise 1

- Create a database user edbstore in your existing cluster

charles=# CREATE ROLE edbstore LOGIN;

Or

charles=# CREATE USER edbstore;

Note: The exercise does not explicitly require to create a password for the user. Later, in order to login, we will have do alter the role and add a password. This could have been done already explicitly now:

charles=# CREATE ROLE edbstore LOGIN PASSWORD '12345';

- Create a edbstore database with ownership of edbstore user

charles=# CREATE DATABASE edbstore OWNER edbstore;
charles=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 charles   | charles  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 edbstore  | edbstore | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

- Login inside edbstore database using edbstore user and create edbstore schema

We must first assign a password to role edbstore

charles=# ALTER USER edbstore PASSWORD '12345';
charles=# \q
charles@charles-development:~$ psql -U edbstore -h localhost
Password for user edbstore: 
psql (9.1.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

edbstore=> select current_user;
 current_user 
--------------
 edbstore

edbstore=# CREATE SCHEMA edbstore;
CREATE SCHEMA
edbstore=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 edbstore | edbuser
 public   | postgres

Note: The creation of the schema is possible, because edbstore is the owner of the database.

Since the role name and the schema name are the same and search_path has as a first entry $user, any new objects will be created in schema edbstore:

edbstore=> show search_path;
  search_path   
----------------
 "$user",public
edbstore=> CREATE TABLE test (id int);
CREATE TABLE
edbstore=> \dt
         List of relations
  Schema  | Name | Type  |  Owner   
----------+------+-------+----------
 edbstore | test | table | edbstore
edbstore=> drop table test;
DROP TABLE

Lab exercise 2

- Create an ebuy user with password 'lion'

The role edbuser has no permissions to create users. Either we assign that role to it or we create the user with another superuser (e.g. postgres).

edbstore=> CREATE ROLE ebuy LOGIN PASSWORD 'lion'; ERROR: permission denied to create role

We opt for variant 1

edbstore=> \q
charles@charles-development:~$ psql edbstore
psql (9.1.8)
Type "help" for help.

edbstore=# select current_user;
 current_user 
--------------
 charles

edbstore=# ALTER ROLE edbstore CREATEROLE;
ALTER ROLE

Now we can login again as edbstore and do the rest.

edbstore=# \q
charles@charles-development:~$ psql -U edbstore -h localhost
Password for user edbstore: 
psql (9.1.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

edbstore=> select current_user;
 current_user 
--------------
 edbstore
edbstore=> CREATE ROLE ebuy LOGIN PASSWORD 'lion';
CREATE ROLE

- Create an ebuy schema which can be used by ebuy user.

Being logged in as edbstore we can create a schema with the same name as the user.

edbstore=> CREATE SCHEMA ebuy;
CREATE SCHEMA
edbstore=> \dn
   List of schemas
   Name   |  Owner   
----------+----------
 ebuy     | edbstore
 edbstore | edbstore
 public   | postgres

And grant create and usage on it to ebuy:

edbstore=> GRANT CREATE, USAGE ON SCHEMA ebuy TO ebuy;

- Login as ebuy user, create a table sample1 and check whether that table belongs to ebuy schema or not.

edbstore=> \q
charles@charles-development:~$ psql -d edbstore -U ebuy -h localhost
Password for user ebuy: 
psql (9.1.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help. 

edbstore=> CREATE TABLE sample1 (id int);
CREATE TABLE
edbstore=> \dt
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 ebuy   | sample1 | table | ebuy

The table was created in schema ebuy, because it matches $user, i.e. the first entry in search_path.

Note: If you are logged in as an admin you can create the role and the schema directly and assign the ownership of the schema to ebuy:

charles=# CREATE ROLE ebuy LOGIN PASSWORD 'lion';
charles=# CREATE SCHEMA ebuy AUTHORIZATION ebuy;

If you do it that way then the list of schemas looks as follows:

edbstore=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 ebuy     | ebuy
 edbstore | edbstore
 public   | postgres

Lab exercise 3

- Retrieve a list of databases using an SQL query and psql meta command.

edbstore=> select datname from pg_database;
  datname  
-----------
 template1
 template0
 postgres
 charles
 edbstore
edbstore=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 charles   | charles  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 edbstore  | edbstore | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

- Retrieve a list of tables in edbstore database and check which schema and owner they have.

edbstore=> \dn
   List of schemas
   Name   |  Owner   
----------+----------
 ebuy     | edbstore
 edbstore | edbstore
 public   | postgres

Module 6 - PSQL

Lab exercise 1

- Connect to a database using psql

dbname = edbstore, dbuser = charles

$ psql -d edbstore -U charles -h localhost

- Switch databases

edbstore=# \c charles charles localhost 5432
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database "charles" as user "charles".

- Describe the customers table

No such table. I install first the database delivered with the course. After that

edbstore=# \d customers
                                         Table "edbstore.customers"
        Column        |         Type          |                           Modifiers                            
----------------------+-----------------------+----------------------------------------------------------------
 customerid           | integer               | not null default nextval('customers_customerid_seq'::regclass)
 firstname            | character varying(50) | not null
 lastname             | character varying(50) | not null
 address1             | character varying(50) | not null
 address2             | character varying(50) | 
 city                 | character varying(50) | not null
 state                | character varying(50) | 
 zip                  | integer               | 
 country              | character varying(50) | not null
 region               | smallint              | not null
 email                | character varying(50) | 
 phone                | character varying(50) |  
 creditcardtype       | integer               | not null
 creditcard           | character varying(50) | not null
 creditcardexpiration | character varying(50) | not null
 username             | character varying(50) | not null
 password             | character varying(50) | not null
 age                  | smallint              | 
 income               | integer               | 
 gender               | character varying(1)  | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customerid)
    "ix_cust_username" UNIQUE, btree (username)
Referenced by:
    TABLE "cust_hist" CONSTRAINT "fk_cust_hist_customerid" FOREIGN KEY (customerid) REFERENCES customers(customerid) ON DELETE CASCADE
    TABLE "orders" CONSTRAINT "fk_customerid" FOREIGN KEY (customerid) REFERENCES customers(customerid) ON DELETE SET NULL

- Describe the customer table including description

edbstore=# \d+ customers
                                                       Table "edbstore.customers"
        Column        |         Type          |                           Modifiers                            | Storage  | Description 
----------------------+-----------------------+----------------------------------------------------------------+----------+-------------
 customerid           | integer               | not null default nextval('customers_customerid_seq'::regclass) | plain    | 
 firstname            | character varying(50) | not null                                                       | extended | 
 lastname             | character varying(50) | not null                                                       | extended | 
 address1             | character varying(50) | not null                                                       | extended | 
 address2             | character varying(50) |                                                                | extended | 
 city                 | character varying(50) | not null                                                       | extended | 
 state                | character varying(50) |                                                                | extended | 
 zip                  | integer               |                                                                | plain    | 
 country              | character varying(50) | not null                                                       | extended | 
 region               | smallint              | not null                                                       | plain    | 
 email                | character varying(50) |                                                                | extended | 
 phone                | character varying(50) |                                                                | extended | 
 creditcardtype       | integer               | not null                                                       | plain    | 
 creditcard           | character varying(50) | not null                                                       | extended | 
 creditcardexpiration | character varying(50) | not null                                                       | extended | 
 username             | character varying(50) | not null                                                       | extended | 
 password             | character varying(50) | not null                                                       | extended | 
 age                  | smallint              |                                                                | plain    | 
 income               | integer               |                                                                | plain    | 
 gender               | character varying(1)  |                                                                | extended | 
Indexes:
    "customers_pkey" PRIMARY KEY, btree (customerid)
    "ix_cust_username" UNIQUE, btree (username)
Referenced by:
    TABLE "cust_hist" CONSTRAINT "fk_cust_hist_customerid" FOREIGN KEY (customerid) REFERENCES customers(customerid) ON DELETE CASCADE
    TABLE "orders" CONSTRAINT "fk_customerid" FOREIGN KEY (customerid) REFERENCES customers(customerid) ON DELETE SET NULL
Has OIDs: no

- List all databases

edbstore=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 charles   | charles  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 edbstore  | edbstore | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

- List all schemas

edbstore=# \dn
   List of schemas
   Name   |  Owner   
----------+----------
 edbstore | edbstore
 public   | postgres

- List all tablespaces

edbstore=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 

- Execute an sql statement, saving the output to a file

edbstore=# \o output.txt
edbstore=# select * from customers limit 1;
edbstore=# \! ls -al | grep output
-rw-rw-r-- 1 charles charles     652 Feb 15 13:48 output.txt
edbstore=# \! cat output.txt
-[ RECORD 1 ]--------+--------------------
customerid           | 1
firstname            | VKUUXF
lastname             | ITHOMQJNYX
[...]

Reset value to display results on screen

edbstore=# \o
edbstore=# select * from customers limit 1;
-[ RECORD 1 ]--------+--------------------
customerid           | 1
firstname            | VKUUXF
lastname             | ITHOMQJNYX
[...]

- Do the same thing, just saving data, not the column header

The switch is \t. To write the output in a file is just like the previous exercise.

edbstore=# select * from inventory limit 1;
 prod_id | quan_in_stock | sales 
---------+---------------+-------
       1 |           138 |     9
edbstore=# \t
Showing only tuples.
edbstore=# select * from inventory limit 1;
       1 |           138 |     9

- Create a script via another method, and execute from psql

edbstore=# \!
charles@charles-development:~$ vi script.sql
[Write this in the file]
SELECT * FROM customers LIMIT 1;
[Esc :wq]
charles@charles-development:~$ exit
exit
edbstore=> \x
Expanded display is on.
[execute script]
edbstore=> \i script.sql 
-[ RECORD 1 ]--------+--------------------
customerid           | 1
firstname            | VKUUXF
lastname             | ITHOMQJNYX
[...]

- Display the dept table in extended format

edbstore=> \x
Expanded display is on.
edbstore=> select * from dept;
-[ RECORD 1 ]------
deptno | 10
dname  | ACCOUNTING
loc    | NEW YORK
-[ RECORD 2 ]------
deptno | 20
dname  | RESEARCH
loc    | DALLAS
-[ RECORD 3 ]------
deptno | 30
dname  | SALES
loc    | CHICAGO
-[ RECORD 4 ]------
deptno | 40
dname  | OPERATIONS
loc    | BOSTON

- Create a script that will not display all data but will echo the table, name and show how long the statement took

edbstore=> \timing
Timing is on.
edbstore=> \o output.txt
edbstore=> select * from dept;
Time: 1.195 ms

Module 8 - Security

Lab exercise 1

- Your server box has 2 network cards:

1.1.1.1    = internal LAN
10.1.10.1  = Web server for external access

- Configure your server to accept connections from external and internal networks. This is done in postgresql.conf

listen_addresses = '*'

Or

listen_addresses = '1.1.1.1,10.1.10.1'

Note: listen_addresse refers to the IP addresses that your server machine has, i.e. the IP addresses that the server is going to bind to listen on your local machine. So if you have many network cards and only want to accept connetions on some of them, then you would restrict the list to those (instead of using *). In the exercise both network cards are used, so it makes no difference which solution you choose.

Lab exercise 2

psql: could not connect to server: Connection refused
        Is the server running on host "1.1.1.1" and accepting
        TCP/IP connections on port 5432?

- Predict the problem and suggest a solution

Probably the server is not running or has not been restarted after the changes done in Lab exercise 1 or the modifications have not been taken.

Check setting of listen_addresses
Restart the database
Check firewalls between client and database server

Lab exercise 3

- New developer with ID = 89. Create a new user by name dev89 and password 'password89'. Then assign the necessary privileges to dev89 so that he can connect to the edbstore database and view all tables.

charles@charles-development:~$ psql edbstore
psql (9.1.8)
Type "help" for help.

edbstore=# select current_user;
 current_user 
--------------
 charles
(1 row)
edbstore=# CREATE ROLE dev89 LOGIN PASSWORD 'password89';
edbstore=# GRANT CONNECT ON DATABASE edbstore TO dev89;
edbstore=# GRANT USAGE ON SCHEMA edbstore TO dev89;
edbstore=# GRANT SELECT ON ALL TABLES IN SCHEMA edbstore TO dev89;
edbstore=# ALTER ROLE dev89 SET search_path="$user",edbstore,public;
edbstore=> \q
charles@charles-development:~$ psql -d edbstore -U dev89 -h localhost
Password for user dev89: 
psql (9.1.8)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

edbstore=> show search_path;
        search_path        
---------------------------
 "$user", edbstore, public
edbstore=> \dt
            List of relations
  Schema  |    Name    | Type  |  Owner   
----------+------------+-------+----------
 edbstore | categories | table | edbstore
 edbstore | cust_hist  | table | edbstore
 edbstore | customers  | table | edbstore
 edbstore | dept       | table | edbstore
 edbstore | emp        | table | edbstore
 edbstore | inventory  | table | edbstore
 edbstore | job_grd    | table | edbstore
 edbstore | jobhist    | table | edbstore
 edbstore | locations  | table | edbstore
 edbstore | orderlines | table | edbstore
 edbstore | orders     | table | edbstore
 edbstore | products   | table | edbstore
 edbstore | reorder    | table | edbstore

Note: The modification of the search_path is just to make the life of dev89 easier.

Lab exercise 4

- New developer on client with IP 1.1.1.89 has a problem connecting to the database server.

FATAl: no pg_hba.conf entry for host "1.1.1.89", user "dev89", database "edbstore", SSL off

Add this entry to the pg_hba.conf file:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
[...]
host    edbstore        dev89           1.1.1.89/32             md5
[...]

This will enable the user to connect using his username and password.

Module 9: SQL Primer

Lab exercise 1

- 1. Initiate a PSQL session

charles@charles-development:~$ psql -U edbstore -d edbstore -h localhost

- 2. PSQL commands access the database

True.

  • SQL queries and DDL statements affect the database
  • psql commands query e.g. the catalog tables to retrieve information on database objects. Psql commands do not change the data in the database.

- 3. This executes correctly

SELECT ename, job, sal AS Salary FROM emp;

True, but the word "Salary" will be written in lower letters (SQL converts everything that is not double quoted to lower letters). If you wanted to have the word starting with a capital letter you should modify the statement to

SELECT ename, job, sal AS "Salary" FROM emp;

- 4. This executes correctly

SELECT * FROM emp;

True.

- 5. Find the coding errors in the statement

SELECT empno, ename, sal x 12 ANNUAL SALARY
FROM emp;
Error 1: The operator for multiplication is "*".
Error 2: If the new name for a column result must be returned as indicated (capital letters and two words) then you must double quote (") it.

Correct statement

SELECT empno, ename, sal * 12 AS "ANNUAL SALARY"
FROM emp;

Note: the "AS" for aliasing the column name is optional.

Lab exercise 2

Write a statement for following:

- Create a list of employees with the name, department number, and department name for all employees.

We need to explore the database a little. We know that employees are in the table emp. Parts of the description that are not relevant for the exercise are cut out (indicated by [...]).

edbstore=> \d emp
                Table "edbstore.emp"
  Column  |            Type             | Modifiers 
----------+-----------------------------+-----------
 ename    | character varying(10)       | 
 deptno   | numeric(2,0)                | 
[...]
Foreign-key constraints:
    "emp_ref_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno)
[...]

We see that the department name is not available and we assume that it is in table dept.

edbstore=> \d dept
           Table "edbstore.dept"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 deptno | numeric(2,0)          | not null
 dname  | character varying(14) | 
[...]
Referenced by:
    TABLE "emp" CONSTRAINT "emp_ref_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno)
[...]

Since we must list all employees let's check if there are any that do not belong to any department.

edbstore=> SELECT COUNT(*) FROM emp WHERE deptno IS NULL;
 count 
-------
     0

None. So we can make a simple join on dept. Also there are no requirements for grouping or ordering.

edbstore=> SELECT emp.ename, emp.deptno, dept.dname
edbstore-> FROM emp
edbstore-> JOIN dept ON (emp.deptno = dept.deptno);
 ename  | deptno | deptno 
--------+--------+--------
 SMITH  |     20 | RESEARCH
 ALLEN  |     30 | SALES
 WARD   |     30 | SALES
[...]

If we had any employee without a department we would need another join.

edbstore=> SELECT emp.ename, emp.deptno, dept.dname
edbstore=> FROM emp
edbstore=> LEFT JOIN dept ON (emp.deptno = dept.deptno);

The left join indicates that all employees (left side of the join) must be retrieved, even if deptno is null.

Alterntive from EnterpriseDB solution video:

edbstore=> SELECT e.ename, e.deptno, d.dname
edbstore-> FROM emp e
edbstore-> JOIN dept d ON e.deptno = d.deptno;

- Create a report to display employees' name and number along with their manager's name and number. Label the columns Employee, Emp#, Manager and Mgr#.

edbstore=> SELECT emp.ename AS "Employee", emp.empno AS "Emp#", emp.mgr AS "Emp#", emp2.ename AS "Manager"
edbstore-> FROM emp emp2
edbstore-> RIGHT JOIN emp ON (emp2.empno = emp.mgr);
 Employee | Emp# | Emp# | Manager  
----------+------+------+---------
 SMITH    | 7369 | 7902 | FORD
 ALLEN    | 7499 | 7698 | BLAKE
 WARD     | 7521 | 7698 | BLAKE
 JONES    | 7566 | 7839 | KING
[...]

Notice that we are now right joining to get all employees (one of them has no boss).

Alternative I did after seeing solution to the first part of this lab.

SELECT e.ename AS "Employee", e.empno AS "Emp#", e.mgr AS "Mgr#", m.ename AS "Manager"
FROM emp e
LEFT JOIN emp m ON (m.empno = e.mgr);

- Create a report with employee names, deparment number and all the employees that work in the same department as a given employee. Give the columns an appropriate name.

Let's assume that the given employee is SMITH. Now we need a list of all employees working in the same department as SMITH.

edbstore=> SELECT ename, deptno
edbstore-> FROM emp
edbstore-> WHERE deptno IN (SELECT deptno FROM emp WHERE ename = 'SMITH');
  ename  | deptno 
---------+--------
 SCOTT   |     20
 ADAMS   |     20
 FORD    |     20
 JONES   |     20
 SMITH   |     20

Lab exercise 3

- Write a query that displays the employee number and name of all employees who work in a department with any employee whose name contains a "u" (use subquery).

edbstore=> SELECT emp.empno, emp.ename
edbstore-> FROM emp
edbstore-> WHERE emp.deptno IN (SELECT emp.deptno FROM emp WHERE strpos(lower(emp.ename),'u') > 0);
 empno | ename  
-------+--------
  7499 | ALLEN
  7521 | WARD
  7654 | MARTIN
  7698 | BLAKE
  7844 | TURNER
  7900 | JAMES
(6 rows)

The solution on the video is much less flexible because it assumes that names are alway written in capital letters.

SELECT empno, ename
FROM emp
WHERE deptno IN (SELECT deptno FROM emp WHERE ename LIKE '%U%');

- Update and delete data in the emp table.

Change the name of the employee 7566 to Drexler.

UPDATE emp SET ename = 'Drexler' WHERE empno = 7566;

Change the salary to $1000 for all employees who have a salary less than $900.

UPDATE emp SET sal = 1000 WHERE sal < 900;

Verify your changes in the table

edbstore=> SELECT empno, ename, sal FROM emp;
 empno |  ename  |   sal   
-------+---------+---------
  7499 | ALLEN   | 1600.00
  7521 | WARD    | 1250.00
  7654 | MARTIN  | 1250.00
  7698 | BLAKE   | 2850.00
  7782 | CLARK   | 2450.00
  7788 | SCOTT   | 3000.00
  7839 | KING    | 5000.00
  7844 | TURNER  | 1500.00
  7876 | ADAMS   | 1100.00
  7900 | JAMES   |  950.00
  7902 | FORD    | 3000.00
  7934 | MILLER  | 1300.00
  7566 | Drexler | 2975.00
  7369 | SMITH   | 1000.00

- Delete MILLER from the emp table.

DELETE FROM emp WHERE ename = 'MILLER';

Lab exercise 4

- Create the emp2 table based on the structure of the emp table. Include only the employee_id, name, salary and department_id columns. Name the columns in the new table id, first_name, salary and deptid.

edbstore=>  CREATE TABLE emp2 AS
edbstore->  SELECT empno AS id, ename AS first_name, sal AS salary, deptno AS deptid
edbstore->  FROM emp LIMIT 0;
edbstore=> \d emp2
             Table "edbstore.emp2"
   Column   |         Type          | Modifiers 
------------+-----------------------+-----------
 id         | numeric(4,0)          | 
 first_name | character varying(10) | 
 salary     | numeric(7,2)          | 
 deptid     | numeric(2,0)          | 

Note: Only create structure, don't include data. For that reason we use limit 0.

- Create a view empvu including employee number, name and department number from emp table. The heading for the employee name must be employee.

edbstore=>  CREATE VIEW empvu AS
edbstore->  SELECT empno, ename AS employee, deptno
edbstore->  FROM emp;
CREATE VIEW

- Confirm that the view works. Display the contents of the empvu view.

edbstore=> SELECT * FROM empvu;
 empno | employee | deptno 
-------+----------+--------
  7499 | ALLEN    |     30
  7521 | WARD     |     30
  7654 | MARTIN   |     30
  7698 | BLAKE    |     30
  7782 | CLARK    |     10
  7788 | SCOTT    |     20
  7839 | KING     |     10
  7844 | TURNER   |     30
  7876 | ADAMS    |     20
  7900 | JAMES    |     30
  7902 | FORD     |     20
  7566 | Drexler  |     20
  7369 | SMITH    |     20
(13 rows)

- Using empvu, write a query to display all employee names and department numbers.

edbstore=> SELECT employee, deptno
edbstore-> FROM empvu;
 employee | deptno 
----------+--------
 ALLEN    |     30
 WARD     |     30
 MARTIN   |     30
 BLAKE    |     30
 CLARK    |     10
 SCOTT    |     20
 KING     |     10
 TURNER   |     30
 ADAMS    |     20
 JAMES    |     30
 FORD     |     20
 Drexler  |     20
 SMITH    |     20
(13 rows)

Lab exercise 5

- Create a sequence for deptno of table dept. Start at 60, max at 90, increment 10 and name dept_id_seq.

First create a sequence.

edbstore=> CREATE SEQUENCE dept_id_seq
edbstore-> INCREMENT 10 MINVALUE 60 MAXVALUE 90 START 60
edbstore-> OWNED BY dept.deptno;
CREATE SEQUENCE

NB: owned by associates the sequence with the table column and is used, e.g. in a cascaded drop of the table. It does not automatically assign the sequence to the column as default value.

Then associate the sequence to the column. Set it as its defaut.

edbstore=> ALTER TABLE dept
edbstore-> ALTER COLUMN deptno SET DEFAULT nextval('dept_id_seq');
ALTER TABLE
edbstore=> \d dept
                               Table "edbstore.dept"
 Column |         Type          |                     Modifiers                     
--------+-----------------------+---------------------------------------------------
 deptno | numeric(2,0)          | not null default nextval('dept_id_seq'::regclass)
[...]

- Test your sequence, inster two rows in dept.

edbstore=> INSERT INTO dept VALUES (DEFAULT, 'IT','DENVER');
INSERT 0 1
edbstore=> INSERT INTO dept VALUES (DEFAULT, 'RELATIONS','WASHINGTON');
INSERT 0 1
edbstore=> SELECT * FROM dept;
 deptno |   dname    |    loc     
--------+------------+------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | IT         | DENVER
     70 | RELATIONS  | WASHINGTON
(6 rows)

- Create an index on deptno of table dept.

edbstore=> CREATE INDEX deptno_idx ON dept(deptno);
CREATE INDEX
edbstore=> \d dept
                               Table "edbstore.dept"
 Column |         Type          |                     Modifiers                     
--------+-----------------------+---------------------------------------------------
 deptno | numeric(2,0)          | not null default nextval('dept_id_seq'::regclass)
[...]
Indexes:
[...]
    "deptno_idx" btree (deptno)
[...]

- Create and test a partial index.

For this we create a partial index for customers in the US (who happen to be the highest number).

edbstore=> CREATE INDEX custid_us_idx ON customers(customerid)
edbstore-> WHERE country = 'US';
CREATE INDEX
edbstore=> \d customers
                                          Table "edbstore.customers"
        Column        |         Type          |                           Modifiers                            
----------------------+-----------------------+----------------------------------------------------------------
 customerid           | integer               | not null default nextval('customers_customerid_seq'::regclass)
[...]
 country              | character varying(50) | not null
[...]
Indexes:
[...]
    "custid_us_idx" btree (customerid) WHERE country::text = 'US'::text

Module 10: Backup/Restore and PITR

Lab exercise 1

- As the root user, create a folder /PostgreSQL_backup and assign ownership to PostgreSQL user using chown utility or windows security tab in folder properties.

Note: For the sake of consistence I will use a different naming and location for the backup directory. On Ubuntu backups are usually stored in the directory /var/backups. The name of the directory itself will simply be postgresql.

charles@charles-development:~$ sudo mkdir /var/backups/postgresql
charles@charles-development:~$ sudo chown postgres /var/backups/postgresql
charles@charles-development:~$ sudo chmod 0700 /var/backups/postgresql
charles@charles-development:~$ sudo -i su - postgres
postgres@charles-development:~$ ls -al /var/backups | grep postgres
drwx------  2 postgres root      4096 Feb 18 08:14 postgresql
postgres@charles-development:~$ ls -al /var/backups/postgresql/
total 8
drwx------ 2 postgres root 4096 Feb 18 08:14 .
drwxr-xr-x 3 root     root 4096 Feb 18 08:14 ..

- Take a full database dump of the edbstore database with the pg_dump utility. The dump should be in plain text format. - Name the dump file as edbstore_full.sql and store it in the PostgreSQL_backup folder.

Remember: Our backup dir is /var/backups/postgresql

postgres@charles-development:~$ pg_dump edbstore > /var/backups/postgresql/edbstore_full.sql
postgres@charles-development:~$ ls -al /var/backups/postgresql/
total 5592
drwx------ 2 postgres root        4096 Feb 18 08:18 .
drwxr-xr-x 3 root     root        4096 Feb 18 08:14 ..
-rw-rw-r-- 1 postgres postgres 5716100 Feb 18 08:18 edbstore_full.sql

Lab exercise 2

- Take a schema-only dump of the edbstore database and name the file as edbstore_schema.sql

postgres@charles-development:~$ pg_dump -s edbstore > /var/backups/postgresql/edbstore_schema.sql

- Take a data-only dump of the edbstore database, disable all triggers for faster restore, use the insert command instead of copy, and name the file as edbstore_data.sql

postgres@charles-development:~$ pg_dump -a --disable-triggers --inserts edbstore > /var/backups/postgresql/edbstore_data.sql

- Take a full dump of only the customers table and name the file as edbstore_customers.sql

postgres@charles-development:~$ pg_dump -t edbstore.customers edbstore > /var/backups/postgresql/edbstore_customers.sql

Summary check

postgres@charles-development:~$ ls -al /var/backups/postgresql
-rw-rw-r-- 1 postgres postgres  3127531 Feb 18 08:44 edbstore_customers.sql
-rw-rw-r-- 1 postgres postgres 10839954 Feb 18 08:41 edbstore_data.sql
-rw-rw-r-- 1 postgres postgres  5716100 Feb 18 08:18 edbstore_full.sql
-rw-rw-r-- 1 postgres postgres    18317 Feb 18 08:38 edbstore_schema.sql

Lab exercise 3

- Take a full database dump of the edbstore in compressed format using the pg_dump utility, name the file as edbstore_full_fc.dmp

postgres@charles-development:~$ pg_dump -Fc edbstore > /var/backups/postgresql/edbstore_full_fc.dmp

- Take a full database cluster dump using pg_dumpall. Remember pg_dumpall supports only plain text format; name the file edbdata.sql

postgres@charles-development:~$ pg_dumpall > /var/backups/postgresql/edbdata.sql

Summary check

postgres@charles-development:~$ ls -al /var/backups/postgresql
-rw-rw-r-- 1 postgres postgres 19131909 Feb 18 09:17 edbdata.sql
-rw-rw-r-- 1 postgres postgres  3127531 Feb 18 08:44 edbstore_customers.sql
-rw-rw-r-- 1 postgres postgres 10839954 Feb 18 08:41 edbstore_data.sql
-rw-rw-r-- 1 postgres postgres  1998165 Feb 18 09:14 edbstore_full_fc.dmp
-rw-rw-r-- 1 postgres postgres  5716100 Feb 18 08:18 edbstore_full.sql
-rw-rw-r-- 1 postgres postgres    18317 Feb 18 08:38 edbstore_schema.sql

Lab exercise 4

- Drop database edbstore.

postgres@charles-development:~$ dropdb edbstore
postgres@charles-development:~$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 charles   | charles  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

- Create database edbstore with edbstore owner.

postgres@charles-development:~$ createdb -O edbstore edbstore
postgres@charles-development:~$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 charles   | charles  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 edbstore  | edbstore | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

- Restore the full dump from edbstore_full.sql and verify all the objects and their ownership.

postgres@charles-development:~$ psql -f /var/backups/postgresql/edbstore_full.sql -d edbstore
postgres@charles-development:~$ psql edbstore
edbstore=# set search_path=edbstore;
SET
edbstore=# show search_path;
 search_path 
-------------
 edbstore
(1 row)

edbstore=# \dt
            List of relations
  Schema  |    Name    | Type  |  Owner   
----------+------------+-------+----------
 edbstore | categories | table | edbstore
 edbstore | cust_hist  | table | edbstore
 edbstore | customers  | table | edbstore
 edbstore | dept       | table | edbstore
 edbstore | emp        | table | edbstore
 edbstore | emp2       | table | edbstore
 edbstore | inventory  | table | edbstore
 edbstore | job_grd    | table | edbstore
 edbstore | jobhist    | table | edbstore
 edbstore | locations  | table | edbstore
 edbstore | orderlines | table | edbstore
 edbstore | orders     | table | edbstore
 edbstore | products   | table | edbstore
 edbstore | reorder    | table | edbstore

- Drop database edbstore.

postgres@charles-development:~$ dropdb edbstore

- Create database edbstore with edbstore owner.

postgres@charles-development:~$ createdb -O edbstore edbstore

- Restore the full dump from compressed file edbstore_full_fc.dmp and verify all the objects and their ownership.

postgres@charles-development:~$ pg_restore -Fc -d edbstore /var/backups/postgresql/edbstore_full_fc.dmp

Lab exercise 5

- Create a directory /opt/arch or c:\arch and give ownership to PostgreSQL user.

charles@charles-development:~$ sudo mkdir /opt/arch
charles@charles-development:~$ sudo chown postgres /opt/arch/
charles@charles-development:~$ sudo chmod 0700 /opt/arch/
charles@charles-development:~$ ls -al /opt | grep arch
drwx------  2 postgres root 4096 Feb 18 09:39 arch

- Open postgresql.conf file of your edbdata cluster and configure your cluster to run in archive mode and archive log location to be /opt/arch or c:\arch.

charles@charles-development:~$ vi /etc/postgresql/9.1/main/postgresql.conf
wal_level = archive
archive_mode = on
archive_command = 'cp -i %p /opt/arch/ < /dev/null'

- Take a full online base backup of your cluster in PostgreSQL_backup directory.

charles@charles-development:~$ sudo service postgresql restart
* Restarting PostgreSQL 9.1 database server
charles@charles-development:~$ sudo -i su - postgres
postgres@charles-development:~$ psql
psql (9.1.8)
Type "help" for help.

postgres=# select pg_start_backup('2013-02-18');
 pg_start_backup 
-----------------
 0/11000020
(1 row)

postgres=# \q
postgres@charles-development:~$ cd /var/lib/postgresql/9.1/
postgres@charles-development:~/9.1$ tar -cf /opt/arch/pg_archive.tar main
postgres@charles-development:~/9.1$ ls /opt/arch/
-rw------- 1 postgres postgres  16777216 Feb 18 10:01 00000002000000000000000F
-rw------- 1 postgres postgres  16777216 Feb 18 10:02 000000020000000000000010
-rw-rw-r-- 1 postgres postgres 154839040 Feb 18 10:02 pg_archive.tar
postgres@charles-development:~/9.1$ psql
psql (9.1.8)
Type "help" for help.

postgres=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/110000A0
(1 row)

postgres=# \q
postgres@charles-development:~$ ls -al /opt/arch
-rw------- 1 postgres postgres  16777216 Feb 18 10:01 00000002000000000000000F
-rw------- 1 postgres postgres  16777216 Feb 18 10:02 000000020000000000000010
-rw------- 1 postgres postgres  16777216 Feb 18 10:02 000000020000000000000011
-rw------- 1 postgres postgres       277 Feb 18 10:02 000000020000000000000011.00000020.backup
-rw-rw-r-- 1 postgres postgres 154839040 Feb 18 10:02 pg_archive.tar

Note: The calls to pg_start_backup and pg_stop_backup could have been done also directly from the linux shell.

charles@charles-development:~$ psql -c "select pg_start_backup('2013-02-17')"
charles@charles-development:~$ psql -c "select pg_stop_backup()"

Module 11: Routine Maintenance Tasks

Lab exercise 1

- Create an explain plan for following query

 o Select * from emp where empno = 1	
 o Select * from emp where empno > 1	
 o Select * from emp where empno is not null	
edbstore=# EXPLAIN SELECT * FROM emp WHERE empno = 1;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.16 rows=1 width=146)
   Filter: (empno = 1::numeric)
(2 rows)

edbstore=# EXPLAIN SELECT * FROM emp WHERE empno > 1;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.16 rows=4 width=146)
   Filter: (empno > 1::numeric)
(2 rows)

edbstore=# EXPLAIN SELECT * FROM emp WHERE empno IS NOT NULL;
                      QUERY PLAN                      
------------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.13 rows=13 width=146)
   Filter: (empno IS NOT NULL)
(2 rows)

- What is difference between different explain plans.

None.

Lab exercise 2

- Write a statement to vacuum whole database including all tables.

edbstore=# VACUUM;

- The “customers” table is very heavily used in DML operations which results in lots of obsolete rows in the table. Execute a command to remove all such rows in order to improve performance.

edbstore=# VACUUM FULL customers;

Module 13: Moving Data

Lab exercise 1

- Unload a table in the edbstore schema to a csv file, with a header and using a pipe (|) delimiter.

We use table emp.

edbstore=# COPY emp TO '/var/backups/postgresql/emp.csv' (FORMAT CSV, DELIMITER '|', HEADER);

- Load the table to a temp tables of similar structure.

edbstore=# CREATE TABLE emp2 AS SELECT * FROM emp LIMIT 0;
SELECT 0
edbstore=# SELECT * FROM emp2;
 empno | ename | job | mgr | hiredate | sal | comm | deptno 
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

edbstore=# COPY emp2 FROM '/var/backups/postgresql/emp.csv' (FORMAT CSV, DELIMITER '|', HEADER);
COPY 13
edbstore=# SELECT * FROM emp2;
 empno |  ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno 
-------+---------+-----------+------+---------------------+---------+---------+--------
  7499 | ALLEN   | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD    | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN  | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE   | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30
  7782 | CLARK   | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT   | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000.00 |         |     20
  7839 | KING    | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10
  7844 | TURNER  | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS   | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100.00 |         |     20
  7900 | JAMES   | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30
  7902 | FORD    | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20
  7369 | SMITH   | CLERK     | 7902 | 1980-12-17 00:00:00 | 1000.00 |         |     20
  7566 | DREXLER | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20
(13 rows)

- Try unloading and loading only using certain columns.

edbstore=# TRUNCATE TABLE emp2;
TRUNCATE TABLE
edbstore=# COPY emp2 (ename, job) FROM '/var/backups/postgresql/empsel.csv' (FORMAT CSV, DELIMITER '|', HEADER);
COPY 13
edbstore=# SELECT * FROM emp2;
 empno |  ename  |    job    | mgr | hiredate | sal | comm | deptno 
-------+---------+-----------+-----+----------+-----+------+--------
       | ALLEN   | SALESMAN  |     |          |     |      |       
       | WARD    | SALESMAN  |     |          |     |      |       
       | MARTIN  | SALESMAN  |     |          |     |      |       
       | BLAKE   | MANAGER   |     |          |     |      |       
       | CLARK   | MANAGER   |     |          |     |      |       
       | SCOTT   | ANALYST   |     |          |     |      |       
       | KING    | PRESIDENT |     |          |     |      |       
       | TURNER  | SALESMAN  |     |          |     |      |       
       | ADAMS   | CLERK     |     |          |     |      |       
       | JAMES   | CLERK     |     |          |     |      |       
       | FORD    | ANALYST   |     |          |     |      |       
       | SMITH   | CLERK     |     |          |     |      |       
       | DREXLER | MANAGER   |     |          |     |      |       
(13 rows)

Note: This worked because the not null constraints of emp was not taken over into emp2.

Lab exercise 2

- Write a command to copy the customers table data in CSV format to a file.

edbstore=# COPY customers TO '/var/backups/postgresql/customers.sql' (FORMAT CSV);
COPY 20000