PostgreSQL (often referred to as Postgres) is an open-source relational database management system that is known for its extensibility and strict adherence to SQL standards. It is a free and powerful database solution, suitable for a wide range of applications, from small projects to large enterprise systems. PostgreSQL is widely used for its reliability, feature-richness, and robustness.
To create a new Proxmox VE PostgreSQL LXC, run the command below in the Proxmox VE Shell.
bash -c "$(wget -qLO - https://github.com/community-scripts/ProxmoxVE/raw/main/ct/postgresql.sh)"
Default Settings: 1GB RAM - 4GB Storage - 1vCPU
To make sure our PostgreSQL is secured with a strong password, set a password for its system user and then change the default database admin user account
Change user password
passwd postgres
Login using Postgres system account
su - postgres
Now, change the Admin database password
psql -c "ALTER USER postgres WITH PASSWORD 'your-password';"
Create a new user.
psql
CREATE USER admin WITH PASSWORD 'your-password';
Create a new database:
CREATE DATABASE homeassistant;
Grant all rights or privileges on created database to the user
GRANT ALL ON DATABASE homeassistant TO admin;
To exit psql
\q
Then type exit to get back to root
Change the recorder: db_url: in your HA configuration.yaml
Example:
recorder:
db_url: postgresql://admin:your-password@192.168.100.20:5432/homeassistant?client_encoding=utf8
Adminer is a full-featured database management tool
Adminer Interface: IP/adminer/