Postgres

Connection string

  postgresql://username:password@localhost:5432/demo_database

List databases, tables, etc

  -- List databases
\l
-- Connect to database
\c app
-- List tables
\dt
-- List columns in table
\d+ tablename

Create user, database and set owner

  CREATE DATABASE "user_database";
CREATE ROLE "user" WITH LOGIN PASSWORD 'new_password';
ALTER DATABASE "your_database_name" OWNER TO "user";

Secure version

Create

  CREATE DATABASE "my_database";
BEGIN;
CREATE SCHEMA app;
CREATE ROLE "my_user" WITH LOGIN PASSWORD 'new_password';
GRANT CONNECT ON DATABASE "my_database" TO "my_user";
GRANT USAGE ON SCHEMA public,app TO "my_user";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public,app TO "my_user";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public,app TO "my_user";
GRANT CREATE ON SCHEMA public,app TO "my_user";
COMMIT;

Drop

  DROP DATABASE "my_database";
DROP ROLE "my_user";

Update user password

  ALTER ROLE "user" WITH PASSWORD 'new_password';

Transaction

Will rollback if there’s any error. Create database if not allowed in transaction.

  BEGIN; -- Start the transaction

-- SQL statements within the transaction

COMMIT; -- End the transaction and commit the changes

Permit login

  ALTER ROLE "user" LOGIN;

Restore database

Routes server port 5432 → localhost 55555

  ssh -NT -L 55555:ip_addr:5432 ansible@ip_addr
psql -U my_user -h localhost -p 55555 my_database < backup.sql

Docker 

    db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: "${DB_USER}"
      POSTGRES_PASSWORD: "${DB_PASSWORD}"
      POSTGRES_DB: "${DB_NAME}"
    volumes:
      - ./data/db:/var/lib/postgresql/data
    ports:
      - "5432:5432"

MariaDB

Create user and db

  CREATE DATABASE my_database;

CREATE USER 'user'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES on my_database.* TO 'user'@'%';
FLUSH PRIVILEGES;

Docker 

    db:
    image: mariadb
    restart: always
    environment:
      MARIADB_ROOT_PASSWORD: "${DB_ROOT_PASSWORD}"
      MARIADB_DATABASE: "${DB_NAME}"
      MARIADB_USER: "${DB_USER}"
      MARIADB_PASSWORD: "${DB_PASSWORD}"
    volumes:
      - ./data/db:/var/lib/mysql
    ports:
      - "3306:3306"