Postgres

Install

container xenial 16.04 (optional)

lxc image copy images:ubuntu/xenial/amd64 local: --alias xenial
lxc launch xenial postgres
lxc exec postgres bash

sudo apt install postgresql

eventually open network for remote connections

listen_addresses = '*'

Basic operations

create role (user)

-- User: gianoauth
DROP USER gianoauth;
CREATE USER gianoauth WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION;
ALTER USER gianoauth SET search_path TO gianoauth;
ALTER ROLE gianoauth IN DATABASE giano SET search_path TO gianoauth;
GRANT ALL PRIVILEGES ON SCHEMA gianoauth TO gianoauth;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA gianoauth TO gianoauth;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA gianoauth TO gianoauth;

developer containers

pgadmin:

user: admin@admin

pass: admin@admin

set static IP on containers

/var/lib/lxd/networks/lxdbr0/dnsmasq.hosts
├── pgmodeler
└── postgres

cat > /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts/pgmodeler <<EOF
00:16:3e:9d:ad:d2,10.8.120.205,pgmodeler
EOF
cat > /var/lib/lxd/networks/lxdbr0/dnsmasq.hosts/postgres <<EOF
00:16:3e:ce:41:53,10.8.120.228,postgres
EOF

pgadmin

apt install wget python3-pip
pip3 install -U pip
cd /tmp
wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v2.0/pip/pgadmin4-2.0-py2.py3-none-any.whl
pip install ./pgadmin4-2.0-py2.py3-none-any.whl
# define ip binding
CONFIG=/usr/local/lib/python3.5/dist-packages/pgadmin4/config.py
sed -i -e "s/DEFAULT_SERVER\s*=.*/DEFAULT_SERVER = '0.0.0.0'/" $CONFIG
# define pgadmin admin user
python3 /usr/local/lib/python3.5/dist-packages/pgadmin4/pgAdmin4.py

[Unit] Description=pgadmin Service After=network.target [Service] User=root ExecStart=/usr/bin/python3 /usr/local/lib/python3.5/dist-packages/pgadmin4/pgAdmin4.py [Install] WantedBy=multi-user.target

systemctl daemon-reload
systemctl start pgadmin
systemctl enable pgadmin

define admin user in postgresql to use from pgadmin

su - postgres
createuser --pwprompt --superuser admin

pgmodeler

* [[https://github.com/ksylvan/docker-pgmodeler/blob/master/Dockerfile|reference]]

PG_VERSION=0.9.1-alpha
# for building, can remove after install
sudo apt install make g++ qt5-qmake libxml2-dev libpq-dev pkg-config libqt5svg5-dev
# runtime dependencies
sudo apt install qt5-default libqt5svg5 postgresql-server-dev-*
cd /user/local/src
sudo apt install wget
wget https://github.com/pgmodeler/pgmodeler/archive/v${PG_VERSION}.tar.gz
sudo apt install qt5-default libqt5svg5 postgresql-server-dev-9.*
tar xvzf v${PG_VERSION}.tar.gz
cd pgmodeler-0.9.1-alpha/
qmake pgmodeler.pro
make
make install
cd ..
rm *.tar.gz
apt clean

if in container enable remote connection on host <file ini /etc/gdm3/custom.conf> [security] DisallowTCP=false

[xdmcp] Enable=true </code>

run pgmodeler in container

xhost +
lxc exec pgmodeler -- pgmodeler