Banco de Dados

SGBD

MySQL Server

Servidor (3306)
- MySQL Server (install mysql)
- Docker MySQL
Cliente
- MySQL Workbench
- DBeaver
- Adminer (docker adminer)
- mysql (CLI)
- VSCode:

$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  mysql:8.3
 
$ docker exec -it mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.3.0 MySQL Community Server - GPL
 
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker stop $(docker ps -a -q -f name=mysql ) && docker rm $(docker ps -a -q -f name=mysql )

$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  mysql:8.3
 
$ docker exec -it mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.3.0 MySQL Community Server - GPL
 
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker stop $(docker ps -a -q -f name=mysql ) && docker rm $(docker ps -a -q -f name=mysql )

Volume:

$ docker volume create mysql-data
 
$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  -v mysql-data:/var/lib/mysql \
  mysql:8.3

$ docker volume create mysql-data
 
$ docker run -d \
  --name mysql \
  -e MYSQL_ROOT_PASSWORD=secret \
  -v mysql-data:/var/lib/mysql \
  mysql:8.3

Docker Compose (MySQL, Adminer):

/codes/database/mysql-server/docker-compose.yml

services:
 
  mysql:
    image: mysql:8.3
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-tier
    ports:
      - 3306:3306
    volumes:
      - mysql_data:/var/lib/mysql
      # - ./data:/var/lib/mysql
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - mysql
 
networks:
  app-tier:
    driver: bridge
 
volumes:
  mysql_data:

/codes/database/mysql-server/docker-compose.yml

services:
 
  mysql:
    image: mysql:8.3
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-tier
    ports:
      - 3306:3306
    volumes:
      - mysql_data:/var/lib/mysql
      # - ./data:/var/lib/mysql
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - mysql
 
networks:
  app-tier:
    driver: bridge
 
volumes:
  mysql_data:

Ao criar este arquivo .env.example renomei para .env:

/codes/database/mysql-server/.env.example

MYSQL_ROOT_PASSWORD=secret
MYSQL_DATABASE=example
MYSQL_USER=devuser
MYSQL_PASSWORD=secret

/codes/database/mysql-server/.env.example

MYSQL_ROOT_PASSWORD=secret
MYSQL_DATABASE=example
MYSQL_USER=devuser
MYSQL_PASSWORD=secret

Instalar docker compose plugin e verifique localmente:

$ docker compose version
Docker Compose version vxxxx
 
$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                docker:default
[+] Running 3/3
 ✔ Network mysql-server_app-tier     Created                     0.0s
 ✔ Container mysql-server-mysql-1    Started                     0.1s
 ✔ Container mysql-server-adminer-1  Started                     0.1s
 
$ docker compose ps
NAME                     IMAGE         COMMAND                  SERVICE   CREATED          STATUS          PORTS
mysql-server-adminer-1   adminer:4.7   "entrypoint.sh docke…"   adminer   11 seconds ago   Up 10 seconds   0.0.0.0:8080->8080/tcp
mysql-server-mysql-1     mysql:8.0     "docker-entrypoint.s…"   mysql     11 seconds ago   Up 10 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp
 
$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

$ docker compose version
Docker Compose version vxxxx
 
$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                docker:default
[+] Running 3/3
 ✔ Network mysql-server_app-tier     Created                     0.0s
 ✔ Container mysql-server-mysql-1    Started                     0.1s
 ✔ Container mysql-server-adminer-1  Started                     0.1s
 
$ docker compose ps
NAME                     IMAGE         COMMAND                  SERVICE   CREATED          STATUS          PORTS
mysql-server-adminer-1   adminer:4.7   "entrypoint.sh docke…"   adminer   11 seconds ago   Up 10 seconds   0.0.0.0:8080->8080/tcp
mysql-server-mysql-1     mysql:8.0     "docker-entrypoint.s…"   mysql     11 seconds ago   Up 10 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp
 
$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

Create Database

$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE DATABASE monitor_db;
Query OK, 1 row affected (0.01 sec)
 
mysql> CREATE DATABASE monitor_db;
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| monitor_db         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)
 
mysql> USE monitor_db;
Database changed
 
