Entrar a posgres, con el usuario postgres
psql -U postgres
Ejecutar comando sin paginación
psql -P pager -Upostgres myDatabase -c "select * from myTable"
No tener paginación en las consultas
\pset pager 0
Listar las bases de datos
\l
Borrar base de datos
drop database myDatabase;
Listar con detalles, como espacio utilizado por base de datos
\l+
Cambiar a una base de datos
\c database_name
Listar tablas
\dt
Describir tabla
\d table_name
Contar filas de una db
SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
LAG para utilizar el dato anterior, calcular la diferencia y mostrar las distintas de 0.
SELECT inserted_at,
asset,
free,
inc
FROM (
SELECT inserted_at,
asset,
free,
Cast(free AS FLOAT) - Cast( Lag(free) OVER ( ORDER BY inserted_at ) AS FLOAT ) AS inc
FROM balances
WHERE asset IN ('BTC')
ORDER BY inserted_at DESC limit 120 ) AS t
WHERE inc != 0;
Es necesario tener un select anidado para poder utilizar inc como filtro, sin esto no se puede usar en el where.
Obtener todos los indices de las tablas de la base de datos
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY tablename;
Backup de base de datos en contenedor.
-F para formato de salida (p, plain)
-E para encoding (UTF-8)
-c clean agregar drop table if exists
docker exec -t container_name pg_dump -c -F p -E UTF-8 -U postgres db_name > dump_`date +%Y%m%d_%H%M%S`.sql
Adicionalmente para no crear roles se pueden agregar las opciones
--no-owner --no-privileges
Restaurar base de datos a contenedor
cat dump_20221213_131716.sql | docker exec -i container_name psql -U postgres db_name
En algunas ocasiones el dump no era cargado porque no encontraba la extensión pgcrypto, la solución fue comentar la linea, incluida en el dump, relacionada con search_path.
-- SELECT pg_catalog.set_config('search_path', '', false);
docker-compose de postgres
version: '3.8'
services:
db:
image: postgres:14.1-alpine
restart: always
environment:
- POSTGRES_USER=postgres
- POSTGRES_PASSWORD=postgres
ports:
- '5432:5432'
volumes:
- db:/var/lib/postgresql/data
volumes:
db: