Tuesday, 10 December 2013

The posgresql and postgis install on Debian Wheezy

Recently, for working reason. I install the postgresql and postgis on Our test sever. Actually, it contains some troubles, so I record the process for make it easy next time.

1, add apt source file:
Create /etc/apt/sources.list.d/pgdg.list. The distributions are called codename-pgdg. In the example, replace squeeze with the actual distribution you are using:
deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main
(You may determine the codename of your distribution by running lsb_release -c.)
Import the repository key from https://www.postgresql.org/media/keys/ACCC4CF8.asc, update the package lists, and start installing packages:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
 
2,install postgresql
apt-get install postgresql-9.3 postgresql-client-9.3 postgresql-contrib-9.3 postgresql-server-dev-9.3
3, Change the default password of postgresql. The default user of postgresql is postgres, 
(1), root@somemachine:~# sudo -u postgres psql  
(2),  postgres=# ALTER USER postgres WITH PASSWORD 'postgres'; 
4, change linux password:
root@somemachine:~#sudo -u postgres passwd

5, change the postgresql configuration files, make it can accessible from remote client:
        root@server2-virtual-machine:~# vi /etc/postgresql/9.1/main/postgresql.conf

         1.Listen to all address access, modify connection permissions
          #listen_addresses = ‘localhost’ change to listen_addresses = ‘*’        
          2.
Enable password authentication
          #password_encryption = on  change to password_encryption = on
          3.the ip limitation of Users can access
           root@server2-virtual-machine:~# vi /etc/postgresql/9.1/main/pg_hba.conf,
 Add fellow consent:
       
# to allow your client visiting postgresql server
          host all all 0.0.0.0 0.0.0.0 md5
 or:
host    all             all             0.0.0.0/0            md5

6, change the encode of postgresql template:

root@somemachine:~# sudo -u postgres psql -U postgres
or
root@somemachine:$ psql -U postgres
psql (9.0.3)
Type "help" for help.
postgres=# update pg_database set datallowconn = TRUE where datname = 'template0';
UPDATE 1
postgres=# \c template0
You are now connected to database "template0".
template0=# update pg_database set datistemplate = FALSE where datname = 'template1';
UPDATE 1
template0=# drop database template1;
DROP DATABASE
template0=# create database template1 with template = template0 encoding = 'UTF8';
CREATE DATABASE
template0=# update pg_database set datistemplate = TRUE where datname = 'template1';
UPDATE 1
template0=# \c template1
You are now connected to database "template1".
template1=# update pg_database set datallowconn = FALSE where datname = 'template0';
UPDATE 1
7, create database:
1.create users
postgres=# create user “zhaofeng” with password ‘123456’ nocreatedb;            2.create database with special user           postgres=# create database “testdb” with owner=”zhaofeng”;             4.shell managment: root@somemachine:~# -u postgres createuser -D -P test1 root@somemachine:~# -u postgres createdb -O test1 db1
8,Install postgis 
apt-get install postgresql-9.3-postgis-2.1
9,enable postgis and postgresql extensions:
psql -d yourdatabase -c "CREATE EXTENSION postgis;"
psql -d yourdatabase -c "CREATE EXTENSION postgis_topology;"
psql -d yourdatabase -c "CREATE EXTENSION fuzzystrmatch;"
psql -d yourdatabase -c "CREATE EXTENSION postgis_tiger_geocoder;"
10 import sql data
postgres@wemet:/$ psql -d kct -f /opt/kct.sql

No comments:

Post a Comment