mysql> CREATE TABLE hosts (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(255) NOT NULL,
    ->   address VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| hosts                |
+----------------------+
1 row in set (0.00 sec)
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   |     | NULL    |                |
| address | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

$ docker compose exec mysql mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE DATABASE monitor_db;
Query OK, 1 row affected (0.01 sec)
 
mysql> CREATE DATABASE monitor_db;
 
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| example            |
| information_schema |
| monitor_db         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)
 
mysql> USE monitor_db;
Database changed
 
mysql> CREATE TABLE hosts (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(255) NOT NULL,
    ->   address VARCHAR(255) NOT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)
 
mysql> SHOW TABLES;
+----------------------+
| Tables_in_monitor_db |
+----------------------+
| hosts                |
+----------------------+
1 row in set (0.00 sec)
 
mysql> DESCRIBE hosts;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int          | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | NO   |     | NULL    |                |
| address | varchar(255) | NO   |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> quit
Bye
 
$ docker compose down
[+] Running 3/3
 ✔ Container mysql-server-adminer-1  Removed                     0.3s
 ✔ Container mysql-server-mysql-1    Removed                     1.4s
 ✔ Network mysql-server_app-tier     Removed                     0.2s

Backup

$ docker compose exec mysql mysqldump -u root -p monitor_db > database.sql
 
$ docker compose exec mysql mysqldump -u root -p --all-databases > database.sql

$ docker compose exec mysql mysqldump -u root -p monitor_db > database.sql
 
$ docker compose exec mysql mysqldump -u root -p --all-databases > database.sql

Recovery

$ docker compose exec mysql mysql -u root -p monitor_db < database.sql
 
$ docker compose exec mysql mysql -u root -p < database.sql

$ docker compose exec mysql mysql -u root -p monitor_db < database.sql
 
$ docker compose exec mysql mysql -u root -p < database.sql

Postgres Server

Server
- Postgres (install)
- Docker Postgres
Client
- pgAdmin (dpage/pgadmin4)
- DBeaver
- Adminer (docker adminer)
- psql (CLI)
- VSCode
  - SQLTools
  - PostgreSQL

Postgres Docker:

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  postgres:16.2
 
$ docker exec -it postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  postgres:16.2
 
$ docker exec -it postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q

Volume:

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16.2

$ docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=secret \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16.2

Docker Compose (Postgres, PgAdmin 4):

/codes/database/postgres-server/docker-compose.yml

services:
 
  postgres:
    container_name: postgres
    image: postgres:16.2
    restart: always
    environment:
      # POSTGRES_USER: postgres # default
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    networks:
      - app-tier
    ports:
      - 5432:5432
    volumes:
      - postgres_data:/var/lib/postgresql/data
      # - ./data:/var/lib/postgresql/data
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - postgres
 
  # pgadmin:
  #   container_name: pgadmin
  #   image: dpage/pgadmin4:8.3
  #   restart: always
  #   environment:
  #     PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL}
  #     PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
  #   networks:
  #     - app-tier
  #   ports:
  #     - 8080:80
  #   depends_on:
  #     - postgres
 
volumes:
  postgres_data:
 
networks:
  app-tier:
    driver: bridge

/codes/database/postgres-server/docker-compose.yml

services:
 
  postgres:
    container_name: postgres
    image: postgres:16.2
    restart: always
    environment:
      # POSTGRES_USER: postgres # default
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    networks:
      - app-tier
    ports:
      - 5432:5432
    volumes:
      - postgres_data:/var/lib/postgresql/data
      # - ./data:/var/lib/postgresql/data
 
  adminer:
    image: adminer:4.8.1
    restart: always
    networks:
      - app-tier
    ports:
      - 8080:8080
    depends_on:
      - postgres
 
  # pgadmin:
  #   container_name: pgadmin
  #   image: dpage/pgadmin4:8.3
  #   restart: always
  #   environment:
  #     PGADMIN_DEFAULT_EMAIL: ${PGADMIN_EMAIL}
  #     PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_PASSWORD}
  #   networks:
  #     - app-tier
  #   ports:
  #     - 8080:80
  #   depends_on:
  #     - postgres
 
volumes:
  postgres_data:
 
networks:
  app-tier:
    driver: bridge

