This is an old revision of the document!
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
- /etc/postgresql/9.5/main/postgresql.conf
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
- /etc/systemd/system/pgadmin.service
[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
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-all tar xvzf v${PG_VERSION}.tar.gz cd pgmodeler-0.9.1-alpha/ qmake pgmodeler.pro make make install sudo mkdir /usr/local/lib/pgmodeler/plugins 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