Mostly Used Command

README.md

Docker

Copy some file to docker container

docker cp <file_to_copy> <container_name>:<dir_destination_in_docker>

PostgreSQl

Restore pg sql dump to docker container

docker container exec <container_db> psql -U <db_user> <db_name> -f <backup>.sql

Backup pg sql data only

docker container exec -it <container_db> pg_dump --data-only -U <db_user> <db_name> > <backup>.sql

Check/List pg enum

SELECT unnest(enum_range(NULL::<enum_name>))

Execute outside *.sql from outside with root

psql -U postgres -f db.sql

CREATE DATABASE <db_name>;
CREATE USER <db_user> WITH ENCRYPTED PASSWORD '<db_pass>';
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <db_user>;

Restore psql with suffix .gz

gunzip path/to/backup.gz

Backup all database

pg_dumpall -U <db_user> > <backup_name>.sql

Backup database

pg_dump -h <host> -U <db_user> -d <db_name> -F c -b -v -f backup-data-$(date +\%Y\%m\%d-\%s).tar

Restore backup dump

pg_restore -U <db_user> -d <db_name> -c -v backup-data-$(date +\%Y\%m\%d-\%s).tar

pg_restore -U <db_user> -d <db_name> -v --no-acl --no-owner -c backup-data-$(date +\%Y\%m\%d-\%s).tar

Get long query running

WITH long_running AS (
    SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
    FROM pg_stat_activity
    WHERE (now() - pg_stat_activity.query_start) > interval '1 minutes'
      and state = 'active'
)
SELECT * from long_running;

Netcut

Healtcheck SMTP using netcut

nc -v -u smtp.example.com 587

Golang Migrate

Native migrate With Golang

migrate -source file://<migrations_dir> -database "postgres://<db_user>:<db_pass>@localhost:5432/<db_name>?sslmode=disable" up