Postgres

questions

terminology

postgres users are called roles
cluster is an instance of postgres server.
database is a collection of schemas
schema has many tables.
every database has a public schema everyone can read and write by default.

Tablespaces are storage locations where database objects such as tables and indexes are stored in the file system.
postgres creates the pg_default and pg_global tablespaces

security

roles are like users in postgres
There are group roles and login roles
a login roles has a set password and a group role doesn't.
This kinda solves authentication, authorization, grouping (and inheritance) in one single "module"

there are 3 lvls of granting privileges

grant connect: allows connection
grant select: select data from specific tables, views or sequences
grant usage: grants permission to use certain basic functionalities of schemas but no data manipulation like select, insert... on the objects within a schema.

(pgAdmin)
granting privileges on the security tab (Usage, Create...) is related to schemas or other BD objects. (more higher level). also allows to use the schema itself.
grainting privileges on the default Privileges tab is perms related to tables, sequences, functions...

To summarize, the Security Tab privileges are more about the ability to interact with schemas and database objects at a structural level, while the Default Privileges Tab privileges are about controlling access to the data within tables and views.

privileges are not hierachical and are not inherited from containing objects.

https://www.youtube.com/watch?v=-2kYJ0gZmCo&ab_channel=E-MultiSkillsDatabaseservices

working with GIS data

Para que la base de datos pueda trabajar con data GIS (geolocalizacion) necesita la extension de postgis.

para instalar postgis parece que hay que instalar unos paquetes en el host OS y tambien agregar la extension a las bases de datos que las necesiten.

como ahora postgres corre en un contenedor de docker, hay un layer de abstraccion por medio, e instalar los paquetes de postgis es un poquito mas complejo. No puedo simplemente entrar al contenedor en instalar los paquetes con apt-get, pq no van a persistir cuando apague el contenedor (que creo fue lo que se hizo)

cambié la imagen de Postgres a una imagen de Postgis que se supone que es un fork de la img regular y que simplemente agrega los paquetes de postgis. no eliminó nada ni rompió la replicación.

replication

Usuario replica es el que tiene permisos de replicacion en las dos bases de datos.

se configuró streaming replication asincrona. No espera respuesta de los servidores standby para seguir operando. Los servidores standby (Miami) no tienen permiso de escritura.

streaming (physical) everything
or logical replication (specific databases / tables)

for streaming replication there seem to be these features
!Pasted image 20240419114947.png
failover would be good for our case. how does traffic get redirected?
I think failover detects if DB goes down and gives write permission to stand-by server.

Redirecting traffic needs to be handled by another service. there is a middleware called pgpool that can handle load balancing, HA, connection pooling, proxy, cache. Similarly there is another one called repmgr.

guias de replicacion:

replicacion asincrona:

notepad

get in container and run psql: docker exec -it postgres bash -c "psql --user postgres"

pg_basebackup --host=10.0.0.0 --pgdata=/var/lib/postgresql/data --username=replica --verbose --progress --wal-method=stream --write-recovery-conf --checkpoint=fast --create-slot --slot=replication

docker run -it --rm --net docker-fixed-network -v ./data/:/var/lib/postgresql/data/ --entrypoint /bin/bash postgres
pg_basebackup -h 10.0.0.0 -p 5432 -U replica -D /data/ -Fp -Xs -R

/var/lib/postgresql/archive/