Relational and Non-relational Databases

Overview

Author: Mario Lassnig, CERN PH-ADP-CO, [1]

File:Slides.pdf

PostgreSQL (Setup)

vim /var/lib/pgsql/9.4/data/pg_hba.conf

set all to trust

add line

host all all 0.0.0.0/0 trust

psql -U gridka01 gridka_db

should not ask for password ctrl-d to exit

sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE ROLE gridka01 PASSWORD 'asdf1234' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;"
sudo -u postgres /usr/pgsql-9.4/bin/psql -c "CREATE DATABASE gridka_db OWNER gridka01 ENCODING 'UTF8'"
sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "CREATE SCHEMA gridka_schema AUTHORIZATION gridka01"
sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON SCHEMA gridka_schema TO gridka01"
sudo -u postgres /usr/pgsql-9.4/bin/psql -d gridka_db -c "GRANT ALL ON DATABASE gridka_db TO gridka01"

vim /var/lib/pgsql/9.4/data/pg_hba.conf

set all to md5

systemctl restart postgresql-9.4.service

psql -U gridka01 gridka_db

should ask for password

ldconfig

ls -la /var/lib/pgsql/9.4/data

PostgreSQL (SQL)

psql -U gridka01 gridka_db

\l .. list all databases
\d .. list all relations

CREATE TABLE test_table ( first_name VARCHAR, last_name VARCHAR );

\d

SELECT * FROM test_table;

INSERT INTO test_table VALUES ('Vincent', 'Vega');

SELECT * FROM test_table;

INSERT INTO test_table (last_name, first_name) VALUES ('Jules', 'Winnfield');

SELECT * FROM test_table;

DELETE FROM test_table;

SELECT * FROM test_table;

DROP TABLE test_table;

SELECT * FROM test_table;