PostgreSQL installation
Archlinux see also this guide:
sudo pacman -S postgresql
sudo -u postgres initdb --locale en_US.UTF-8 -E UTF8 -D '/var/lib/postgres/data'
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service
sudo zypper in postgresql postgresql-server postgresql-contrib
sudo dnf install postgresql-server postgresql-contrib
sudo systemctl enable postgresql
sudo postgresql-setup initdb
sudo systemctl start postgresql
sudo apt-get install postgresql postgresql-client
sudo apt-get install postgis \
postgresql-9.4-postgis \
postgresql-9.4-postgis-scripts \
postgresql-contrib-9.4
Create database and users
First create a postgresql user for your shell user account. The postgresql user will have superuser permissions:
sudo -u postgres createuser -s <myshellusername>
Now, from the shell, create a user that will be used for accessing your database, for example 'django' user. Choose a password:
createuser -P django
Then create a database owned by 'django' user:
createdb --encoding=UTF8 --owner=django <mydatabase>
An alternative way to create the database is by using the psql user with the PostgresSQL shell:
sudo -u postgres psql
CREATE DATABASE <mydatabase> OWNER django ENCODING 'utf8';
Check local connection for this new user:
psql -d <mydatabase> -U django -W
If it doesn't work it might be necessary to edit /var/lib/pgsql/data/pg_hba.conf:
host all all 127.0.0.1/32 md5
To add PostGIS support to the database:
sudo pacman -S postgis
psql <mydatabase>
mydatabase=# ALTER USER django WITH PASSWORD 'mypassword';
mydatabase=# CREATE EXTENSION postgis;
mydatabase=# CREATE EXTENSION postgis_topology;
To delete a database from shell account (be careful !):
dropdb <mydatabase>
To delete database from psql:
sudo -u postgres psql
DROP DATABASE <mydatabase>;
You can connect to psql using shell account because you must specify a database to connect to and the you can't delete it:
psql -d <mydatabase>
Restore a backup
To restore a PSQL backup you must delete the database first, then create an empty one and read the backup data to this empty database.
dropdb <mydatabase>
cat mybackup.psql | psql <mydatabase>
Create a backup
To create a PSQL backup:
pg_dump <mydatabase> > backup.psql
Virtual environment for Python
How to create a virtual environment with the necessary system development packages to be able to compile and install with pip the most usual python packages.
First install virtualenv and virtualenvwrapper:
Archlinux:
sudo pacman -S python-virtualenvwrapper
Debian:
sudo apt-get install python3-virtualenv virtualenvwrapper
sudo apt-get install build-essential python3.4-dev
source /usr/share/virtualenvwrapper/virtualenvwrapper.sh
Fedora:
sudo dnf install python-virtualenvwrapper python3-virtualenv
sudo dnf install rpm-build postgresql-devel
sudo dnf install libjpeg-turbo-devel zlib-devel
OpenSUSE:
sudo zypper in postgresql-contrib
sudo zypper in python3-devel gcc
sudo zypper in zlib-devel libjpeg8-devel
Now configure virtualenvwrapper. Edit ~/.zshrc or ~/.basrc:
export WORKON_HOME=$HOME/.virtualenvs
source /usr/bin/virtualenvwrapper.sh
# For Debian or Ubuntu use this instead:
# source /usr/share/virtualenvwrapper/virtualenvwrapper.sh
To create a virtualenv:
mkvirtualenv -p /usr/bin/python3 <myvirtenv>
pip install --upgrade pip
pip install ipdb
pip install psycopg2
If you have a requirements file (list of packages to install):
pip install -r requrements.txt