Initial Application of postgresql

Initial Application of postgresql

1. initialize the database: initdb-D/usr/local/pgsql/Data

2. Start the posgresql Database: postmaster-I-d/usr/local/pgsql/Data

3. Create a spatial database: createdb gisdb

4. Open the GIS database: Psql gisdb, and then you can perform SQL statement operations on the command line.

5. If you want to launch a command line, enter ";/Q ".

6. Stop the database: pg_ctl stop-D/usr/local/pgsql/Data

7. Import an SQL file: Psql-D gisdb-F test. SQL

8. Create a superuser: Create role test1 with superuser password '000000'

9. Describe a user or role: Drop role test1

10. You can enter/du in the command line to view all users, or query the role table to view: Select * From pg_roles

11. If you want to log on remotely, you must authorize the login permission: Alter role Test2 with login.

Remarks: BecausePostgreSQLThe default database encoding isUTF-8Encoding, so ifTelnetThe Chinese characters are displayed normally on. Bash_profileSet inLANG = zh_CN.GBKEncoding, which leads to inconsistent encoding. Therefore, the client-to-server encoding must be performed.$ PGSQL_HOME/data/postgresql. confClient_encoding = GBK(You need to restart the database)

 

Pgadmin installation Configuration

1. Download and install pgadmin3 for Windows

2. Set the remote connection account, "file"-"Add Server account"

3. Enter the database name, host address, port number, and user name in the dialog box.

4. Click "OK ".

5. Before connection, you must open the remote listening port on the server. in Linux, add access rules in/etc/sysconfig/iptables.

6. Modify the attributes in the pg_cmd.conf configuration file and grant the client access permission. For example, [host database name username IP address subnet mask permission]: host all 192.168.10.8 255.255.255.0 trust

7. Add a user Role test1 and grant it the SUPERUSER permission: create user test1 SUPERUSER;

8. If the permission is granted by default, the data cannot be viewed in pgAdmin. the following error occurs: permission denied for relation test1.

 

PostGis Configuration

1. Import the SQL file to the gisdb Database

# Cd $ POSTGIS_HOME/share

# Psql-d gisdb-f lwpostgis. SQL

# Psql-d gisdb-f lwpostgis_update. SQL

# Psql-d gisdb-f spatial_res_sys. SQL

2. Projection coordinate parameters placed in spatial_res_sys. SQL around the world