012-postgres

postgres install¶

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt install -y postgresql-15 postgresql-client-15
sudo apt install -y postgresql-contrib-15
/usr/lib/postgresql/*/bin/postgres -V
sudo service postgresql restart

postgres dev config¶

ALTER USER postgres WITH PASSWORD 'passw0rd';
CREATE USER user0 WITH PASSWORD 'passw0rd';
ALTER USER user0 WITH SUPERUSER;

sudo -u postgres -H -- psql postgres -f $DIR/postgres-config.sql

postgres usefull config¶

https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e

sudo -u postgres -H -- psql
CREATE USER admin WITH PASSWORD '12345';
create database test_db;
grant all privileges on database test_db to admin;

docker pgadmin¶

extra_hosts:
- "host.docker.internal:host-gateway"

how-to-configure-postgresql-to-accept-all-incoming-connections¶

PostgreSQL访问控制，允许&禁止指定IP访问
https://stackoverflow.com/questions/3278379/how-to-configure-postgresql-to-accept-all-incoming-connections

sudo vim /etc/postgresql/15/main/pg_hba.conf

hostssl    all             all             xxx.xxx.xx.xxx/32       md5
hostssl    all             all             192.168.1.0/24         md5
host    all             all             172.0.0.0/8             md5

password url encoding¶

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]