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