EnterpriseDB PostgreSQL Exercises
Contents
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
