Install postgres 9.4
Add source
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main" >> /etc/apt/sources.list'
add key
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
update
sudo apt-get update
install
sudo apt-get install postgresql-9.4-postgis-2.1 pgadmin3 postgresql-contrib
Add user
# sudo su - postgres
$ createdb mydb
$ psql -s mydb
# create user someuser password 'somepassword';
# GRANT ALL PRIVILEGES ON DATABASE mydb TO someuser;
Enable PostGIS
#sudo su - postgres
#psql
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
To verify
\dx postgis*
Restore backup
psql database < file
Nice to know commands
mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;
mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';
mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';
Leave a Reply