/codes/database/postgres-server/.env.example

POSTGRES_PASSWORD=secret

PGADMIN_EMAIL=user@email.com
PGADMIN_PASSWORD=secret

/codes/database/postgres-server/.env.example

POSTGRES_PASSWORD=secret

PGADMIN_EMAIL=user@email.com
PGADMIN_PASSWORD=secret

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

PostgreSQL Client Applications

clusterdb — cluster a PostgreSQL database
createdb — create a new PostgreSQL database
createuser — define a new PostgreSQL user account
dropdb — remove a PostgreSQL database
dropuser — remove a PostgreSQL user account
ecpg — embedded SQL C preprocessor
pg_amcheck — checks for corruption in one or more PostgreSQL databases
pg_basebackup — take a base backup of a PostgreSQL cluster
pgbench — run a benchmark test on PostgreSQL
pg_config — retrieve information about the installed version of PostgreSQL
pg_dump — extract a PostgreSQL database into a script file or other archive file
pg_dumpall — extract a PostgreSQL database cluster into a script file
pg_isready — check the connection status of a PostgreSQL server
pg_receivewal — stream write-ahead logs from a PostgreSQL server
pg_recvlogical — control PostgreSQL logical decoding streams
pg_restore — restore a PostgreSQL database from an archive file created by pg_dump
pg_verifybackup — verify the integrity of a base backup of a PostgreSQL cluster
psql — PostgreSQL interactive terminal
reindexdb — reindex a PostgreSQL database
vacuumdb — garbage-collect and analyze a PostgreSQL database

psql

Command	Description
`help`, `\h`	Help
`\q`	Quit
`\l`	List databases
`\c <database>`	Connect to a database
`\d`	List tables in database
`\d <table>`	Show table
`\d+ <table>`	More detailed table definition
`\dt .`	List tables from all schemas
`\du`	List users
`\dn`	List schemas
`\df`	List functions

Create Database

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# CREATE DATABASE monitor_db;
 
monitor_db=# \l
 monitor_db | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 postgres   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 
postgres=# \c monitor_db
 
monitor_db=# CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);
 
monitor_db=# \dt public.*
 public | hosts | table | postgres
 
monitor_db=# \d hosts
 id       | integer                |           | not null | nextval('hosts_id_seq'::regclass)
 name     | character varying(255) |           | not null |
 address  | character varying(255) |           | not null |
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

$ ls -a
.  ..  .env  docker-compose.yml
 
$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network postgres-server_app-tier     Created                                                                               0.0s
 ✔ Container postgres                   Started                                                                               0.1s
 ✔ Container postgres-server-adminer-1  Started                                                                               0.1s
 
$ docker compose ps
NAME                        IMAGE           COMMAND                  SERVICE    CREATED          STATUS          PORTS
postgres                    postgres:16.2   "docker-entrypoint.s…"   postgres   37 seconds ago   Up 36 seconds   0.0.0.0:5432->5432/tcp
postgres-server-adminer-1   adminer:4.8.1   "entrypoint.sh php -…"   adminer    37 seconds ago   Up 36 seconds   0.0.0.0:8080->8080/tcp
 
$ docker compose exec postgres psql -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.
 
postgres=# CREATE DATABASE monitor_db;
 
monitor_db=# \l
 monitor_db | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 postgres   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           |
 template0  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 
postgres=# \c monitor_db
 
monitor_db=# CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);
 
monitor_db=# \dt public.*
 public | hosts | table | postgres
 
monitor_db=# \d hosts
 id       | integer                |           | not null | nextval('hosts_id_seq'::regclass)
 name     | character varying(255) |           | not null |
 address  | character varying(255) |           | not null |
 
postgres=# \q
 
$ docker compose down
[+] Running 3/3
 ✔ Container postgres-server-adminer-1  Removed                                                                               0.4s
 ✔ Container postgres                   Removed                                                                               0.4s
 ✔ Network postgres-server_app-tier     Removed                                                                               0.2s

Backup

$ psql -l -U postgres
 
$ pg_dump database_name_here > database.sql
 
$ pg_dump -Fc database_name_here > database.bak # compressed binary format
 
$ pg_dump -Ft database_name_here > database.tar # tarball

