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?
<br /> CREATE ROLE lbs WITH LOGIN PASSWORD 'mypassword' SUPERUSER INHERIT CREATEDB CREATEROLE;<br />

How do I change the password for a user/role?
<br /> ALTER ROLE lbs PASSWORD 'mynewpassword';<br />

How to provide/restrict access privileges to tables?
<br /> GRANT SELECT ON TABLE table TO user;<br /> REVOKE SELECT ON TABLE table FROM user;<br />

How to dump database in a text file?
<br /> pg_dump -U lbs -d cybersoftbjv1 -h 127.0.0.1 -W > cybersoftbjv1.sql<br />

How to dump database cleanly?
<br /> pg_dump -c -d -E UTF8 -h 127.0.0.1 -U lbs -W platform_v1 > platform_v1.sql<br />

How to rename a database?
<br /> ALTER DATABASE beijing_app RENAME TO beijing_app_20080801;<br />

How to update using two tables?
<br /> UPDATE road_for_update u<br /> SET the_geom = r.the_geom<br /> FROM roads r<br /> WHERE r.rd_id = u.rd_id;<br />

How to change a column type with Cast?
<br /> ALTER TABLE roads ALTER COLUMN class_new TYPE integer USING class_new::integer;<br />

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