Pekka Alaluukas /Databases

Relational databases

PostgreSQL

PostgreSQL is quite popular, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

You can download PostgreSQL from https://www.postgresql.org/

When you install it you will get

PostgreSQL Server
PostgreSQL Client (Command Shell)
PgAdmin (WebClient)

Here is a good tutorial for PostgreSQL http://www.postgresqltutorial.com/

Starting and Stopping the Server

In Windows you can configure how the PostgreSQL starts with below commands
Note

You should replace postgresql-x64-12 with the version, you are using
You have to execute below commands as the administrator

Disable Postgresql service
sc config postgresql-x64-12 start=disabled
Change Postgresql to manual start
sc config postgresql-x64-12 start=demand
Set the service start type to automatic
sc config postgresql-x64-12 start=auto

You can start/stop the server with below commands

net start postgresql-x64-12
net stop postgresql-x64-12

psql commands

The following command connects to a database under a specific user. After pressing Enter PostgreSQL will ask for the password of the user.

	psql -d database -U  user

If you want to connect to a database that resides on another host, you add the -h option as follows:

	psql -h host -d database -U user

Switch connection to a new database

	\c dbname username

List available databases

\l

List available tables

\dt

List available schema

\dn

Execute the previous command

\g

Get help on psql commands

\?

Quit psql

\q

Schema in PostgreSQL

In PostgreSQL, a schema is a logical namespace that holds database objects such as tables, views, functions, and indexes. It allows multiple users or applications to organize and isolate data within the same database.

Default Schema: public

Every PostgreSQL database contains a default schema called public. If no schema is explicitly specified when creating or querying objects, PostgreSQL uses this schema by default.

Creating a Schema

You can create your own schema with the following SQL command:

CREATE SCHEMA libschema;

Using a Schema

When creating objects inside a specific schema, you can prefix the schema name:

CREATE TABLE libschema.books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255),
  author VARCHAR(255)
);

Setting the Search Path

To make a schema the default for object lookup, you can set the search path in your session:

SET search_path TO libschema;

After setting this, you can reference tables and other objects without specifying the schema name.

Benefits of Schemas

Organize database objects more clearly
Support multi-tenancy within a single database
Control access and permissions at schema level
Avoid naming conflicts between applications

Listing Schemas

In psql, you can list all schemas using:

\dn

User/Schema management

The default administrator is postgres. You have to first login with that user and then you can create new users and give privileges to them.

Example: Create a new user libuser and give privileges to all tables in database library to that user.

CREATE ROLE libuser WITH LOGIN PASSWORD 'libpass';

And then you can give permissions to that user, like this

Set the default database with the command
```
\c library
```
Add privileges to public schema with the command
```
grant ALL ON SCHEMA public to libuser;
```

Add privileges to all tables with the command

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO libuser;

Create the schema named libschema and assign libuser as its owner
```
CREATE SCHEMA libschema AUTHORIZATION libuser;
```

Backup and Restore database

pg_dump works mostly the same way as myslq_dump. So you can start it from the command line and backup a database like this

pg_dump -U user database > db.sql

Then you can restore the database with this command

psql -U user -d database < db.sql

Note: In Windows pg_dump and psql are located in ProgramFiles\PostgreSQL\...\bin
In order to make it work from any folder, you shoul add that path to your environment variables

pgAdmin

When you have installed PostgreSQL, you probably have also installed pgAdmin, which is a quite handy PostgreSQL-client. If you don't have it yet, you can get it from https://www.pgadmin.org

DBeaver

DBeaver is also quite handy graphical SQL-client. And infact, you can use it also with MySQL and SQL-server. You can download it from https://dbeaver.io/.

Exercise

CREATE DATABASE test;
\c test;

CREATE ROLE user1 WITH LOGIN PASSWORD 'user1';
CREATE ROLE user2 WITH LOGIN PASSWORD 'user2';

CREATE SCHEMA schema1;
CREATE SCHEMA schema2;

CREATE TABLE schema1.table1(
	idTable1 INT primary key,
	name CHAR(50)
);

CREATE TABLE schema2.table2(
	idTable2 INT primary key,
	name CHAR(50)
);

insert into schema1.table1 VALUES (1,'Jim');
insert into schema2.table2 VALUES (1,'Lisa');

GRANT USAGE ON SCHEMA schema1 to user1;
GRANT USAGE ON SCHEMA schema2 to user2;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema1 to user1;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema2 to user2;

Then login as a user1 (psql -U user1 -d test) and execute below commands. Some of them does not work. Try to figure out why.

\dt
SELECT * FROM table1;
SELECT * FROM schema1.table1;
SELECT * FROM schema2.table2;

set search_path to schema1;
\dt
SELECT * FROM table1;

Then you can delete the database and user1 and user2.

user1 and user2 should connect out

as a administrator
\c test
DROP TABLE schema1.table1;
DROP SCHEMA schema1;
DROP USER user1;

DROP TABLE schema2.table2;
DROP SCHEMA schema2;
DROP USER user2;

\c postgres
DROP DATABASE test

Toggle Menu