$ psql -l -U postgres
 
$ pg_dump database_name_here > database.sql
 
$ pg_dump -Fc database_name_here > database.bak # compressed binary format
 
$ pg_dump -Ft database_name_here > database.tar # tarball

Recovery

$ psql -d database_name_here < database.sql
 
$ psql -d database_name_here -f database.sql
 
$ pg_restore database.sql
 
$ pg_restore -Fc database.bak # restore compressed binary format
 
$ pg_restore -Fc -C database.bak # create the database before restoring into it
 
$ pg_restore -Ft database.tar # restore tarball

$ psql -d database_name_here < database.sql
 
$ psql -d database_name_here -f database.sql
 
$ pg_restore database.sql
 
$ pg_restore -Fc database.bak # restore compressed binary format
 
$ pg_restore -Fc -C database.bak # create the database before restoring into it
 
$ pg_restore -Ft database.tar # restore tarball

SQLite

SQLite
Docker SQLite
client
- DB Browser for SQLite
- DBeaver
- Adminer (docker adminer)
- sqlitebrowser
- sqlite3 (CLI)
- VSCode
  - SQLite
  - SQlite Viewer

SQLite Docker:

/codes/database/sqlite/Dockerfile

FROM alpine:3.19

RUN apk --no-cache add sqlite

WORKDIR /db

# ENTRYPOINT ["/bin/sh"]

# CMD [ "sqlite3", "initial.db" ]

/codes/database/sqlite/Dockerfile

FROM alpine:3.19

RUN apk --no-cache add sqlite

WORKDIR /db

# ENTRYPOINT ["/bin/sh"]

# CMD [ "sqlite3", "initial.db" ]

$ docker build -t sqlite:1.0 .
 
$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
 
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .quit
 
/db # exit

$ docker build -t sqlite:1.0 .
 
$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .quit
 
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .quit
 
/db # exit

sqlite3 (dot-commands)

.databases: List names and files of attached databases
.dump: Dump database in SQL text format
.help: Show help
.quit: Stop interpreting input stream
.schema: Show the CREATE statements
.show: Displays current settings for various parameters
.tables: List names of tables

Data Types:

NULL
INTEGER: signed integer (1, 2, 3, 4, 6, 8 bytes)
REAL: floating point value (8-byte IEEE floating point number)
TEXT: text string (UTF-8, UTF-16BE or UTF-16LE)
BLOB: blob of data

Create Database

$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .databases
main: /db/database.db r/w
 
sqlite> CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);
 
sqlite> .tables
hosts
 
sqlite> .schema hosts
CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   address TEXT NOT NULL
 );
 
sqlite> .quit
 
/db # exit

$ docker run -it --rm -v ./data:/db sqlite:1.0 sh
/db # sqlite3 database.db
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .databases
main: /db/database.db r/w
 
sqlite> CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);
 
sqlite> .tables
hosts
 
sqlite> .schema hosts
CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   address TEXT NOT NULL
 );
 
sqlite> .quit
 
/db # exit

Backup

$ sqlite3 ./dev.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
sqlite> .schema table_name
sqlite> .dump
sqlite> .dump table_name
sqlite> .output ./backup.sql
sqlite> .exit
 
$ sqlite3 dev.db .dump > dev.sql

$ sqlite3 ./dev.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .schema
sqlite> .schema table_name
sqlite> .dump
sqlite> .dump table_name
sqlite> .output ./backup.sql
sqlite> .exit
 
$ sqlite3 dev.db .dump > dev.sql

Recovery

$ sqlite3 ./restore.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .read ./backup.sql
sqlite> .exit
 
$ sqlite3 restore.db < backup.sql

$ sqlite3 ./restore.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> .read ./backup.sql
sqlite> .exit
 
$ sqlite3 restore.db < backup.sql

MongoDB Server

Server
- MongoDB (install)
- Docker Mongo
- MongoDB Atlas
Client
- MongoDB Compass
- DBeaver
- Adminer (docker adminer)
- mongo-express
- MongoDB Shell - mongosh (CLI)
- VSCode
  - MongoDB for VS Code
Guias
- bradtraversy/mongodb_cheat_sheet.md
- SQL to MongoDB Mapping Chart

