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.
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: