Difference between revisions of "Relational and Non-relational Databases"
From Gridkaschool
(→PostgreSQL) |
(→PostgreSQL (Setup)) |
||
Line 38: | Line 38: | ||
ls -la /var/lib/pgsql/9.4/data |
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; |
Revision as of 10:37, 8 September 2015
Overview
Author: Mario Lassnig, CERN PH-ADP-CO, [1]
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;