mathz.nu Asterisk Blacklist Hobby webbhotell

2015/06/11

Postgres database dump

Filed under: Postgresql — Mathz @ 07:10

To export schema from postgres database use:

pg_dump -s databasename

2015/05/19

Postgres on Ubuntu 14.04

Filed under: Postgresql,Server — Mathz @ 15:26

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';

Powered by WordPress