Skip to main content

3 posts tagged with "sql"

View All Tags

· 3 min read
Hreniuc Cristian-Alexandru

Useful documentation with examples:

Connect to the postgresql

psql -p 5432 -h localhost -U postgres -W

Database and Schema

Show all databases

\l
# Or
SELECT datname FROM pg_database;

Switch to another database

Similar to USE from mysql.

\c databasename

Create database

Documentation

CREATE DATABASE test_db;

Drop/Delete database

Documentation

DROP DATABASE IF EXISTS test_db;

A database may contain multiple schemas, which groups tables from a database. By default public schema is created for every database. And also you don't have to specify it in your queries, eg: public.staff is the same as staff. More examples and documentation can be checked here

User/Role

Show all users

\du
# With description
\du+
# Or sql
SELECT rolname FROM pg_roles;

Create a user

Documentation

CREATE USER test_user WITH PASSWORD 'pass';

Grant user rights to connect to db

GRANT CONNECT ON DATABASE test_db TO test_user;

Try to connect afterwards:

psql -p 5432 -h localhost -U test_user -W -d test_db

Create superuser

Documentation

CREATE ROLE test_user2 WITH LOGIN SUPERUSER CREATEDB PASSWORD 'pass';
# Connect afterwards
psql -p 5432 -h localhost -U test_user2 -W -d postgres

Alter user, add createdb rights

ALTER ROLE test_user2 WITH CREATEDB;

Grant all privileges on the database

Documentation

GRANT ALL PRIVILEGES ON DATABASE test_db TO test_user2;

Common users

You ussually need the following users:

Reader:

CREATE USER reader_user WITH PASSWORD 'reader_user';
GRANT CONNECT ON DATABASE test_db TO reader_user;

\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_user
IN SCHEMA public
GRANT SELECT ON TABLES TO reader_user;

Writer:

CREATE USER reader_writer WITH PASSWORD 'reader_writer';
GRANT CONNECT ON DATABASE test_db TO reader_writer;

\c databsename
ALTER DEFAULT PRIVILEGES
FOR USER reader_writer
IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO reader_writer;

Admin

CREATE USER admin WITH PASSWORD 'admin';
GRANT CONNECT ON DATABASE test_db TO admin;

\c test_db
ALTER DEFAULT PRIVILEGES
FOR USER admin
IN SCHEMA public
GRANT ALL ON TABLES TO admin;

Tables

Create table

Documentation, official documentation

CREATE TABLE accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL,
last_login TIMESTAMP
);

CREATE TABLE address (
id serial PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL
);

CREATE TABLE people (
id serial PRIMARY KEY,
name VARCHAR ( 50 ) NOT NULL
);

Describe table

Documentation

\d accounts

Show tables

Documentation

\dt+

Grant SELECT on table

Documentation, Official docs

First you need to connect with an user that has grant privileges and then switch to the database that contains that table:

\c test_db
GRANT SELECT ON accounts TO test_user;

Grant select on all tables even when new tables are added*

Documentation, official documentation

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO test_user;

· 2 min read
Hreniuc Cristian-Alexandru

First we need to install everything, here are the steps:

These steps are for centos First step is to install percona-release:

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Enable the package that contains the pt-query-digest tool:

percona-release enable tools

Next install percona-toolkit:

yum install percona-toolkit

How to use pt-query-digest:

pt-query-digest /../mysql_slow/slow-query.log

Set interval:

pt-query-digest --since '2019-02-07 08:09:00' --until '2019-02-07 08:02:03' /../mysql_slow/slow-query.log > output.out

Filter host, database or other things:

# Ignore queries from a specific database: ($event->{db} || "") && $event->{db} ne "ignored_schema"
# Ignore queries are not on a database:" ($event->{db} || "") , Ex PING
#Ignore queries that came from a specific IP address: ($event->{host} || "") && $event->{host} ne "192.168.2.1"
pt-query-digest --filter '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_schema" && $event->{cmd} ne "Admin" && ($event->{host} || "") && $event->{host} ne "192.168.2.1" && $event->{host} ne "192.168.0.1"' /../mysql_slow/slow-query.log > output.out

To see all components of event run this command:

# This will print the $event variable with all it's componets:

pt-query-digest pdbx5-slow-query.log --filter '($event->{db} || "") && $event->{db} && $event->{db} ne "ignored_database" && print Dumper $event' --no-report --sample 1


Uninstall:

yum remove percona-toolkit

percona-release disable tools

yum remove percona-release

· One min read
Hreniuc Cristian-Alexandru

Show indexes for a specific table:

SHOW INDEXES FROM TABLE_NAME;

Note: INDEX_TYPE ='HASH' is slower than INDEX_TYPE ='BTREE'

Show indexes for tables from a specific database or all databases:

SELECT *
FROM INFORMATION_SCHEMA.STATISTICS
[WHERE TABLE_SCHEMA = 'database_name'];

Add new index for a specific table:

ALTER TABLE `table_name` ADD INDEX `index_name` USING BTREE(`column1`, `column2`);

Rename table:

ALTER TABLE current_table_name RENAME new_table_name;

Show global system variables:

SHOW GLOBAL variables;

Set global variable:

SET GLOBAL long_query_time = 20;

This will have effect only if the field is dynamic, check the documentaion for each variable.

Show global status:

SHOW GLOBAL status;

Get size total size of all MEMORY tables:

SELECT (data_length+index_length)/(1024*1024*1024) table_size,
TABLE_NAME,
TABLE_SCHEMA
FROM information_schema.tables
WHERE ENGINE LIKE 'MEMORY'
ORDER BY table_size DESC;