PostgreSQL Server

¿Qué es un servidor PostgreSQL?

PostgreSQL is a popular open-source relational database management system (RDBMS). It is often referred to simply as "Postgres" and is known for its reliability, performance, and feature richness.

A PostgreSQL server is a software program that runs on a computer and manages a PostgreSQL database. It is responsible for storing and organizing the data in the database, and for providing access to the data for other programs and users.

A PostgreSQL server typically runs on a dedicated server or computer, and can be accessed by other systems or programs over a network using a standard network protocol, such as TCP/IP. The server supports a wide range of client applications and programming languages, and can be integrated with other systems and tools, such as web servers and data analysis tools.

Overall, a PostgreSQL server is a powerful and versatile tool for managing and accessing data in a PostgreSQL database. It is widely used in a variety of applications and industries, and is known for its reliability, performance, and feature richness.

Ubuntu ssh connection

Create image with pi imager, enable ssh and wifi connection in settings or network-config file. Enable ssh connection: sudo apt update; sudo apt upgrade; sudo apt install net-tools; sudo apt install openssh-server; sudo apt ufw enable;sudo ufw allow 22; sudo ufw status Cargar en VSCode: Remote SSH plugin; si no carga eliminar known_hosts (C:\Users\casas.ssh)

Instalar postgresql (version 12) en Ubuntu

allow ports: sudo ufw allow 5432; sudo reboot now Instalarlo: sudo apt install postgresql Actividad: systemctl status postgresql.service Find postgres files: cd /lib/systemd/system/; cat postgresql.service; ls -l postgres*; cat postgresql@.service Añadir contraseña: sudo cat /etc/passwd; sudo cat /etc/group; sudo cat /etc/shadow; sudo passwd postgres ('ubu_postgres') Iniciar postgres: su postgres; psql; '\l'; du; create database apps; ; Operaciones con la db (crear db, tablas, squemas, insertar, drop...); \q; exit; pg_

su postgres; psql
'\l'; du
create database apps; <----- ojo,  
Operaciones con la db (crear db, tablas, squemas, insertar, drop...); 
\q; exit

Configurar conexiones remotas

open the port 5432: /etc/postgresql/14/main/postgresql.conf; sudo nano postgresql.conf (listen_addresses='*'); service; service postgresql restart; service postgresql status; configurar el ususario remoto con acceso a todo: sudo nano pg_hba.conf (client authentification, add in IPv4 local connections host-all-postgres-all-md5; service postgresql restart; service postgresql status; change user encripted: access db arduino; c arduino; sudo -u postgres psql arduino; arduino=# alter user postgres with encrypted password 'ubu_postgres'; ; invoke-rc.d postgresql restart

sudo nano pg_hba.conf (client authentification, add in IPv4 local connections host-all-postgres-all-md5
service postgresql restart; service postgresql status

access db arduino
c arduino
sudo -u postgres psql arduino
alter user postgres with encrypted password 'ubu_postgres';

Instalar postgresql en Windows: Los archivos están en programData>PostgreSQL (igual ubuntu) -> Hay que modificar firewall.

Instalar Python

Instalar desde la extensión de VSCode Python, Arduino, SerialMonitor and SQLTools. Instalar modulos python con pip.

sudo apt-get install libpq-dev
sudo apt-get install python3-Psycopg2
sudo apt-get install python3-pip

pip install pyserial
sudo apt-get install libpq-dev
sudo pip3 install Psycopg2

linux server: https://www.youtube.com/watch?v=uyF3gC9lQtE instalacion postgresql en ubuntu server: https://www.youtube.com/watch?v=2UUwHg1-erE configurar conexiones remotas: https://www.youtube.com/watch?v=Y0fluLyEEds move default data directory: https://www.youtube.com/watch?v=uPM4SwpPP4M C:\Program Files\PostgreSQL\13\data Instalar Python and Psycopg2: https://www.codevoila.com/post/2/python3-connect-postgresql-with-psycopg2-on-ubuntu