mobile and gis dev notes

This is a quick-command list of Postres. If you want detailed instructions, please visit the Postgres Manual.

How do I Show all databases?
1. Using “psql -l”

2. Using

postgres=# \l
List of databases
Name       |  Owner   | Encoding
------------------+----------+----------
postgis          | postgres | UTF8
postgres         | postgres | UTF8
template0        | postgres | UTF8
template1        | postgres | UTF8
template_postgis | postgres | UTF8
(5 rows)

Note: Do not drop template databases if not necessary.

How do I run a script from the prompt?
psql -d cybersoftbj -u user -f myfile.sql

Its very useful in reloading user-defined functions.

How do I create a user/role?

CREATE ROLE lbs WITH LOGIN PASSWORD 'mypassword' SUPERUSER INHERIT CREATEDB CREATEROLE;


How do I change the password for a user/role?

ALTER ROLE lbs PASSWORD 'mynewpassword';


How to provide/restrict access privileges to tables?

GRANT SELECT ON TABLE table TO user;
REVOKE SELECT ON TABLE table FROM user;


How to dump database in a text file?

pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql


How to dump database cleanly?

pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql


How to rename a database?

ALTER DATABASE beijing_app RENAME TO beijing_app_20080801;


How to update using two tables?

UPDATE road_for_update u
SET the_geom = r.the_geom
FROM roads r
WHERE r.rd_id = u.rd_id;


How to change a column type with Cast?

ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;


How to add a geometry column to a table?
EXAMPLE: SELECT AddGeometryColumn(‘public’, ‘poi’, ‘the_geom’, 4326, ‘POINT’, 2)

Changing column names with spaces?
ALTER TABLE class_aroundme RENAME "level 1" TO level_1;

Setting kernel shmmax for postgres
sysctl -w kernel.shmmax=134217728
Note: For permanent changes see /etc/sysctl.cfg11. How to backup table(s) from pg_dump?
pg_dump poi_beijing -t class -t poi_class -f $BACKUPDIR/test_$MYDATE.sql