Mongo Docker:

$ docker run --name mongo -d mongo:7.0
 
$ docker exec -it mongo mongosh
Current Mongosh Log ID: 65de1e4556e305af36af7b82
Connecting to:          mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.
 
test>exit

$ docker run --name mongo -d mongo:7.0
 
$ docker exec -it mongo mongosh
Current Mongosh Log ID: 65de1e4556e305af36af7b82
Connecting to:          mongodb://127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.
 
test>exit

Volume:

$ docker run -d \
  --name mongo \
  -e MONGO_ROOT_USERNAME=root MONGO_ROOT_PASSWORD=secret \
  -v mongodata:/data/db \
  mongo:7.0

$ docker run -d \
  --name mongo \
  -e MONGO_ROOT_USERNAME=root MONGO_ROOT_PASSWORD=secret \
  -v mongodata:/data/db \
  mongo:7.0

Docker Compose (Mongo, Mongo Express):

$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network mongo-server_app-tier  Created                                                                                     0.1s
 ✔ Container mongo                Started                                                                                     0.7s
 ✔ Container mongo-express        Started                                                                                     0.1s
 
$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test>exit
 
$docker compose down
[+] Running 3/3
 ✔ Container mongo-express        Removed                                                                                     0.0s
 ✔ Container mongo                Removed                                                                                     0.5s
 ✔ Network mongo-server_app-tier  Removed                                                                                     0.2s

$ docker compose up -d
[+] Building 0.0s (0/0)                                                                                             docker:default
[+] Running 3/3
 ✔ Network mongo-server_app-tier  Created                                                                                     0.1s
 ✔ Container mongo                Started                                                                                     0.7s
 ✔ Container mongo-express        Started                                                                                     0.1s
 
$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test>exit
 
$docker compose down
[+] Running 3/3
 ✔ Container mongo-express        Removed                                                                                     0.0s
 ✔ Container mongo                Removed                                                                                     0.5s
 ✔ Network mongo-server_app-tier  Removed                                                                                     0.2s

mongosh

use: Set current database
show
- show databases, show dbs: Print a list of all available databases.
- show collections, show tables: Print a list of all collections for current database.
- show profile: Prints system.profile information.
- show users: Print a list of all users for current database.
- show roles: Print a list of all roles for current database.
- show logs: Print all logs.

Create Database (CRUD)

Create
- db.<collection>.insertOne()
- db.<collection>.insertMany()
- db.<collection>.bulkWrite()
Read
- db.<collection>.find()
- db.<collection>.aggregate()
Update
- db.<collection>.updateOne()
- db.<collection>.updateMany()
- db.<collection>.findOneAndUpdate()
Delete
- db.<collection>.deleteOne()
- db.<collection>.deleteMany()
- db.<collection>.findOneAndDelete()

$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test> show dbs
admin   100.00 KiB
config   12.00 KiB
local    72.00 KiB
 
test> use monitor_db
switched to db monitor_db
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
 
monitor_db> db.getCollectionNames()
[]
 
monitor_db> db.hosts.insertOne(
...   {
...     name: 'Google DNS',
...     address: '8.8.8.8'
...   }
... )
{
  acknowledged: true,
  insertedId: ObjectId('66de735b5e9174c5eec76a8c')
}
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
monitor_db   40.00 KiB
 
 
monitor_db> db.getCollectionNames()
[ 'hosts' ]
 
monitor_db> db.hosts.find()
[
  {
    _id: ObjectId('66de735b5e9174c5eec76a8c'),
    name: 'Google DNS',
    address: '8.8.8.8'
  }
]
 
monitor_db> exit

$ docker compose exec mongo mongosh -u root -p
Enter password: ******
Current Mongosh Log ID: 65de236628a45b1cc031174b
Connecting to:          mongodb://<credentials>@127.0.0.1:27017/?directConnection=true&serverSelectionTimeoutMS=2000&appName=mongosh+2.1.4
Using MongoDB:          7.0.5
Using Mongosh:          2.1.4
 
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
 
------
   The server generated these startup warnings when booting
   2024-02-27T17:59:07.794+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem
   2024-02-27T17:59:08.592+00:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'
   2024-02-27T17:59:08.592+00:00: vm.max_map_count is too low
