Postgres no docker

 -- Criar compose

nano docker-compose.yml

version: '3.7'

###

services:

  db:

    image: postgres

    volumes:

      - postgres_data:/var/lib/postgresql/data

    environment:

      POSTGRES_PASSWORD: seu_password

      POSTGRES_DB: FastEPI

      POSTGRES_USER: intelisys

      POSTGRES_INITDB_OPTIONS: --encoding=UTF8 --locale=C --lc-collate=C --lc-ctype=C

    ports:

      - "5432:5432"

volumes:

  postgres_data:

###

docker-compose up -d

-- Configurando Firewall

# Verificar o status do firewall

sudo ufw status

# Permitir o tráfego na porta 5432

sudo ufw allow 5432/tcp

# Aplicar as regras

sudo ufw enable

-- Entrar no bash do container

Docker ps -- saber nome

docker exec -it root-db-1 bash

-- Conectar ao servidor PostgreSQL

psql -U postgres

-- Criar um banco de dados

CREATE DATABASE FastEPI;

-- Criar um usuário 

CREATE USER intelisys WITH PASSWORD 'seu_password';

-- Conceder todas as permissões no banco de dados

GRANT ALL PRIVILEGES ON DATABASE FastEPI TO intelisys;

Oracle - Listar datas do mês

 select TRUNC(SYSDATE)  + level - 1 dt

from   dual

connect by level <= (

  LAST_DAY(SYSDATE) - TRUNC(SYSDATE) + 1

)

Cursor com Oracle

 BEGIN

-- Create cursor and start loop

FOR C_PAR in (SELECT * FROM TGFPAR WHERE CODPARC < 100) LOOP

-- run commands for each item here

DBMS_OUTPUT.PUT_LINE(C_PAR.CODPARC || ' - ' || C_PAR.RAZAOSOCIAL);

END LOOP;

END;

Resultado:

Creating user to acess SQL databases on Azure

 

Open Azure Data Studio and execute these commands:

-- create login credentials on the server

USE master;

CREATE LOGIN user_name WITH password='p455w0rd';

GO

-- create user on the database

USE DATABASE_NAME;

CREATE USER user_name FROM LOGIN user_name;

GO

-- grant permission for user

USE DATABASE_NAME;

EXEC sp_addrolemember 'db_datareader', 'user_name';

EXEC sp_addrolemember 'db_datawriter', 'user_name';

EXEC sp_addrolemember 'db_backupoperator', 'user_name';

GO

SQL Backup Docker container

Create account in https://sqlbak.com/ and copy the key (You will need after)

Download sqlBak:

Run: docker run --name sqlbak_container -d -v sqlbak_volume:/opt/sqlbak pranasnet/sqlbak

Run the container with your key

Run: docker exec sqlbak_container sqlbak --register --key=YOUR_KEY_HERE -n sqlbak

Now you need to add a connection to DBMS (MySQL, PostgreSQL, MSSQL Server). 

Run: docker exec 9d8a0302a4a3 sqlbak -ac -dt <dbms_type> -h <host> -u <user_name> -p <password>

SQL Server on Docker Container

After instal Docker run following commands (depends on your main OS, maybe you need add "sudo" on begin for each command): 

// install SQL Server

docker pull mcr.microsoft.com/mssql/server:2017-latest

// run container

docker run --name sqlserver --hostname sqlserver -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourPasswordHere" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest 

// run container with autorun on restart (add --restart always)

docker run -- restart always --name sqlserver --hostname sqlserver -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourPasswordHere" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest 

// list services

docker ps -a

// open bash terminal inside into container

docker exec -it sqlserver "bash"

// run sqlcmd tool

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "YourPasswordHere"

// stop container

docker stop sqlserver

// remove 

docker rm sqlserver

Oracle - Week number per day

 Query:

SELECT TODAY, 

TO_CHAR(TODAY,'IW') "WEEK_NUM"

from (select (TO_DATE('01/01/'||TO_CHAR(2021), 'DD/MM/YYYY') - 1) + level TODAY 

from DUAL 

connect by LEVEL <= 366)

Result: