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

  1. You should replace postgresql-x64-12 with the version, you are using
  2. 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

A database can contain one or multiple schemas while each schema belongs to only one database. Two schemas can have different objects that share the same name. PostgreSQL creates a schema named public for every new database. Read more about schemas from http://www.postgresqltutorial.com/postgresql-schema/

User 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 libsuer 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
\c libary
GRANT ALL PIVILEGES ON TABLE book TO libuser;
		or
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO 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

Login to your PostgreSQL as administrator (=postgres) and execute below commannds

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