------
 
test> show dbs
admin   100.00 KiB
config   12.00 KiB
local    72.00 KiB
 
test> use monitor_db
switched to db monitor_db
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
 
monitor_db> db.getCollectionNames()
[]
 
monitor_db> db.hosts.insertOne(
...   {
...     name: 'Google DNS',
...     address: '8.8.8.8'
...   }
... )
{
  acknowledged: true,
  insertedId: ObjectId('66de735b5e9174c5eec76a8c')
}
 
monitor_db> show dbs
admin       100.00 KiB
config       12.00 KiB
local        72.00 KiB
monitor_db   40.00 KiB
 
 
monitor_db> db.getCollectionNames()
[ 'hosts' ]
 
monitor_db> db.hosts.find()
[
  {
    _id: ObjectId('66de735b5e9174c5eec76a8c'),
    name: 'Google DNS',
    address: '8.8.8.8'
  }
]
 
monitor_db> exit

Databases

Database	Create Database	Use Database	Create Table	Insert table
MySQL	`CREATE DATABASE database_name;`	`USE database_name;`	`CREATE TABLE table_name (...);`	`INSERT INTO table_name ...;`
Postgres	`CREATE DATABASE database_name;`	`\c database_name`	`CREATE TABLE table_name (...);`	`INSERT INTO table_name ...;`
SQLite		`sqlite3 database_name.db`	`CREATE TABLE table_name (...);`	`INSERT INTO table_name ...;`
MongoDB	`use database_name;`	`use database_name;`		`db.collection_name.insertOne();`

SQL (CREATE TABLE)

Banco de Dados Comando SQL

MySQL

Banco de Dados	Comando SQL
MySQL	`CREATE TABLE hosts ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );` `CREATE TABLE hosts ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );`
PostgreSQL	`CREATE TABLE hosts ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );` `CREATE TABLE hosts ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, address VARCHAR(255) NOT NULL );`
SQLite	`CREATE TABLE hosts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL );` `CREATE TABLE hosts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL );`

CREATE TABLE hosts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

CREATE TABLE hosts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

PostgreSQL

CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

CREATE TABLE hosts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL
);

SQLite

CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);

CREATE TABLE hosts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  address TEXT NOT NULL
);

Tipos de Dados

Types	MySQL	Postgres	SQLite
Character	`CHAR(n)`, `VARCHAR(n)`, `TINYTEXT(n)`, `TEXT(n)`, `MEDIUMTEXT(n)`, `LONGTEXT(n)`, `BINARY(n)`, `VARBINARY(n)`	`CHARACTER (N)`, `CHAR(n)`, `CHARACTER VARYING (N)`, `VARCHAR(n)`, `TEXT`	`TEXT`
Numeric	`BIT`, `TINYINT`, `SMALLINT`, `MEDIUMINT`, `INT`, `BIGINT`, `DECIMAL`, `FLOAT`, `DOUBLE`, `BOOLEAN` (`TINYINT(1)`)	`SMALLINT`, `INTEGER`, `BIGINT`, `DECIMAL`, `NUMERIC`, `REAL`, `DOUBLE`, `SMALLSERIAL`, `SERIAL`, `BIGSERIAL`,	`NUMERIC`, `INTEGER`, `REAL`
Date/Time	`DATE`, `DATETIME`, `TIMESTAMP`, `TIME`, `YEAR`	`DATE`, `INTERVAL` , `TIMESTAMP`, `TIME`
Large Object (LOB)	`TINYBLOB`, `BLOB(n)`, `MEDIUMBLOB`, `LONGTEXT`	`BYTEA`	`BLOB`
Especial	Espacial (`POINTS`, `LINES`), `JSON`	`MONEY`, `BOOLEAN`, `ENUM`, Espacial (`POINTS`, `LINES`), Network (`INET`, `CIDR`, `MACADDR`), `TSQUERY` , `UUID`, `XML`, `JSON`, `JSONB`, `ARRAY`, `TYPE`, `NUMRANGE`

Referências

SGBD
- MySQL
- Postgres
- MongoDB
- SQLite
Tipo de dados
- MySQL
- Postgres
- SQLite

Editar esta página